Connections Guide

Reusable connections and other resources

A database connection is a resource: a value a plugin owns, which Ibex code can bind, pass to functions, return from them, and close, but never look inside. One connection serves many queries, so a script logs in once and keeps session state such as temporary tables between statements.

Back to Docs I/O guide: ADBC

Overview

One connection, many queries

`adbc::read`: one-off reads

Opens a connection, runs one query, reads the result and closes the connection. Each call starts fresh: nothing one call creates on the database connection is visible to the next.

`adbc::connect`: a reusable connection

Opens a connection that stays open until it is closed or its last binding goes away. adbc::query runs a query on it and returns a table, adbc::execute runs a statement, adbc::write writes a table into the database, adbc::begin / adbc::commit / adbc::rollback group them into a transaction, and adbc::close closes it.

import "adbc";

let db = adbc::connect("sqlite", ":memory:");
adbc::execute(db, "create table trades (symbol text, qty integer, px real)");
adbc::execute(db, "insert into trades values ('AAPL', 10, 150.0), ('MSFT', 5, 300.0)");

// A query result is an ordinary table operand.
let notional = adbc::query(db, "select symbol, qty, px from trades")[
    select { notional = sum(qty * px) }, by symbol
];

// Write an Ibex table back: create, append, replace or create_append.
adbc::write(db, notional, "notional");

adbc::close(db);   // 1 the first time, 0 after

examples/adbc_connection.ibex is a runnable version, including the functions below. Install the SQLite driver once with scripts/install_adbc_driver.sh sqlite; see the I/O guide for drivers, URIs and options.

The API

What `import "adbc"` declares

extern type adbc::Connection from "adbc.hpp";

extern fn adbc::connect(driver: String, uri: String, options: String = "")
    -> adbc::Connection from "adbc.hpp";
extern fn adbc::query(mutable db: adbc::Connection, sql: String,
                     params: DataFrame = Table {}) -> DataFrame
    from "adbc.hpp";
extern fn adbc::execute(mutable db: adbc::Connection, sql: String,
                       params: DataFrame = Table {}) -> Int
    from "adbc.hpp";
extern fn adbc::write(mutable db: adbc::Connection, df: DataFrame, table: String,
                     mode: String = "create") -> Int from "adbc.hpp";
extern fn adbc::tables(mutable db: adbc::Connection) -> DataFrame from "adbc.hpp";
extern fn adbc::table_schema(mutable db: adbc::Connection, table: String,
                            schema: String = "", catalog: String = "") -> DataFrame
    from "adbc.hpp";
extern fn adbc::begin(mutable db: adbc::Connection) -> Int from "adbc.hpp";
extern fn adbc::commit(mutable db: adbc::Connection) -> Int from "adbc.hpp";
extern fn adbc::rollback(mutable db: adbc::Connection) -> Int from "adbc.hpp";
extern fn adbc::close(mutable db: adbc::Connection) -> Int from "adbc.hpp";

`adbc::connect(driver, uri, options)`	Open a connection. `driver` and `options` work as for `adbc::read`; `stmt.` options apply to every query on the connection.
`adbc::query(db, sql, params)`	Run SQL on the connection and return the result as a table.
`adbc::execute(db, sql, params)`	Run a statement that returns no rows (DDL, `insert`, `update`, `delete`). Returns the affected-row count, or `-1` when the driver reports none.
`params`	Optional. A table whose columns bind, by position, to the statement's placeholders in the driver's syntax (`?` for SQLite and MySQL, `$1` for PostgreSQL). The statement is prepared once and runs once per row: a query's results follow one another in row order, and `adbc::execute` returns the summed count. A table with no rows runs nothing. SQLite rejects a column count that does not match the placeholders; PostgreSQL ignores extra columns. DuckDB's driver binds one row per execution, so there Ibex runs the rows one by one itself, with the same result.
`adbc::write(db, df, table, mode)`	Bulk-insert `df` into `table` and return the rows written. `mode`: `create` (default; the table must not exist), `append`, `replace` or `create_append`. `df` is any table expression, but a connection call in it must be bound with `let` first.
`adbc::tables(db)`	The tables and views the connection sees: one row each, with `catalog`, `schema`, `table` and `type`. See Discovery.
`adbc::table_schema(db, table, schema, catalog)`	One row per column of `table`, without running your query: `column`, `arrow_type`, `ibex_type`, `nullable` and `reason`. An empty `schema` or `catalog` (the default) means the driver's default.
`adbc::begin(db)`	Start a transaction; returns `1`. An error when one is already open: transactions do not nest.
`adbc::commit(db)`	Commit the transaction and return to autocommit; returns `1`. An error when none is open, and when a statement in it failed: then the transaction is rolled back instead (see Transactions).
`adbc::rollback(db)`	Discard the transaction and return to autocommit. Returns `1`, or `0` when none was open.
`adbc::close(db)`	Close the connection for every binding of it. Returns `1` the first time and `0` after; a query on a closed connection is an error.

