Liking cljdoc? Tell your friends :D

Fulcro SQL

This library is under development. The API can change, especially around the graph traversal code. The general PostgreSQL database component is fairly solid as are the seeding and test utilities.

A utility library for working with SQL databases. It includes the following:

  • A com.stuartsierra component for starting, building a configurable connection pooling datasource, and running migrations.

  • A with-database test wrapper, for easily writing integration tests, that manages the complete lifecycle of a test database within each test.

  • Database seeding tools, useful for both testing, development, and production.

  • Tools that can convert the Om Next (Datomic pull query) syntax into an SQL query.

Thus, it is useful for general SQL development (the graph utilities are relatively small), and is particularly well-suited when you need to use SQL databases from Fulcro or Om Next.

Connection pooling is provided by HikariCP. Migrations are provided by Flyway.

Configuring the SQLDatabaseManager

The current version comes with a PostgreSQLDatabaseManager. SQLDatabaseManager is a protocol that defines two methods: start-databases and get-dbspec. It is meant to manage any number of instances (and their associated datasources).

The PostgreSQLDatabaseManager expects that have a config injected into it. It assumes it will take the same basic form that is generated by Fulcro’s server config code. That is to say, config has the following shape (as a component):

{:value
  { :sqldbm
    { :database-name {:hikaricp-config "test.properties"
                      :auto-migrate? true
                      :create-drop? true
                      :migrations      ["classpath:migrations/test"]}}}}

The top two keys (:value and :sqldbm) are in the path for the map because the assumption is you’ll be using a more complex config where other data may appear that you do not want to collide with. If you are using Fulcro server, then this allows you to place this in your config file:

{ :port   3000
  :sqldbm { :database-name {:hikaricp-config "test.properties"
                            :auto-migrate?   true
                            :create-drop?    true
                            :migrations      ["classpath:migrations/test"]}}}

The allowed keys are:

  • hikaricp-config : A relative (if on classpath) or absolute (if on disk) path to the Java properties file (on classpath if relative, or disk if absolute) that contains your desired configuration for HikariCP connection pooling.

  • migrations : A vector of directories that can be prefixed with classpath: or filesystem. The named directories will be searched for migrations to run. See Flyway documentation for the naming conventions of these files.

Example files:

in resources/migrations we could place V1__initial.sql:

CREATE TABLE boo (id serial);

on the filesystem disk we could write /usr/local/etc/pool.props:

dataSourceClassName=org.postgresql.ds.PGSimpleDataSource
dataSource.user=test
dataSource.password=
dataSource.databaseName=test
dataSource.portNumber=5432
dataSource.serverName=localhost

Then the configuration for this database would be:

{:value
  { :sqldbm
    { :test {:hikaricp-config "/usr/local/etc/pool.props"
             :auto-migrate? true
             :create-drop? true
             :migrations      ["classpath:migrations"]}}}}

Describing Your Schema

In order for most of this library to work you must describe the parts of your schema that you want to use with it. This can be placed into an EDN map anywhere in your source. It is probably also possible to automate the generation of it with a little bit of elbow grease against your database’s metadata.

(ns schema
  (:require [fulcr-sql.core :as core]))

(def schema {::core/om->sql {}
             ::core/joins   {:account/members [:account/id :member/account_id]}
             ::core/pks     {:account :id
                             :member  :id}})

  • ::core/om→sql - A map from Om property names to SQL. The SQL naming must follow the convention :table/column, where the table and column portions exactly match a table and column name in your database. This allows you to use whatever UI Om properties you wish, and map them to their correct location in the database.

  • ::core/joins - A map whose key is an Om property that is obtained by following an SQL join, and whose value is a vector of SQL :table/col keywords that describe (in order) the tables and columns that have to be traversed to resolve the join. (WORK IN PROGRESS. MAY CHANGE)

  • ::core/pks - A map whose keys are SQL table names (as keywords) and the columns that represent the PK for that table. These default to :id, so technically you only need them if you used something else.

Configuring The Connection Pooling

The connection pooling is provided by HikariCP. In order to support testing, development, and adminstrative production tuning we use the properties-based configuration. This allows you to specify a Java properties file on the classpath or the disk.

The tests for this library have a connection pool set up for use with PostgreSQL in test-resources/test.properties.

Writing Migrations

The migration support is provided by Flyway. Here are the basics:

  1. Define some direction (typically in resources) that will hold SQL files.

  2. Tell this library where that is (see configuration above).

  3. Indicate during startup that you want migration to happen, or write a separate command-line utility or something that can run them by starting a database with migrations turned on (you might want to be paranoid about migrations in production).

