SQL databases Preview

SqlCli connects to a relational database, runs queries with bound parameters, and returns rows as plain JavaScript objects. It is synchronous like everything else in SyncJS.

var cli = new SqlCli().Driver("pg").ConnString(connectionString);
if (!cli.Connect()) {
    Log.Error("could not connect to the database");
    Exit(1);
}

var rows = cli.Query("SELECT allowed FROM upload_policy WHERE username = ?", [CtxUsername()]);
cli.Close();

if (rows.length === 0 || !rows[0].allowed) {
    Log.Warn("no upload policy for " + CtxUsername() + ", refusing");
    Exit(1);
}

Supported drivers

Database	Driver name
Couchbase	n1ql
FirebirdSQL	firebirdsql
Google BigQuery	bigquery
Microsoft SQL Server	mssql
MySQL and MariaDB	mysql
Oracle	oracle
PostgreSQL	pg
SQLite	sqlite

All eight are compiled into the Connector. Nothing needs installing, and there is no ODBC layer.

Set the driver before calling Connect

Connect() with no driver set stops the script. Set both Driver() and ConnString() first.

Connection strings

var cli = new SqlCli();
cli.Driver("mysql");
cli.ConnString("user:pass@(localhost:3306)/testdb");

Driver	Format
pg	host=localhost port=5432 user=u password=p dbname=d sslmode=require
mysql	user:password@(host:port)/dbname
mssql	sqlserver://user:password@host/instance?database=d&connection+timeout=30
oracle	user/password@host:port/database
sqlite	A file path, or :memory:
firebirdsql	user:password@host/path/to/database.fdb
bigquery	bigquery://projectid/location/dataset
n1ql	host:port, or a full URL for a cluster

Every part of an mssql connection string must be URL encoded, because it is parsed as a URL.

For PostgreSQL, prefer sslmode=require or stricter. sslmode=disable sends the password and every row in clear text.

Querying

Query(sql: string, params?: any[]): object[];

Returns an array of objects, one per row, keyed by column name. ? placeholders are substituted from params in order. The array is optional when the statement has no placeholders.

var rows = cli.Query("SELECT place, code FROM places WHERE code = ? AND place = ?", [42, "universe"]);
Log.Info(JSON.stringify(rows));
// [{"place":"universe","code":42}]

rows.forEach(function (r) {
    Log.Info(r.place + " is " + r.code);
});

Inserting, updating and deleting

Exec(sql: string, params?: any[]): SqlResult;

Everything that is not a query goes through Exec, which returns:

{
  LastInsertedId: any,   // driver dependent, often absent on PostgreSQL
  RowsAffected: number
}

var res = cli.Exec(
    "INSERT INTO transfer_log (username, path, event, at) VALUES (?, ?, ?, ?)",
    [CtxUsername(), CtxRelPath(), EventHandler(), FormatDateTime("YYYY-MM-DD HH:mm:ss")]
);
Log.Info("inserted " + res.RowsAffected + " row, id " + res.LastInsertedId);

LastInsertedId is only meaningful where the driver supports it. On PostgreSQL use RETURNING id with Query() instead.

Always parameterize

String concatenation into SQL is an injection, and a file name is user input

A user names a file; that name reaches your script through CtxRelPath(); if you paste it into a statement, they are writing SQL on your database.

// DANGEROUS. Never do this.
cli.Query("SELECT * FROM files WHERE path = '" + CtxRelPath() + "'");

// Correct.
cli.Query("SELECT * FROM files WHERE path = ?", [CtxRelPath()]);

The placeholder form is not merely safer; it is also the only form that handles quotes, unicode and nulls correctly.

Close what you open

Connect(): boolean;
Close(): boolean;

Every execution builds a fresh engine and a fresh client, so a connection lives at most as long as one script run. Close it anyway: a busy Connector that opens a connection per upload and never closes one will exhaust the database's connection limit long before anything else goes wrong.

var cli = new SqlCli().Driver("pg").ConnString(dsn);
if (cli.Connect()) {
    try {
        // work here
    } catch (e) {
        Log.Error("query failed: " + e);
    }
    cli.Close();
}

A database on the data path is a database that can stop your transfers

Connect() opens a fresh connection every time the handler fires, which on a busy virtual file system may be many times per second. There is no pooling. A before handler that queries a database makes every upload wait for that database, and a fail closed handler makes every upload depend on it being up.

Where a policy lookup genuinely has to gate an operation, keep the query trivial and indexed, set a short handler timeout, and decide deliberately whether a database outage should stop transfers or let them through.

Keep the password out of the connection string

A connection string written into a script puts the password in the script source, readable by every Connector administrator and carried in every database backup. Keep it in the secret store and assemble the string at run time:

var pw = GetSecret("reporting-db-password");
var cli = new SQLCli("postgres", "host=db.example.com user=reporting password=" + pw + " dbname=ops");

Then give that account the narrowest rights that work: read only where it only reads, one table where it only writes one table, and never the account your application uses. The store limits where a leaked credential spreads; the account's rights limit what a leak is worth.