Accessing Databases

Crystal can work with a growing number^[50] of SQL and NoSQL databases. Among them are SQLite, MySQL (or MariaDB), Postgres, MongoDB,^[51] Redis,^[52] and ReThinkDB.^[53]

The Crystal team realized the importance of database access for a programming language, so they made a DB module in a package called crystal-db,^[54] which provides a common unified database API. Crystal-db works seamlessly together with the SQLite, MySQL, and Postgres driver. You don’t need to explicitly require it. For other databases, you will need to add specific drivers.

Let’s illustrate the basic database operations using the SQLite sample database chinook.db. This database represents a music shop and contains tables artists and albums among others. Because of crystal-db, how the operations are written is essentially the same for other databases.

First create a Crystal project (name it crchinook) with the command $ crystal init app crchinook. Then you need to add the shard crystal-sqlite3 by editing your shard.yml file with:

Next, issue the $ shards command to install this dependency. You’ll write all the code in src/crchinook.cr. Don’t forget to add require "sqlite3" at the top. In the following code snippet, you can see how common db actions are done:

​①​

Read all artists.

​②​

Read one artist by artistid.

​③​

Read one value with scalar.

​④​

Read oldest employee by substituting a variable.

​⑤​

Use exec for DDL (Data Definition) statements.

​⑥​

Read all table names.

Build the app with $ crystal build src/crchinook.cr and run it with $ ./crchinook.

Make a connection with the database by providing DB.open with a connection URI as the argument. These take the forms:

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

By using DB.open with a do end block, the database connection is automatically closed at end. If this isn’t appropriate, you have to use the following pattern:

db or DB.open creates a DB::Database object that automatically provides you with a connection pool—that is, several database connections are made available to execute your queries. This pool can be configured^[55] for maximum number of connections, checkout time, retry number, and retry delay.

Getting the Database Type

You can extract the database type from the database URIs shown here with db.uri.scheme. This comes in handy when you need to tailor the SQL according to the database. Just use case...when, like shown here: ​  sql = ​case​ db.​uri​.​scheme​ ​  ​when​ ​"postgres"​ ​  ​# SQL for postgres​ ​  ​when​ ​"mysql"​ ​  ​# SQL for mysql​ ​  ​when​ ​"sqlite3"​ ​  ​# SQL for sqlite3​ ​  ​else​ ​  ​raise​ ​"SQL not implemented for ​​#{​db.​uri​.​scheme​​}​​"​ ​  ​end​

Select queries are done with the query method, which returns a ResultSet object. You can iterate over that object with the each method to obtain and process each row successively. You should indicate the type of each field, as in rs.read(Int32), because at compilation time there is no type information about specific database fields available. The minimum set of types supported is given in DB::Types, and includes Nil, String, Bool, Int32, Int64, Float32, Float64, Time, and Bytes. The type DB::Any is an alias for any of them because it is the union of all these types.

The column names can be accessed by index as rs.column_name(1), or returned as an array with column_names. Multiple columns can be read at once like this: artistid, name = rs.read(Int32, String). Use query_one to read one row, scalar to read the first value of the first row, and query_all specified as: Type to return an array.

Queries ​④​ and ​⑤​ in the crchinook listing show how values can be interpolated into a SQL string by using one or more ? in the case of SQLite. For Postgres, you have to use $1 and $2. Each database has its own approach to this. Using parameters in your SQL statements like this avoids SQL injection.

For queries that create database objects or change values (insert, update, delete), use the exec method, as in line ​⑤​. You see that for insert statements, you can just populate an array and substitute it in the ? value placeholders. Then in line ​⑥​ we use query_all to read all table names from the database, so use it only if you don’t mind getting all records back! query_all executes a query and returns an array where the first column’s value of each row is read as the given type. Here^[56] are the docs of all available query methods.

SQL statements that need to be executed together can also be enveloped in a transaction.

Exception Handling with Databases
	All interactions with a database can give exceptions for various reasons, so in production code you should envelop that code within a begin … rescue … ensure … end block.

If you’d rather move a step up the abstraction ladder, you might want to use an ORM (Object-Relational Mapping) framework. Crystal offers many choices, which you can explore at awesome crystal^[57] or crystalshards.^[58] Good places to start include:

• topaz,^[59] which is a simple DB wrapper.

• crecto,^[60] inspired by the Ecto framework in Elixir to be used in larger projects. It even contains an admin dashboard.

• granite-orm,^[61] which is used with the amber web framework, specifically for Postgres, MySQL, and SQLite.

You can find out much more about the many available DB objects and methods in the docs.^[62]