extern type declares a nominal resource type: two resource types are never interchangeable, and a resource is not a scalar or a table. It cannot be compared, printed as data, used in arithmetic, or stored in a column. The type of each resource argument is checked before the call.

Bindings

Binding, aliasing and closing

Aliases share one connection

let db = adbc::connect("sqlite", ":memory:");
let alias = db;          // same connection
adbc::close(alias);       // 1
adbc::close(db);          // 0: already closed

The last binding closes it

A connection lives while any binding holds it. Rebinding the last name to something else, or ending the session or script, releases it without an explicit adbc::close. A failed rebinding (let db = no_such_table;) keeps the old connection.

A resource binding takes no type annotation: its type is the declared return type of the call that produced it.

Discovery

What is in the database, and how Ibex will read it

Both return ordinary tables, so they can be filtered, ordered and joined like any other.

adbc::tables(db)[filter schema == "public", order table];
adbc::table_schema(db, "events")[filter ibex_type is null];

`adbc::tables`

One row per table and view, as the driver reports them. SQLite has catalogs (main, temp, attached databases) and an empty schema; PostgreSQL has one catalog, the database it is connected to, and its schemas, pg_catalog and information_schema included. MySQL has no schemas: each database is a catalog, with an empty schema.

`adbc::table_schema`

ibex_type is the type a query would give the column, decided by the same importer queries use (Int64, Decimal(12, 2), ...). When there is none it is null and reason says why, with the SQL cast that reads the column: the same message the query would fail with, found before running it. nullable is false when the driver reports the column NOT NULL, and true when it may hold nulls or the driver does not say.

What each driver reports

Both functions pass on what the driver reports, unchanged. A database with a catalog of declared types and constraints can report them; the drivers tested so far report less, by design of the drivers (ADBC 1.12, DuckDB 1.5.6, ADBC Driver Foundry MySQL 0.6.1 against MariaDB 11). Only MySQL reports NOT NULL.

