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 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:
SELECTqueries 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 }
);
Named parameters are only available with the in-memory database. When using a MySQL database via db_use, use ? positional placeholders instead. 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.
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. 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 database instead. Call db_use("memory") to switch back to the in-memory database.
Arguments:
account- the name of a MySQL 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)");
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.
See also: db_load, db_list, db_remove
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.
See also: db_save, db_load, db_remove
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
Currently two database types are supported:
- the built-in in-memory database
- external MySQL databases
The in-memory database and MySQL databases have important behavioral differences.
Bulk inserts
Bulk inserts work differently between the two 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],
]);
Named parameters
Named parameters are only available with the in-memory database. The in-memory database supports @name and :name syntax. MySQL accounts only support ? positional placeholders.
Transactions
Transactions are handled automatically for bulk inserts with the in-memory database. With MySQL, there is no automatic transaction wrapping. Use explicit BEGIN and COMMIT statements if needed.
Data lifetime
The in-memory database starts empty and is removed after execution. Changes to a MySQL database are permanent and visible to other systems.