CockroachDB via Crystal ... In 50 lines

I’ve been getting rather excited about the Crystal programming language recently and I’ve finally gotten round to creating a very simple CockroachDB-backed Crystal API!

In this example, I’m going to create a very contrived API to manage todos. When the API is up and running, you’ll be able to list the todos stored in the database, find a todo by its ID, and add new ones. These use cases lend themselves nicely to demonstrating 3 of the DB::QueryMethods.

In this post, I’ll show you how to install Crystal, bring in the dependencies we’ll need to create our API and walk you through the code that’ll give us the following endpoints:

POST /todos
GET /todos
GET /todos/:id

Setting things up

First, install Crystal. There are a number of ways to do this listed on their install page. I use macos, so will keep things Mac-specific for this post but you’ll be able to follow along using the information on their website if you’re on a different platform:

$ brew update
$ brew install crystal

Check your installation by running the crystal executable:

$ crystal version
Crystal 0.32.1 (2019-12-18)

LLVM: 9.0.0
Default target: x86_64-apple-macosx

Next, create a project for our code to go into. This can all be doing using Crystal’s CLI:

$ crystal init app api
    create  api/.gitignore
    create  api/.editorconfig
    create  api/LICENSE
    create  api/README.md
    create  api/.travis.yml
    create  api/shard.yml
    create  api/src/api.cr
    create  api/spec/spec_helper.cr
    create  api/spec/api_spec.cr
Initialized empty Git repository in /Users/me/crystal/learning/api/.git/
$ cd api

Finally, create a database for our application to connect to:

$ cockroach demo --nodes=1 --empty
...
root@127.0.0.1:64044/defaultdb>

Note that the terminal prompt resulting from a successful call to this command will be the address of the cluster we’ll be connect to. In this case, “127.0.0.1:64044”.

Using the resulting terminal prompt, we can create a table to house our data:

root@127.0.0.1:64044/defaultdb> create table "todo" (
	"id" uuid primary key default uuid_v4()::uuid,
	"value" text not null
);
CREATE TABLE

Time: 3.213ms
The components

Crystal’s standard library - like the standard libraries of most other programming languages - doesn’t contain specific database drivers. We’ll need a Postgres driver in order to talk to CockroachDB. According to the Crystal docs, will/crystal-pg is the one to use.

Crystal’s dependency manager is called Shards and in your api directory, the crystal init command would have created a file called shard.yml for you. Into that file, add the following to reference the Postgres driver as a dependency:

dependencies:
  pg:
    github: will/crystal-pg

Next, run the following to pull in the dependencies listed above. Note that it’s possible to specify versions for your dependencies but for this example, the latest version will be fine:

$ shards install
Fetching https://github.com/will/crystal-pg.git
Fetching https://github.com/crystal-lang/crystal-db.git
Using pg (0.20.0)
Using db (0.8.0)

Rather than rolling my own API routing code, I’m going to use the well-known Kemal web framework. It creates macros that result in very simple and elegant API code. Bring in this dependency in the same way you brought in the database driver, by adding it to the dependencies section of your shard.yml file:

dependencies:
  kemal:
    github: kemalcr/kemal
  pg:
    github: will/crystal-pg
$ shards install
Fetching https://github.com/kemalcr/kemal.git
Fetching https://github.com/luislavena/radix.git
Fetching https://github.com/jeromegn/kilt.git
Fetching https://github.com/crystal-loot/exception_page.git
Fetching https://github.com/will/crystal-pg.git
Fetching https://github.com/crystal-lang/crystal-db.git
Using kemal (0.26.1)
Using radix (0.3.9)
Using kilt (0.4.0)
Using exception_page (0.1.2)
Using pg (0.20.0)
Using db (0.8.0)
The code

All of our code will go into src/api.cr. First, we’ll want to reference our dependencies.

require "kemal"
require "db"
require "pg"
require "json"

The reference to kemal will allow us to use the fancy HTTP macros and start the server. The reference to db (a dependency automatically required by our explicit crystal-pg dependency) will allow us to make calls to the database. The reference to pg brings in the Postgres driver and provides a helper method we can use to connect to CockroachDB. The reference to json (in the standard library) will allow us to respond to callers in JSON.

Next, we open a connection to the database with the connection information we obtained when starting the CockroachDB cluster:

db = PG.connect "postgres://root@localhost:64044/defaultdb?sslmode=disable"

To keep the code clean, I create a Todo class to store the information on the todos being dealt with by the API. In this class definition, there’s a constructor, which provides shortcut syntax that creates two instance variables; “id” and “value” and a JSON.mapping function, which tells Crystal how the Todo class is to be serialised to and deserialised from JSON:

class Todo
  def initialize(@id : String, @value : String)
  end
  
  JSON.mapping({
    id: {type: String},
    value:  {type: String},
  })
end

Kemal provides some very cool macros which are listed in the guide. I’m using a “filter” macro called “before_all”, which - as you’ll probably guess - gets invoked before all endpoint calls, allowing me to jump into the web call flow and inject a response header to ensure the caller always gets a “Content-Type” of “application/json”:

before_all do |env|
  env.response.content_type = "application/json"
end

Onto the handlers!

POST /todos

Let’s start by getting some todos into the database. We’ll need a POST endpoint for that, and the “post” function created from a macro in Kemal is just the thing. Kemal captures the request and the convention for that, is |env|. From env, we can extract stuff from the JSON body.

Once inserted into the database, we’ll want the returned ID, so the scalar function is perfect for this, as we’ll return it from the INSERT statement. Once we’ve got the ID, we can build up a simple JSON object to return to the caller:

post "/todos" do |env|
  value = env.params.json["value"].as(String)
  id = db.scalar("insert into todo (value) values ($1) returning id", value).as(String)
  
  { "id": id }.to_json
end
GET /todos

Next, we’ll create an endpoint to view all of the todos in the database. This time we don’t need to capture the user’s request and will use the query function to fetch all of the todos with a ResultSet, building up an array of todos before returning the JSON representation:

get "/todos" do |env|
  todos = Array(Todo).new
  db.query "select id, value from todo" do |rs|
    rs.each do
      id, value = rs.read(String, String)
      todo = Todo.new(id, value)
      todos << todo
    end
  end

  todos.to_json
end
GET /todos/:id

Finally, we’ll make use of the query_one function to fetch a single user. To narrow down the search, we’ll capture the ID of the todo the user wants to find and use it when querying the database:

get "/todos/:id" do |env|
  id = env.params.url["id"]
  id, value = db.query_one "select id, value from todo where id = $1 limit 1", id, as: { String, String }
  
  todo = Todo.new(id, value)
  todo.to_json
end

You’ll notice that the todo’s ID is already provided, so I could have used the scalar function and return just the value of the todo but in a real-world scenario, there will likely be more columns to return and query_one is the correct function for that.

Kemal

The only remaining thing to do now, is kick off the Kemal server. To do that, we run the Kemal.run function. By default, Kemal will listen on port 3000 but a custom port can be provided:

Kemal.run 1234

By default, Kemal will log all of the requests it receives. To disable this, run the following instead:

logging false
Kemal.run 1234
Testing

Create a todos using the POST /todos endpoint:

$ curl -X POST \
  http://localhost:1234/todos \
  -H 'Content-Type: application/json' \
  -d '{
	"value": "Productionise todo API"
}'
{"id":"e548f471-5772-473b-b7d2-39e18c57d483"}

Fetch all of the todos using the GET /todos endpoint:

$ curl -X GET \
  http://localhost:1234/todos \
  -H 'Content-Type: application/json' \
  -d '{
	"value": "Productionise todo API"
}'
[{"id":"e548f471-5772-473b-b7d2-39e18c57d483","value":"Productionise todo API"}]

Fetch the todo we created with the GET /todos/:id endpoint:

$ curl -X GET \
  http://localhost:1234/todos/e548f471-5772-473b-b7d2-39e18c57d483 \
  -H 'Content-Type: application/json' \
  -d '{
	"value": "Productionise todo API"
}'
{"id":"e548f471-5772-473b-b7d2-39e18c57d483","value":"Productionise todo API"}

Our Kemal server would have been logging these requests as well:

2019-12-30 21:31:02 UTC 200 POST /todos 2.15ms
2019-12-30 21:33:17 UTC 200 GET /todos 922.05µs
2019-12-30 21:34:06 UTC 200 GET /todos/e548f471-5772-473b-b7d2-39e18c57d483 1.52ms

I’m still learning the Crystal ropes, so will continue to blog about the language as I stumble across new things!