Driver	Column types	`nullable`	Schema names
SQLite	SQLite columns have no fixed type, so the driver guesses from the first 64 rows of `SELECT *`, both here and in queries: every column starts as `int64` and widens to `float64` or `utf8` as values need. An empty table reads as all `int64`; a `CAST` in the query fixes a type.	Always `true`.	`""`: SQLite has catalogs (`main`, `temp`, attached databases) and one unnamed schema in each.
PostgreSQL	The declared types, from `pg_attribute`; the table is not read.	Always `true`: the driver does not read `NOT NULL`.	The schemas of the connected database, `pg_catalog` and `information_schema` included.
DuckDB	The declared types, from a `SELECT * … LIMIT 0`: no rows are read, and `DECIMAL(12, 2)` reads as `Decimal(12, 2)`.	Always `true`: DuckDB marks every result column nullable.	`main` and any others, in catalog `memory` for an in-memory database (the file's name otherwise). Tables have type `BASE TABLE`, as in `information_schema`, not `table`.
MySQL	The declared types: `DECIMAL(12, 2)` reads as `Decimal(12, 2)`, and `BOOLEAN`, which MySQL stores as `TINYINT(1)`, as `Int64`.	Reported: `false` for a `NOT NULL` column.	`""`: every database is a catalog, the system databases (`information_schema`, `mysql`, …) included. Tables have type `BASE TABLE`.

Transactions

All of the statements, or none

A connection runs in autocommit: each statement is kept as soon as it has run. Between adbc::begin and adbc::commit, queries, statements and writes on the connection are one transaction. Other connections see none of it until the commit, and adbc::rollback discards it.

fn reload(mutable db: adbc::Connection, rows: DataFrame) -> Int {
    adbc::begin(db);
    adbc::execute(db, "delete from positions");
    adbc::write(db, rows, "positions", "append");
    adbc::commit(db);
}

A failure dooms the transaction

When any query, statement or write in a transaction fails, adbc::commit rolls the transaction back and reports an error; nothing is committed. PostgreSQL behaves this way on its own (and would report the COMMIT as a success); SQLite and MySQL would keep the statements that worked. Ibex applies the rule on every driver, so a script means the same everywhere. A failure before adbc::begin does not count.

Closing never commits

adbc::close, the last binding going away, or the end of the session or script rolls back an open transaction. A script that stops on an error inside a function like reload leaves the transaction open on the caller's connection until one of those, or an explicit adbc::rollback. The adbc.connection.autocommit option is refused unless it is true: use adbc::begin.

MySQL commits the open transaction whenever it creates, alters or drops a table, and goes on in autocommit. Ibex cannot see that in SQL passed to adbc::execute, so keep DDL out of a MySQL transaction; adbc::write refuses its create, replace and create_append modes there.

Functions

Passing, opening and returning connections

A fn can take a resource parameter, open resources of its own, and return one. Any function that takes, returns or (directly or through other functions) calls something that uses a resource is a resource function, and follows the placement rules below.

Take the caller's connection

fn positions(mutable conn: adbc::Connection) -> DataFrame {
    adbc::query(conn, "select symbol, sum(qty) as qty
                      from trades group by symbol");
}

positions(db)[filter qty > 5];

The argument is shared, not moved: the function uses the caller's connection and its session state, and db stays usable afterwards.

Open one locally

fn table_count(uri: String) -> DataFrame {
    let scratch = adbc::connect("sqlite", uri);
    adbc::query(scratch, "select count(*) as n
                         from sqlite_master");
}

A connection the function opens is closed when the call ends, whether it returns normally or fails. Calling it in a loop of statements does not accumulate open connections.

Return a connection

fn open_warehouse(user: String) -> adbc::Connection {
    adbc::connect("postgresql",
                 "postgresql://${user}@warehouse/prod");
}

let wh = open_warehouse("analyst");

The last expression may be a resource. Its type is checked against the declared return type, and the caller takes a share of it before the function's own bindings are released.

Pass it explicitly

let db = adbc::connect("sqlite", ":memory:");

fn broken() -> DataFrame {
    adbc::query(db, "select 1 as x");
}
broken();
// error: function 'broken' uses resource 'db', which is
// bound outside it; pass it as a parameter

A function sees only the resources passed to it and those its body binds, so which connections a call can touch is visible at the call site.

Placement

Where resource functions can be called

Resource functions run one statement at a time, in source order, before the rest of the statement. That is only possible where the call is evaluated once, so a resource function can be called:

`let t = adbc::query(db, "...");`	As a statement's value.
`adbc::query(db, "...")[filter x > 1]`	As a table operand: the base of a block or a side of a join.
`adbc::query(adbc::connect(...), "...")`	As an argument of another call. A connection opened this way is closed at the end of the statement.
`adbc::write(db, adbc::query(db, "...")[filter x > 1], "t")`	In a table argument, as its value or a table operand. The query runs first, as if bound with `let` just before the statement. A scalar argument cannot call a resource function; bind the result with `let`.

It cannot be called inside a query clause (filter, select, update, aggregates, windows) or inside a larger expression, because those run per row or per group. This also covers a helper function that only reaches a connection through other functions. The statement is rejected before any of its resource functions run, so a misplaced call never half-executes:

fn remote_count(uri: String) -> Int {
    let db = adbc::connect("sqlite", uri);
    scalar(adbc::query(db, "select count(*) as n from trades"), "n");
}

trades[update { n = remote_count(uri) }];   // rejected

let n = remote_count(uri);                  // fine: once, at statement level
trades[update { n = ^n }];

A query result is read in full before the statement uses it: a table operand from adbc::query is a materialized table, not a stream from the database. Push filters and aggregations into the SQL when the result would be large.

Lifetime

When a connection is released

`let alias = db`	Another owner of the same connection; nothing new is opened.
Pass `db` to a function	The parameter shares the connection for the duration of the call.
Connection opened in a function	Released when the call ends, normally or with an error, in reverse order of binding.
Connection returned from a function	Owned by the caller's binding.
Rebinding or shadowing a name	The new value is evaluated first; on success the old connection loses that owner, on failure it is kept.
Unbound temporary	Released at the end of its statement.
REPL session or script	Top-level bindings are released when the session or script ends.
`adbc::close(db)`	Closes the connection for every owner at once; later queries on any alias fail.

Release never commits anything on its own. Outside an adbc::begin each statement runs in the driver's default (usually autocommit) mode; a transaction still open when its connection is released is rolled back.

Limits

Not supported yet

Language and tools

In ibex_compile, a function that takes, opens or returns a connection must end in a table, a connection name, or a call, and cannot have a scalar let in its body; it sees the program's constants but not its tables or connections. Decimal and column parameters are not compiled either.
Scripts that call resource functions run statement by statement rather than through the whole-script planner, so their queries are not optimized across statements.
No resource-typed let annotations, columns, or default parameter values.
mutable on a resource parameter documents intent but is not enforced.

ADBC

One active query per connection; a second concurrent use reports the connection as busy.
No connection pooling. Ctrl+C in the REPL cancels a running query or statement on the server (PostgreSQL, and any driver that supports ADBC cancellation); with other drivers it stops at the next result batch.
No nested transactions or savepoints: adbc::begin on a connection with an open transaction is an error.
A connection released because its last binding went away reports no error if the driver fails to close it; call adbc::close to see one.

Plugin authors

Providing a resource type

A plugin defines a resource by deriving from ibex::runtime::Resource. Cleanup belongs in the destructor, which runs when the last owner lets go and must not throw. A cleanup failure there has no caller to return it to: report it with ibex::runtime::warn(message) (ibex/runtime/warnings.hpp), which the REPL prints as a warning. A long blocking call in a plugin can poll ibex::runtime::interrupt_requested() to stop on Ctrl+C; both reach the host once the plugin has registered a function.

class Session final : public ibex::runtime::Resource {
   public:
    auto type_name() const noexcept -> std::string_view override {
        return "my::Session";   // matches `extern type Session` in `namespace my`
    }
    ~Session() override { /* close handles; never throw */ }
};

registry.register_resource("my::open", [](const ExternArgs& args) {
    return ExternValue{std::make_shared<Session>(/* ... */)};
});
registry.register_table("my::query", [](const ExternArgs& args) {
    auto session = args.resource_as<Session>(0);   // null if not a Session
    // ...
});

namespace my {
extern type Session from "my_plugin.hpp";
extern fn open(uri: String) -> Session from "my_plugin.hpp";
extern fn query(mutable s: Session, sql: String) -> DataFrame from "my_plugin.hpp";
}

Scalar arguments keep their positions in args; a resource argument occupies its position as a null scalar, so existing plugins that read only scalars are unaffected. Register each function under its qualified name and return the qualified type name from type_name(); for ibex_compile, the header declares the entry points in namespace ibex::ext::my. The full rules are in SPEC.md §11.5.