Comment by ojosilva
2 years ago
> First is that SQLite is simple. The database is a literal file on disk. The engine is a single executable.
No, it's not a single executable. There's no database executable as far as your app goes. There's no engine. The "engine" is a library, meant to be embedded into other code and may be concurrent and have many "executables" if you implement it that way or have different apps access the database.
Think of SQLite as an intricate file format for which your app will use an API (ie sqlite.c) to access.
It's also not just "a file on disk". I know this because I re-read the docs about locking last night: https://www.sqlite.org/lockingv3.html
Between this and WAL, which-ever one you pick, you have the following caveats:
- You aren't supposed to use a SQLite DB through a hard or soft link
- By default, in rollback journal mode, it will create a temporary `-journal` file during every write
- If you're doing atomic transactions on multiple DBs, it also creates a super-journal file
- You definitely can't `cp` a database file if it's in use, the copy may be corrupt
- It relies on POSIX locks to cooperate with other SQLite threads / processes, the docs advise that locks don't work right in many NFS implementations
- In WAL mode it needs a `-wal` and a `-shm` file, and I believe the use of shared memory makes it extra impossible to run it over NFS
SQLite is not simple, it's like a million lines of code. It is as simple as a DB can be while still implementing SQL and ACID without any network protocol.
I am thinking of writing a toy DB for fun and frankly I am not sure if I want to start with SQLite's locking and pager or just make an on-demand server architecture like `sccache` uses. Then the only lock I'd have to worry about is an exclusive lock on the whole DB file.
Yes. If you write your server in eg Go or Rust, you can have a “single executable deployment” so to say. But the main limitation is no horizontal scalability. So if you want web scale, you have to use something faster, like /dev/null.
Webscale always reminds me of this video [1] - MongoDB is webscale.
[1] - https://www.youtube.com/watch?v=b2F-DItXtZs
edit: and now I believe you are directly referencing this but may be useful for others who don't get the reference
> and now I believe you are directly referencing this
Yep haha.
> but may be useful for others who don't get the reference
Probably also yes. Important lore for techno-archeologists.
You might be interested in https://devnull-as-a-service.com/
There are ODBC drivers for SQLite. It can make sense when you want to offer SQLite as an option alongside other, more traditional DBMS. If your queries and data are relatively simple, it will probably work just fine.
ODBC drivers for SQLite are just a thin abstraction over the C library.