Databases

One set of commands for every database engine. Each connection says which engine it uses, and the commands work the same way on all of them.

Supported versions: The oldest SQLite version toil supports hasn’t been established. toil uses the SQLite built into Node, so the version is whichever your Node ships. PostgreSQL is tested against 17; the catalog columns toil reads need 11 or later. MySQL is tested against 8.4 and MariaDB against 11.8; the oldest versions toil supports haven’t been established. SQL Server is tested against 2022; the catalog views and OFFSET ... FETCH toil uses need 2012 or later. Azure SQL Database speaks the same protocol but hasn’t been tested. Oracle is tested against 23ai; Thin mode and the dictionary columns toil reads need 12.1 or later. No release older than 23ai has been run. Teradata goes through version 1.0 of the Teradata Query Service’s REST API; no Query Service release or database version has been run yet. Hive is tested against 4.2 and Impala against 4.5; both need HiveServer2 protocol V6 (Hive 0.13) or later, and the oldest releases toil supports haven’t been established. Hive 3.1, the release most clusters run, was also run (the official apache/hive:3.1.3 image) on a developer’s machine: everything works, except that Hive 3.1 reports no row count for a write, so db execute’s changes is 0 there, and a read that wrote goes unreported. Impala reports keys only from 4.0.

SQLite connections

There is nothing to install: toil uses the SQLite built into Node.

Environment variables, for the default db connection:

export TOIL_DB_ENGINE=sqlite
export TOIL_DB_PATH=/srv/data/app.db
# Only if this connection may be written to:
export TOIL_DB_WRITES=true

A named connection, defined entirely from the environment (handy in CI):

export TOIL_CONN_SCRATCH_AREA=db
export TOIL_CONN_SCRATCH_ENGINE=sqlite
export TOIL_CONN_SCRATCH_PATH=/tmp/scratch.db
export TOIL_CONN_SCRATCH_WRITES=true

Config file (~/.toil/config.json), with a read-only connection as the default and a writable scratch copy beside it:

{
  "version": 2,
  "connections": {
    "app": { "area": "db", "engine": "sqlite", "path": "/srv/data/app.db" },
    "scratch": { "area": "db", "engine": "sqlite", "path": "/tmp/scratch.db", "writes": true }
  },
  "defaults": { "db": { "connection": "app" } }
}

On Windows, write the path with forward slashes or escaped backslashes in JSON: "C:/data/app.db".

PostgreSQL connections

toil speaks PostgreSQL’s wire protocol in plain JavaScript (the pg driver, bundled into toil): no libpq, no psql, nothing to install.

Environment variables, for the default db connection:

export TOIL_DB_ENGINE=postgres
export TOIL_DB_HOST=db.imagile.dev
export TOIL_DB_PORT=5432
export TOIL_DB_DATABASE=app
export TOIL_DB_USER=reader
export TOIL_DB_PASSWORD=<the role's password>
export TOIL_DB_SSL=true

Or keep the connection in config and the password in the OS credential store:

{
  "version": 2,
  "connections": {
    "app-pg": { "area": "db", "engine": "postgres", "host": "db.imagile.dev", "database": "app", "user": "reader", "ssl": true }
  },
  "defaults": { "db": { "connection": "app-pg" } }
}

toil connections login app-pg   # prompts for the password and stores it

A named connection’s password can also come from TOIL_CONN_APP_PG_PASSWORD.

Already have the connection in DBeaver, SSMS, pgAdmin, DataGrip, MySQL Workbench or a connection string? toil connections import --from <tool> previews bringing it in, and --save creates it. The password is never imported, so you still run toil connections login <name>. See core.md, Importing database connections.

toil ignores the libpq environment (PGHOST, PGUSER, PGPASSWORD, PGSSLMODE, PGOPTIONS and the rest) and ~/.pgpass: a connection reaches only the host its own settings name, with only its own password.

Connect with an ordinary role that can read what the agent should see, ideally one with only SELECT grants. See the known limit under “Reads are read-only at the engine”.

MySQL and MariaDB connections

One engine, mysql, covers both. toil speaks their wire protocol in plain JavaScript (the mysql2 driver, bundled into toil): no client library, no mysql CLI, nothing to install.

Environment variables, for the default db connection:

export TOIL_DB_ENGINE=mysql
export TOIL_DB_HOST=db.imagile.dev
export TOIL_DB_PORT=3306
export TOIL_DB_DATABASE=app
export TOIL_DB_USER=reader
export TOIL_DB_PASSWORD=<the account's password>
export TOIL_DB_SSL=true

Or in config, with the password in the OS credential store:

{
  "version": 2,
  "connections": {
    "app-mysql": { "area": "db", "engine": "mysql", "host": "db.imagile.dev", "database": "app", "user": "reader", "ssl": true }
  },
  "defaults": { "db": { "connection": "app-mysql" } }
}

toil connections login app-mysql   # prompts for the password and stores it

toil reads no option file (my.cnf, ~/.mylogin.cnf) and no MYSQL_* variable. It never sends the password in clear text (an account using mysql_clear_password fails with AUTH), and it never uploads a local file: LOAD DATA LOCAL is off.

Connect with an account that has only SELECT on what the agent should see. See the known limits under “Reads are read-only at the engine”.

SQL Server connections

