db

Clojure SQL Superpowers

Warning this library is a work in progress

Install

Add this thing to your deps.edn file along with either sqlite or postgres jdbc adapters

coast-framework/db {:mvn/version "0.1.0-SNAPSHOT"}
org.xerial/sqlite-jdbc {:mvn/version "3.28.0"}

Docs

There are some pretty comprehensive docs available for this monster library

Read the docs

Quickstart

This library handles everything you need for database management in a web application. Consider this section more of a crash course than an easy snippet to copy.

Create a database

Create a sqlite database, the process is similar for a postgres database. First create a db.edn file in the root of your project or in your resources folder for you uberjar-ers out there.

cd your-project-folder && touch db.edn

Fill that db.edn in:

{:dev {:database "usability_dev.sqlite3"
       :adapter "sqlite"
       :debug true}

 :test {:database "usability_test.sqlite3"
        :adapter "sqlite"
        :debug true}

 :prod {:database "usability.sqlite3"
        :adapter "sqlite"}}

Now we're ready to create the :dev database:

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

(db/create (db/context :dev))

Migrations

Unlike other clojure sql libraries, this one also does migrations! Create a migration like this:

(let [ctx (db/context :dev)]
  (db/migration "create-table-account" "name:text" "email:text" "password:text"))

This creates a new folder in your project, db and it also creates a migrations subfolder in that folder with a file named something like this 20190725281234_create_table_account.clj that looks like this:

(ns 20190725281234-create-table-account
  (:require [db.migrator.helper :refer :all]))

(create-table :account
  (text :name :null false)
  (text :email :unique true :null false)
  (text :password :null false))

I took the liberty of adding the :null false and :unique true bits.

Connections

Create a connection pool and connect to the database

(def conn (db/connect (db/context :dev)))

Go ahead and run that migration

(db/migrate conn)

It's just that easy. If you make a mistake don't forget to rollback

(db/rollback conn)

Insert rows

Inserts, updates and deletes are designed to be easy, not simple

(db/insert conn {:account {:name "name" :email "[email protected]" :password "pw"}})

Insert two or more records

(db/insert-all conn {:account [{:name "name1" :email "[email protected]" :password "pw"}
                               {:name "name2" :email "[email protected]" :password "pw"}]})

Queries

There are a few ways to query things. You could get a single row by id

(db/fetch conn [:account 1]) ; => {:name "name" :email "[email protected]" :password "pw"}

or you could get rows by table name

(db/fetch conn [:account]) ; => [{:name "name" ...} {:name "name1" ...} ...]

There's also rows by where clause

(db/from conn {:account {:email "[email protected]" :name "name"}})
; => ["select * from account where email = ? and name = ?", "[email protected]", "name"]

; qualified keywords work too
(db/from conn {:account/email "[email protected]" :account/name "name"})
; => ["select * from account where email = ? and name = ?", "[email protected]" "name"]

Or a more complex query

(db/q '[:select *
        :from account
        :where email = ?email :or name = ?name
        :order-by id
        :limit 10]
  {:email "[email protected]" :name "name1"})

Update and deletes

Update that last one, oh and you don't need nested maps, qualified keywords work too

(db/update conn {:account/name "name3"} {:email "[email protected]"}
; => ["update account set name = ? where email = ?", "name3", "[email protected]"]

Delete the last two records from before

(db/delete-all conn {:account [{:email "[email protected]"} {:email "[email protected]"}]})
; => ["delete from account where email in (?, ?)", "[email protected]", "[email protected]"]

There's a lot more where that came from.

WAT

What is this monster lib that doesn't follow clojure conventions of small libs?

This is coast's database library and it handles the following:

• Database management (Dropping/Creating new databases)
• Associations (Similar to rails' has-many/belongs-to model definitions)
• Migrations
• SQL Helpers
• Connection Pooling

You either die a small library or you live long enough to become a big one.