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

21–30 of 41 posts

Re: SQLite 2022 Recap

#21

Post by johnny22 »

justsomeuser wrote: post #14
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 u…

well it makes sense that the browser can't work outside of the opfs boundary, but shouldn't there be a way to talk to the opfs from outside the browser?

Re: SQLite 2022 Recap

#22

Post by wenus »

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

Nutting?

Re: SQLite 2022 Recap

#23

Post by streptomycin »

justsomeuser wrote: post #14
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 u…

The original plan from Google for the file system access API was to allow read/write of real files, but both Mozilla and Apple said that was too dangerous, and the OPFS was created as a compromise.

Yeah, it's going to be confusing for users when they want to actually want to use one of these files outside of the application that created it. But it's better than nothing!

Re: SQLite 2022 Recap

#24

Post by tenken »

mulmboy wrote: post #18
HelloNurse wrote: post #17

Earlier quoted context omitted.

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?

In the application layer

Re: SQLite 2022 Recap

#25

Post by freedomben »

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

I worked at a startup who did the whole "every table schema is "id, data" where data is a JSON blob with all the data." I loved it at first. For initial development, it felt so freeing and so productive.

Then one of the devs left and I inherited his code, and started to realize exactly how much the rigid schema helps make an app more robust and more maintainable. I will never, ever take that shortcut again. The production outages/downtime caused by database issues that never should have been an issue in the first place (like a corner case row that missing data? Spreading rampant defensive nil checks out across every function that checks any field of a record since any at any time might be inconsistent or nil) contributed to killing the company by pissing off customers.

Elixir/Phoenix does help a lot though because I still treat most rows like JSON objects, but under the hood it's a normal rigid postgres schema. Best of both worlds IMHO.

Re: SQLite 2022 Recap

#26

Post by breeze1990 »

mulmboy wrote: post #18
HelloNurse wrote: post #17

Earlier quoted context omitted.

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?

define schema with data class and you have tools like protobuf/thrift. And I find it really unnecessary to map every attribute to a SQL column.

Re: SQLite 2022 Recap

#27

Post by chris1993 »

wenus wrote: post #22
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

Nutting?

Thinking. British English idiom. Using your nut - your head.

Re: SQLite 2022 Recap

#28

Post by mulmboy »

wenus wrote: post #22
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

Nutting?

https://idioms.thefreedictionary.com/nutting+out
Post reply on HN