MySQL or Postgres Storage for Datomic

In responding to a recent Stack Overflow question, I ran into a few hitches getting MySQL and PostgreSQL working as backend storages. As a reference, I followed this section of the documentation, to setup MySQL and PostgreSQL storage.

MySQL logo Postgresql logo

These snippits assume that you have Postgres and MySQL installed and that you are operating from the Datomic root directory on your local machine.

Setup the SQL database (create the table and users)

Postgres:

psql -f bin/sql/postgres-db.sql -U postgres
psql -f bin/sql/postgres-table.sql -U postgres -d datomic
psql -f bin/sql/postgres-user.sql -U postgres -d datomic

MySQL:

mysql -u root < bin/sql/mysql-db.sql 
mysql -u root datomic < bin/sql/mysql-table.sql
mysql -u root < bin/sql/mysql-user.sql 

Start the transactor

Command:

bin/transactor config/samples/sql-transactor-template.properties 

Postgres properties file:

protocol=sql
host=localhost
port=4334
sql-url=jdbc:postgresql://localhost:3306/datomic
sql-user=datomic
sql-password=datomic
sql-driver-class=org.postgresql.Driver

MySQL properties file:

protocol=sql
host=localhost
port=4334
sql-url=jdbc:mysql://localhost:3306/datomic
sql-user=datomic
sql-password=datomic
sql-driver-class=com.mysql.jdbc.Driver

With the addition of the Client library in 0.9.5530 you can start a peer server to serve access to databases for the client library. If you’re going to do so on SQL storage you’ll need to ensure the database is created before standing up the peer-server. To do so, launch a peer against the transactor (in this case, I executed bin/repl from the Datomic root directory) and run the following:

MySQL:

(require '[datomic.api :as d])
(def uri "datomic:sql://test?jdbc:mysql://localhost:3306/datomic?user=datomic&password=datomic")
(d/create-database uri)

Postgres

(require '[datomic.api :as d])
(def uri "datomic:sql://test?jdbc:postgresql://localhost:3306/datomic?user=datomic&password=datomic")
(d/create-database uri)

Now that you have your database created you can pass in the “test” db name to the peer server connection string:

./bin/run -m datomic.peer-server -h localhost -p 8998 -a myaccesskey,mysecret -d demo,"datomic:sql://test?jdbc:mysql://localhost:3306/datomic?user=datomic&password=datomic"

Please note that when launching your peer-server, you’ll need to ensure you have the proper credentials (username and password) as configured in MySQL or PostgreSQL.

Let me know if you have any questions on setting up MySQL or PostgreSQL as your underlying storage for Datomic.

Cheers,

Jaret

Written on April 25, 2017