`sql.connection`

import sql.connection

sql lifts part of this module out to its own top level; each name below is shown with the path that reaches it. Anything still spelled sql.connection.* needs import sql.connection.

The connection a program holds.

Everything that is the same whichever database is underneath lives here: translating placeholders, shaping results, running transactions and their savepoints, generating the four statements that are always the same, and getting an id back from an insert on an engine that has no last insert id.

What is left over is the adapter’s, reachable through native() for the cases where an engine’s own feature is the point.

Classes

`Connection`

class sql.Connection

An open connection to a database.

printable — has a @to_string(), so echo and print() show something useful

Fields

Field	Type	Description
`schema`	`Schema`	Introspection: what tables exist, what columns they have, and how they are indexed.

Constructor

sql.Connection(connection, driver)

Parameters

connection (DriverConnection) — The adapter’s own connection.
driver (Driver) — The driver that opened it.

`Connection.driver_connection()`

sql.Connection.driver_connection() -> DriverConnection

The adapter’s own connection, for the engine-specific things the shared contract does not cover.

Returns DriverConnection

`Connection.native()`

sql.Connection.native() -> DriverConnection

The same thing, under the name that reads better at a call site.

db.native().create_function('slug', 1, @(text) => slugify(text))

Returns DriverConnection

`Connection.driver()`

sql.Connection.driver() -> Driver

The driver behind this connection.

Returns Driver

`Connection.driver_name()`

sql.Connection.driver_name() -> string

The adapter’s name, such as 'sqlite'.

Returns string

`Connection.capabilities()`

sql.Connection.capabilities() -> dict

What this engine can do.

Returns dict

`Connection.supports()`

sql.Connection.supports(flag: string) -> bool

Whether this engine supports flag.

if db.supports('returning') {
  ...
}

Parameters

flag (string)

Returns bool

`Connection.server_version()`

sql.Connection.server_version() -> string

The engine’s version, as it reports it.

Returns string

`Connection.query()`

sql.Connection.query(sql: string, params) -> ResultSet

Runs a query and reads its whole result.

Parameters

sql (string) — Written with ? or :name placeholders.
params (list|dict|nil)

Returns ResultSet

`Connection.exec()`

sql.Connection.exec(sql: string, params) -> ExecResult

Runs a statement for its effect.

Parameters

sql (string)
params (list|dict|nil)

Returns ExecResult

`Connection.fetch_one()`

sql.Connection.fetch_one(sql: string, params) -> dict|nil

The first row of a query, or nil when it returns none.

Parameters

sql (string)
params (list|dict|nil)

Returns dict|nil

`Connection.fetch_all()`

sql.Connection.fetch_all(sql: string, params) -> list[dict]

Every row of a query.

Parameters

sql (string)
params (list|dict|nil)

Returns list[dict]

`Connection.fetch_value()`

sql.Connection.fetch_value(sql: string, params, fallback) -> any

The first column of the first row, for a query written to return one value.

Parameters

sql (string)
params (list|dict|nil)
fallback (any) — What to return for an empty result.

Returns any

`Connection.fetch_column()`

sql.Connection.fetch_column(sql: string, params, column) -> list

One column’s values, as a list.

Parameters

sql (string)
params (list|dict|nil)
column (string|number|nil) — Which column, the first by default.

Returns list

`Connection.stream()`

sql.Connection.stream(sql: string, params, options) -> Cursor

Runs a query and returns a cursor over its result rather than reading all of it.

Parameters

sql (string)
params (list|dict|nil)
options (dict|nil) — { batch } on engines that fetch in batches.

Returns Cursor

`Connection.prepare()`

sql.Connection.prepare(sql: string) -> Statement

Compiles a statement for repeated use.

Parameters

sql (string)

Returns Statement

`Connection.exec_script()`

sql.Connection.exec_script(sql: string)

Runs one or more statements for their effect, as a script.

This is the path for a schema file or a batch of pragmas. It binds no parameters and returns no rows, and an engine that cannot run several statements at once runs them one at a time.

Parameters

sql (string)

`Connection.insert()`

sql.Connection.insert(table: string, values: dict, options) -> any

Inserts a row and returns its id.

How the id comes back depends on the engine, which is the point of having this rather than writing the insert by hand. An engine with a last insert id reports one; an engine without gets a RETURNING clause added. An engine that can do neither raises rather than returning something that is not an id.

var id = db.insert('posts', { title: 'Hello', body: 'World' })

Parameters

table (string)
values (dict) — Column to value.
options (dict|nil) — { returning } names the column to read the id from, which matters on an engine using RETURNING when the key is not called id.

Returns any — The new row’s id, or nil where the engine reports none and no returning column was named.

`Connection.insert_many()`

sql.Connection.insert_many(table: string, rows: list) -> number

Inserts several rows in one statement.

Every row has to name the same columns. Large batches are split so that no one statement binds more parameters than the engine allows.

Parameters

table (string)
rows (list) — Dictionaries of column to value.

Returns number — How many rows were inserted.

`Connection.update()`

sql.Connection.update(table: string, values: dict, where) -> number

Updates the rows matching where.

Passing nil for where updates every row, which has to be asked for rather than happening because a dictionary came out empty.

Parameters

table (string)
values (dict) — Column to new value.
where (dict|nil) — Column to value; a list matches any of them and nil matches null.

Returns number — How many rows changed.

`Connection.delete()`

sql.Connection.delete(table: string, where) -> number

Deletes the rows matching where.

Parameters

table (string)
where (dict|nil)

Returns number — How many rows went.

`Connection.find()`

sql.Connection.find(table: string, where, options) -> ResultSet

Selects the rows matching where.

db.find('posts', { published: true }, {
  columns: ['id', 'title'],
  order: ['created_at desc'],
  limit: 10,
})

Parameters

table (string)
where (dict|nil)
options (dict|nil) — columns, order, limit and offset.

Returns ResultSet

`Connection.find_one()`

sql.Connection.find_one(table: string, where, options) -> dict|nil

The first row matching where, or nil.

Parameters

table (string)
where (dict|nil)
options (dict|nil)

Returns dict|nil

`Connection.count()`

sql.Connection.count(table: string, where) -> number

How many rows match where.

Parameters

table (string)
where (dict|nil)

Returns number

`Connection.transaction()`

sql.Connection.transaction(body: function, isolation) -> any

Runs body inside a transaction, committing when it returns and rolling back when it raises.

db.transaction(@(tx) {
  tx.exec('insert into audit (what) values (?)', ['transfer'])
  tx.update('accounts', { balance: 0 }, { id: 1 })
})

A transaction() called inside another takes a savepoint, so only the outermost commits and a failure inside undoes just that inner piece of work.

Parameters

body (function) — Called with a Transaction.
isolation (string|nil) — One of the level constants, for the outermost transaction only.

Returns any — Whatever body returned.

`Connection.begin()`

sql.Connection.begin(isolation) -> Transaction

Opens a transaction to be committed or rolled back by hand.

The closure form is safer and should be preferred. This exists for a transaction whose lifetime is not a block, such as one held open across an entire request.

Parameters