sqlopen — SQLite files: sqlopen, sqlexec, sqlclose

Mix has an embedded SQLite client (the sqlite feature; the mix binary turns it on), so a script can create, query and update a database file without the sqlite3 CLI. Handles are plain numbers. These builtins are FsWrite: they are arbitrary filesystem authority. A host that hands a script one store should use the scoped Db seam (db_query) instead (mix man capabilities).

sqlopen(path[, mode])      open a database             -> numeric handle
sqlexec(h, sql[, params])  run one statement           -> rows or {affected}
sqlclose(h)                close the handle (an unknown handle raises)

Modes

The default is read-only, so a script that only reads a database (a secrets store, another program's state) cannot change it, or its journal mode, by accident. A write through a read-only handle raises. To create a database, say so with "rwc". Any other mode, including nil, "RW" and "", raises OPTION_INVALID; before 0.117.0 an unknown mode silently opened read-only, which is why sqlopen(path, "rwc") used to fail on a new file with only unable to open database file.

Opening read-write sets PRAGMA journal_mode=WAL, which is persistent: the file stays in WAL mode and gets -wal and -shm siblings while it is open. Open another program's database read-only unless the script owns it.

":memory:" is a private in-memory database that lives as long as the handle. Open it with "rw", since a read-only one can never hold a table.

$path = "/tmp/sqlopen-demo.db"
$db = sqlopen($path, "rwc")
sqlexec($db, "CREATE TABLE IF NOT EXISTS seen (id TEXT PRIMARY KEY, at INTEGER)")
sqlexec($db, "INSERT OR IGNORE INTO seen VALUES (?, ?)", ["msg-1", 1760000000])
sqlclose($db)

$ro = sqlopen($path)
print(json_encode(sqlexec($ro, "SELECT id FROM seen")))
sqlclose($ro)

[{"id":"msg-1"}]

Results and parameters

sqlexec returns a list of row maps for anything that produces columns (SELECT, row-returning PRAGMAs) and {affected: n} for a write. SQLite decides which (sqlite3_stmt_readonly), so REPLACE and WITH … INSERT take the write path. Column values map NULL to nil, INTEGER and REAL to a number, and TEXT to a string. A BLOB comes back as the placeholder string <blob N bytes>, not as bytes.

params binds ? placeholders: a list binds positionally, and a single non-list value is one bind. Binds are typed: nil is NULL, a bool is INTEGER 0/1, a whole number is INTEGER, a fractional one is REAL, a string is TEXT and bytes are a BLOB. A list or map param raises. Always bind data; never splice values into the SQL string.

Errors

See also

• io: the rest of the filesystem builtins.
• data: how Mix values cross serialization boundaries.