Database

To access a relational database you will need a shard designed for the database server you want to use. The package crystal-lang/crystal-db offers a unified api across different drivers.

The following packages are compliant with crystal-db

This guide presents the api of crystal-db, the sql commands might need to be adapted for the concrete driver due to differences between postgres, mysql and sqlite.

Also some drivers may offer additional functionality like postgres LISTEN/NOTIFY.

Installing the shard

Choose the appropriate driver from the list above and add it as any shard to your application’s shard.yml

There is no need to explicitly require crystal-lang/crystal-db

During this guide crystal-lang/crystal-mysql will be used.

  1. dependencies:
  2.   mysql:
  3.     github: crystal-lang/crystal-mysql

Open database

DB.open will allow you to easily connect to a database using a connection uri. The schema of the uri determines the expected driver. The following sample connects to a local mysql database named test with user root and password blank.

  1. require "db"
  2. require "mysql"
  4. DB.open "mysql://root@localhost/test" do |db|
  5.   # ... use db to perform queries
  6. end

Other connection uris are

  • sqlite3:///path/to/data.db
  • mysql://user:password@server:port/database
  • postgres://server:port/database

Alternatively you can use a non yielding DB.open method as long as Database#close is called at the end.

  1. require "db"
  2. require "mysql"
  4. db = DB.open "mysql://root@localhost/test"
  5. begin
  6.   # ... use db to perform queries
  7. ensure
  8.   db.close
  9. end

Exec

To execute sql statements you can use Database#exec

  1. db.exec "create table contacts (name varchar(30), age int)"

To avoid SQL injection values can be provided as query parameters. The syntax for using query parameters depends on the database driver because they are typically just passed through to the database. MySQL uses ? for parameter expansion and assignment is based on argument order. PostgreSQL uses $n where n is the ordinal number of the argument (starting with 1).

  1. # MySQL
  2. db.exec "insert into contacts values (?, ?)", "John", 30
  3. # Postgres
  4. db.exec "insert into contacts values ($1, $2)", "Sarah", 33

Query

To perform a query and get the result set use Database#query, arguments can be used as in Database#exec.

Database#query returns a ResultSet that needs to be closed. As in Database#open, if called with a block, the ResultSet will be closed implicitly.

  1. db.query "select name, age from contacts order by age desc" do |rs|
  2.   rs.each do
  3.     # ... perform for each row in the ResultSet
  4.   end
  5. end

When reading values from the database there is no type information during compile time that crystal can use. You will need to call rs.read(T) with the type T you expect to get from the database.

  1. db.query "select name, age from contacts order by age desc" do |rs|
  2.   rs.each do
  3.     name = rs.read(String)
  4.     age = rs.read(Int32)
  5.     puts "#{name} (#{age})"
  6.     # => Sarah (33)
  7.     # => John Doe (30)
  8.   end
  9. end

There are many convenient query methods built on top of #query.

You can read multiple columns at once:

  1. name, age = rs.read(String, Int32)

Or read a single row:

  1. name, age = db.query_one "select name, age from contacts order by age desc limit 1", as: {String, Int32}

Or read a scalar value without dealing explicitly with the ResultSet:

  1. max_age = db.scalar "select max(age) from contacts"

All available methods to perform statements in a database are defined in DB::QueryMethods.