Needs investigation

From here

Need to investigate the truth of the following:

• Concurrency - do we need this? here
• Foreign key checking (and cascading deletion for that matter) are turned off by default. You need to enable them using PRAGMA foreign_keys = ON;
• There are practically no downsides (and a number of upsides) to using the WAL journalling mode (at least for "use a local database and store it on the disk" use cases). The main one being that reads will conflict with writes if you don't enable it! (which is a problem in a multi-threaded environment) Unfortunately, that's another feature you must remember to enable: PRAGMA journal_mode = WAL; (for obvious reasons, this one "stays enabled" after you turn it on).
• Full auto-vacuum cannot be enabled after you start writing to the database unless you enabled incremental auto-vacuum. If you're unsure, it's a good idea to enable incremental auto-vacuum to keep that option open. But, here again, that's not the default: you need PRAGMA auto_vacuum = INCREMENTAL.

Implementation

• We need to find a way to detect primary keys and foreign keys and store them in a structure in the app's memory. This will need to happen every time that the application loads and not on every request, as it will slow down the request significantly.
• If we support creating and altering tables in the future, then we'll have to repopulate the structure.

parser

• We need a way to parse the create table statements of the sqlite file from the sqlite_master table. We can do this using the parser.y of the latest sqlite, or this repo sqlite-createtable-parser. Unfortunately, both are going to output code in C, so we need to find a way to translate that to python, or find an interop solution.