db.

iyi get github.com/iyilang/db

iyi-db

The common database interface for iyi: a port of crystal-db 0.15.0, written in iyi with no Crystal code and no Crystal standard library underneath. A driver for a specific database is needed to access one.

It keeps crystal-db's shape - open a URI, a pooled Database, exec, scalar and query with positional or args: arguments, ResultSet#read, nested transactions, a connection pool configured from the URI, object mapping, and the shared specs a driver runs - and changes what iyi's rules require: errors are values, types are closed, and tasks are structured.

Requirements

iyi 0.20.0 or later: a Database shared by tasks on every worker needs spawn_parallel (0.19.0), the @[Share] defs and SharedProc that check the blocks it keeps (0.19.1), and 0.20.0's scheduler, on which a checkout parked on a full pool is woken when a connection comes back.

Installation

iyi-db is an iyi module. Add it to a project under the short name db:

iyi get github.com/iyilang/db --as db

which writes this line to iyi.mod:

require github.com/iyilang/db v0.4.0 as db

Files then import db for the root module, Db.open and the driver registry, and its other modules as db/db/...: the short name, then the module's path in the package.

Usage

module app

import db
import db/db/core::*
import db/db/errors::{DBError}
import db/db/value::{DBValue}
import sqlite3/sqlite3 # a driver: importing it registers "sqlite3"

def contacts : Nil | DBError
  Db.open("sqlite3:./file.db") do |db|
    db.exec("create table contacts (name text, age integer)")!
    db.exec("insert into contacts values (?, ?)", "John Doe", 30)!

    args = [] of DBValue
    args << "Sarah"
    args << 33
    db.exec("insert into contacts values (?, ?)", args: args)!

    puts "max age:"
    puts db.scalar("select max(age) from contacts")! # => 33

    puts "contacts:"
    db.query("select name, age from contacts order by age desc") do |rs|
      puts "#{rs.column_name(0)} (#{rs.column_name(1)})"
      # => name (age)
      rs.each do
        puts "#{rs.read(String)!} (#{rs.read(Int32)!})"
        # => Sarah (33)
        # => John Doe (30)
      end!
    end!
  end
end

case contacts
in Nil     then puts "done"
in DBError then puts "failed: #{it.message}"
end

The example uses SQLite, where ? marks an argument; PostgreSQL writes $1, $2, ... iyi-db does not interpret the statements.

Several columns are read as a tuple or named tuple type: rs.read(Tuple(String, Int32)), db.query_all(sql, as: NamedTuple(name: String, age: Int32)).

The pool is configured from the URI's query string: initial_pool_size (default 1), max_pool_size (0, unlimited), max_idle_pool_size (1), checkout_timeout (5.0 seconds), retry_attempts (1) and retry_delay (0.2 seconds). prepared_statements (true) and prepared_statements_cache (true) choose how statements run.

On every worker

A Database is Share, so one opened once and kept in a constant serves parallel tasks (spawn_parallel) on every worker, a web server's handlers among them, with no Share error and no warning from the compiler:

DATABASE = Db.open(ENV["DATABASE_URL"]).or_panic

def ask(n : Int64) : Int64
  DATABASE.query_one("SELECT $1::int8", n, as: Int64).or_panic
end

group do |g|
  32.times { |t| g.spawn_parallel { ask(t.to_i64); nil } }
end

The pool hands each connection to one task at a time, decides which one under a Mutex, and parks a checkout that finds none free - the task, not its worker - until a release hands it one. The blocks a database keeps run on whichever worker opens a connection or runs a statement, several at once, so the compiler checks each where it is written as it checks a parallel task's: the factory given to Pool.new, Database.new and Database.open, setup_connection's block and Statement.def_around_query_or_exec's hooks may capture only Share values. Count in an Atomic, keep things behind a Mutex.

close closes the idle connections at once, and each one a task holds when the task releases it.

Reading rows into objects

import db/db/serializable::{db_serializable, DBField}

class Employee
  property title : String

  @[DBField(key: "firstname")]
  property name : String?

  derive db_serializable
end

employees = db.query_all("SELECT title, firstname FROM employees", as: Employee)!
employees[0].title # => "Manager"

derive db_serializable_non_strict passes over columns the type has no property for. Employee.from_db_row(rs) reads the current row and Employee.from_rs(rs) every row. mapping({...}), crystal-db's deprecated DB.mapping, is there for code written against it.

Logging and instrumentation

Every statement logs Executing query at debug level to the std/log logger "db", with the query and its arguments as the entry's data: Executing query -- query="select name from contacts where age > ?" args=[18]. Db::Core::LOG is that logger behind a lock, with its level in an atomic beside it, so a statement with debug off takes no lock. Its level is read off std's logger when the first statement asks, and set after that with Db::Core::LOG.level = Severity::Debug; a level given to std's loggers directly once it has been read is not seen. A backend is set on std's Log.for("db"). Statement.def_around_query_or_exec { |stmt, args, run| ...; run.call; ... } wraps every query a driver runs.

Modules

What is different from crystal-db

Each difference follows from a rule of iyi (SPEC.md); the API is otherwise crystal-db's.

Writing a driver

Subclass Driver (answer a ConnectionBuilder from connection_builder(uri)), ConnectionBuilder, Connection, Statement and ResultSet from db/core, and call Db.register_driver("name", MyDriver) at the top level of the driver's module, so importing the driver registers it. The abstract methods say what to implement; the comments on Driver, Connection, Statement and ResultSet say how. A lost connection answers ConnectionLost.new(connection), so the pool retries on another one. Values of the driver's own types are classes inheriting CustomValue.

A Database that Db.open makes calls the driver's ConnectionBuilder#build from whichever worker opens a connection, several at once, and takes that on trust: a builder holds what the URI said, made once, and build only reads it.

Test a driver with the shared specs:

import db/db/spec::{DriverSpecs}

DriverSpecs.run do |specs|
  specs.connection_string "sqlite3:./test.db"
  specs.sample_value "hello", "varchar(25)", "'hello'"
  specs.binding_syntax { |index| "?" }
  # ... the other *_syntax blocks
end

db/driver_specs_test.iyi runs them against an in-memory driver.

Development

iyi test db     # every *_test.iyi

The tests are crystal-db's specs, ported; db/testing/dummy_driver.iyi is its dummy driver, safe to use from every worker. db/parallel_test.iyi runs db#1's program and the pool's other cases on twelve workers. Every test also builds from artifacts (iyi build --emit-iyimod DIR then --use-iyimod DIR). manual/ holds the two programs crystal-db runs by hand: iyi run manual/pool_concurrency.iyi -- --duration=3, whose tasks run on every worker, and iyi run manual/load.iyi.

iyi check a program, not a module that only queries: a block passed to Database#query is typed through the driver's Statement, and a module with no driver in it has none, so iyi 0.18.0 reports "can't infer block return type" there. A program that imports a driver checks clean.

Contributors

• bcardiff Brian J. Cardiff - creator and maintainer of crystal-db