Database access for Jolt and Janet: SQLite and PostgreSQL.

clj/jdbc/core.clj implements the clojure.jdbc API over Janet database drivers — no java.sql, no JDBC drivers:

• SQLite via janet-lang/sqlite3 (declared as a :jpm/module dep, auto-installed)
• PostgreSQL via andrewchambers/janet-pq (jpm install https://github.com/andrewchambers/janet-pq; needs libpq — on macOS PKG_CONFIG_PATH=/opt/homebrew/opt/libpq/lib/pkgconfig for the install, and janet-pq's spork dependency needs an up-to-date jpm)

;; deps.edn
jolt-lang/db {:git/url "https://github.com/jolt-lang/db" :git/sha "..."}

(require '[jdbc.core :as jdbc])

(with-open [conn (jdbc/connection "sqlite:app.db")]   ; or "postgres://..."
  (jdbc/execute! conn "create table person (id integer primary key, name text)")
  (jdbc/insert!  conn :person {:name "ada"})
  (jdbc/fetch    conn ["select * from person where name = ?" "ada"])
  ;; => [{:id 1 :name "ada"}]
  (jdbc/atomic conn
    (jdbc/update! conn :person {:name "ada lovelace"} ["id = ?" 1])
    (jdbc/set-rollback! conn)))   ; nested atomics use savepoints

The surface: connection (uri string or dbspec map), execute!, fetch, fetch-one, insert!, insert-multi!, update!, delete!, atomic/atomic-apply, set-rollback!, last-insert-id. Queries are strings or sqlvecs with JDBC ? placeholders (rewritten to $N on postgres). Rows are vectors of keyword-keyed maps.

Divergences from JVM clojure.jdbc: no lazy cursors (fetch-lazy) or prepared-statement objects yet; execute! returns affected rows on sqlite and nil on postgres (use ... returning * with fetch when you need the rows back).

Tests: jolt-deps -M:test (sqlite, in-memory). Set JOLT_TEST_PG_URI=postgres://127.0.0.1/some_db to also run the postgres battery.

The original janet library (from joy-framework/db) lives on under src/ — migrations, a query DSL, and the db CLI script.

jpm install https://github.com/joy-framework/db

You also need one of the following libs for a database driver:

# for postgres
jpm install http://github.com/andrewchambers/janet-pq

# or this for sqlite
jpm install http://github.com/janet-lang/sqlite3

After installing, it should create an executable janet file named db in (dyn :syspath)/bin Make sure that directory is in your PATH

db reads your current os environment for the connection string in the variable DATABASE_URL, which can either start with postgres or if it doesn't, db defaults to sqlite3.

# your environment variables

# for postgres
DATABASE_URL=postgres://user3123:passkja83kd8@ec2-117-21-174-214.compute-1.amazonaws.com:6212/db982398

# for sqlite
DATABASE_URL=db982398.sqlite3

db supports two databases

1. sqlite
2. postgres

Run this command to create a sqlite database in the current directory

db create database:sqlite todos_dev.sqlite3

todos_dev.sqlite3 can be any name

Run this command to create a postgres database, assuming a running postgres server and a createdb cli script in the current PATH

db create database:postgres todos_dev

Creating migrations happens with the same cli program which should get installed when you run jpm install

Note: Make sure you have the janet module bin folder in your PATH.

db create migration 'create-table-todos'

This should create a new folder in your current directory named db/migrations and in that folder, there should be an empty .sql file named <long-number>-create-table-todos.sql:

-- up:
-- down:

We can do a little better than that though:

db create table 'todos' 'name text not null' 'completed-at datetime'

This should create a new sql file that looks like this:

-- up:
create table todos (
  name text not null,
  completed_at datetime
);

-- down:
drop table todos;

Kebab-case gets converted to snake_case automatically.

Run that migration:

db migrate

Roll that migration back just because

db rollback

(import db)

(db/connect (os/getenv "DATABASE_URL"))

; # or

(db/connect) ; # uses the DATABASE_URL environment variable implicitly

(db/disconnect)

Given a table that looks like this:

create table todos (
  id integer primary key,
  name text
);

insert

(db/insert {:db/table :todos :name "mow the lawn"}) ; # => {:name "mow the lawn" :db/table :todos :id 1}
; # or
(db/insert :todos {:name "mow the lawn"}) ; # => {:name "mow the lawn" :db/table :todos :id 1}

update

(db/update :todos {:id 1 :name "mow the lawn!"}); # => {:name "mow the lawn!" :db/table :todos :id 1}
; # or
(db/update {:db/table :todos :id 1 :name "mow the lawn!"}); # => {:name "mow the lawn!" :db/table :todos :id 1}

delete

(db/delete {:db/table :todos :id 1}) ; # => {:name "mow the lawn!" :db/table :todos :id 1}
; # or
(db/delete :todos 1) ; # => {:name "mow the lawn!" :db/table :todos :id 1}

There are a few ways in db to do queries, the first way is to find a row by primary key

(db/find :todos 1) ; # => {:name "mow the lawn!" :db/table :todos :id 1}

Another way is to "fetch" by primary key, the main difference being is that you can "scope" things by foreign key.

(db/fetch [:todos 1]) ; # => {:name "mow the lawn!" :db/table :todos :id 1}

Let's say there was an accounts table and you wanted to get only the todos for a given account row, so with this schema:

create table accounts (
  id integer primary key,
  name text
);

create table todos (
  id integer primary key,
  accounts_id integer references accounts(id),
  name text
);

You would get the todos by account like so:

(db/insert :accounts {:name "account #1"})
(db/insert :accounts {:name "account #2"})

(db/insert :todos {:name "todo #1" :account-id 1})
(db/insert :todos {:name "todo #2" :account-id 1})
(db/insert :todos {:name "todo #3" :account-id 2})
(db/insert :todos {:name "todo #4" :account-id 2})

(db/fetch [:accounts 1 :todos 1]) ; # return only the todo row for that account

To return all the rows from an account, use fetch-all

(db/fetch-all [:accounts 1 :todos]) ; # returns all todos for that account

; # you can apply sql options like so:

(db/fetch-all [:accounts 1 :todos] :limit 10 :offset 0 :order "todos.id desc")

That's scoping in db. The next trick is a more flexible way of querying, not by primary key, from

(db/from :accounts
         :where ["name like ?" "%#1"]) ; # => [{:id 1 :name "account #1"}]

The same thing applies there with the sql options, :limit, :order, :offset and :join should all work. Here's another example with a few options:

(db/from :accounts
         :join :todos
         :where ["id = ?" 1])

; # => [{:id 1 :name "account #1" :todos/id 2 :todos/name "todos #2" ...} ...]

There's one more thing that make from and find-by a little special, :join/one and :join/many:

(db/from :accounts :join/many :todos)

; # returns

[{:id 1
  :name "account #1"
  :todos [{:id 1
           :name "todo #1"}
          {:id 2
           :name "todo #2"}]}]

There are a few other things db can do that haven't been documented, check the tests for a more complete look if you're interested.