Gotchas with SQLite in Production
SQLite is popular for web apps but has limitations like single-threaded access, network challenges, and constraints in concurrency and transactions. Consider alternatives like MySQL for complex scenarios.
Read original article
SQLite is gaining popularity for production web applications due to its simplicity and suitability for many use cases. Despite its benefits, there are several "gotchas" to consider. Firstly, SQLite requires proper configuration for multi-threaded access, which may not be set up by default. Secondly, connecting to SQLite over a network can be challenging due to its single-file design. Additionally, SQLite's limitations in concurrency, transactions, backups, and migrations can pose issues for certain use cases. While SQLite is ideal for single-machine setups with low operational complexity, it may not be suitable for scenarios requiring multiple machines, heavy writes, or complex transactions. In such cases, traditional databases like MySQL or Postgres are recommended. Various projects aim to address SQLite's limitations, but caution is advised when considering them. Ultimately, understanding these "gotchas" will help in determining whether SQLite is the right choice for a specific web application.
Related
Graph-Based Ceramics
The article explores managing ceramic glazes in a kiln and creating an app for this purpose. It compares Firebase and Instant databases, Supabase, Postgres, and InstaQL for efficient data handling.
Graph-Based Ceramics
The article discusses managing ceramic glazes, introducing a custom app. It compares Firebase and Supabase databases, opting for Supabase's better data modeling. Instant database is explored for a hybrid solution.
First Contact with SQLite
The article explores surprising aspects of SQLite, like limitations in altering columns and data types. It notes the recent jsonb support and handling date/time values, praising SQLite's streaming features but favoring PostgreSQL.
3 commentsIt is not safe to use network file systems (NFS, SMB, or otherwise) in WAL mode, since they cannot access the shared memory.
There are a few more limitations that I can dig up.
https://sqlite.org/lang_attach.html
Transactions involving multiple attached databases are atomic, assuming that the main database is not ":memory:" and the journal_mode is not WAL. If the main database is ":memory:" or if the journal_mode is WAL, then transactions continue to be atomic within each individual database file. But if the host computer crashes in the middle of a COMMIT where two or more database files are updated, some of those files might get the changes where others might not.
When backing up a SQLite database that is in running use, you should never use `.backup`. That command will restart the backup from scratch every time the database is written to, so if you're backing up a big database with a lot of periodic writes the backup process may never complete.
You can sometimes use the C backup API from the same process that is writing to the database, since the C backup API is aware of writes _from the same process_. It will still have to restart the backup procedure if writes from another process interfere.
Related
Graph-Based Ceramics
The article explores managing ceramic glazes in a kiln and creating an app for this purpose. It compares Firebase and Instant databases, Supabase, Postgres, and InstaQL for efficient data handling.
Graph-Based Ceramics
The article discusses managing ceramic glazes, introducing a custom app. It compares Firebase and Supabase databases, opting for Supabase's better data modeling. Instant database is explored for a hybrid solution.
First Contact with SQLite
The article explores surprising aspects of SQLite, like limitations in altering columns and data types. It notes the recent jsonb support and handling date/time values, praising SQLite's streaming features but favoring PostgreSQL.