Databases

postgres and mysql — one surface, parameterised queries, no pooling.

LexerLang

use postgres

$db = new postgres({
    host: "localhost",
    port: 5432,
    user: "postgres",
    password: "postgres",
    database: "testdb"
})

$result as array = []
with $db
    .query("SELECT * FROM users ORDER BY id ASC")
    .rows.to_array($result)
end with

$db.close()

Call	Meaning
`new postgres({...})`	connects; `user` and `database` are required
`$db.query($sql, ...)`	runs a query, updates `$db.rows`, returns `$db`
`$db.execute($sql, ...)`	a command returning no rows; returns affected rows
`$db.rows.to_array($array)`	appends rows to the array and returns it
`$db.rows.length` / `.first()`	row count / first row
`$db.close()` / `$db.is_closed()`	closes / state

Parameters#

LexerLang

$db.query("SELECT name FROM users WHERE id = $1", 5)

Values travel as parameters, not pasted into the query text — a value containing a quote cannot turn into a command. There is no separate escaping function for SQL, because none is needed. Multiple statements are disabled at the driver level.

The one place parameters do not cover is column names:

LexerLang

security.identifier($sort, ["name", "price"])

No pooling#

Each new postgres opens one connection. Because the page runs again on every request, one connection is opened per request and closed automatically when the request ends; otherwise every request would leak a connection.

A connection opened during the startup run is the program's own responsibility.

Type mapping#

int→int, float→float, bool→bool, text→string, NULL→null, timestamp→ISO string, uuid→standard uuid string.

numeric arrives as a string. Converting it to floating point would damage precision, so this is deliberate: losing the last cent of a monetary amount costs more than carrying it as text.

Rendering results into a page#

A backtick block is an expression; a statement such as for cannot go inside it. Rows are collected first, then joined:

LexerLang

func rows($data) as string
    $rows as array = []

    for $row in $data
        $rows.add(`
            <tr>
                <td>` + $row.id + `</td>
                <td>` + $row.device_name + `</td>
            </tr>
        `)
    end for

    return `<table><tbody>` + $rows.join("") + `</tbody></table>`
end func

join returns raw HTML when one of the parts is raw HTML, and the data interpolated into it is escaped — the same rule as the + operator.

To pass data to JavaScript, use json.encode:

LexerLang

$doc.body.add(`
    <script>
        const data = ` + json.encode($data) + `;
    </script>
`)

MySQL#

mysql uses the same surface, and the configuration is identical. The same calls: query, execute, rows.to_array, rows.first, close, is_closed.

Extra option	Meaning
`charset`	connection character set
`collation`	collation rule
`tls`	`"true"`, `"skip-verify"`, `"preferred"`

There are two deliberate differences:

LexerLang

# postgres — numbered
$db.query("SELECT * FROM products WHERE id = $1", 5)

# mysql — positional
$db.query("SELECT * FROM products WHERE id = ?", 5)

The parameter marker is part of the database's syntax; hiding it would mean rewriting the query text, and that carries a risk of a wrong result.

LexerLang

$db.execute("INSERT INTO products (name) VALUES (?)", "Leather Wallet")
out($db.last_insert_id)

last_insert_id exists only in mysql; the postgres equivalent is calling query with INSERT ... RETURNING id.

Next#

Redis and sessions.