17. Database Support

MALDA ships three clients with the same API: SqliteClient, SqlServerClient, and PostgresClient. Start with SQLite — it is a file on disk (or an in-memory database), no server. Connection, parameterized SQL, the query builder, inserts, and transactions are identical on the other two; only the connection string and placeholder syntax change.

The clients run on the interpreter and C# transpile. They are not available on the JavaScript backend — keep SQL on the server and call it over HTTP from browser code. See 2. Tools & Tooling (runtime modes) and the backend capability matrix.

This chapter is the SQL client API. Durable workflow storage is a separate SQLite file under .malda/ — see 23. Durable Workflows. Similarity search is 16. VectorDB, not these clients.

17.1 SQLite from zero

Connect, create a table, insert, query, disconnect. Parameters use @name (never concatenate user input into SQL). Data Source=:memory:; is an in-memory database that disappears when you disconnect — use it in tests and snippets so nothing is left on disk.

var db = new SqliteClient();
db.connect("Data Source=:memory:;");
print(db.isConnected);

db.execute("CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, age INTEGER)");
db.insert("users", {name: "Alice", age: 30});

var one = db.queryOne("SELECT name, age FROM users WHERE name = @name", {name: "Alice"});
print(one.name);
print(one.age);

var allRows = db.query("SELECT name FROM users");
print(allRows.length);

db.disconnect();
print(db.isConnected);

Constructor: new SqliteClient() or new SqliteClient(connectionString). Passing the string to the constructor does not open the connection — you still call connect(connectionString). File databases use Data Source=path/to/db.sqlite; (relative paths are resolved from the process working directory).

Same file in the catalog: Examples/Databases/sqlite_basic.malda (prints the API without connecting). A real file-backed starter is malda new webapi --local-first / malda new fullstack --local-first.

17.2 Shared client API

All three classes expose the same methods. Call connect before anything else; SQL before connect throws Not connected to database. Call connect() first.

Query results are arrays of objects whose properties are the column names as returned by the driver (aliases included). SQL NULL becomes MALDA null. Integers and floats keep their numeric kinds; SQLite DATETIME values come back as strings yyyy-MM-dd HH:mm:ss.

var users = db.query("SELECT name, email FROM users WHERE age > @age", {age: 25});
print(users[0].name + " - " + users[0].email);

var missing = db.queryOne("SELECT name FROM users WHERE id = @id", {id: -1});
print(missing == null);

Parameter objects accept keys with or without @: {age: 25} and {"@age": 25} both bind @age on SQLite and SQL Server. PostgreSQL uses $1, $2, … — see 17.5. There is no lastInsertId property on the client; insert / execute return the rows-affected count. Read the new key with SQL (17.6).

17.3 Query builder

The builder is a small helper on the same client, not a separate SQL dialect. It covers SELECT plus value-parameterized INSERT / UPDATE / DELETE. There is no orderBy, limit, join, or OR — use raw SQL for those.

17.3.1 Select

select(...columns).from(table).where(...).query() runs the built SELECT. query() with no arguments is the builder finish; query(sql, params?) is still raw SQL. where(column, value) means =; where(column, operator, value) passes the operator through (>, <, !=, LIKE, …). Extra where calls are combined with AND.

var db = new SqliteClient();
db.connect("Data Source=:memory:;");
db.execute("CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, email TEXT, age INTEGER)");
db.insert("users", {name: "Jane", email: "jane@example.com", age: 30});
db.insert("users", {name: "Bob", email: "bob@example.com", age: 20});

var adults = db.select("name", "email")
            .from("users")
            .where("age", ">", 25)
            .query();
print(adults.length);
print(adults[0].name);
db.disconnect();

Each where column should be unique: parameter names are derived from the column, so two conditions on the same column overwrite each other. The builder concatenates table and column names into SQL; only values are parameterized. Do not put user input in those names.

17.3.2 Insert, update, delete

insert(table, values) is a single call — it does not use a prior where.

update(table, values) and delete(table) require a prior select / from / where chain that populated the builder. Call where first, then update or delete. Chaining .update(...) after .where(...) is the same thing, because those methods return the client. Calling update / delete without a where throws. There is no “update then where” form.

var db = new SqliteClient();
db.connect("Data Source=:memory:;");
db.execute("CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, age INTEGER)");
db.insert("users", {name: "Jane", age: 30});
db.insert("users", {name: "Bob", age: 20});

db.select("name").from("users").where("name", "Bob").update("users", {age: 21});
var bob = db.queryOne("SELECT age FROM users WHERE name = @name", {name: "Bob"});
print(bob.age);

db.select("name").from("users").where("name", "Bob").delete("users");
print(db.query("SELECT name FROM users").length);
db.disconnect();

17.4 Transactions

One transaction at a time per client. A second beginTransaction() throws Transaction already in progress. commit() / rollback() without an open transaction throw No transaction in progress. disconnect() rolls back if a transaction is still open.

