SQLite connection starts without foreign key enforcement

[Yagich]

SQLite's enforcement of foreign key constraints is disabled by default. It can be enabled with a PRAGMA foreign_keys = ON statement.

While it can be manually called in user code, it's not clear where the best place for it is. In my app, I did it in app.lua, in the outermost scope. That makes the statement run seemingly every time app is required by something, and I'm not sure if it matters. I wasn't able to find any way to add a db connection hook or something like that.

Would it be possible to add that query when establishing the connection on Lapis' side? If not, is there some function that is run on app init, after the connection was established?

[mjstephan]

The only way to really fix this is by changing the code in sqlite.lua in lapis/db/sqlite.lua. If you try to fix this outside of connect function, you would need to issue PRAGMA statements for every single query, otherwise if the connection is closed (if connect is called again) the pragma statements would no longer be in effect.

Originally I made my own module that returns a connection from lapis.db with the pragma commands already having been applied to the connection (besides foreign_keys, I also find it very important to be able to set busy_timeout). But the proper solutions is to set these pragma statements when the connection is created.

The other issue is the fact that right now there is no transaction support for SQLite. In another issue about transactions, the solutions was to create multiple sql statements wrapping the statements in a BEGIN TRANSACTION and COMMIT. This might work for PostgreSQL and MySQL, but for SQLite the query function uses lsqlite3 db:nrows method, which only allows a single statement. This was talked about here: https://github.com/leafo/lapis/issues/732#issuecomment-1352309164

In order to fix these issue, I modified lapis/db/sqlite.lua and added some extra helper methods:

Here is the commit message describing the changes I made to sqlite.lua in my own repository:

Added functionality for setting SQLite pragma commands needed for all connections (foreign_keys, busy_timeout) in connect function. To use these config file would need to have a connection_pragma_defaults keys like this (key is pragma command, value is pragma value):

sqlite = {
    database = "db/sqlite_database.db",
    connection_pragma_defaults = {
      foreign_keys = true,
      busy_timeout = 5000
    }

Also added the following functions exec -allowing the use of lsqlite3 db:exec function, which allows multiple statements and transactions exec_scalar - utility function for easily retrieving a single value prepare - allowing the use of lsqlite3 db:prepare for prepared statements

For instance, if you have the config file setup like the example above:

-- use my own version of sqlite.lua
local db = require("services.db.sqlite")
local timeout = db.exec_scalar("PRAGMA busy_timeout;")
print(timeout) -- 5000

local foreign_keys = db.exec_scalar("PRAGMA foreign_keys;")
print(foreign_keys) -- 1

My repository with the changed sqlite.lua is here: sqlite.lua

I would like to submit this change to Lapis, but I'm not currently familiar with MoonScript.

Maybe if someone else here can help me create the changes needed to sqlite.moon, then we can create a pull request and get this functionality put into Lapis.