toil speaks TDS, SQL Server’s wire protocol, in plain JavaScript (the tedious driver, bundled into toil): no ODBC driver, no sqlcmd, nothing to install. It signs in with a SQL Server login (user name and password), which needs the server to allow SQL Server authentication (mixed mode), or, on Windows only, with Windows authentication ("auth": "windows", below).

Environment variables, for the default db connection:

export TOIL_DB_ENGINE=sqlserver
export TOIL_DB_HOST=sql.imagile.dev
export TOIL_DB_PORT=1433
export TOIL_DB_DATABASE=app
export TOIL_DB_USER=toil_reader
export TOIL_DB_PASSWORD=<the login's password>
export TOIL_DB_SSL=true

Or keep the connection in config and the password in the OS credential store:

{
  "version": 2,
  "connections": {
    "app-sql": { "area": "db", "engine": "sqlserver", "host": "sql.imagile.dev", "database": "app", "user": "toil_reader", "ssl": true }
  },
  "defaults": { "db": { "connection": "app-sql" } }
}

toil connections login app-sql   # prompts for the password and stores it

A named connection’s password can also come from TOIL_CONN_APP_SQL_PASSWORD.

Windows authentication (Windows only)

"auth": "windows" signs in as the Windows user running toil, with no password stored anywhere: Windows proves who you are with the Kerberos ticket (or NTLM) it already holds from your logon.

{
  "version": 2,
  "connections": {
    "app-sql": { "area": "db", "engine": "sqlserver", "host": "sql.imagile.dev", "database": "app", "auth": "windows", "ssl": true }
  }
}

From the environment, for the default db connection, as the current Windows user:

export TOIL_DB_ENGINE=sqlserver
export TOIL_DB_HOST=sql.imagile.dev
export TOIL_DB_DATABASE=app
export TOIL_DB_AUTH=windows

To sign in as a different domain account, add domain and user (TOIL_DB_DOMAIN, TOIL_DB_USER), and store that account’s password with toil connections login app-sql (Windows Credential Manager), or set TOIL_CONN_APP_SQL_PASSWORD.

How it works: Node has no way to ask Windows for a sign-in token, so for each connection toil runs one Windows PowerShell (powershell.exe from the Windows folder, -NoProfile -NonInteractive), which asks Windows’ SSPI Negotiate package for tokens for the server’s SPN, MSSQLSvc/<host>:<port>. The script and every token pass over the process’s stdin and stdout, never its command line. Use the server’s fully qualified name in host, the name its SPN is registered under; with any other name Kerberos can’t find the SPN, and Windows falls back to NTLM if the server and domain allow it.

Windows PowerShell must run in Full Language mode. Where an AppLocker or WDAC policy puts it in Constrained Language mode, Add-Type is blocked, and toil says so with AUTH naming the mode instead of failing the login vaguely. Windows PowerShell 5.1’s Add-Type also compiles a small helper with csc.exe into TEMP, so TEMP must be writable.

Windows authentication is untested against a real server: its tests run the helper against Windows’ own SSPI, and a full multi-step handshake completes, but no run against SQL Server on a domain has happened yet, nor one under a Constrained Language Mode policy.

Connect with a read-only login. SQL Server has no read-only transaction, so toil can’t ask the server to refuse writes the way it does for PostgreSQL. It rolls back every read instead (see “Reads are read-only at the engine”), and the login is what makes the server itself refuse a write. A login whose database user is only in db_datareader is enough:

CREATE LOGIN toil_reader WITH PASSWORD = '<a strong password>';
USE app;
CREATE USER toil_reader FOR LOGIN toil_reader;
ALTER ROLE db_datareader ADD MEMBER toil_reader;

Use a second connection, with a login that may write and writes: true, for db execute.

Oracle connections

toil speaks Oracle Net in plain JavaScript (the oracledb driver in Thin mode, bundled into toil): no Instant Client, no sqlplus, no native library, nothing to install. toil never switches the driver to Thick mode.

export TOIL_DB_ENGINE=oracle
export TOIL_DB_HOST=db.imagile.dev
export TOIL_DB_PORT=1521
export TOIL_DB_DATABASE=ORCLPDB1
export TOIL_DB_USER=reader
export TOIL_DB_PASSWORD=<the user's password>

{
  "version": 2,
  "connections": {
    "app-ora": { "area": "db", "engine": "oracle", "host": "db.imagile.dev", "database": "ORCLPDB1", "user": "reader" }
  },
  "defaults": { "db": { "connection": "app-ora" } }
}

toil connections login app-ora   # prompts for the password and stores it

toil builds the connect descriptor itself from host, port and database, so it reads no tnsnames.ora, sqlnet.ora, TNS_ADMIN or wallet. Thin mode needs the user’s password to have an 11G or 12C verifier; an older one fails with AUTH, saying so.

Connect with a read-only account: a user granted SELECT (or READ) on what the agent should see, and nothing that writes. On Oracle this matters more than on the other engines; see the known limit under “Reads are read-only at the engine”.

Teradata connections

toil reaches Teradata through the Teradata Query Service, Teradata’s REST service for SQL over HTTPS: no driver, no native library, no bteq, nothing to install. Each request goes through toil’s own HTTP stack, so it uses HTTPS_PROXY and NO_PROXY, trusts the OS certificate store, and shows in --verbose and the audit log like any other HTTP integration.

Check your site has the Query Service. It is hosted for you on VantageCloud Enterprise and VantageCloud Lake. An on-premises VantageCore site has to install it, so ask whoever runs your Teradata whether it’s there, and for its URL and the system’s alias.

