Namespaces: main
// Thrown by every operation of this package.
+ error Error (connect, ssl, auth, unsupported, protocol, error, closed) payload { message: String, sqlstate: String (""), severity: String (""), detail: String (""), hint: String ("") }Thrown by every operation of this package.
connect: the TCP connection or the startup handshake failed.ssl: SSL was required but could not be established.auth: the server rejected the credentials.unsupported: the server asked for something this package does not implement.protocol: the server sent bytes this package did not expect.error: the server reported an error for a query.sqlstateholds the SQLSTATE (for example42P01for an unknown table),message,detailandhintthe text the server sent.closed: the connection is closed.
// How `connect` treats SSL.
+ enum SslMode { disable, prefer, require, verify_full }
// The kinds of `Value`.
+ enum TYPE { null, int, float, string, bool, array }How connect treats SSL.
The kinds of Value.
// Opens a connection and logs in.
+ fn connect(host: String, user: String, password: String, db: ?String, port: u32 (5432), ssl: SslMode (SslMode.prefer)) Connection !Error
// Same as `connect`, with SSL settings beyond the mode: the CA file that the server certificate must lead to, and a client certificate for a server that asks for one.
+ fn connect_with(host: String, user: String, password: String, db: ?String, port: u32, ssl: SslOptions) Connection !Error
// Converts any supported value (integers, floats, bools, strings, json values, arrays of those, and nullable versions) into a `Value`.
+ fn convert(ndata: $T) Value
// Returns the connection as a `sql.Db`, the database type of the `valk-sql` package.
+ fn database(con: Connection) DbOpens a connection and logs in.
db is the database to connect to, or null for the database named after the user.
ssl decides whether the connection is upgraded to SSL before logging in (see SslMode).
Supported authentication methods: trust, password (cleartext), md5 and SCRAM-SHA-256.
Same as connect, with SSL settings beyond the mode: the CA file that the server
certificate must lead to, and a client certificate for a server that asks for one.
let con = postgres.connect_with("db.internal", "app", "", "app", 5432, postgres.SslOptions {
ca_file: "/etc/ssl/db-ca.pem"
certificate_file: "/etc/ssl/app.crt"
private_key_file: "/etc/ssl/app.key"
}) ! panic("%{E.message}")
Converts any supported value (integers, floats, bools, strings, json values, arrays of
those, and nullable versions) into a Value.
Returns the connection as a sql.Db, the database type of the valk-sql package.
Everything valk-sql offers — the query builder, migrations, pools, rows read into your own
classes — then works on this database, and the same code runs on SQLite or MySQL by opening
it with their driver instead.
The connection itself stays usable: this is a view of it, not a replacement.
use sql
use postgres
let db = postgres.database(postgres.connect("127.0.0.1", "user", "password", "app") ! panic("%{E.message}"))
db.exec("INSERT INTO users (name) VALUES (:name)", .{ "name" => "Ada" }) ! panic("%{E.message}")
+ class Connection {
// Rows affected by the last completed command (`INSERT`, `UPDATE`, `DELETE`, ...). For a `SELECT` it is the row count, known once every row has been fetched.
~ affected_rows: uint
~ closed: bool
// Command tag of the last completed command, such as `INSERT 0 1`.
~ command_tag: String
+ debug: bool
+ debug_bytes: bool
// Message of the last notice (`NOTICE`, `WARNING`, ...) the server sent.
~ last_notice: String
// Notifications received through `LISTEN`, oldest first. Remove what you have handled.
~ notifications: Array[Notification]
// Runtime parameters reported by the server, such as `server_version` and `TimeZone`.
~ parameters: Map[String]
// Process id of the server backend serving this connection.
~ process_id: u32
// Counts the statements that have run, so that the rows of a query can tell whether another statement took the connection from under them.
~+ query_serial: uint
// Number of prepared statements kept per connection. Single statement queries are prepared once and reused while they stay in the cache.
+ statement_cache_size: uint
// Transaction status of the last ReadyForQuery: 'I' idle, 'T' in a transaction, 'E' in a failed transaction.
~ transaction_status: u8
// Runs `BEGIN`.
+ fn begin() void !Error
// Binds a value to the `:name` placeholder of the next query.
+ fn bind(name: String, value: $T) void
// Binds a value to the next `?` placeholder of the next query. Values that are not consumed by a `?` are bound to the `$1`, `$2`, ... placeholders in order.
+ fn bindv(value: $T) void
// Removes the values bound with `bind` and `bindv`.
+ fn clear_binds() void
// Closes the connection. Further calls throw `closed`.
+ fn close() void
// Returns whether column `index` arrived in binary format. A prepared query that runs again receives integers, floats, bools, dates and timestamps in binary form; `col_view` then holds those bytes instead of text.
+ fn col_binary(index: uint) bool
// Returns column `index` as a bool: true for `t`, `true`, `yes`, `on` and numbers other than 0.
+ fn col_bool(index: uint) bool
// Returns the number of columns of the current result.
+ fn col_count() uint
// Returns column `index` as a float, 0 for NULL or text that is not a number.
+ fn col_float(index: uint) float
// Returns the index of the column named `name`.
+ fn col_index(name: String) uint !LookupError
// Returns column `index` as an integer, 0 for NULL or text that is not a number.
+ fn col_int(index: uint) int
// Returns whether column `index` of the current row is NULL (or missing).
+ fn col_is_null(index: uint) bool
// Returns the name of column `index`, or "" when there is no such column.
+ fn col_name(index: uint) String
// Returns column `index` as a new string, "" for NULL.
+ fn col_string(index: uint) String
// Returns the type oid of column `index` (`pg_type.oid`), or 0 when there is no such column.
+ fn col_type(index: uint) uint
// Returns column `index` as a `Value`, typed by the column type like `fetch_row` does.
+ fn col_value(index: uint) Value
// Returns the bytes of column `index` as a view into the receive buffer, empty for NULL. The view is valid until the next row is read. See `col_binary`.
+ fn col_view(index: uint) &[u8]
// Runs `COMMIT`.
+ fn commit() void !Error
// Runs a `COPY ... FROM STDIN` statement and sends it everything `data` reads, such as a CSV file. Returns the number of rows copied.
+ fn copy_in(statement: String, data: Reader) uint !Error
// Runs a `COPY ... TO STDOUT` statement and writes what the server sends to `out`, such as a file. Returns the number of rows copied.
+ fn copy_out(statement: String, out: Writer) uint !Error
// Inserts `rows` into `table` with one `COPY`, which is many times faster than an `INSERT` per row. Every row holds the values of `columns` in that order; null stores NULL. Returns the number of rows.
+ fn copy_rows(table: String, columns: Array[String], rows: Array[Array[?Value]]) uint !Error
// Fetches every remaining row.
+ fn fetch_all() Array[Map[Value]] !Error
// Fetches the next row and discards the rest. Returns null when there is no row.
+ fn fetch_one() ?Map[Value] !Error
// Reads the next row into `row` (cleared first), keyed by column name. Returns false when there are no more rows. A query with several statements continues into the rows of the next statement.
+ fn fetch_row(row: Map[Value]) bool !Error
// Returns whether a transaction is open (`BEGIN` was sent and not yet committed or rolled back).
+ fn in_transaction() bool
// Reads the next row without building a map. Its columns are read with the `col_*` methods and stay valid until the next `next_row`, `fetch_row` or `query`. Returns false when there are no more rows.
+ fn next_row() bool !Error
// Prepares a statement on the server, to run many times with other values each time.
+ fn prepare(sql: String) Statement !Error
// Runs a query. Rows, if any, are read with `fetch_row`, `fetch_one` or `fetch_all`.
+ fn query(q: String, binds: ?Map[?Value] (null)) void !Error
// Runs `ROLLBACK`.
+ fn rollback() void !Error
// Returns the version string reported by the server (for example `16.4`).
+ fn server_version() String
// Sets the socket read and write timeouts in milliseconds; 0 waits forever (the default).
+ fn set_timeouts(read_timeout_ms: uint, write_timeout_ms: uint) void
// Returns whether the connection is encrypted with SSL.
+ fn ssl_enabled() bool
// Waits for a notification on a channel this connection listens to (`LISTEN name`) and returns the oldest one, or null when none arrives within `timeout_ms`; 0 waits forever.
+ fn wait_notification(timeout_ms: uint (0)) ?Notification !Error
}Rows affected by the last completed command (INSERT, UPDATE, DELETE, ...).
For a SELECT it is the row count, known once every row has been fetched.
Command tag of the last completed command, such as INSERT 0 1.
Message of the last notice (NOTICE, WARNING, ...) the server sent.
Notifications received through LISTEN, oldest first. Remove what you have handled.
Runtime parameters reported by the server, such as server_version and TimeZone.
Process id of the server backend serving this connection.
Counts the statements that have run, so that the rows of a query can tell whether another statement took the connection from under them.
Number of prepared statements kept per connection. Single statement queries are prepared once and reused while they stay in the cache.
Transaction status of the last ReadyForQuery: 'I' idle, 'T' in a transaction, 'E' in a failed transaction.
Runs BEGIN.
Binds a value to the :name placeholder of the next query.
Binds a value to the next ? placeholder of the next query. Values that are not
consumed by a ? are bound to the $1, $2, ... placeholders in order.
Removes the values bound with bind and bindv.
Closes the connection. Further calls throw closed.
Returns whether column index arrived in binary format. A prepared query that runs
again receives integers, floats, bools, dates and timestamps in binary form;
col_view then holds those bytes instead of text.
Returns column index as a bool: true for t, true, yes, on and numbers other than 0.
Returns the number of columns of the current result.
Returns column index as a float, 0 for NULL or text that is not a number.
Returns the index of the column named name.
Returns column index as an integer, 0 for NULL or text that is not a number.
Returns whether column index of the current row is NULL (or missing).
Returns the name of column index, or "" when there is no such column.
Returns column index as a new string, "" for NULL.
Returns the type oid of column index (pg_type.oid), or 0 when there is no such column.
Returns column index as a Value, typed by the column type like fetch_row does.
Returns the bytes of column index as a view into the receive buffer, empty for NULL.
The view is valid until the next row is read. See col_binary.
Runs COMMIT.
Runs a COPY ... FROM STDIN statement and sends it everything data reads, such as a
CSV file. Returns the number of rows copied.
The data must be in the format the statement names: text (tab separated, the default),
csv or binary. When reading data fails, the copy is called off and nothing of it is
stored.
let file = fs.stream("users.csv") ! panic("cannot open the file")
let count = con.copy_in("COPY users (name, email) FROM STDIN (FORMAT csv, HEADER)", file) ! panic("%{E.message}")
Runs a COPY ... TO STDOUT statement and writes what the server sends to out, such
as a file. Returns the number of rows copied.
let file = fs.stream("users.csv", fs.OpenOptions { write: true, create: true, truncate: true }) ! panic("cannot open the file")
con.copy_out("COPY (SELECT name, email FROM users) TO STDOUT (FORMAT csv, HEADER)", file) ! panic("%{E.message}")
Inserts rows into table with one COPY, which is many times faster than an INSERT
per row. Every row holds the values of columns in that order; null stores NULL.
Returns the number of rows.
The table and column names are quoted as given, so they must match exactly: the
lower case form of a name that was not quoted when the table was made. A name with a
schema is written schema.table.
con.copy_rows("users", .{ "name", "age" }, .{ .{ "Ada", 36 }, .{ "Bob", null } }) ! panic("%{E.message}")
Fetches every remaining row.
Fetches the next row and discards the rest. Returns null when there is no row.
Reads the next row into row (cleared first), keyed by column name. Returns false
when there are no more rows. A query with several statements continues into the
rows of the next statement.
Returns whether a transaction is open (BEGIN was sent and not yet committed or rolled back).
Reads the next row without building a map. Its columns are read with the col_*
methods and stay valid until the next next_row, fetch_row or query. Returns
false when there are no more rows.
Prepares a statement on the server, to run many times with other values each time.
The server parses and plans the SQL once, here; running the statement only sends its
values. Values go in by name, :name, and a name used twice takes the same value.
let find = con.prepare("SELECT * FROM users WHERE id = :id") ! panic("%{E.message}")
defer find.close()
find.query(.{ "id" => 1 }) ! panic("%{E.message}")
let user = con.fetch_one() ! panic("%{E.message}")
Runs a query. Rows, if any, are read with fetch_row, fetch_one or fetch_all.
Values in binds are bound to :name placeholders. Values bound earlier with bind
and bindv are used as well. A single statement is prepared and cached, so that
running it again is cheaper. A query may also contain several statements separated
by ;; it is then sent as is and cannot have bound values.
Runs ROLLBACK.
Returns the version string reported by the server (for example 16.4).
Sets the socket read and write timeouts in milliseconds; 0 waits forever (the default).
Returns whether the connection is encrypted with SSL.
Waits for a notification on a channel this connection listens to (LISTEN name) and
returns the oldest one, or null when none arrives within timeout_ms; 0 waits forever.
One that already arrived during another query is returned at once. The connection must be idle: not in the middle of reading the rows of a query. Inside a coroutine only the coroutine waits.
con.query("LISTEN jobs") ! panic("%{E.message}")
while true {
let job = con.wait_notification(30_000) ! break
if isset(job) : println("new job: " + job.payload)
}
// A notification received through `LISTEN`.
+ class Notification {
// The channel name given to `NOTIFY`.
+ channel: String
// The payload given to `NOTIFY`, or "".
+ payload: String
// Process id of the backend that sent it.
+ process_id: u32
}A notification received through LISTEN.
The channel name given to NOTIFY.
The payload given to NOTIFY, or "".
Process id of the backend that sent it.
// Runs prepared statements for many coroutines of one thread over one connection, without waiting for each result before sending the next query.
+ class Pipeline {
// Whether the pipeline stopped working, after `close` or a connection failure.
~ closed: bool
// Why the connection stopped working; every query after that throws `closed`.
~ failure: String
// Stops the pipeline and closes its connection. Queries still waiting throw `closed`.
+ fn close() void
// Takes over `con`, whose prepared statements stay usable through `query`.
+ static fn new(con: Connection) Pipeline !Error
// Runs `statement` with `values` bound to its `:name` placeholders and returns its rows once they arrived. The calling coroutine waits; other coroutines keep running and may add their queries meanwhile.
+ fn query(statement: Statement, values: ?Map[?Value] (null)) PipelineResult !Error
// Starts a query of `statement` whose values follow with `arg`, in the order of its placeholders, and which `run` sends: `pipeline.start(find).arg(id).run()`.
+ fn start(statement: Statement) PipelineQuery
// The number of queries that were sent or queued and have no result yet.
+ fn waiting() uint
}Runs prepared statements for many coroutines of one thread over one connection, without waiting for each result before sending the next query.
Queries that coroutines start while the previous batch is on its way go out together in one write, and the results come back in one stream, so a busy server makes far fewer round trips and system calls than with a connection per query. Each query is its own implicit transaction: an error fails that query only.
A pipeline belongs to the thread that made it; its connection must not be used directly any more.
let pipeline = postgres.Pipeline.new(con) ! panic("%{E.message}")
let find = con.prepare("SELECT name FROM users WHERE id = :id") ! panic("%{E.message}")
let result = pipeline.query(find, .{ "id" => 1 }) ! panic("%{E.message}")
while result.next_row() : println(result.col_string(0))
Whether the pipeline stopped working, after close or a connection failure.
Why the connection stopped working; every query after that throws closed.
Stops the pipeline and closes its connection. Queries still waiting throw closed.
Takes over con, whose prepared statements stay usable through query.
Runs statement with values bound to its :name placeholders and returns its rows
once they arrived. The calling coroutine waits; other coroutines keep running and may
add their queries meanwhile.
Throws error with the SQLSTATE when the server rejects the query, and closed when
the connection failed.
Starts a query of statement whose values follow with arg, in the order of its
placeholders, and which run sends: pipeline.start(find).arg(id).run().
The same as query without a map, so nothing is allocated per value. Call run on
it in the same expression; a started query that is never run is dropped.
The number of queries that were sent or queued and have no result yet.
// A query of a prepared statement being built with `Pipeline.start`: add its values with `arg`, in the order of the placeholders, then send it with `run`.
+ class PipelineQuery {
// Adds the value of the next placeholder: an integer, float, bool, `String`, null for a nullable type, or anything `postgres.convert` takes. Returns the query, for chaining.
+ fn arg(value: $T) PipelineQuery
// Sends the query and returns its rows once they arrived; see `Pipeline.query`.
+ fn run() PipelineResult !Error
}A query of a prepared statement being built with Pipeline.start: add its values with
arg, in the order of the placeholders, then send it with run.
Adds the value of the next placeholder: an integer, float, bool, String, null for a
nullable type, or anything postgres.convert takes. Returns the query, for chaining.
Sends the query and returns its rows once they arrived; see Pipeline.query.
Throws error when the number of values does not match the placeholders.
// The rows and outcome of one `Pipeline.query`. Rows are read with `next_row` and the `col_*` methods, like on a connection.
+ class PipelineResult {
// The number of rows the command changed, or a `SELECT` found.
~ affected_rows: uint
// Returns whether column `index` arrived in binary; `col_view` then returns the raw value.
+ fn col_binary(index: uint) bool
// Returns column `index` as a bool.
+ fn col_bool(index: uint) bool
// Returns the number of columns.
+ fn col_count() uint
// Returns column `index` as a float, 0 for NULL or text that is not a number.
+ fn col_float(index: uint) float
// Returns column `index` as an integer, 0 for NULL or text that is not a number. A binary timestamp gives its microseconds since 2000-01-01 UTC.
+ fn col_int(index: uint) int
// Returns whether column `index` is NULL.
+ fn col_is_null(index: uint) bool
// Returns column `index` as a new string, "" for NULL.
+ fn col_string(index: uint) String
// Returns the Postgres type OID of column `index`.
+ fn col_type(index: uint) uint
// Returns the bytes of column `index` as a view, empty for NULL. It is valid while the result lives.
+ fn col_view(index: uint) &[u8]
// Moves to the next row; false after the last one. Its columns are read with `col_*`.
+ fn next_row() bool
// Gives the result back to its pipeline, which reuses it and its buffers for a later query. Optional: a result that is never released is freed like any other object. The result and every view of its columns must not be used afterwards.
+ fn release() void
}The rows and outcome of one Pipeline.query. Rows are read with next_row and the
col_* methods, like on a connection.
The number of rows the command changed, or a SELECT found.
Returns whether column index arrived in binary; col_view then returns the raw value.
Returns column index as a bool.
Returns the number of columns.
Returns column index as a float, 0 for NULL or text that is not a number.
Returns column index as an integer, 0 for NULL or text that is not a number. A
binary timestamp gives its microseconds since 2000-01-01 UTC.
Returns whether column index is NULL.
Returns column index as a new string, "" for NULL.
Returns the Postgres type OID of column index.
Returns the bytes of column index as a view, empty for NULL. It is valid while the
result lives.
Moves to the next row; false after the last one. Its columns are read with col_*.
Gives the result back to its pipeline, which reuses it and its buffers for a later query. Optional: a result that is never released is freed like any other object. The result and every view of its columns must not be used afterwards.
// SSL settings for `connect_with`.
+ class SslOptions {
// A PEM file with CA certificates to trust besides the system store, for a server certificate from a private CA. Only checked in `verify_full` mode.
+ ca_file: ?String
// A PEM file with the client certificate, optionally followed by the intermediate certificates, sent when the server asks for one (`clientcert` in `pg_hba.conf`).
+ certificate_file: ?String
// The password of an encrypted private key.
+ key_password: String
// How SSL is used. The default verifies the server certificate and its host name.
+ mode: SslMode
// The PEM private key of `certificate_file`. Null reads it from `certificate_file`.
+ private_key_file: ?String
}SSL settings for connect_with.
A PEM file with CA certificates to trust besides the system store, for a server
certificate from a private CA. Only checked in verify_full mode.
A PEM file with the client certificate, optionally followed by the intermediate
certificates, sent when the server asks for one (clientcert in pg_hba.conf).
The password of an encrypted private key.
How SSL is used. The default verifies the server certificate and its host name.
The PEM private key of certificate_file. Null reads it from certificate_file.
// A statement prepared once on the server and run as often as needed, made by `Connection.prepare`.
+ class Statement {
~ closed: bool
// The SQL the statement was prepared from.
~+ sql: String
// Releases the statement on the server. Running it afterwards throws `closed`.
+ fn close() void
// Runs the statement with `values` bound to its `:name` placeholders. Rows, if any, are read with `fetch_row`, `fetch_one`, `fetch_all` or `next_row` of the connection.
+ fn query(values: ?Map[?Value] (null)) void !Error
// Runs the statement and returns how many rows it changed; for a `SELECT`, how many rows it found.
+ fn run(values: ?Map[?Value] (null)) uint !Error
}A statement prepared once on the server and run as often as needed, made by
Connection.prepare.
Its values go in by name, as with Connection.query, and its rows are read with the fetch
methods of the connection. close releases it on the server; closing the connection
releases them all.
let insert = con.prepare("INSERT INTO users (name, age) VALUES (:name, :age)") ! panic("%{E.message}")
defer insert.close()
each people as person {
insert.run(.{ "name" => person.name, "age" => person.age }) ! panic("%{E.message}")
}
The SQL the statement was prepared from.
Releases the statement on the server. Running it afterwards throws closed.
Runs the statement with values bound to its :name placeholders. Rows, if any, are
read with fetch_row, fetch_one, fetch_all or next_row of the connection.
Every placeholder needs a value; a name without one throws. An array is sent as a
Postgres array, for id = ANY(:ids).
Runs the statement and returns how many rows it changed; for a SELECT, how many rows
it found.
// A value read from a result row or bound to a query.
+ class Value {
// The items when `type` is `array`.
+ array_items: ?Array[Value]
// Which of the value kinds this is.
+ type: TYPE
// Returns whether the value is NULL.
+ fn is_null() bool
// Returns a NULL value.
+ static fn null() Value
// Returns the value as a bool: `t`, `true`, `1`, `yes` and `on` are true for text, any number other than 0 is true.
+ fn to_bool() bool
// Like `to_bool`, but null for NULL.
+ fn to_bool_or_null() ?bool
// Returns the raw bytes of a `bytea` column (the server sends them hex encoded as `\x...`).
+ fn to_bytes() String
// Returns the value as a float; text is parsed, NULL is 0.
+ fn to_float() float
// Like `to_float`, but null for NULL.
+ fn to_float_or_null() ?float
// Returns the value as an integer; text is parsed, floats are truncated, NULL is 0.
+ fn to_int() int
// Like `to_int`, but null for NULL.
+ fn to_int_or_null() ?int
// Parses a `json`/`jsonb` column. Returns json null when the text is not valid JSON.
+ fn to_json() Value
// Like `to_json`, but null for NULL.
+ fn to_json_or_null() ?(Value)
// Returns the value as text: the text the server sent, a number, `true`/`false`, or an array literal like `{1,2}`. NULL becomes "".
+ fn to_string() String
// Like `to_string`, but null for NULL.
+ fn to_string_or_null() ?String
}A value read from a result row or bound to a query.
Integer columns become int, float4/float8 columns float, bool columns bool
and every other column type (text, numeric, timestamps, json, uuid, bytea, ...) keeps
the text the server sent as string. The to_* methods convert between them.
The items when type is array.
Which of the value kinds this is.
Returns whether the value is NULL.
Returns a NULL value.
Returns the value as a bool: t, true, 1, yes and on are true for text, any number other than 0 is true.
Like to_bool, but null for NULL.
Returns the raw bytes of a bytea column (the server sends them hex encoded as \x...).
Any other value is returned as its string form.
Returns the value as a float; text is parsed, NULL is 0.
Like to_float, but null for NULL.
Returns the value as an integer; text is parsed, floats are truncated, NULL is 0.
Like to_int, but null for NULL.
Parses a json/jsonb column. Returns json null when the text is not valid JSON.
Like to_json, but null for NULL.
Returns the value as text: the text the server sent, a number, true/false, or an array literal like {1,2}. NULL becomes "".
Like to_string, but null for NULL.