SQLite as the default server database

[ellie]

Ever since Atuin first released, we have had requests to support SQLite on the server.

For a very long time, I have resisted this. SQLite might work for the average self-hoster (my assumption is that most people self host just for themselves, or perhaps for them and a couple of friends), but it does not work for api.atuin.sh - at time of writing, we are approaching 2500 users and are growing pretty rapidly. I did not want to test each release for two database backends.

@conradludgate implemented an "unofficial" SQLite server over here, which somewhat solves the problem. Lately I've been thinking about ensuring api.atuin.sh continues to scale well, but this may involve making more changes that make self hosting awkward.

So the proposal here is to switch this around. The in-tree database implementation should be SQLite, and we can maintain a separate implementation for api.atuin.sh. This should ensure we are free to handle scaling issues, but also means that the average self-hoster can very easily get started. Integration tests are much easier to write with sqlite, as we can just run against :memory:.

What I would appreciate is input from anyone self hosting

1. How many users do you host a server for?
2. Would SQLite be a "better" or "easier" solution for you?
3. Would you be ok with some manual steps to migrate from postgres -> sqlite in a possible v17?
4. Any other thoughts?

I'm also interested in hearing from anyone hosting Atuin for their company, and what we can do to support you better.

See: #1107, #356, etc

[sberrevoets]

I tried setting up self-hosting on a raspberry pi the other day, and the dependency on Postgres 14 was a showstopper since that wasn't available as a pre-compiled package so I had to compile it myself which was too much work.

I looked at the SQLite option but since it wasn't official I didn't want to rely on it.

I currently don't sync at all, but an RPi + SQLite setup for me personally would be great :) Would be just for me at home.

[ellie]

Ok perfect, thank you!

dependency on Postgres 14 was a showstopper since that wasn't available as a pre-compiled package so I had to compile it myself which was too much work.

Sounds like docker would also be a dealbreaker?

[sberrevoets]

Yeah though probably not for a good reason as I don't have a ton of docker experience. But removing/not introducing another dependency seems like a win either way assuming it doesn't incur a ton of maintenance cost on atuin itself.

[CygnusHyoga]

What I would appreciate is input from anyone self hosting

How many users do you host a server for?

6 servers / workstation currently...more to come when I can installed in more devices. 1 user, me :-)

Would SQLite be a "better" or "easier" solution for you?

No. I think SQLite it's good for low resources devices, and for test the software as an optionally alternative...I prefer PostgreSQL or Mysql/MariaDB...I don't trust SQLite for example in nfs pvc in k8s cluster, sometimes works great, sometimes gave corruption error.

Would you be ok with some manual steps to migrate from postgres -> sqlite in a possible v17?

I think as an alternative solution, make them both available, for people to have freedom to choose, what solution is best for their uses cases.

Any other thoughts?

If SQLite will be default, some people will want PostgreSQL or Mysql/MariaDB alternative (like me) :-( In other language of programming (like java) do you have a database abstraction, it's will easy to maintain different alternatives (I don't know in rust) Maybe some library like this one: https://docs.rs/quaint/latest/quaint/

[ellie]

Awesome, thanks for the feedback! NFS is pretty much the only filesystem setup SQLite will have issues with, generally you'll be fine with it.

We have considered maintaining a database abstraction within Atuin, but realistically we're not willing to ship software that has not been thoroughly tested in all modes of deployment - supporting a whole bunch of databases adds an unrealistic testing load to every deployment. Plus it means we can't optimise things to a specific SQL flavour

(and I kinda hate ORMs...)

[theesm]

Would SQLite be a "better" or "easier" solution for you?

Neither. I personally and generally favor Postgresql over SQLite for scaleability reasons, but these don't apply for my single-user instance. Considering the assumption that most atuin server instances may be rather small and most will probably be run by hobbyist, I do however see how sqlite would be beneficial for said crowd and simplify deployment.

Any other thoughts?

I think api.atuin.sh is probably the largest instance & that more people will default to using that instead to self-host something; and those that do self-host most likely wont have to deal with the scalability/performance considerations api.atuin.sh has to deal with if it continues growing. So:

The in-tree database implementation should be SQLite, and we can maintain a separate implementation for api.atuin.sh.

seems like the way to go (I say this from a generally postgresql-favorable and more often than not sqlite sceptical point of view).

[ellie]

Considering the assumption that most atuin server instances may be rather small and most will probably be run by hobbyist

This is generally the assumption I make, but as we don't track anything at all it's hard to know without asking haha

seems like the way to go

Ideal, thanks for the input!

[mattgodbolt]

How many users do you host a server for?

Around a dozen

Would SQLite be a "better" or "easier" solution for you?