export TOIL_DB_ENGINE=teradata
export TOIL_DB_BASE_URL=https://teradata.imagile.dev:1443
export TOIL_DB_SYSTEM=prod
export TOIL_DB_USER=toil_reader
export TOIL_DB_PASSWORD=<the user's password>
export TOIL_DB_DATABASE=sales
export TOIL_DB_LOG_MECH=LDAP

With a token instead of a user and password:

export TOIL_DB_TOKEN=<the bearer token>

{
  "version": 2,
  "connections": {
    "warehouse": { "area": "db", "engine": "teradata", "baseUrl": "https://teradata.imagile.dev:1443", "system": "prod", "user": "toil_reader", "database": "sales" }
  },
  "defaults": { "db": { "connection": "warehouse" } }
}

toil connections login warehouse   # prompts for the password (or token) and stores it

The base path differs between Query Service releases and sites, which is why baseUrl is the whole of it: toil adds systems/<system>/sessions and systems/<system>/queries to the end. A 404 naming the system means the alias or the base path is wrong.

Connect as a read-only user. toil can’t make Teradata refuse writes: the database has no read-only transaction, and the Query Service has no read-only session. db query relies on the user, and toil doesn’t parse your SQL to decide what writes, so create a user that can only read:

CREATE USER toil_reader FROM sales_admin AS PERM = 0, PASSWORD = "<a strong password>";
GRANT SELECT ON sales TO toil_reader;

What toil does do is roll back. Every read runs in a session of its own, opened in ANSI mode with auto-commit off, and ends with ROLLBACK and the session closed (see “Reads are read-only at the engine”). Use a second connection, with a user that may write and writes: true, for db execute.

Hive and Impala connections

One provider covers both, because both speak HiveServer2’s Thrift protocol: Hive natively, and Impala on its HiveServer2 port. toil speaks it in plain JavaScript (the thrift runtime and the HiveServer2 client generated with hive-driver, both bundled into toil): no ODBC or JDBC driver, no beeline or impala-shell, nothing to install.

export TOIL_DB_ENGINE=hive
export TOIL_DB_HOST=hive.imagile.dev
export TOIL_DB_PORT=10000
export TOIL_DB_DATABASE=sales
export TOIL_DB_USER=toil_reader
export TOIL_DB_PASSWORD=<the account's LDAP password>
export TOIL_DB_SSL=true

{
  "version": 2,
  "connections": {
    "lake-hive": { "area": "db", "engine": "hive", "host": "hive.imagile.dev", "database": "sales", "user": "toil_reader", "ssl": true },
    "lake-impala": { "area": "db", "engine": "impala", "host": "impala.imagile.dev", "database": "sales", "user": "toil_reader", "ssl": true }
  },
  "defaults": { "db": { "connection": "lake-hive" } }
}

toil connections login lake-hive   # prompts for the password and stores it

Connect with a read-only account. toil can’t make Hive or Impala refuse a write. Neither engine has a read-only session or transaction, and neither has anything to roll back: a statement that writes has written by the time it returns. SQL Server has no read-only transaction either, but toil still rolls back every read there; on Hive and Impala there is nothing to roll back. toil doesn’t parse SQL to decide whether a statement writes, so db query on these engines is read-only only because the account is. Use an account whose role grants SELECT and nothing else: a Ranger policy, a Sentry role, or Hive’s SQL standard authorization (GRANT SELECT ON TABLE ... TO USER toil_reader). The account must also be refused Hive’s non-SQL commands (dfs, add, delete, compile, reload), which Ranger and SQL standard authorization refuse to anyone who isn’t an administrator. When the account is refused a write, toil reports WRITE_DISABLED; see “Reads are read-only at the engine” for what toil does when a write gets through.

Use a second connection, with an account that may write and writes: true, for db execute.

Kerberos isn’t supported. Signing in with Kerberos needs a native GSSAPI library, which toil doesn’t ship, so a cluster that accepts only Kerberos can’t be reached: it refuses toil’s sign-in with AUTH, saying the server expects Kerberos (the server’s own words are usually Unsupported mechanism type PLAIN). Many clusters run an LDAP HiveServer2 endpoint beside the Kerberos one (another port, or a gateway); point the connection at that. HiveServer2’s HTTP transport (hive.server2.transport.mode=http, often behind a gateway) isn’t supported either: toil connects to the binary port only. Nor is ZooKeeper service discovery: host names one HiveServer2 or one coordinator.

Cloud and private CAs

A TLS connection ("ssl": true) trusts Node’s bundled CAs, NODE_EXTRA_CA_CERTS and the OS certificate store. A managed database’s certificate is usually signed by its cloud’s own CA, which is in none of them, so the connection fails with a NETWORK error about an untrusted certificate. Add the CA by any one of three routes; none of them turns verification off.

