Support for SQLite Server Database Backend

[RickyGrassmuck]

Just got started setting up my own personal server instance and while it works well once everything is setup, having to run a full Postgresql database for my single user seems a bit overkill.

I did do some poking around through the codebase and it appears that the sqlx package used for the Postgres code also supports SQLite. If implemented, the server would be deployable as a single binary with no external dependencies which would be amazing for personal usecases!

I would have put forth a PR for this but unfortunately i'm not familiar with Rust at all and that learning curve is steep! If I find some free time one of these days I may take a stab at it.

[conradludgate]

We've designed our database code to be fairly flexible for the use cases their intended for. So it's certainly possible that we can switch out the database impls on the fly. I suppose the challenge then is which databases should we choose to maintain going forward.

I do like the idea of the sqlite on server for quick deployments. This plus #301 would make a good combo for one off setups

[RickyGrassmuck]

So I poked at it a bit earlier after looking at the sqlx crate documentation and it was surprisingly very little code change to switch to the crates Any module which basically acts as a wrapper around the other drivers and relies on the db connection string to determine what flavor of database it's using.

The hardest part was actually redoing the DB Migration files to make them compatible with SQLite and this likely would deter the use of the Any:: module. My guess is the easiest path would be to have a config option to select the database type you want to use and just maintain 2 different sets of migration files.

I haven't pushed any code (I'm positive that the changes I made to the migrations would actually break the Postgres database lol) but I did grab this short screen recording showing the custom build running and working with SQLite.

I can fork and push up a branch with the horrendous changes I made if you're interested in seeing the Any driver and changes needed for the db migrations. Just let me know and i'll get that pushed up and link to it here.

Edit: if the screen recording linked above doesn't work, I uploaded it to vimeo as well https://vimeo.com/703970244

[ellie]

Oh awesome, that's fantastic! We don't use anything particularly Postgres specific and I doubt we will do - beyond indexes and the like, but that will all be in migrations.

I'd love to review a PR for this if you have the time to push something up! Thank you!

[RickyGrassmuck]

@ellie Just opened #414 for you to look at, it definitely needs some changes around the configuration side of things lol.

I started to poke at some various ways of handling the configuration but my complete lack of Rust knowledge was resulting in me fighting the language more than anything so I decided to push what I had working for you to look at.

We don't use anything particularly Postgres specific and I doubt we will do - beyond indexes and the like, but that will all be in migrations.

Yeah, I ended up just splitting out the migrations and just run the ones necessary for the specific db type that's specified in the configuration. This has the negative side effect of not being backwards compatible with previous server config files as it adds a new option (db_type) that needs to be set but I figured I'd leave that for discussion in the PR for the best way forward on that front.

[ellie]

That's fantastic, thank you! I'll give it a look over after work

Tbh I'd just suggest defaulting the db_type to be postgres, that way things will carry on as they always have without breaking any existing configs

[conradludgate]

The core functionality required is almost ready in #1036. I have added unofficial sqlite server support in https://github.com/conradludgate/atuin-server-sqlite. @ellie and I decided recently that we don't intend to have sqlite support in tree but are happy for others to support their own backends themselves