Connection

In Crystal we have two ways of building this connection. And so, coming up next, we are going to present examples with some advice on when to use each one.

The DB module, is our place to stand when working with databases in Crystal. As written in the documentation: is a unified interface for database access.

One of the methods implemented in this module is . Using this method is the first way for creating a connection. Let’s see how to use it.

When using DB#connect we are indeed opening a connection to the database. The uri passed as the argument is used by the module to determine which driver to use (for example: mysql://, postgres://, sqlite://, etc.) i.e. we do not need to specify which database we are using.

Here is the example:

It’s worth mentioning that the method returns a DB::Connection object. Although more specifically, it returns a MySql::Connection object, it doesn’t matter because all types of connections should be polymorphic. So hereinafter we will work with a DB::Connection instance, helping us to abstract from specific issues of each database engine.

When creating a connection manually (as we are doing here) we are responsible for managing this resource, and so we must close the connection when we are done using it. Regarding the latter, this little details can be the cause of huge bugs! Crystal, being a language for humans, give us a more safe way of manually creating a connection using blocks, like this:

require "mysql"
end                # the connection will be closed here

Ok, now we have a connection, let’s use it!

First, in this example, we are using a transaction (check the transactions section for more information on this topic) Second, it’s important to notice that the connection given by the transaction is the same connection that we were working with, before the transaction begin. That is, there is only one connection at all times in our program. And last, we are using the method #exec and #query. You may read more about executing queries in the section.

require "mysql"
db = DB.open("mysql://root:root@localhost/test")
puts typeof(db) # DB::Database

As with a connection, we should close the database once we don’t need it anymore. Or instead, we could use a block and let Crystal close the database for us!

But, where is the connection? Well, we should be asking for the connections. When a database is created, a pool of connections is created with connections to the database prepared and ready to use! (Do you want to read more about pool of connections? In the connection pool section you may read all about this interesting topic!)

How do we use a connection from the database object? For this, we could ask the database for a connection using the method Database#checkout. But, doing this will require to explicitly return the connection to the pool using Connection#release. Here is an example:

And we want a safe way (i.e. no need for us to release the connection) to request and use a connection from the database, we could use Database#using_connection:

DB.open "mysql://root:root@localhost/test" do |db|
  db.using_connection do |cnn|
    puts typeof(cnn)
    # use cnn
end

In the next example we will let the database object to manage the connections by itself, like this:

Given the examples, it may come to our attention that the number of connections is relevant. If we are programming a short living application with only one user starting requests to the database then a single connection managed by us (i.e. a DB::Connection object) should be enough (think of a command line application that receives parameters, then starts a request to the database and finally displays the result to the user) On the other hand, if we are building a system with many concurrent users and with heavy database access, then we should use a object; which by using a connection pool will have a number of connections already prepared and ready to use (no bootstrap/initialization-time penalizations). Or imagine that you are building a long-living application (like a background job) then a connection pool will free you from the responsibility of monitoring the state of the connection: is it alive or does it need to reconnect?