std/db

NAME

std/db - DBI-powered SQL database access.

SYNOPSIS

  from std/db import DB;
  from std/io import Path;

  let dbh := DB.temp({
    auto_commit: false,
    isolation_level: "immediate"
  });
  dbh.begin();
  dbh.prepare(
    "create table users (id integer, name text)"
  ).execute();
  dbh.commit();

  let insert := dbh.prepare(
    "insert into users (id, name) values (?, ?)"
  );
  insert.execute(1, "Ari");

  let query := dbh.prepare(
    "select id, name from users order by id"
  );
  query.execute();
  for ( let row in query ) {
    say( row{name} );
  }
  let cols := query.column_names();
  let types := query.column_types();

  let file_db := DB.open( new Path("app.sqlite") );

IMPLEMENTATION SUPPORT

This module is supported by zuzu.pl, zuzu-rust, and zuzu-js on Node and Electron. It is not supported by zuzu-js in the browser.

DESCRIPTION

This module provides a lightweight SQL interface.

EXPORTS

Classes

DB
Static methods:
- connect(String dsn, Dict settings?)
  Parameters: dsn is a database connection string and settings contains optional connection settings. Returns: DatabaseHandle. Opens a database connection.
- temp(Dict settings?)
  Parameters: settings contains optional connection settings. Returns: DatabaseHandle. Opens an in-memory SQLite database.
- open(path, Dict settings?)
  Parameters: path is a string or std/io Path and settings contains optional connection settings. Returns: DatabaseHandle. Opens a SQLite database file. Optional settings keys: auto_commit, raise_error, print_error, sqlite_unicode, and isolation_level. For SQLite, isolation_level may be deferred, immediate, or exclusive.
DatabaseHandle
Methods:
- prepare(String sql)
  Parameters: sql is SQL text. Returns: StatementHandle. Prepares a statement.
- quote(String value)
  Parameters: value is text. Returns: String. Quotes a string for SQL where supported.
- begin(), commit(), rollback()
  Parameters: none. Returns: null. Controls a transaction.
- execute_batch(String sql, Array rows)
  Parameters: sql is SQL text and rows is an array of bind rows. Returns: null. Prepares once, then executes with multiple bind rows.
StatementHandle
Methods:
- execute(...)
  Parameters: bind values match the prepared statement. Returns: StatementHandle. Executes the statement.
- execute_batch(Array rows)
  Parameters: rows is an array of bind rows. Returns: StatementHandle. Executes the statement for multiple bind rows.
- column_names(), column_types()
  Parameters: none. Returns: Array. Returns statement column metadata.
- next_array(), next_dict()
  Parameters: none. Returns: Array, Dict, or null. Fetches the next row.
- all_array(), all_dict()
  Parameters: none. Returns: Array. Fetches all remaining rows.
- next_typed_array(), next_typed_dict()
  Parameters: none. Returns: Array, Dict, or null. Fetches the next row with type coercions.
- all_typed_array(), all_typed_dict()
  Parameters: none. Returns: Array. Fetches all remaining rows with type coercions.
- to_Iterator()
  Parameters: none. Returns: Function. Returns an iterator function suitable for for loops.

Typed fetches

Typed fetch methods inspect statement metadata and coerce values for common SQL numeric and boolean types.

ERROR HANDLING

Database and SQL errors are raised as exceptions.

COPYRIGHT AND LICENCE

std/db is copyright Toby Inkster.

It is free software; you may redistribute it and/or modify it under the terms of either the Artistic License 1.0 or the GNU General Public License version 2.

Module