spikes/database-clojureapp

Which DB has best bindings?

Testing binding for Clojure for Postgresql and MongoDB.

Postgresql

Start postgres

either locally...

initdb pg
postgres -D pg &
createdb decode

....or in a Docker container

Create a docker-compose.yml with:

version: '2'
services:
  db-postgres:
    image: postgres:latest
    ports:
     - "5432:5423"

Run the service in the bg mapping to the port of the host device

docker-compose run -p 5432:5432 -d db-postgres

Interact

Add dependencies to the project.clj

...
  :dependencies [...
                 [org.clojure/java.jdbc "0.7.0"]
                 [org.postgresql/postgresql "42.1.1"]]
...

Make sure you are in the project folder to have the dependencies available, then start repl

lein repl

Require dependencies

user=> (require '[clojure.java.jdbc :as sql])
nil

Create database

user=> (sql/db-do-commands "postgresql://localhost:5432/decode"
                           (sql/create-table-ddl :users [
                           [:id :text][:email :text][:password :text]]))

Insert

user=> (sql/insert-multi! "postgresql://localhost:5432/decode" :users
                    [{:id "1" :email "user@email.com" :password "password"}
                    {:id "2" :email "admin@email.com" :password "password"}])

Get all

user=> (sql/query "postgresql://localhost:5432/decode" ["select * from users"])

Query

user=> (sql/query "postgresql://localhost:5432/decode"
                  ["select * from users where email='admin@email.com'"])

Drop table

user=> (sql/db-do-commands "postgresql://localhost:5432/decode" "drop table users")

Docs

• Jdbc

Other resources

• Connecting to postgres in a docker container
• Populating database

---

MongoDB

Get Mongo

either installing locally...

Install MongoDB

brew install mongodb

Create the folder for MongoDB to store data

mkdir -p /data/db

Give permissions to the folder

sudo chmod -R go+w /data/db

...or in a Docker container

Add a docker-compose.yml with

version: '2'
services:
  db-mongo:
    image: mongo:latest
    ports:
     - "27017:27017"

Run the service in the bg mapping to the port of the host device

docker-compose run -p 27017:27017 -d db-mongo

Interact with Mongo

Add dependencies to the project.clj

...
  :dependencies [...
                 com.novemberain/monger "3.1.0"]]
...

Make sure you are in the project folder to have the dependencies available, then start repl

lein repl

Require dependencies

user=> (require '[monger.core :as mg])
user=> (require '[monger.collection :as mc])
nil

Connect to the database

user=> (let [conn (mg/connect {:port 27017})])

Get/create user document

user=> (let [conn (mg/connect {:port 27017}) db (mg/get-db conn "users")])

Insert data

user=> (let [conn (mg/connect {:port 27017})
      db (mg/get-db conn "users")]
      (mc/insert-batch db "users"
        [{ :_id "1" :email "admin@email.com" :password "password" }
        { :_id "2" :email "test@email.com" :password "password" }]))

Find one as clojure map

user=> (let [conn (mg/connect {:port 27017})
      db (mg/get-db conn "decode")
      data (mc/find-one-as-map db "users" {:email "admin@email.com"})]
      (println data))

Find all as clojure map

(let [conn (mg/connect {:port 27017})
   db (mg/get-db conn "decode")]
   (mc/find-maps db "petitions"))

Docs

• Monger

---

Extra notes

docker run --name postgress-decode -d -p 5434:5432 33b13ed6b80a
docker cp data/users.csv ffbb46e3ada8:/tmp
docker exec -it dbe6da1fc5d6 bash
psql -U postgres -c 'CREATE DATABASE decode;'
psql -U postgres -d decode -c 'CREATE TABLE users (id text, email text, password text);'
psql -U postgres -d decode -c "copy users from '/tmp/users.csv' WITH CSV HEADER;"
psql -U postgres -d 'decode' -c 'SELECT * FROM users;'