Database

The database functions provide access to a relational database for storing, querying, and manipulating structured data during package execution. This is useful when a package needs to collect data from multiple sources, transform or filter it using SQL, or build up a result set incrementally across steps.

By default, every package execution starts with a fresh, empty in-memory database. This database is private to the execution, concurrent runs each get their own isolated database, and it is automatically removed when the execution finishes.

With the sql() function, any SQL statement can be executed against this database. For packages that need to query or modify data in an existing MySQL or Microsoft SQL Server database, db_use can switch sql() to operate against an external database account instead.

The snapshot functions (db_save, db_load, db_list, db_remove) let you preserve the in-memory database across package executions, for example to cache data that is expensive to fetch. Snapshots are scoped to the profile environment, so different packages can also use them to share data.

sql

Executes a SQL statement against the current database.

let rows = sql(query, ...params);

Arguments:

query - a SQL statement (CREATE, SELECT, INSERT, UPDATE, DELETE, etc.).
params - optional values that replace ? placeholders or named parameters in the query.

Returns:

SELECT queries return an array of row objects, e.g. [{ name: "Alice", age: 30 }].
Write queries (INSERT, UPDATE, DELETE, CREATE) do not return a meaningful value.

Example:

sql("CREATE TABLE users (name TEXT, age INTEGER)");

sql("INSERT INTO users VALUES (?, ?)", "Alice", 30);

let result = sql("SELECT * FROM users");
// result = [{ name: "Alice", age: 30 }]

Parameterized queries

Instead of concatenating values directly into the SQL string, use parameters to safely pass values into a query. Parameters are placeholders in the SQL statement that get replaced with the provided values at execution time. This prevents SQL injection and handles type conversion automatically.

Positional parameters

Use ? placeholders:

// As individual arguments
sql("INSERT INTO users VALUES (?, ?)", "Alice", 30);

// As an array
sql("INSERT INTO users VALUES (?, ?)", ["Alice", 30]);

Named parameters

Use @name syntax (also :name):

sql(
  "INSERT INTO users VALUES (@name, @age)",
  { name: "Alice", age: 30 }
);

warning

Named parameters are available with the in-memory database and with SQL Server databases. When using a MySQL database via db_use, use ? positional placeholders instead. SQL Server databases only support named parameters. See differences between databases for details.

Null values

Both null and undefined values are inserted as SQL NULL:

sql("INSERT INTO users VALUES (?, ?)", "Alice", null);
sql("INSERT INTO users VALUES (@name, @age)", { name: undefined, age: 30 });

Bulk inserts

Multiple rows can be inserted in a single sql() call by passing an array of arrays or an array of objects.

note

Bulk inserts work differently depending on which database is active. The syntax below applies to the in-memory database. When using a MySQL database via db_use, use MySQL's native VALUES ? syntax instead. With a SQL Server database, pass the rows as JSON and read them with OPENJSON.

See differences between databases for details.

Array of arrays

sql("INSERT INTO users VALUES (?, ?)", [
  ["Alice", 30],
  ["Bob", 42],
  ["Carol", 19],
]);

Array of objects (named parameters)

sql("INSERT INTO users (name, age) VALUES (@name, @age)", [
  { name: "Alice", age: 30 },
  { name: "Bob", age: 42 },
]);

Complete example

sql("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)");

// Single inserts
sql("INSERT INTO users (name) VALUES (?)", "Alice");
sql("INSERT INTO users (name, age) VALUES (?, ?)", "Bob", 42);
sql("INSERT INTO users (name, age) VALUES (@name, @age)", { name: "Carol", age: null });

// Bulk insert
sql("INSERT INTO users (name, age) VALUES (?, ?)", [
  ["David", 28],
  ["Eve", 35],
]);

let result = sql("SELECT name, age FROM users");
// result = [
//   { name: "Alice", age: null },
//   { name: "Bob",   age: 42 },
//   { name: "Carol", age: null },
//   { name: "David", age: 28 },
//   { name: "Eve",   age: 35 }
// ]

db_use

Switches the database that sql() operates on.

db_use(account);

By default, sql() uses the built-in in-memory database. Call db_use with an account name to direct all subsequent sql() calls to an external MySQL or Microsoft SQL Server database instead. Call db_use("memory") to switch back to the in-memory database.

Arguments:

account - the name of a MySQL or Microsoft SQL Server account configured in the automator, or "memory" to switch back to the in-memory database.

Returns: the account name as confirmation.

Example

// Switch to an external MySQL database
db_use("my_mysql_account");

let customers = sql("SELECT * FROM customers WHERE active = ?", 1);

// Switch back to the in-memory database
db_use("memory");

sql("CREATE TABLE temp (id INTEGER, value TEXT)");

