External Database, Phase 1

We’ve gone pretty far with our application so far, but it’s time to make that big leap, and convert things over to an actual database. We’ll be running PostgreSQL inside a Docker container.

We’re definitely going to be taking two steps backward before taking further steps forward, but the majority of the changes will be in the my.clojure-game-geek.db namespace; the majority of the application, including the field resolvers, will be unaffected.

Dependency Changes

deps.edn
{:paths ["src" "resources"]
 :deps  {org.clojure/clojure              {:mvn/version "1.11.1"}
         com.walmartlabs/lacinia          {:mvn/version "1.2-alpha-4"}
         com.walmartlabs/lacinia-pedestal {:mvn/version "1.1"}
         org.clojure/java.jdbc            {:mvn/version "0.7.12"}
         org.postgresql/postgresql        {:mvn/version "42.5.1"}
         com.mchange/c3p0                 {:mvn/version "0.9.5.5"}
         com.stuartsierra/component       {:mvn/version "1.1.0"}
         io.aviso/logging                 {:mvn/version "1.0"}}
 :aliases
 {:run-m {:main-opts ["-m" "my.clojure-game-geek"]}
  :run-x {:ns-default my.clojure-game-geek
          :exec-fn    greet
          :exec-args  {:name "Clojure"}}
  :build {:deps       {io.github.seancorfield/build-clj
                       {:git/tag   "v0.8.2" :git/sha "0ffdb4c"
                        ;; since we're building an app uberjar, we do not
                        ;; need deps-deploy for clojars.org deployment:
                        :deps/root "slim"}}
          :ns-default build}
  :dev   {:extra-paths ["dev-resources"]}
  :test  {:extra-paths ["test"]
          :extra-deps  {org.clojure/test.check {:mvn/version "1.1.1"}
                        io.github.cognitect-labs/test-runner
                        {:git/tag "v0.5.0" :git/sha "48c3c67"}}}}}

This adds several new dependencies for accessing a PostgreSQL database:

  • The Clojure java.jdbc library
  • The PostgreSQL driver that plugs into the library
  • A java library, c3p0, that is used for connection pooling

Database Initialization

We’ll be using Docker’s compose functionality to start and stop the container.

docker-compose.yml
version: '3'
services:
  db:
    ports:
      - 25432:5432
    image: postgres:15.1-alpine
    environment:
      POSTGRES_PASSWORD: supersecret

The image key identifies the name of the image to download from hub.docker.com. Postgres requires you to provide a server-level password as well, that’s specified in the service’s environment.

The port mapping is part of the magic of Docker … the PostgreSQL server, inside the container, will listen to requests on its normal port: 5432, but our code, running on the host operation system, can reach the server as port 25432 on localhost.

To start working with the database, we’ll let Docker start it:

> docker compose up -d
[+] Running 2/2
 ⠿ Network clojure-game-geek_default  Created                                                                                          0.0s
 ⠿ Container clojure-game-geek-db-1   Started                                                                                          0.3s

You’ll see Docker download the necessary Docker images the first time you execute this.

The -d argument detaches the container from the terminal, otherwise PostgreSQL would write output to your terminal, and would shut down if you hit Ctrl-C.

Later, you can shut down this detached container with docker compose down.

