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¶
{: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.
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:
#!/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).
{: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.
(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.
(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. |