Not in our case: we run the atuin server on nomad (k8s-ish thing) as a cluster behind a load balancer, so having the server be totally stateless is important. Persistence comes from an existing database, and our atuin processes can come and go as they please.

Would you be ok with some manual steps to migrate from postgres -> sqlite in a possible v17?

So long as we can choose postgres we would continue to do so.

Any other thoughts?

Great idea to make this more accessible, so long as we can also pick postgres or some other remote database too.

[ellie]

Around a dozen

Still the biggest self-hosted deployment I've heard about!

Not in our case: we run the atuin server on nomad (k8s-ish thing) as a cluster behind a load balancer, so having the server be totally stateless is important. Persistence comes from an existing database, and our atuin processes can come and go as they please.

Good to know, thanks for sharing 🙏

So long as we can choose postgres we would continue to do so.

Always - I don't plan on taking away things that users are already able to do, just make self hosting a bit more accessible + reduce the ops-y questions we get for deployments that really don't need to be that complicated.

[nebkor]

I swore I checked the issues for something like this one today but didn't see it :) I also literally just discovered Atuin today, after watching the Chris Biscardi video.

How many users do you host a server for?

I just started self-hosting, with one user (me) and two machines.

Would SQLite be a "better" or "easier" solution for you?

Yes, I prefer to not run Docker or Postgres.

Would you be ok with some manual steps to migrate from postgres -> sqlite in a possible v17?

Yep! I mean, I just spent the day adding the ability to do this (similar to conrad's schema changes, but more comprehensive, and with a working server), so I figure getting my data to v17 from my current db won't be harder than that.

Any other thoughts?

I noted that the records table is using UUIDv7s. If you used Julids, you could ditch the created_at field for the records table, have the Serde and SQLx traits already done, generate the IDs inside the database on insert, etc. Jus' sayin' :)

[ellie]

I just started self-hosting, with one user (me) and two machines.

Ok awesome, sqlite does make a lot of sense for you then

I noted that the records table is using UUIDv7s. If you used Julids, you could ditch the created_at field for the records table, have the Serde and SQLx traits already done, generate the IDs inside the database on insert, etc. Jus' sayin' :)

I'd rather have a small library/chunk of code in the application that follows a more official standard than load a third party database extension that may or may not work well across platforms, but thank you for the suggestion!

[nebkor]

Also, I get more than 20k insertions/sec with NORMAL durability and WAL mode with my other sqlite-backed, Axum-based server, with a lot more complicated triggers and indices (write amplification) than the Atuin tables, on my regular desktop PC with nvme drives. I fully believe that Postgres can be necessary, especially if you can't scale vertically anymore, but I don't think many people appreciate how far sqlite can take them.

[ellie]

The concerns for the server here are more to do with writer concurrency and HA, which SQLite tends to fall short on. It's getting better at the latter now with all the WAL streaming stuff, but postgres has been doing that for far longer

SQLite does do super well at high performance single-writer workloads though, and usually does outperform database servers in that regard. Hard to beat a network call vs embedded process.

[evanpurkhiser]

Thank you for considering this! This is very exciting!!

---

How many users do you host a server for?

Single user

Would SQLite be a "better" or "easier" solution for you?

For me absolutely. I have a small little server at home that I run a few personal things via podman containers: like Home Assistant and nginx. I've really wanted to use a self hosted Atuin instance and would LOVE for it to just be a single podman container. I've started wanting it bad enough however that I'll probably work out how to get a simple postgres docker image.

Would you be ok with some manual steps to migrate from postgres -> sqlite in a possible v17?

Absolutely, no problem

[ellie]

Thank you for considering this! This is very exciting!!

Glad you think so! 😊

I've really wanted to use a self hosted Atuin instance and would LOVE for it to just be a single podman container. I've started wanting it bad enough however that I'll probably work out how to get a simple postgres docker image.

Ok ideal, that's pretty much exactly what I imagine the setup is for the average self-hoster

Absolutely, no problem

🙏

[Zorlin]

Thank you for making very careful and considered choices when it comes to how you affect your users, and especially for taking care of selfhosters.

I support the move to SQLite and think it's a good "default", as long as I still have the option of moving back to PostgreSQL if hosting a huge instance for a lot of people.

I think for most selfhosters, optimising towards "one" user makes sense than optimising for 2500.

[ellie]

Thank you for making very careful and considered choices when it comes to how you affect your users, and especially for taking care of selfhosters.

No worries! I feel like our self hosted setup still needs some work, so I'd like to make that easier + smoother rather than make it harder as a side effect of scaling up api.atuin.sh

I support the move to SQLite and think it's a good "default", as long as I still have the option of moving back to PostgreSQL if hosting a huge instance for a lot of people.

