Switched ScreenJournal to ncruces/go-sqlite3

[mtlynch]

I just completed my first migration to ncruces/go-sqlite3. It's for my app ScreenJournal, an open-source TV/movie reviewing app:

https://github.com/mtlynch/screenjournal

Here's the actual migration:

mtlynch/screenjournal#413

It was interesting migrating over because I didn't realize how permissive the mattn driver is, so it doesn't seem to enforce foreign key constraints by default or enforce any kind of type checking. It was great to be able to take advantage of newer features of sqlite like strict tables and CHECK assertions, but it meant a bit of cleanup for my tests.

Thanks again for all of your work on this package!

[ncruces]

Thanks for the feedback!

Yeah, since it's new software, I opted to disable deprecated (mis)features, and enable by default additional things that SQLite only doesn't because of strict backwards compatibility concerns.

I'm glad you support the decision, but for the benefit of others who might have more trouble with these choices…

Many of these decisions can be reverted if necessary, and again, it should be easy to build SQLite with your own configuration; certainly much easier than building the modernc version from source.

One example of this is the bcw2 module which enables additional (some experimental) SQLite features.

[mtlynch]

So far, your defaults feel very sensible. It was some extra work to make my tests work with SQLite's constraint checks, but if I didn't want those checks, I could just write SQL without them.

I feel silly that all these years I've been using SQLite and thought that type and foreign key constraints were doing something, but I wasn't actually getting any benefit from them until I switched to your implementation.

[ncruces]

Great! Again, for the benefit of others…

Foreign key constraints can be enabled in mattn using the _fk query parameter, and in modernc using the _pragma=foreign_keys(on).

Check constraints I think are enabled by default in all drivers and strict tables can be used on all of them.

So the major difference in default behavior is foreign keys.