I haven't used it at all.
Do you host it yourself? What about backup, replication, and availability?
back
2 comments
Your web server can easily host SQLite. It’s the only mainstream database that doesn’t need a server. It’s actually just a single file of C code, and most languages have an api binding to it so you don’t have to set anything up besides giving it a file to write to!
Backup, yes you can just copy the file to back it up, but SQLite documentation has a preferred method that uses its own backup api which is likely safer than a file copy.
Replication, I don’t think there is a way to keep multiple DB’s in sync. You will likely need to move to a client server DB like Postgres if you get to that point.
Availability, only allows one writer at a time, so that’s the biggest issue for performance. The docs say it’s used on websites that get 100k’s or hits a day, but likely not great for workloads that have a high amount of writes. You obviously will be constrained by how fast the servers CPU and I/O is.
The author of SQLite says for blobs of about 20kb or less, it’s faster than reading and writing to the file system due to the overhead of opening a file.
While I’m not sure it can be considered ‘mainstream’, Firebird (www.firebirdsql.org) is well-established and can run with a server process as well as without one.
I can totally vouch for SQLite in this case, however.
An sqlite database is a single file. So backing it up is as easy as copying that single file to an archive directory.
Essentially true, but you need to make sure the file will be in a consistent state. Rather than locking it, it’s better to use the Backup API or VACUUM INTO command.