CockroachDB via Crystal ... In under 50 lines
Tags: database, sql, crystal, api
Published: 2019-12-30 22:09:07

I've been getting rather excited about the [Crystal](https://crystal-lang.org) 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](https://crystal-lang.github.io/crystal-db/api/0.4.0/DB/QueryMethods.html).

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](#post-todos) <br />
[GET /todos](#get-todos) <br />
[GET /todos/:id](#get-todos-id)

##### Setting things up

First, install Crystal. There are a number of ways to do this listed on their [install](https://crystal-lang.org/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](https://crystal-lang.org/reference/database), [will/crystal-pg](https://github.com/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:

``` yaml
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](https://kemalcr.com) web framework. It creates [macros](https://crystal-lang.org/reference/syntax_and_semantics/macros.html) 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:

``` yaml
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.

``` ruby
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:

``` ruby
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:

``` ruby
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](https://kemalcr.com/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":

``` ruby
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:

``` ruby
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:

``` ruby
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:

``` ruby
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:

``` ruby
Kemal.run 1234
```

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

``` ruby
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!