SQLite (Experimental)

Available since v0.4.0

Use a SQLite Database

local sqlite = require "sqlite"

open(path)

Opens a SQLite database. The path can be a file or :memory: for an in-memory database.

local db = sqlite.open(":memory:")
db:exec [[
  create table msg (
    id text primary key
  )
]]

exec(sql, params)

Executes an SQL statement and returns the resulting status. Use ? placeholders in the SQL statement and provide the values in the params table.

local id = uuid()
local res = db:exec("insert into msg (id) values (?)", {id})
-- res = {
--   rows_affected = 1,
--   last_insert_id = 1
-- }

query(sql, params)

Executes an SQL statement and returns the resulting rows. Use ? placeholders in the SQL statement and provide the values in the params table.

local rows = db:query("select count(*) count from msg")
-- rows = {
--   {
--     count = 1
--   }
-- }

sqlite.null

SQL NULL values can be represented in Lua via the sqlite.null constant.

db:exec("insert into tbl (name) values (?)", {db.null})
-- Translates to the following SQL:
-- insert into tbl (name) values (NULL)

Notes on concurrency

NOTE: The SQLite package does not presently support transactions.

The SQLite package is not presently threadsafe. It is advised to use a mutex to synchronize access to the database.

local mutex = require "mutex"
local dblock = mutex()

local function query(sql, params)
    return dblock(function()
        return db:query(sql, params)
    end)
end