16. Database Support

MALDA ships three clients with the same API: SqliteClient, SqlServerClient, and PostgresClient. Start with SQLite — it is a file on disk, 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.

16.1 SQLite from zero

Connect, create a table, insert, query, disconnect. Parameters use @name (never concatenate user input into SQL).

var db = new SqliteClient();
db.connect("Data Source=app.db;");

db.execute("CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT, email TEXT, age INTEGER)");
db.insert("users", {name: "Jane", email: "jane@example.com", age: 30});

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

var one = db.select("name", "email")
            .from("users")
            .where("age", ">", 25)
            .query();

db.disconnect();

Constructor: new SqliteClient() or new SqliteClient(connectionString). Connection string: Data Source=path/to/db.sqlite;.

16.2 Shared client API

All three classes expose:

• connect(connectionString), disconnect(), isConnected
• query(sql, params?) → array of row objects; queryOne(sql, params?) → one row or null; execute(sql, params?) → rows affected
• Query builder: select(...columns).from(table).where(column, operator, value).query() — where(column, value) means =
• insert(table, values); update(table, values) and delete(table) require a prior where
• beginTransaction(), commit(), rollback()

db.beginTransaction();
try {
    db.execute("INSERT INTO users (name) VALUES (@name)", {name: "Test"});
    db.commit();
} catch (e) {
    db.rollback();
}

Query results are arrays of objects whose properties are column names:

var users = db.query("SELECT name, email FROM users");
print(users[0].name + " - " + users[0].email);

16.3 SQL Server and PostgreSQL

Same methods, different connection strings and placeholders. The query builder numbers PostgreSQL parameters for you.

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();

16.4 Security

All clients use parameterized queries. Always pass parameters as an object instead of concatenating strings.