See Flyway’s documentation for more details. You can also examine the test suite of this library, which has migrations in test-resources/migrations/test.

Seeding Data

The fulcro-sql.core/seed! function is a simple but powerful way to put data in your database for a number of development, testing, and production reasons:

  • Seeding tests

  • Adding data that has to always be in a production database

  • As a way to write convenient mutation functions. (e.g. when the creation of an object requires insertions and relations).

The seed! function is row-based, but it includes support for ID generation, relations, and the return of the IDs of interest. Here is an example:

Say I want to insert two rows: A person and their address. Address has a FK pointer back to the person. After the insertion, I’d like to know the IDs of the resulting person and address (perhaps for return to the caller, or for test assertions):

(let [rows [(core/seed-row :person {:id :id/joe :name "Joe"})
            (core/seed-row :address {:id :id/address :street "111 Nowhere" :person_id :id/joe})]
      {:keys [id/joe id/address]} (core/seed! db schema rows)]
  ... use `joe` and `address`, which are numbers that correspond to the db row PKs ...)

Keywords-as-ids must appear in a PK column before they are used anywhere else. If you fail to do this then seeding will fail with a database error, since it won’t understand the (unresolved) keyword as an ID. However, this restriction may cause you problems, since some combinations of inserts have loops in them.

In order to resolve this there is also a core/seed-update function that can be used in the vector of items to seed. It is needed when you cannot resolve the order of inserts. Say your person table had a last_updated_by column whose FK pointed to person.id. If Joe last updated Sam and Sam last updated Joe, you’d need this:

(let [rows [(core/seed-row :person {:id :id/joe :name "Joe"})
            (core/seed-row :person {:id :id/sam :name "Sam" :last_updated_by :id/joe})
            (core/seed-update :person :id/joe {:last_updated_by :id/sam})]
      {:keys [id/joe id/sam]} (core/seed! db schema rows)]
  ...)

Writing Integration Tests

Create an alternate connection pool for your tests, typically in the test source or resources of the project, that describes where you’d like to run your test database. Typically you will use the same migrations/schema as your production server.

The combination of seeding and database support makes writing a test very easy. If you’re using fulcro-spec, and have placed your migrations and test.properties on the classpath, then a test specification might look like this:

(def test-database {:hikaricp-config "test.properties"
                    :migrations      ["classpath:migrations"]})
(def schema { ... schema as described above ...})

(specification "Doing things to the database"
  (with-database [db test-database]
    (let [{:keys [rowid/a]} (core/seed! db schema [(core/seed-row :table {:id :rowid/a ...})])]
      (jdbc/query db ...)))

The with-database macro creates a let-like binding environment in which your database is started, migrated, and afterwards cleaned up. You can use seed! to populate your database, etc.

The bound variable (db) is a simple map, containing nothing but :datasource. This is a Java JDBC DataSource, and having it in the map makes it compatible with the clojure.java.jdbc library for convenience.

Integrating With a Fulcro Server

Fulcro comes with a config component that lays out configuration in a way that is compatible with the DatabaseManager component(s) in this library. Remember that the database manager can control any number of databases (of that kind).

(easy/make-fulcro-server
  ; inject config into the database manager
  :components {:dbs (component/using (fulcro-sql.core/map->PostgreSQLDatabaseManager {})} [:config])
  :parser-injections #{:dbs})

and now your server-side reads and mutations can access dbs in the env. You can obtain a dbspec compatible with clojure.java.jdbc using (get-dbspec dbs :dbname). This is just a map with the key :datasource whose value is a connection-pooled JDBC data source:

(defmutation boo [params]
  (action [{:keys [dbs]}]
    (let [dbspec (fulcro-sql.core/get-dbspec dbs :test)]
      (jdbc/insert! dbspec ...))))

Contributing to Fulcro-SQL Development

Please join the #fulcro Slack channel in http://clojurians.slack.com. Discuss how you’d like to help.

Contributions should include tests, and all tests should be passing.

Running tests for development:

  1. Start a REPL

  2. Run (test-suite)

  3. Browse to http://localhost:8888/fulcro-spec-server-tests.html

The tests are encoded via fulcro-spec, but are just plain clj tests, so you can run them however you would normally run clojure tests; however, the output and UI are much better if you use the web-based rendering.

Can you improve this documentation?Edit on GitHub

cljdoc is a website building & hosting documentation for Clojure/Script libraries

Keyboard shortcutsReport a problemcljdoc on GitHub
× close