Migrate DB to SQLite

[martpie]

I would like to migrate our DB (again!) to SQLite. Possible before the final release for Museeks Tauri.

SQLite would allow us to fully separate the back-end from the storage layer, as the back-end could be re-written in something else. Also, it would mean people could query the database themselves, etc.

I had a look at many crates to handle SQLite, from diesel to sea_orm to raw sqlite, and I was satisfied with none:

• diesel: codegen is ok, but platform-specific codegen is not.
• sea_orm: omg the doc is insanely unclear, especially with SQLite
• sqlx: learning another query language... meh, but why not
• tauri-plugin-sql: probably the simplest setup, uses sqlx under the hood

If anyone has experience with SQLite and rust, please wave :)

This is all assuming performances are ok.

[igorer88]

I was checking sqlx and it's like pg for Js. We could create a class based on it and then make the configuration so we just need to import and use it. But the boilerplate is quite big and when the queries get bigger it doesn't get any easier.

Would be nice something like sqlc, that generates code while we write plain old sql still. But I don't know if it works with rust.

SQLite doesn't get corrupt? There should be a way to check the DB status and then be able to restore it from backup.

[martpie]

I really like the level of abstraction Sequelite provides (https://github.com/siddiqus/sequelite), but I played a bit with it and it's a bit immature (all ints need to be signed, no support for JSON field, etc.

Going for a more complex solution like an ORM like seaorm, diesel is also feasible, but it's quite heavy for the simplicity of Museeks' DB.

Regarding corruption, we should be fine, it depends on the Pragma we use with SQLite iiuc.

[martpie]

So far, I'm playing with Rusqlite (basically like sqlx, but a bit more simple and more tailored for SQLite). In the end, using raw sql for now is ok, and switching to something higher level later would be a cherry on top.

[igorer88]

Check this Prisma. I don't like that it isn't an official pacakge but It could be an option.

[martpie]

Yep, I looked at it and it looks great on paper, unfortunately, it does not seem maintained: https://github.com/Brendonovich/prisma-client-rust/issues/468#issuecomment-2396706158

Maybe it's stable enough to use though.

Ah, and this is an issue imho:

The generated client must not be checked into source control. It cannot be transferred between devices or operating systems. You will need to re-generate it wherever you build your project. If using git, add it to your .gitignore file.

[martpie]

• https://crates.io/crates/arel
• https://crates.io/crates/ormlite
• https://crates.io/crates/sqlb

are interesting options

[uklotzde]

Diesel is broadly used, actively maintained, and has stood the test of time. Platform-agnostic when enabling the "bundled" feature of libsqlite3-sys.

With Rusqlite you have to manage migrations yourself.

[igorer88]

Well, I liked ormlite the most. Looks good and easy to query for new contributors.

[martpie]

I really like ormlite too, I'll need to check two things though: run migrations on startup, and connect to a local DB without an env car (or set the env car on startup)

[igorer88]

Will it run on memory or on a file in disk?

[martpie]

It needs to be persisted, so on disk.

[martpie]

Boom. Everything should be working as before. I'll test things more extensively in the next days.

It should be possible to unit test all the DB helpers, which would be great to add.