There’s also bin/psql.sh` to launch a SQL command prompt for the database (not shown here).

After starting the container, it is necessary to create the cggdb database and populate it with initial data, using the setup-db.sh script:

bin/setup-db.sh
#!/usr/bin/env bash

docker exec -i --user postgres clojure-game-geek-db-1 createdb cggdb

docker exec -i --user postgres clojure-game-geek-db-1 psql cggdb -a  <<__END
create user cgg_role password 'lacinia';
grant create on schema public to cgg_role;
__END

docker exec -i clojure-game-geek-db-1 psql -Ucgg_role cggdb -a <<__END
drop table if exists designer_to_game;
drop table if exists game_rating;
drop table if exists member;
drop table if exists board_game;
drop table if exists designer;

CREATE OR REPLACE FUNCTION mantain_updated_at()
RETURNS TRIGGER AS \$\$
BEGIN
   NEW.updated_at = now();
   RETURN NEW;
END;
\$\$ language 'plpgsql';

create table member (
  member_id int generated by default as identity primary key,
  name text not null,
  created_at timestamp not null default current_timestamp,
  updated_at timestamp not null default current_timestamp);

create trigger member_updated_at before update
on member for each row execute procedure
mantain_updated_at();

create table board_game (
  game_id int generated by default as identity primary key,
  name text not null,
  summary text,
  min_players integer,
  max_players integer,
  created_at timestamp not null default current_timestamp,
  updated_at timestamp not null default current_timestamp);

create trigger board_game_updated_at before update
on board_game for each row execute procedure
mantain_updated_at();

create table designer (
  designer_id int generated by default as identity primary key,
  name text not null,
  uri text,
  created_at timestamp not null default current_timestamp,
  updated_at timestamp not null default current_timestamp);

create trigger designer_updated_at before update
on designer for each row execute procedure
mantain_updated_at();

create table game_rating (
  game_id int references board_game(game_id),
  member_id int references member(member_id),
  rating integer not null,
  created_at timestamp not null default current_timestamp,
  updated_at timestamp not null default current_timestamp,
  primary key (game_id, member_id));

create trigger game_rating_updated_at before update
on game_rating for each row execute procedure
mantain_updated_at();

create table designer_to_game (
  designer_id int  references designer(designer_id),
  game_id int  references board_game(game_id),
  primary key (designer_id, game_id));

insert into board_game (game_id, name, summary, min_players, max_players) values
  (1234, 'Zertz', 'Two player abstract with forced moves and shrinking board', 2, 2),
  (1235, 'Dominion', 'Created the deck-building genre; zillions of expansions', 2, null),
  (1236, 'Tiny Epic Galaxies', 'Fast dice-based sci-fi space game with a bit of chaos', 1, 4),
  (1237, '7 Wonders: Duel', 'Tense, quick card game of developing civilizations', 2, 2);

alter table board_game alter column game_id restart with 1300;

insert into member (member_id, name) values
  (37, 'curiousattemptbunny'),
  (1410, 'bleedingedge'),
  (2812, 'missyo');

alter table member alter column member_id restart with 2900;

insert into designer (designer_id, name, uri) values
  (200, 'Kris Burm', 'http://www.gipf.com/project_gipf/burm/burm.html'),
  (201, 'Antoine Bauza', 'http://www.antoinebauza.fr/'),
  (202, 'Bruno Cathala', 'http://www.brunocathala.com/'),
  (203, 'Scott Almes', null),
  (204, 'Donald X. Vaccarino', null);

alter table designer alter column designer_id restart with 300;

insert into designer_to_game (designer_id, game_id) values
  (200, 1234),
  (201, 1237),
  (204, 1235),
  (203, 1236),
  (202, 1237);

insert into game_rating (game_id, member_id, rating) values
  (1234, 37, 3),
  (1234, 1410, 5),
  (1236, 1410, 4),
  (1237, 1410, 4),
  (1237, 2812, 4),
  (1237, 37, 5);
__END

The DDL for the cggdb database includes a pair of timestamp columns, created_at and updated_at, in most tables. Defaults and database triggers ensure that these are maintained by PostgreSQL.

Primary Keys

There’s a problem with the data model we’ve used in prior chapters: the primary keys.

We’ve been using simple numeric strings as primary keys, because it was convenient. Literally, we just made up those values. But eventually, we’re going to be writing data to the database, including new Board Games, new Publishers, and new Members.

With the change to using PostgreSQL, we’ve switched to using numeric primary keys. Not only are these more space efficient, but we have set up PostgreSQL to allocate them automatically. We’ll circle back to this issue when we add mutations to create new entities.

In the meantime, our database schema uses numeric primary keys, so we’ll need to make changes to the GraphQL schema to match [1]; the id fields have changed type from type ID (which, in GraphQL, is a kind of opaque string) to type Int (which is a 32 bit, signed integer).

resources/cgg-schema.edn
{:objects
 {:BoardGame
  {:description "A physical or virtual board game."
   :fields
   {:id            {:type (non-null Int)}
    :name          {:type (non-null String)}
    :summary       {:type        String
                    :description "A one-line summary of the game."}
    :ratingSummary {:type        (non-null :GameRatingSummary)
                    :description "Summarizes member ratings for the game."}
    :description   {:type        String
                    :description "A long-form description of the game."}
    :designers     {:type        (non-null (list :Designer))
                    :description "Designers who contributed to the game."}
    :minPlayers    {:type        Int
                    :description "The minimum number of players the game supports."}
    :maxPlayers    {:type        Int
                    :description "The maximum number of players the game supports."}
    :playTime      {:type        Int
                    :description "Play time, in minutes, for a typical game."}}}

  :GameRatingSummary
  {:description "Summary of ratings for a single game."
   :fields
   {:count   {:type        (non-null Int)
              :description "Number of ratings provided for the game. Ratings are 1 to 5 stars."}
    :average {:type        (non-null Float)
              :description "The average value of all ratings, or 0 if never rated."}}}

  :Member
  {:description "A member of Clojure Game Geek.  Members can rate games."
   :fields
   {:id      {:type (non-null Int)}
    :name    {:type        (non-null String)
              :description "Unique name of the member."}
    :ratings {:type        (list :GameRating)
              :description "List of games and ratings provided by this member."}}}

  :GameRating
  {:description "A member's rating of a particular game."
   :fields
   {:game   {:type        (non-null :BoardGame)
             :description "The Game rated by the member."}
    :rating {:type        (non-null Int)
             :description "The rating as 1 to 5 stars."}}}

  :Designer
  {:description "A person who may have contributed to a board game design."
   :fields
   {:id    {:type (non-null Int)}
    :name  {:type (non-null String)}
    :url   {:type        String
            :description "Home page URL, if known."}
    :games {:type        (non-null (list :BoardGame))
            :description "Games designed by this designer."}}}

  :Query
  {:fields
   {:gameById
    {:type        :BoardGame
     :description "Access a BoardGame by its unique id, if it exists."
     :args
     {:id {:type Int}}}

    :memberById
    {:type        :Member
     :description "Access a ClojureGameGeek Member by their unique id, if it exists."
     :args
     {:id {:type (non-null Int)}}}}}

  :Mutation
  {:fields
   {:rateGame
    {:type :BoardGame
     :description "Establishes a rating of a board game, by a Member."
     :args
     {:gameId {:type (non-null Int)}
      :memberId {:type (non-null Int)}
      :rating {:type (non-null Int)
               :description "Game rating as number between 1 and 5."}}}}}}}

In addition, the id field on the BoardGame, Member, and Publisher objects has been renamed: to game_id, member_id, and publisher_id, respectfully. This will be handy when performing joins across tables.

org.clojure/java.jdbc

This java.jdbc library is the standard approach to accessing a typical SQL database from Clojure code. java.jdbc can access, in a uniform manner, any database for which there is a Java JDBC driver.

The clojure.java.jdbc namespace contains a number of functions for acessing a database, including functions for executing arbitrary queries, and specialized functions for peforming inserts, updates, and deletes.

For all of those functions, the first parameter is a database spec, a map of data used to connect to the database, to perform the desired query or other operation.

In a trivial case, the spec identifies the Java JDBC driver class, and provides extra information to build a JDBC URL, including details such as the database host, the user name and password, and the name of the database.

That is fine for initial prototyping, but a JDBC connection is created and destroyed every time a query is executed.

In production, opening up a new connection for each operation has unacceptible performance, so we’ll jump right in with a database connection pooling library, C3P0, from the get go.

java.jdbc supports this with the :datasource key in the spec. A class in C3P0 implements the javax.sql.DataSource interface, making it compatible with java.jdbc.

my.clojure-game-geek.db

In prior chapters, the :db component was just a wrapper around a Clojure Atom; starting here, we’re going to revise the component to be a wrapper around a pooled connection pool to the PostgreSQL database running in the Docker container.

Our goal in this chapter is to update just one basic query to use the database, the query that retrieves a board game by its unique id. We’ll make just the changes necessary for that one query before moving on.

src/my/clojure_game_geek/db.clj (partial)
(ns my.clojure-game-geek.db
  (:require [clojure.java.jdbc :as jdbc]
            [clojure.set :as set]
            [com.stuartsierra.component :as component])
  (:import (com.mchange.v2.c3p0 ComboPooledDataSource)))

(defn- pooled-data-source
  [host dbname user password port]
  (doto (ComboPooledDataSource.)
    (.setDriverClass "org.postgresql.Driver")
    (.setJdbcUrl (str "jdbc:postgresql://" host ":" port "/" dbname))
    (.setUser user)
    (.setPassword password)))

(defrecord ClojureGameGeekDb [^ComboPooledDataSource datasource]

  component/Lifecycle

  (start [this]
    (assoc this :datasource (pooled-data-source "localhost" "cggdb" "cgg_role" "lacinia" 25432)))

  (stop [this]
    (.close datasource)
    (assoc this :datasource nil)))

The requires for the db namespace have changed; we’re using the clojure.java.jdbc namespace to connect to the database and execute queries, and also making use of the ComboPooledDataSource class, which allows for pooled connections.

The ClojureGameGeekDb record has changed; it now has a datasource field, and that is the connection pool for the PostgreSQL database. The start method now creates the connection pool stop method shuts down the connection pool.

For the meantime, we’ve hardwired the connection details (hostname, username, password, and port) to our Docker container. A later chapter will discuss approaches to configuration. Also note that we’re connecting to port 25432 on localhost; Docker will forward that port to the container port 5432, which is the port the PostgreSQL server listens to.

By the time the start method completes, the :db component is in the correct shape to be passed as a clojure.java.jdbc database spec; it will have a :datasource key.

find-game-by-id

That leaves the revised implementation of the find-game-by-id function; the only data access function so far rewritten to use the database. It simply constructs and executes the SQL query.

src/my/clojure_game_geek/db.clj (partial)
(defn- remap-board-game
  [row-data]
  (set/rename-keys row-data {:game_id     :id
                             :min_players :minPlayers
                             :max_players :maxPlayers
                             :created_at  :createdAt
                             :updated_at  :updatedAt}))

(defn find-game-by-id
  [component game-id]
  (-> (jdbc/query component
        ["select game_id, name, summary, min_players, max_players, created_at, updated_at
                    from board_game where game_id = ?" game-id])
    first
    remap-board-game))

With clojure.java.jdbc the query is a vector consisting of a SQL query string followed by zero or more query parameters. Each ? character in the query string corresponds to a query parameter, based on position.

The query function returns a seq of matching rows. By default, each selected row is converted into a Clojure map, and the column names are converted from strings into keywords.

For an operation like this one, which returns at most one map, we use first.

Further, we remap the keys from their database snake_case names, to their GraphQL camelCase names, where necessary. This could be done in the query using the SQL AS keyword, but it makes the SQL code harder to read and write and is easy to do in Clojure code.

If no rows match, then the seq will be empty, and first will return nil. That’s a perfectly good way to identify that the provided Board Game id was not valid.

At the REPL

Starting a new REPL, we can give the new code and schema a test:

(start)
=> :started
(q "{ gameById(id: 1234) { id name summary minPlayers maxPlayers }}")
=>
{:data {:gameById {:id 1234,
                   :name "Zertz",
                   :summary "Two player abstract with forced moves and shrinking board",
                   :minPlayers 2,
                   :maxPlayers 2}}}

Great! That works!

Meanwhile all the other my.clojure-game-geek.db namespace functions, expecting to operate against a map inside an Atom, are now broken. We’ll fix them in the next couple of chapters.

Summary

We now have our application working against a live PostgreSQL database and one operation actually works. However, we’ve been sloppy about a key part of our application development: we’ve entirely been testing at the REPL.

In the next chapter, we’ll belatedly write some tests, then convert the rest of the db namespace to use the database.

[1]This kind of change is very incompatible - it could easily break clients that expect fields and arguments to still be type ID, and should only be considered before a schema is released in any form.