Fantastic! Yep PostgreSQL (or whatever database api.atuin.sh uses in the future) will always be an option, but it just makes sense to opt in to a more awkward setup than opt out

I think for most selfhosters, optimising towards "one" user makes sense than optimising for 2500.

Ideal - it's sounding like this is the direction I'll be going in. Thank you for your input!

[rigrig]

How many users do you host a server for?

Just myself

Would SQLite be a "better" or "easier" solution for you?

Not really anymore I guess: I'm running Postgres for other services anyway, and the account setup is already done.
If I hadn't been running Postgres yet, I probably wouldn't have bothered setting it up at all though.

Would you be ok with some manual steps to migrate from postgres -> sqlite in a possible v17?

Sure. (Or even with just recreating the server altogether: reconnecting+syncing all clients would restore everything, right?)

Any other thoughts?

SQLite would feel more comfortable, just because there are less moving parts. Having a simple SQLite file as backup also feels saver than a PostgreSQL database dump.
But you need Postgres support anyway, so adding SQLite would use a bunch of effort which might be spent better elsewhere, and having the single most-used server running different code from everybody else doesn't seem great either.

[gmemstr]

Just chipping in here as a contributor to the SQLite backend

How many users do you host a server for?

Just myself at the moment

Would SQLite be a "better" or "easier" solution for you?

Yes, because I don't currently have PostgreSQL running anywhere but I do have https://litestream.io/ setup with several other services for SQLite disaster recovery, so the database is constantly streamed to S3. While standing up a PostgreSQL database isn't tricky, Atuin would be the only thing using it.

Would you be ok with some manual steps to migrate from postgres -> sqlite in a possible v17?

Not really applicable since I already use the SQLite backend :)

Any other thoughts?

I'm personally a huge fan of SQLite and many of my self hosted services run on a small Kubernetes cluster made of 2 Raspberry Pi 4Bs, so it's very nice not having to run a separate server for the database portion of applications to free up compute. Layering Litestream on top gives me relative peace of mind that I won't experience catastrophic data loss.

That said, I have noticed that Atuin's Postgres migrations do take more advantage of PostgreSQL datatypes that SQLite simply doesn't have. For the most part I haven't see that as an issue but is worth keeping in mind.

[davidolrik]

How many users do you host a server for?

Just me currently

Would SQLite be a "better" or "easier" solution for you?

No, I prefer Postgres - and already have an instance running.

Would you be ok with some manual steps to migrate from postgres -> sqlite in a possible v17?

Sure - as long as I get to keep using Postgres =)

[clonejo]

How many users do you host a server for?

None yet. The postgres requirement stopped me from setting up sync altogether.

Would SQLite be a "better" or "easier" solution for you?

Absolutely!

Would you be ok with some manual steps to migrate from postgres -> sqlite in a possible v17?

Not syncing yet, so not an issue.

Any other thoughts?

The prospect of learning how to administrate and secure a postgres server has put me off sync so far. While sqlite would be a perfect solution for me, a fully contained docker container would also be very appreciated. Ie. a container that already includes the postgres setup. This could result in some tricky issue reports for you, though, eg. when postgres major version migration has gone wrong for some reason.

[ellie]

Thanks for your input!

a fully contained docker container would also be very appreciated. Ie. a container that already includes the postgres setup.

We have one of those here: https://docs.atuin.sh/self-hosting/docker/

The prospect of learning how to administrate and secure a postgres server has put me off sync so far.

For what it's worth, it's not much more than securing sqlite + Atuin would be. Ensure nobody but the required users can access the database files, and ensure only the intended users can access it over the network.

So far I've held off on sqlite as there's a very large number of postgres users, and I'd rather avoid supporting both

[clonejo]

So far I've held off on sqlite as there's a very large number of postgres users, and I'd rather avoid supporting both

Yes, that's a wise decision.

[lunik1]

1. Just me
2. In a vacuum yes, SQLite would be easier. However, I am hosting other services that require postgres, so it's not much of an inconvenience in practice.
3. Yes, although I'm not sure I would migrate immediately (if at all). Would this migration path work in future versions?
4. Once/if it's implemented, some guidance of the performance tradeoffs would be useful at various user scales (although I'm not sure if there are any/many "medium sized" instances).

[Sierra1011]

For myself:

1. Single user, a handful of client machines
2. SQLite would not be easier (but this is because my atuinsh deployment is stateless in k3s with database state in an external Postgres instance)
3. Having a migration pathway between database types can never be a bad thing when it's between two popular options; as others have said SQLite is popular for many. However if that comes at significant dev burden it's worth thinking about how it as a feature balances against other work.
4. I don't think it's unfair to say that, initially, users that self host are likely to have the technical chops to make this work without embedded SQLite. That said, broader compatibility increases uptake, so 🤷