Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

SQLite 2022 Recap

sqlite.org

Post reply on HN
Print view View original

11–20 of 41 posts

Re: SQLite 2022 Recap

#11

Post by samwillis »

NeutralForest wrote: post #3
hiAndrewQuinn wrote: post #2

The stable release of the WebAssembly and JavaScript APIs strike me as particularly monumental. Long live SQLite! https://sqlite.org/wasm/doc/trunk/index.md

I don't understand the use case? From the docs [ https://sqlite.org/wasm/doc/trunk/demo-123.md ], the database disappears on page reload on top of requiring using workers for longer running processes.

The intention is to make use of the new "Origin privet file system" api, this provides the website with a sandboxed block level file system on the users device that can be used for efficient access and writes. It will be possible for WASM SQLite, or any other DB engine ported to WASM, to have full ACID compliance.

The SQLite team have been working with browser developers to ensure the new API is sufficient to enable all this.

Honestly, and I keep going on about it, SQLite in the browser via WASM is the missing piece to make "offline first" PWAs a serious contender when deciding an architecture for an app.

2023 is going to be the year of SQLite in the browser.

https://webkit.org/blog/12257/the-file-system-access-api-wit...

https://sqlite.org/wasm/doc/trunk/persistence.md#opfs

https://chromestatus.com/feature/5702777582911488

Re: SQLite 2022 Recap

#12

Post by NeutralForest »

samwillis wrote: post #11
NeutralForest wrote: post #3

Earlier quoted context omitted.

I don't understand the use case? From the docs [ https://sqlite.org/wasm/doc/trunk/demo-123.md ], the database disappears on page reload on top of requiring using workers for longer running processes.

The intention is to make use of the new "Origin privet file system" api, this provides the website with a sandboxed block level file system on the users device that can be used for efficient access and writes. It will be possible for WASM SQLite, or any other DB engine ported to WASM, to have full ACID compliance. The SQLite team have been working with browser developers to ensure the new API is sufficient to enable…

Very interesting, seems like I have a lot of reading to do, thanks!

Re: SQLite 2022 Recap

#13

Post by mulmboy »

PreInternet01 wrote: post #4

A pretty boring and stable year in SQLite land, which is just how I like it. The JSON -> and ->> operators introduced in 3.38 are my personal favorites, as these really help in implementing my "ship early, then maybe iterate as you're starting to understand what you're doing" philosophy: 1. In the initial app.sqlite database, have a few tables, all with just a `TEXT` field containing serialized JSON, mapping 1:1 to i…

As a counterpoint I find that nutting out the schema upfront is an incredibly helpful process to define the functionality of the app. Once you have a strict schema that models the application well nearly everything else just falls into place. Strong foundation

Re: SQLite 2022 Recap

#14

Post by justsomeuser »

hiAndrewQuinn wrote: post #2

The stable release of the WebAssembly and JavaScript APIs strike me as particularly monumental. Long live SQLite! https://sqlite.org/wasm/doc/trunk/index.md

It would be nice if OPFS (Origin Private File System) allowed reading/writing to an actual SQLite file on the users disk.

At the moment, as I understand it, the OPFS virtual disk is completely isolated from the users disk.

This means you cannot just lightly query a 1GB file without first copying the 1GB from the users filesystem to the OPFS.

Any writes mean you must then copy the 1GB SQLite db file from OPFS to the users local filesystem too.

Is this correct?

Re: SQLite 2022 Recap

#15

Post by nonethewiser »

PreInternet01 wrote: post #4

A pretty boring and stable year in SQLite land, which is just how I like it. The JSON -> and ->> operators introduced in 3.38 are my personal favorites, as these really help in implementing my "ship early, then maybe iterate as you're starting to understand what you're doing" philosophy: 1. In the initial app.sqlite database, have a few tables, all with just a `TEXT` field containing serialized JSON, mapping 1:1 to i…

This seems awful but maybe I'm underestimating the number of failed apps you're dealing with. And at that rate, maybe the app ideas should be vetted a bit more.

Re: SQLite 2022 Recap

#16

Post by di456 »

PreInternet01 wrote: post #4

A pretty boring and stable year in SQLite land, which is just how I like it. The JSON -> and ->> operators introduced in 3.38 are my personal favorites, as these really help in implementing my "ship early, then maybe iterate as you're starting to understand what you're doing" philosophy: 1. In the initial app.sqlite database, have a few tables, all with just a `TEXT` field containing serialized JSON, mapping 1:1 to i…

Thanks for this explanation.

I've been hacking together one-off python scripts to parse out bits of saved API responses, flatten the data to csv's, and load it to SQLite tables. Looks like I can skip all of this and go straight to querying the raw JSON text.

Re: SQLite 2022 Recap

#17

Post by HelloNurse »

mulmboy wrote: post #13
PreInternet01 wrote: post #4

A pretty boring and stable year in SQLite land, which is just how I like it. The JSON -> and ->> operators introduced in 3.38 are my personal favorites, as these really help in implementing my "ship early, then maybe iterate as you're starting to understand what you're doing" philosophy: 1. In the initial app.sqlite database, have a few tables, all with just a `TEXT` field containing serialized JSON, mapping 1:1 to i…

As a counterpoint I find that nutting out the schema upfront is an incredibly helpful process to define the functionality of the app. Once you have a strict schema that models the application well nearly everything else just falls into place. Strong foundation

With JSON blobs in simple tables a very large part of designing a database schema becomes designing a JSON schema, with different opportunities to make mistakes but (potentially) the same strictness.

Re: SQLite 2022 Recap

#18

Post by mulmboy »

HelloNurse wrote: post #17
mulmboy wrote: post #13

Earlier quoted context omitted.

As a counterpoint I find that nutting out the schema upfront is an incredibly helpful process to define the functionality of the app. Once you have a strict schema that models the application well nearly everything else just falls into place. Strong foundation

With JSON blobs in simple tables a very large part of designing a database schema becomes designing a JSON schema, with different opportunities to make mistakes but (potentially) the same strictness.

How do you enforce the JSON schema?

Re: SQLite 2022 Recap

#19

Post by ec109685 »

samwillis wrote: post #11
NeutralForest wrote: post #3

Earlier quoted context omitted.

I don't understand the use case? From the docs [ https://sqlite.org/wasm/doc/trunk/demo-123.md ], the database disappears on page reload on top of requiring using workers for longer running processes.

The intention is to make use of the new "Origin privet file system" api, this provides the website with a sandboxed block level file system on the users device that can be used for efficient access and writes. It will be possible for WASM SQLite, or any other DB engine ported to WASM, to have full ACID compliance. The SQLite team have been working with browser developers to ensure the new API is sufficient to enable…

Unfortunately unlike apps, the system doesn’t support backing up local storage managed by the browser, so if you get a new phone, you lose your data.
Post reply on HN