var db = new SqliteClient();
db.connect("Data Source=:memory:;");
db.execute("CREATE TABLE t (name TEXT)");

db.beginTransaction();
db.execute("INSERT INTO t (name) VALUES (@name)", {name: "Temp"});
db.rollback();
print(db.query("SELECT name FROM t").length);

db.beginTransaction();
try {
    db.execute("INSERT INTO t (name) VALUES (@name)", {name: "Keep"});
    db.commit();
} catch (e) {
    db.rollback();
}
print(db.query("SELECT name FROM t").length);
db.disconnect();

17.5 SQL Server and PostgreSQL

Same methods, different connection strings and placeholders. The query builder numbers PostgreSQL parameters for you, so select / from / where / insert look the same as SQLite; only raw SQL uses $1.

var sql = new SqlServerClient();
sql.connect("Server=localhost;Database=mydb;User Id=user;Password=pass;");
var rows = sql.query("SELECT * FROM users WHERE age > @age", {age: 25});
sql.disconnect();

var pg = new PostgresClient();
pg.connect("Host=localhost;Database=mydb;Username=user;Password=pass;");
var rows2 = pg.query("SELECT * FROM users WHERE age > $1", {$1: 25});
pg.disconnect();

Catalog samples (print the API, do not connect): Examples/Databases/sql_server_basic.malda, Examples/Databases/postgres_basic.malda.

17.6 Inserted row ids and JSON columns

After an insert, ask the database for the new key. SQLite:

var db = new SqliteClient();
db.connect("Data Source=:memory:;");
db.execute("CREATE TABLE t (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT)");
db.execute("INSERT INTO t (name) VALUES (@name)", {name: "A"});
var row = db.queryOne("SELECT last_insert_rowid() AS id");
print(row.id);
db.disconnect();

SQL Server typically uses SELECT SCOPE_IDENTITY() AS id in the same session (inside a transaction if other writers exist). PostgreSQL can INSERT … RETURNING id and queryOne that statement. None of these is a field on the MALDA insert result.

JSON in a TEXT column is an ordinary string: write toJSON, read parseJSON. Column aliases in the SELECT list become MALDA property names.

var db = new SqliteClient();
db.connect("Data Source=:memory:;");
db.execute("CREATE TABLE settings (key TEXT PRIMARY KEY, value_json TEXT NOT NULL)");
var payload = toJSON({title: "ACME", ok: true});
db.execute("INSERT INTO settings (key, value_json) VALUES (@key, @value)", {key: "brand", value: payload});
var row = db.queryOne("SELECT value_json AS valueJson FROM settings WHERE key = @key", {key: "brand"});
var parsed = parseJSON(row.valueJson);
print(parsed.title);
print(parsed.ok);
db.disconnect();

SQLite pragmas go through execute like any other SQL. Local-first scaffolds turn on WAL and foreign keys:

db.execute("PRAGMA journal_mode = WAL");
db.execute("PRAGMA foreign_keys = ON");

17.7 Runtime, errors, and backends

• Failed SQL is a runtime error whose message starts with SQL query error: or SQL execute error: plus the driver text.
• Re-calling connect closes the previous connection and opens the new string.
• JavaScript transpile does not emit these clients. Browser UI should call a MALDA REST or @PAGE backend that owns the database — see 28. Browser JavaScript UI Backend and 29. REST API Server.
• Workflows, the jobs queue, and GraphMemory persist with their own files; do not point SqliteClient at .malda/workflows.db unless you are inspecting it as a DBA.

17.8 Security

All three clients send values as parameters. Always pass an object instead of concatenating strings into SQL.

17.9 malda db (local-first scaffolds)

malda db is a CLI for scaffolded local-first SQLite projects (malda new webapi --local-first or malda new fullstack --local-first). It is not a general migration tool for arbitrary SqliteClient programs. By default it reads config/data.example.json for databaseFile and migrationModule.

malda db status
malda db migrate
malda db seed
malda db rollback
malda db --help

• status — inspect the SQLite file and migration registry without writing (latest applied id, seed/rollback support, source-vs-database drift). --format json / --json for machines.
• migrate — apply pending entries from localMigrationRegistry via initLocalDataPlatform() / runLocalMigration<ID>().
• seed — migrate, then run idempotent starter rows from seedLocalDataPlatform().
• rollback — revert only the latest applied registry entry that still exists in source, through openLocalDataPlatform() and rollbackLocalMigration<ID>().

Optional flags: --project / -p (project root), --module / -m (migration .malda path), --database / -d (SQLite file). Prefer additive, idempotent schema changes with the generated helpers (ensureColumn, ensureIndex, ensureLifecycleColumns) instead of ad hoc startup SQL. Seed and rollback follow those scaffold conventions; they do not discover arbitrary layouts.