1. For one connection: tlsCaFile. Download Amazon’s global bundle (https://truststore.pki.rds.amazonaws.com/global/global-bundle.pem) and point the connection at it with an absolute path. toil adds its certificates to what that connection trusts, for that connection only, and says so under --debug (TLS, trusting 108 certificate(s) from /etc/ssl/rds/global-bundle.pem). It works for PostgreSQL, MySQL and MariaDB, SQL Server, Hive and Impala; Oracle, Teradata and SQLite refuse it. The path must be absolute (on Windows, with a drive letter; no ~, no network or device path, nothing under ~/.toil), the file at most 1 MB, and PEM.

   export TOIL_DB_SSL=true
   export TOIL_DB_TLS_CA_FILE=/etc/ssl/rds/global-bundle.pem

   $env:TOIL_DB_SSL = "true"
   $env:TOIL_DB_TLS_CA_FILE = "C:\certs\global-bundle.pem"

   In the config file’s JSON, write the Windows path with forward slashes: "tlsCaFile": "C:/certs/global-bundle.pem".

2. For every toil and Node program you run: NODE_EXTRA_CA_CERTS. Set it to the path of the PEM file before starting toil (or the agent that starts toil mcp). Node reads it only at startup, so a change needs a new process, and an MCP client must be restarted to pick it up.

   • Windows (PowerShell): $env:NODE_EXTRA_CA_CERTS = "C:\certs\global-bundle.pem" for this session, or [Environment]::SetEnvironmentVariable("NODE_EXTRA_CA_CERTS", "C:\certs\global-bundle.pem", "User") to keep it, then open a new terminal.
   • macOS and Linux: export NODE_EXTRA_CA_CERTS=/etc/ssl/rds/global-bundle.pem, and put the line in your shell profile (~/.zshrc, ~/.bashrc) to keep it.

3. Machine-wide: the OS certificate store. Install the CA there (an administrator’s job on a managed machine) and every program that reads the store, toil included, trusts it.

Reaching a server by another name: tlsServerName

Sometimes the name you can reach the server by isn’t the name on its certificate: a VPN’s DNS alias, an SSH or SSM tunnel on 127.0.0.1, a proxy in front of a managed endpoint whose certificate names the canonical host. Set host to the address that works and tlsServerName to the name the certificate carries:

"app-pg": { "area": "db", "engine": "postgres", "host": "pg-alias.imagile.dev", "tlsServerName": "app.cluster-abc12345.us-east-1.rds.amazonaws.com", "database": "app", "user": "reader", "ssl": true }

toil opens the socket to host and port, sends tlsServerName as the TLS server name, and verifies the certificate’s chain and that it names tlsServerName. Nothing is relaxed: a certificate that doesn’t name it is refused exactly as one that doesn’t name host would be, and there is no setting that skips the name check. It needs "ssl": true, and takes a DNS name, never an IP address, wildcard, scheme or port. PostgreSQL, MySQL and MariaDB, SQL Server, Hive and Impala support it. Oracle, Teradata and SQLite refuse it. --debug shows TLS, verifying <name>.

Azure SQL: with tlsServerName, use the Proxy connection policy. Under the default Redirect policy the gateway sends the client to a worker node on another address, and tedious verifies that node’s certificate against tlsServerName too, so the redirect is refused (it fails closed, with a certificate error) rather than trusted. That redirect is also the one case where SQL Server’s socket goes to an address other than host.

Commands

Every command takes --connection <name>. --schema defaults to the engine’s default schema (main for SQLite, public for PostgreSQL, the connection’s database for MySQL, where a schema is a database, dbo for SQL Server, and the user’s own schema for Oracle, the connection’s database or else the user’s own for Teradata, and the connection’s database, else default, for Hive and Impala). PostgreSQL names are case-sensitive as stored: an unquoted CREATE TABLE People is stored as people. MySQL table names are case-sensitive on most Linux servers. Oracle stores an unquoted name in upper case and reports names as stored (PEOPLE, ID); a schema or table you name that doesn’t exist as written is looked up again in upper case, so table describe people finds PEOPLE. A quoted mixed-case name ("People") must be typed exactly. Teradata compares names without regard to case, as it does itself, and reports them as stored. Hive and Impala store names in lower case, and a name you type is looked up in lower case.

Rows come back as arrays in the order of columns, so a join’s two id columns both survive:

{ "columns": [{ "name": "id", "type": "INTEGER" }, { "name": "name", "type": "TEXT" }],
  "rows": [[1, "Ada"], [2, "Grace"]] }

type is the column’s declared type, or null for an expression. Integers too large for JavaScript come back as strings, and binary values as base64.

PostgreSQL names every result column’s type (integer, character varying(100), timestamp with time zone). numeric comes back as a string, so no digits are lost; json and jsonb as JSON; arrays as arrays. Dates, times, timestamps and intervals come back as the text the server sent, never shifted into the local time zone.

MySQL names each result column’s type from the protocol (INT, BIGINT, VARCHAR, DECIMAL, DATETIME), without lengths; table describe gives the full column type (varchar(100)). DECIMAL comes back as a string; dates, times and timestamps as the text the server sent; JSON as JSON; BLOB, BINARY and BIT as base64. table preview orders a table by its primary key, so pages stay stable. In table describe, a foreign key’s NO ACTION and RESTRICT both read as null: InnoDB enforces them the same way, and MariaDB reports an unstated rule as RESTRICT. SQL Server names every result column’s type as you’d write it in DDL (int, varchar(100), nvarchar(max), decimal(10,2), datetime2(7)). bigint comes back as a number when it fits and a string when it doesn’t; bit as true or false; uniqueidentifier as a string; binary as base64. date, time, datetime, datetime2 and smalldatetime come back as the text of the value the server holds, never shifted into a time zone (2026-01-02 03:04:05.123), to the millisecond: digits past that are dropped. datetimeoffset comes back as the instant in UTC (2026-01-02T01:04:05.000Z), without its original offset. decimal, numeric and money come back as JSON numbers, so a value with more than about 15 significant digits loses the rest; CAST it to varchar in the query when every digit matters. table preview pages in primary-key order, or in the engine’s own order for a table without one.

Oracle names each result column’s type (NUMBER, VARCHAR2, DATE), and table describe gives the declared form (NUMBER(10,2), VARCHAR2(100)). A NUMBER is a JSON number when that’s exactly its value, and a string otherwise, so no digits are lost. CLOB comes back as text, BLOB and RAW as base64, BOOLEAN as a boolean, and BINARY_DOUBLE’s NaN and infinities as strings. Dates and timestamps come back exactly as stored, in the shape PostgreSQL and MySQL use, never through the local time zone of the machine running toil: DATE as 2026-01-02 03:04:05, TIMESTAMP with every fractional digit it holds (2026-01-02 03:04:05.123456), and TIMESTAMP WITH TIME ZONE with its offset (2026-01-02 03:04:05+02, +05:30). A value stored with a named region (Europe/Paris) and TIMESTAMP WITH LOCAL TIME ZONE come back as the same instant in UTC (+00).

Teradata reports each result column’s type as the Query Service names it, and table describe gives the declared form from DBC.ColumnsV (VARCHAR(100), DECIMAL(10,2), TIMESTAMP(6)). A BIGINT too large for JavaScript, and a DECIMAL or NUMBER with more than 15 significant digits, come back as strings, so no digits are lost; dates and timestamps come back as the text the service sends. table describe takes the primary key from a PRIMARY KEY constraint, or, when there is none, from a unique primary index, which is what Teradata makes of a PRIMARY KEY declared without a primary index; the primary index is listed with the other indexes, named primary index when it has no name. A foreign key’s columns come from DBC.All_RI_ChildrenV, and Teradata has no ON UPDATE or ON DELETE actions, so both are null. table preview pages in primary-key order, or in the engine’s own order for a table without one.

Hive and Impala name each result column’s type as HiveQL writes it (INT, BIGINT, VARCHAR(100), DECIMAL(10,2), TIMESTAMP), and an untyped NULL as null. BIGINT comes back as a number when it fits and a string when it doesn’t; DECIMAL as a string, so no digit is lost; BOOLEAN as true or false; BINARY as base64. TIMESTAMP and DATE come back as the text the server sent (2026-01-02 03:04:05.123456), never shifted into a time zone, and ARRAY, MAP and STRUCT as the server’s JSON-like text ([1,2], {"k":1}), not parsed. In table describe, indexes is always empty, because neither engine has indexes; the primary and foreign keys are the informational ones the table declares (DISABLE NOVALIDATE), which neither engine enforces, so onUpdate and onDelete are null. Neither reports NOT NULL or DEFAULT through the protocol, so nullable is true and default null for every column outside the primary key. Impala reports no keys for an Iceberg table. Hive renames a second result column with the same name (id, id_1); Impala keeps both names. table preview reads with a LIMIT that covers the page, in the engine’s own order: a table with no ORDER BY can come back in a different order next time, so use db query with ORDER BY to page through a large one.

Paging and row caps

table preview and query return 100 rows per page unless you pass --limit (1 to 1000). When there are more, meta.page.nextCursor holds a cursor; pass it back with --cursor, along with the same SQL or table, to get the next page. A cursor only works with the query it came from. Use ORDER BY in a query you page through, so the pages are stable. On Teradata the Query Service skips and caps the rows itself (row_offset and row_limit), so each page runs the query again.

Pre-flight lint

Before db query or db execute sends anything, toil checks the SQL against the connection’s engine, with no network and no model. An error refuses the statement before a connection is even opened, with a fix in that engine’s own form; a warning lets it run and lands in meta.warnings (code LINT). meta.lint names the engine and every rule that ran, on success and failure alike.

Every finding has its rule, a message, a fix, and its position in the SQL: offset from the start, and 1-based line and column. An error’s error.details.lint lists them.

--no-lint (lint: false over MCP) skips the warnings for one call. Errors always run: --no-lint can’t send a write through db query, and it can’t send SQL the engine would refuse anyway.

toil db lint <sql> runs the same lint without running anything, and returns the findings as data. With --engine, it uses no connection at all: none is looked up and no secret is read, so it works with nothing set up. Without it, it takes the engine from the connection. --as execute checks the SQL as db execute would (a script of changes) instead of as db query would (the default).

toil db lint "SELECT TOP 10 * FROM people" --engine postgres

The lint is a check, not a guard. It refuses a write on db query a little earlier, but what keeps a read read-only is the engine, below, and that still holds for any SQL the lint lets through. A table’s size isn’t known locally, so the lint has no rule for a missing WHERE on a large table.

Reads are read-only at the engine

db query, table preview and the listing commands open SQLite read-only with PRAGMA query_only on. A statement that tries to write fails with WRITE_DISABLED, whatever the connection’s writes setting. db query runs exactly one statement; SQL holding several is refused with INVALID_INPUT.

VACUUM is refused on every read with INVALID_INPUT, before it reaches SQLite. VACUUM INTO '<file>' creates its target file even on a read-only connection, so reads never get the chance. To vacuum or to write a copy, use db execute with writes on; it runs a lone VACUUM outside the usual transaction, because SQLite can’t vacuum inside one, and refuses VACUUM mixed with other statements.

One known gap remains: ATTACH DATABASE from a read-only query can open another existing SQLite file for reading (it can’t create one). If the files a connection can reach matter, run toil as a user that can read only what it should.

PostgreSQL runs every read in a transaction begun with SET TRANSACTION READ ONLY and rolled back, on a connection opened for that one call. The server refuses any write (INSERT, UPDATE, DDL, nextval, a data-modifying WITH) with SQLSTATE 25006, which comes back as WRITE_DISABLED. db query sends its statement on the extended protocol, which refuses a second one, so several statements are INVALID_INPUT here too.

COPY and DO are refused on every read with INVALID_INPUT, before they reach the server: COPY ... TO '<file>' writes a file on the server, and TO PROGRAM runs one, even in a read-only transaction, for a role allowed to, and a DO block can run the same COPY. Neither returns rows a query could page. To run them, use db execute.

Known limit: functions with effects outside the database still run in a read-only transaction for a role allowed to call them. lo_export writes a server file, pg_terminate_backend ends sessions, pg_reload_conf reloads the server’s config, and an extension such as dblink can reach another database. They can’t be refused by name, so connect with an ordinary role: not a superuser, and not a member of pg_write_server_files, pg_execute_server_program or pg_signal_backend.

MySQL and MariaDB run every read on a session set to SET SESSION TRANSACTION READ ONLY, inside START TRANSACTION READ ONLY, rolled back, on a connection opened for that one call. Both are needed: DDL commits the transaction implicitly, and the session setting makes the server refuse it all the same. Any write (INSERT, UPDATE, DDL, LOAD DATA) fails with error 1792, which comes back as WRITE_DISABLED. db query sends one statement with multi-statements off; several are INVALID_INPUT.

SELECT ... INTO OUTFILE, INTO DUMPFILE and every SET are refused on reads with INVALID_INPUT, before they reach the server: INTO OUTFILE writes a server file even in a read-only transaction, for an account with FILE, and SET GLOBAL or SET PERSIST changes the server’s configuration. To run them, use db execute.

Known limits: a stored procedure that commits and switches its own session to read-write can still write when a read CALLs it, and administrative statements that aren’t writes (KILL, FLUSH, SHUTDOWN) run for an account allowed to. Neither can be refused by name without refusing ordinary reads, so connect with an account that has SELECT only: no FILE, SUPER, SYSTEM_VARIABLES_ADMIN or CONNECTION_ADMIN, and no EXECUTE on procedures that change data. SQL Server has no read-only transaction. toil runs every read in a transaction, on a connection opened for that one call, and always rolls it back, so nothing a read does is kept. The server reports the type of every statement it runs, and a read whose statement the server reports as changing data or schema (INSERT, UPDATE, DELETE, MERGE, SELECT INTO, CREATE, ALTER, DROP, TRUNCATE, UPDATE STATISTICS, including inside EXEC) comes back as WRITE_DISABLED. With a read-only login the server refuses the write itself, which comes back as WRITE_DISABLED too. db query returns one result: SQL that returns a second result is INVALID_INPUT. When the first result fills the page, toil stops the batch there with a TDS attention, so anything after it in the same SQL never runs.

COMMIT, ROLLBACK, BEGIN TRANSACTION and SAVE TRANSACTION are refused on every read with INVALID_INPUT, before they reach the server: a COMMIT would end the transaction toil rolls back, and keep the write before it. An OUTPUT clause is refused on reads too: an INSERT, UPDATE, DELETE or MERGE with OUTPUT sends its rows before the server reports the change, so a full page could stop it before toil learns it was a write. A column or alias named output in a plain SELECT is fine; in SQL that also holds one of those words, bracket-quote it as [output]. A read-only login’s refusal of a permission that only reads (a sys.dm_db_* view needing VIEW DATABASE STATE, SHOWPLAN) comes back as DATABASE, naming the permission.

Known limit: transaction control inside dynamic SQL or a procedure (an EXEC whose text holds a COMMIT) can’t be refused by name, and neither can an OUTPUT clause there (EXEC('UPDATE ... OUTPUT ...')): its changed rows can fill a page and come back as an ordinary result, though the change is still rolled back. A write under SET NOCOUNT ON isn’t reported by the server. toil notices the transaction ending and says so with DATABASE (“anything it changed may have been saved”), but by then the change is made. That is why a SQL Server connection for reads should use a read-only login: with one, the server refuses the write whatever the SQL does.

Oracle runs every read in a transaction begun with SET TRANSACTION READ ONLY and rolled back, on a connection opened for that one call. The server refuses INSERT, UPDATE, DELETE, MERGE and SELECT ... FOR UPDATE with ORA-01456, which comes back as WRITE_DISABLED, as do a read-only user or session (ORA-28193) and a database open read-only (ORA-16000). db query runs one statement; several are INVALID_INPUT.

That transaction doesn’t hold everything, so some statements are refused on every read before they reach the server:

• DDL (CREATE, ALTER, DROP, TRUNCATE, RENAME, GRANT, REVOKE, COMMENT, ANALYZE, PURGE, FLASHBACK, NOAUDIT, ASSOCIATE, DISASSOCIATE) is WRITE_DISABLED: Oracle commits before and after DDL, so it runs, and stays, inside a read-only transaction.
• PL/SQL (BEGIN, DECLARE, CALL, and WITH FUNCTION or WITH PROCEDURE) is INVALID_INPUT: a block can COMMIT, EXECUTE IMMEDIATE DDL, or run an autonomous transaction, and CALL reaches built-ins such as DBMS_UTILITY.EXEC_DDL_STATEMENT.

To run any of them, use db execute.

Known limit: a stored function that runs an autonomous transaction (PRAGMA AUTONOMOUS_TRANSACTION) still writes when a SELECT calls it, and a function can’t be refused by name. Connect with an account that can’t write: then nothing a query calls can either. AUDIT isn’t refused either: it needs the audit privileges an ordinary account doesn’t have. A trigger or stored function with an autonomous transaction commits on its own; toil can’t see it, so “nothing was applied” after a failed db execute covers only the script’s own transaction.

Teradata has no read-only transaction, and the Query Service has no read-only session, so toil can’t enforce read-only on Teradata, as on SQL Server: db query relies on a read-only user (see “Teradata connections”). What toil adds is a rollback. Each read opens an explicit Query Service session of its own with transaction_mode ANSI and auto_commit off. In ANSI mode a transaction stays open from the first statement until a COMMIT or ROLLBACK, and logging off rolls back whatever is still open, so toil ends every read with ROLLBACK and closes the session: anything the read changed is undone. A result the service reports as an update count instead of rows (an INSERT, UPDATE, MERGE, DDL, a macro or procedure that writes) comes back as WRITE_DISABLED, rolled back with the rest, and so does a read-only user’s refusal of a write (“does not have INSERT access”). db query runs one statement; several, or SQL that returns more than one result, are INVALID_INPUT.

COMMIT, ROLLBACK, ABORT, BT, ET, BEGIN TRANSACTION and END TRANSACTION are refused on every read with INVALID_INPUT, before anything is sent: each would end the transaction toil rolls back.

Known limits: this is built from the session settings Teradata documents and has been tested only against a stubbed Query Service, never a live one. A stored procedure can COMMIT inside itself in ANSI mode, and work outside the transaction (an external procedure, a CALL that reaches another system) isn’t undone by a rollback. ANSI mode also changes some semantics from Teradata mode, the default in tools such as BTEQ: for example how string comparisons treat case, and whether a value too long for its column is an error or silently truncated. A query that answers differently here than in your usual tool may be meeting that difference.

Hive and Impala have no read-only mode, and toil can’t enforce one on them: reads there are read-only only because the account is. That is why their connections require a read-only account (see “Hive and Impala connections”). Unlike SQL Server, where toil still rolls back every read, there is nothing to roll back: a statement that writes has written when it returns. Within that:

• A write the account is refused (INSERT, UPDATE, DELETE, DDL; Hive’s SQL standard authorization, Ranger and Impala each word the refusal their own way) comes back as WRITE_DISABLED, and nothing was changed.
• The pre-flight lint refuses the obvious write on db query before anything is sent (the read-only-write rule, which also knows Hive’s LOAD, MSCK, EXPORT, IMPORT, ANALYZE, KILL, ABORT and multi-insert FROM ... INSERT, and Impala’s UPSERT, LOAD, COMPUTE STATS, INVALIDATE METADATA and REFRESH). It isn’t a guard: toil doesn’t parse SQL to decide whether it writes.
• A read the server reports as having changed rows comes back as DATABASE, saying the change has been made: Hive 4.0 and later report the rows a statement changed when it finishes (Hive 3.1 doesn’t, so there it goes unreported), and Impala when it’s closed. With a read-only account this can’t happen.

db query runs exactly one statement; SQL holding several is refused with INVALID_INPUT before anything is sent. A trailing semicolon and comments are fine.

Writes

db execute is the only command that changes data. It needs both:

1. writes: true on the connection (TOIL_DB_WRITES=true, or TOIL_CONN_<NAME>_WRITES=true), set by the person who owns the config, and
2. --allow-write on the call (allowWrite: true over MCP).

Without either it fails with WRITE_DISABLED and changes nothing. It runs one or more statements, separated by semicolons, in one transaction that commits only if every statement succeeds. A script that starts or ends a transaction of its own (BEGIN, COMMIT, END, ROLLBACK, SAVEPOINT, RELEASE, and for PostgreSQL also START, ABORT and PREPARE TRANSACTION; for MySQL also START TRANSACTION, XA, LOCK and UNLOCK TABLES, and SET autocommit) is refused with INVALID_INPUT before any of it runs.

On MySQL and MariaDB, DDL (CREATE, ALTER, DROP, TRUNCATE) commits implicitly, so a script holding DDL isn’t all or nothing: what ran before the DDL is committed even if a later statement fails. Keep DDL in a script of its own. A server running read_only, or an account whose transactions default to read-only, fails writes with DATABASE, saying so. On SQL Server that means BEGIN TRANSACTION, COMMIT, ROLLBACK and SAVE TRANSACTION; BEGIN ... END blocks, TRY ... CATCH and CASE are fine. A SQL Server script runs as one batch, so leave out GO, and a statement that must start a batch (CREATE VIEW, CREATE PROCEDURE) goes in a call of its own. changes counts the rows INSERT, UPDATE, DELETE, MERGE and SELECT INTO reported; under SET NOCOUNT ON the server reports none.

On PostgreSQL, statements that can’t run inside a transaction (VACUUM, CREATE DATABASE, CREATE INDEX CONCURRENTLY) fail with DATABASE; run those with your own tools. A connection that reaches a read-only standby, or a role whose transactions default to read-only, fails writes with DATABASE, saying so.

On Oracle, a script’s statements are separated by semicolons, and a PL/SQL unit (a BEGIN or DECLARE block, CREATE FUNCTION, PROCEDURE, PACKAGE, TRIGGER or TYPE) runs to a line holding only /, as in SQL*Plus, or to the end of the script. COMMIT, ROLLBACK, SAVEPOINT and SET TRANSACTION are refused before any of it runs. Two things Oracle does break all-or-nothing, and toil can’t stop them: DDL commits everything before it, and itself, as it runs, and a PL/SQL block can commit inside. When a script fails after Oracle has committed part of it (a DDL statement that ran, or failed after parsing), the error says which statements stayed committed (Oracle committed statements 1 to 2 of 4). After a PL/SQL block or a CALL ran, toil can’t see whether it committed, so the error says the statements up to the failure may have committed; check before running the script again. Only when nothing in the script could have committed does it say nothing was applied (an autonomous-transaction trigger or function is the exception; see the known limit above). Keep DDL in a script of its own, or first.

On Teradata, the script is sent as one request in the call’s own ANSI-mode session, and toil sends COMMIT only after every statement succeeded; otherwise it sends ROLLBACK. Several statements in one request are a Teradata multi-statement request, and the database runs them as one. Teradata doesn’t allow DDL in a multi-statement request, so a DDL statement goes in a call of its own. BT, ET, BEGIN TRANSACTION, END TRANSACTION, COMMIT, ROLLBACK and ABORT are refused before any of it runs, anywhere in the script, a procedure body included; a temporary table’s ON COMMIT PRESERVE ROWS or ON COMMIT DELETE ROWS is fine. changes adds up the update counts the service reports.

On Hive and Impala, db execute runs one statement per call. Neither engine has a transaction to hold several together, so a script that failed halfway would leave its first half applied; SQL holding more than one statement is refused with INVALID_INPUT before any of it runs. Send each statement in its own call, in order. Hive can UPDATE, DELETE and MERGE only transactional (ACID) tables, and Impala only Iceberg and Kudu tables; elsewhere the server refuses them with DATABASE. changes is the row count the server reports: Hive’s for the statement (from 4.0; Hive 3.1 reports none, so it’s 0 there), Impala’s from closing it. For an Iceberg UPDATE, which Impala counts as rows deleted and written again, it’s the rows updated.

Time limits and cancelling

Every command stops at TOIL_TIMEOUT_MS (default 120000) with TIMEOUT, and on Ctrl+C or an MCP cancel with CANCELLED. The answer comes back straight away. SQLite can only be stopped between rows, though: a statement that computes for a long time before producing its first row keeps running in the background until it finishes, and the CLI process exits only then. Prefer LIMIT and indexed filters on large tables.

PostgreSQL stops the statement on the server: on a time limit or a cancel, toil asks the server to cancel it (pg_cancel_backend, from a second short connection), so a long sort or pg_sleep doesn’t keep running after toil has answered. db query reads rows a batch at a time from a server-side portal, so the server stops producing rows once the page is full.

MySQL stops the statement on the server too, with KILL <id> from a second short connection. That ends the whole session, so a cancelled db execute rolls back and runs none of its remaining statements. Its protocol has no portal, so db query reads rows as they stream and, once the page is full, drops the connection; the server stops at the next row it tries to send. SQL Server stops the statement on the server too: on a time limit or a cancel, toil sends a TDS attention, which ends the running batch, and the read’s transaction is rolled back when its connection closes.

Oracle is sent a break (connection.break()) on a time limit or a cancel, which stops the statement on the server at its next check: a long join or sort stops at once. Thin mode’s break travels in the data stream, so a session parked in DBMS_SESSION.SLEEP hears it only when the sleep ends; toil still answers straight away. db query reads rows a batch at a time from a server-side cursor.

Teradata is asked to stop the statement too: on a time limit or a cancel, toil lists the queries running in the call’s session (GET /systems/<system>/queries?session=<id>), asks the Query Service to abort each one (DELETE /systems/<system>/queries/<id>), and then closes the session, which rolls back anything still open. Those requests go out after toil has already answered, each with a 10-second limit of its own. Each page is answered in one response, so there is no server-side cursor to stop early. Known limit: a cancel that lands while the session is still being opened leaves toil without its id, so it can’t close it; the session counts against the user’s session limit until the Query Service’s idle timeout closes it.

Hive and Impala are sent CancelOperation for the running statement on a time limit or a cancel, which stops it on the server (Hive kills its job), and the session is closed. The cancel goes out on a second connection as well as the call’s own, because the server answers one request per connection at a time and the call’s own may be waiting on a fetch. db query fetches rows a batch at a time and closes the statement once the page is full, so the server stops producing more.

Examples

toil db table list
toil db table describe people
toil db table preview people --limit 20
toil db query "SELECT id, name FROM people WHERE active = 1 ORDER BY id" --limit 50
toil db query "SELECT id, name FROM people WHERE active = 1 ORDER BY id" --limit 50 --cursor <meta.page.nextCursor>
toil db execute "UPDATE people SET active = 0 WHERE id = 7" --allow-write --connection scratch

Over MCP:

{ "name": "db_query", "arguments": { "sql": "SELECT id, name FROM people ORDER BY id", "limit": 50 } }

Errors