neyuki 0.3.0

local table = require("@neyuki/table")

local sql = {}

-- The drivers are native (src/vm/libs/sql.rs, on the `postgres` and `mysql`
-- crates); this module validates arguments and builds the `Connection`
-- object. Statements take positional parameters in the server's own
-- syntax, `$1, $2` on PostgreSQL and `?` on MySQL, and a params array
-- supplies them, so values never get spliced into SQL text. A nil entry
-- binds as NULL.
--
-- Rows come back as { column = value } tables: integers stay integers,
-- floats floats, booleans booleans (PostgreSQL) and text strings; NULL is
-- nil. Decimals, dates, times, timestamps, uuids and json arrive as strings
-- in the server's text form so nothing is rounded.

function checkString(s: string, name: string)
    if (s == nil) then error(name .. " must be provided") end
    if (type(s) != "string") then error(name .. " must be a string") end
end

function checkOptionalTable(t: {any}?, name: string)
    if (t != nil and type(t) != "table") then error(name .. " must be a table") end
end

-- Opens a connection. `target` is a URL such as
-- "postgres://user:pass@host:5432/db" or "mysql://user:pass@host/db", or a
-- table with `driver` ("postgres" or "mysql") plus `host`, `port`, `user`,
-- `password`, `database`, `ssl` (true to require TLS, false to refuse it;
-- unset, TLS is required for any host but this machine unless a PostgreSQL
-- URL sets `sslmode`) and `timeout` (seconds to wait for the connection). A
-- table may carry a `url` too, with the other fields overriding parts of it.
function sql.connect(target: string | ConnectOptions): Connection
    local options
    if (type(target) == "string") then
        options = { url = target }
    elseif (type(target) == "table") then
        options = target
    else
        error("target must be a URL string or an options table")
    end

    local id, driver = __sql_open(options)
    local closed = false
    local connection = {}
    connection.driver = driver

    function checkOpen()
        if (closed) then error("connection is closed") end
    end

    -- Runs a statement and returns its rows as an array of { column = value }
    -- tables; `rows.columns` lists the column names in select order. Columns
    -- that are NULL are absent from their row.
    function connection.query(self: Connection, statement: string, params: {any}?): {Row}
        checkOpen()
        checkString(statement, "statement")
        checkOptionalTable(params, "params")
        return __sql_query(id, statement, params)
    end

    -- The first row `query` would return, or nil when there is none.
    function connection.queryOne(self: Connection, statement: string, params: {any}?): Row?
        local rows = self:query(statement, params)
        return rows[1]
    end

    -- Runs a statement for its effect and returns the number of rows it
    -- touched, followed by the id MySQL assigned to an auto-increment
    -- insert (nil on PostgreSQL, where `RETURNING id` with `query` does the
    -- same job).
    function connection.execute(self: Connection, statement: string, params: {any}?): (number, number?)
        checkOpen()
        checkString(statement, "statement")
        checkOptionalTable(params, "params")
        return __sql_execute(id, statement, params)
    end

    function connection.begin(self: Connection): ()
        self:execute("BEGIN")
    end

    function connection.commit(self: Connection): ()
        self:execute("COMMIT")
    end

    function connection.rollback(self: Connection): ()
        self:execute("ROLLBACK")
    end

    -- Runs `body(connection)` inside a transaction: committed once `body`
    -- returns, rolled back if it raises, in which case the error is raised
    -- again here. Returns what `body` returned.
    function connection.transaction(self: Connection, body: (Connection) -> any): any
        if (type(body) != "function") then error("body must be a function") end
        self:begin()
        local ok, result = try(body, self)
        if (ok) then
            self:commit()
            return result
        end
        -- The rollback is best effort: if the connection is gone, the
        -- original error is the one worth reporting.
        try(self.rollback, self)
        error(result)
    end

    -- Whether the server still answers on this connection.
    function connection.ping(self: Connection): boolean
        checkOpen()
        return __sql_ping(id)
    end

    function connection.close(self: Connection): ()
        if (closed) then return end
        closed = true
        __sql_close(id)
    end

    function connection.isOpen(self: Connection): boolean
        return not closed
    end

    return table.freeze(connection)
end

return table.freeze(sql)