// Switch to an external SQL Server database
db_use("my_sqlserver_account");

const customers = sql("SELECT * FROM customers WHERE active = @active", { active: true });

db_save

Creates a snapshot of the current in-memory database.

db_save(snapshot_name);

A saved snapshot persists until it is explicitly removed, so a package can load it on the next run to continue where it left off. This is useful for caching data that is expensive to fetch, or for building up a result set over multiple runs. Snapshots are scoped to the profile environment, which means different packages in the same environment can also share data through snapshots.

A snapshot with the same name will overwrite the previous one.

Arguments:

snapshot_name - a name for the snapshot.

Limits: up to 10 snapshots per environment. The function fails if this limit would be exceeded.

Example

sql("CREATE TABLE results (id INTEGER, status TEXT)");
sql("INSERT INTO results VALUES (?, ?)", [
  [1, "ok"],
  [2, "error"],
]);

db_save("daily_results");

db_load

Loads a previously saved snapshot, replacing the current in-memory database.

let loaded = db_load(snapshot_name);

The loaded database is a copy of the snapshot. Changes made after loading do not affect the saved snapshot until db_save is called again.

Arguments:

snapshot_name - the name of the snapshot to load.

Returns: true if the snapshot was loaded, false if no snapshot with that name exists.

Example

if (!db_load("daily_results")) {
  // First run - initialize the database
  sql("CREATE TABLE results (id INTEGER, status TEXT)");
}

// Add today's data
sql("INSERT INTO results VALUES (?, ?)", 3, "ok");

// Save the updated database for the next run
db_save("daily_results");

db_list

Lists all snapshot names in the current environment.

let snapshots = db_list();

Returns: an array of snapshot name strings, or an empty array if no snapshots exist.

Example

let snapshots = db_list();
log("available snapshots", snapshots);
// e.g. ["daily_results", "weekly_summary"]

db_remove

Removes a snapshot by name.

db_remove(snapshot_name);

Arguments:

snapshot_name - the name of the snapshot to remove.

Example

db_remove("daily_results");

Differences between databases

The following database types are supported:

the built-in in-memory database (SQLite)
external MySQL databases
external Microsoft SQL Server databases

They have important behavioral differences:

	In-memory	MySQL	SQL Server
Parameters	`?`, `@name` and `:name`	`?`	`@name`
Bulk inserts	array of rows	`VALUES ?`	`OPENJSON`
Lists (`IN`)	not supported	`IN (?)` with an array	`OPENJSON`
Transactions	automatic for bulk inserts	not supported	within one `sql()` call
Data lifetime	one execution	permanent	permanent

Bulk inserts

Bulk inserts work differently between the databases.

With the in-memory database, you write the INSERT statement for a single row and pass all rows as an array of arrays. The database iterates over the rows internally and wraps them in a transaction:

// In-memory database: single-row INSERT, array of rows
sql("INSERT INTO users VALUES (?, ?)", [
  ["Alice", 30],
  ["Bob", 42],
]);

With a MySQL database, use MySQL's native VALUES ? syntax with a nested array:

// MySQL database: use VALUES ? with a nested array
db_use("my_mysql_account");
sql("INSERT INTO users (name, age) VALUES ?", [
  ["Alice", 30],
  ["Bob", 42],
]);

With a SQL Server database, pass the rows as a JSON string, as described in bulk inserts with OPENJSON.

Named parameters

The in-memory database supports @name and :name syntax, as well as ? positional placeholders. MySQL accounts only support ? positional placeholders. SQL Server accounts only support @name named parameters.

Transactions

Transactions are handled automatically for bulk inserts with the in-memory database. MySQL accounts do not support transactions. Each sql() call can run on a different connection to the database, and every statement is committed immediately. Do not start a transaction with BEGIN or START TRANSACTION, because it stays open on a connection that other sql() calls reuse. With SQL Server, start and commit the transaction in a single sql() call, see transactions on SQL Server.

Data lifetime

The in-memory database starts empty and is removed after execution. Changes to a MySQL or SQL Server database are permanent and visible to other systems.

MySQL

Connection settings

A MySQL account always connects over an encrypted (TLS) connection, so the server must support TLS.

Trust server certificate: accept the certificate of the server without validating it. Enable it when the server uses a self-signed certificate. Otherwise, the certificate must be issued by a public certificate authority or by Amazon RDS, and match the hostname.

Microsoft SQL Server

This section describes how to work with a SQL Server database through a Microsoft SQL Server account. It applies to SQL Server 2016 and later, and to Azure SQL Database.

Connection settings

A Microsoft SQL Server account connects with a SQL Server login (a username and password).

