Subscribe now

JSON & Databases [08.22.2017]

Welcome back to our final chapter of this Crystal introduction! In the previous episode we built a basic web application in Crystal using Kemal. We will now add database access via the crystal-db shard.

[Open https://crystal-lang.org/docs/database/]

The official shard crystal-db provides a unified way to access relational databases. You will note the library is fairly unopinionated on how to make queries. It offers low level access to query execution, a connection pool, and a very simple object-relational mapping. However, it does not attempt to abstract the underlying SQL dialect.

There are other libraries that offer more complex functionality, but for this episode, we’ll stick with crystal-db, since we aren’t going to need a full ORM.

There are drivers for PostgreSQL, MySQL and SQLite. Let’s install the one for SQLite, and retrieve records from a test database.

[Open shard.yml]

dependencies:

  # [...]

  sqlite3:

    github: crystal-lang/crystal-sqlite3

[Open terminal]

crystal deps

Let’s create a db file where we will manage our database connection. Typically, we will move the connection string to configuration, but this will do for now.

[Open src/hello/db.cr]

require "db"

require "sqlite3"

module Hello::DB

  @@database : ::DB::Database?

  def self.db

    @@database ||= ::DB.open "sqlite3://./data.db"

  end

end

A few things to note in this module. First, see that we are declaring the database using double @, this means that it is a module-level variable. Also, its type is of a nilable database, since it is nil until the db method is first invoked.

Note also the usage of the conditional assignment operator, which is commonly used for memoization: if @@database is not nil, it is returned; otherwise, the DB.open method is called, and its result is assigned to the database variable and returned.

Let’s now create a contacts table in a local database.

[On console]

sqlite3 data.db

CREATE TABLE contacts (name string, age integer);

We’ll change our POST action in our web app to insert a contact in the database.

[On hello.cr]

require "./hello/db"

post "/" do |env|

  env.response.content_type = "application/json"

  name = env.params.json["name"].as(String)

  age = env.params.json["age"].as(Int64)

  Hello::DB.db.exec("INSERT INTO contacts (name, age) VALUES (?, ?)", name, age)

  {name: name, age: age}.to_json

end

The key here is the call to db.exec, where we pass a query as an argument, along with the positional arguments. Always make sure to use placeholders and never use string interpolation, in order to prevent SQL injection.

Also, we’ll make sure the database connection pool is closed on exit. To do this, we’ll use the at_exit global method which will run whenever the program exits.

at_exit { Hello::DB.db.close }

Let’s now test it by running a curl and checking the table contents to see the added record.

[On console]

curl -XPOST -d'{"name": "John", "age": 30}' -H 'Content-type: application/json' [http://localhost:3000/](http://localhost:3000/)

sqlite3 data.db "SELECT * FROM contacts"

Let’s change the get method to return all users now:

get "/" do |env|

  Hello::DB.db.query("SELECT name, age FROM contacts") do |rs|

    rs.each do

      env.response.puts("#{rs.read(String)} (#{rs.read(Int32)})")

    end

  end

end

The query method returns a resultset, which we can then iterate to go through its rows, and read each column specifying its type. As we read the resultset, we stream the fields to the HTTP response. Neat, right?.

However, this is fairly low-level. Crystal DB provides a simple way to map resultset rows to Crystal objects, by specifying a mapping.

class Contact

  ::DB.mapping({

    name: String,

    age:  Int32,

  })

end

Here we are stating that the Contact class has two fields, name and age, along with their types, and that an instance of a Contact can be constructed from a resultset by reading a name and an age column.

We can now simplify our database access by using the query_all method, if we don’t mind loading all records into memory.

[On hello.cr]

get "/" do |env|

  contacts = Hello::DB.db.query_all("SELECT name, age FROM contacts", as: Contact)

  contacts.map(&.inspect).join("\n")

end

The last parameter tells crystal-db how to return the records from the resultset. If you didn’t want to map to an instance of a class, you could also have specified a tuple.

Let’s see it at work.

[On console]

curl -XGET localhost:3000

That’s great, but returning the result of inspecting each contact isn’t very useful. We could return a JSON array here. To do that, we can either build the JSON manually by iterating through the contacts and using a JSON builder... or we could use a JSON mapping for that:

class Contact

  ::DB.mapping({

    name: String,

    age:  Int32,

  })

  JSON.mapping({

    name: String,

    age:  Int32,

  })

end

Here, just like in the database mapping, we tell Crystal how to serialize and deserialize our Contact class into JSON. So, now we can just invoke to_json on it and check the results.

get "/" do |env|

  contacts = Hello::DB.db.query_all("SELECT name, age FROM contacts", as: Contact)

  contacts.to_json

end

[On console]

curl -XGET localhost:3000

This concludes this episode, which covered the basics of building a simple web JSON API backed by a SQL database, and also this series of videos. Make sure to check the documentation for more options on database access and JSON mappings, and visit Crystal’s Community page to get in touch with other Crystallers and help the language grow. We hope you have enjoyed the series, and the language itself. Happy Crystalling!