LuaJIT FFI SQLite C Interface Demo

sqlite.lua

local sql = arg[1]
if not sql then
    print(('usage: %s SQL'):format(arg[0]))
    os.exit()
end

local ffi = require('ffiex')

local lib = ffi.load('sqlite3')

ffi.cdef[[
    # include "sqlite3.h"
]]

local dbh_ptr = ffi.new('sqlite3 *[1]')
assert(lib.sqlite3_open(':memory:', dbh_ptr) == 0)
local dbh = dbh_ptr[0]

local assert_ok = function(result_code)
    if (
        result_code == ffi.defs.SQLITE_OK or
        result_code == ffi.defs.SQLITE_ROW or
        result_code == ffi.defs.SQLITE_DONE
    ) then
        return result_code
    end
    local msg = lib.sqlite3_errmsg(dbh)
    error(('%s (%d)'):format(ffi.string(msg), result_code))
end

assert_ok(lib.sqlite3_extended_result_codes(dbh, 1))

local prepare_stmt = function(sql)
    local stmt_ptr = ffi.new('sqlite3_stmt *[1]')
    assert_ok(lib.sqlite3_prepare_v3(dbh, sql, -1, 0b000, stmt_ptr, nil))
    return stmt_ptr[0]
end

local get_column_value = function(stmt, column_index)
    local column_type = lib.sqlite3_column_type(stmt, column_index);
    if column_type == ffi.defs.SQLITE_NULL then
        return ffi.NULL
    elseif column_type == ffi.defs.SQLITE_INTEGER then
        return tonumber(lib.sqlite3_column_int64(stmt, column_index))
    elseif column_type == ffi.defs.SQLITE_FLOAT then
        return tonumber(lib.sqlite3_column_double(stmt, column_index))
    elseif column_type == ffi.defs.SQLITE_TEXT then
        return ffi.string(lib.sqlite3_column_text(stmt, column_index))
    elseif column_type == ffi.defs.SQLITE_BLOB then
        return ffi.string(
            lib.sqlite3_column_blob(stmt, column_index),
            lib.sqlite3_column_bytes(stmt, column_index)
        )
    end
    error('unsupported column type: ' .. column_type)
end

local execute_stmt = function(stmt)
    local column_count = lib.sqlite3_column_count(stmt);
    local returns_data = column_count > 0
    local rows = {}
    while true do
        local result_code = assert_ok(lib.sqlite3_step(stmt))
        if result_code == ffi.defs.SQLITE_DONE then
            break
        end
        if returns_data then
            local row = {}
            for column_index = 0, column_count - 1 do
                table.insert(row, get_column_value(stmt, column_index))
            end
            table.insert(rows, row)
        end
    end
    if returns_data then
        return rows
    end
    return nil
end

local stmt = prepare_stmt(sql)
for row_index, row in ipairs(execute_stmt(stmt)) do
    print(row_index, unpack(row))
end

assert_ok(lib.sqlite3_finalize(stmt))

Uses the amazing ffiex library:

• https://github.com/umegaya/ffiex
• https://luarocks.org/modules/umegaya/ffiex

$ luajit sqlite.lua 'VALUES (12, "foo"), (34, "bar")'
1	12	foo
2	34	bar