Hostname: the name of the server, for example crm-db.company.com. To connect to a named instance, add the name of the instance after a backslash, for example sql01\SQLEXPRESS. The port is ignored for a named instance.
Port: the port of the server. Defaults to 1433.
Trust server certificate: accept the certificate of the server without validating it. Enable it when the server uses a self-signed certificate, which is common for on-premises servers.

Parameters

SQL Server accounts only support named parameters. Pass them as a single object, and refer to them with @name in the statement:

db_use("my_sqlserver_account");

const users = sql("SELECT * FROM users WHERE name = @name AND age > @age", { name: "Alice", age: 30 });

The value of a parameter must be a single value: a string, number, boolean, Date, null or undefined. Arrays and objects are rejected. To pass a list of values or a set of rows, pass it as a JSON string and read it with OPENJSON, as shown below.

Lists of values

To filter on a list of values, pass the list as a JSON string and read it with OPENJSON:

const ids = [1, 2, 3];
const users = sql("SELECT * FROM users WHERE id IN (SELECT value FROM OPENJSON(@ids))", { ids: JSON.stringify(ids) });

Bulk inserts with OPENJSON

To insert many rows with a single sql() call, pass the rows as a JSON string, and use OPENJSON with a WITH clause to turn the JSON into rows with typed columns:

const rows = [
  { name: "Alice", age: 30 },
  { name: "Bob", age: 42 },
];

sql(
  `INSERT INTO users (name, age)
   SELECT name, age FROM OPENJSON(@rows) WITH (name nvarchar(100), age int)`,
  { rows: JSON.stringify(rows) },
);

All rows are sent in a single parameter and inserted by a single statement, which is much faster than inserting rows one by one. It also avoids limits that SQL Server imposes on the number of parameters in a statement (2100) and the number of rows in a VALUES list (1000).

Generated keys

To retrieve the key that SQL Server generated for an inserted row, add an OUTPUT clause. The statement then returns the inserted values as rows:

const [user] = sql("INSERT INTO users (name) OUTPUT INSERTED.id VALUES (@name)", { name: "Alice" });
log(user.id); // => 42

OUTPUT also works for bulk inserts with OPENJSON, returning one row for each inserted row.

Transactions on SQL Server

Each sql() call can run on a different connection to the database. A transaction must therefore start and end within a single sql() call. Send the statements of the transaction as one batch:

sql(
  `BEGIN TRANSACTION;
   UPDATE accounts SET balance = balance - @amount WHERE id = @from;
   UPDATE accounts SET balance = balance + @amount WHERE id = @to;
   COMMIT;`,
  { amount: 100, from: 1, to: 2 },
);

When one of the statements fails, the whole transaction is rolled back.

Return values

A statement that returns rows, such as a SELECT or a statement with an OUTPUT clause, returns an array of row objects. When a batch returns several result sets, only the first one is returned.
Other statements return an object with the number of affected rows, for example { affectedRows: 2 }.
bigint values are returned as strings (because they can exceed the range of a JavaScript number).
decimal, numeric and money values are returned as numbers, which can lose precision for large values or many decimals. When precision matters, convert such values to a string in the SQL query (CAST(amount AS nvarchar(40))).
uniqueidentifier values are returned as uppercase strings.
date, datetime and datetime2 values are returned as Date objects in UTC.

Columns without a name, such as COUNT(*), are returned under an empty name. Give them an alias to access them by name:

db_use("my_sqlserver_account");

const [unnamed] = sql("SELECT COUNT(*) FROM users");
log(unnamed); // => { "": 5 }

const [{ total }] = sql("SELECT COUNT(*) AS total FROM users");
log(total); // => 5

sql​

Parameterized queries​

Positional parameters​

Named parameters​

Null values​

Bulk inserts​

Array of arrays​

Array of objects (named parameters)​

Complete example​

db_use​

Example​

db_save​

Example​

db_load​

Example​

db_list​

Example​

db_remove​

Example​

Differences between databases​

Bulk inserts​

Named parameters​

Transactions​

Data lifetime​

MySQL​

Connection settings​

Microsoft SQL Server​

Connection settings​

Parameters​

Lists of values​

Bulk inserts with OPENJSON​

Generated keys​

Transactions on SQL Server​

Return values​

sql

Parameterized queries

Positional parameters

Named parameters

Null values

Bulk inserts

Array of arrays

Array of objects (named parameters)

Complete example

db_use

Example

db_save

Example

db_load

Example

db_list

Example

db_remove

Example

Differences between databases

Bulk inserts

Named parameters

Transactions

Data lifetime

MySQL

Connection settings

Microsoft SQL Server

Connection settings

Parameters

Lists of values

Bulk inserts with OPENJSON

Generated keys

Transactions on SQL Server

Return values