[230305] clojure and sqlite

1. Motivation
2. code
- 2.1. deps.edn
- 2.2. core.clj
3. with next.jdbc
4. reference

1 Motivation

I've encountered an application called "Litestream" basically. basically, it backs-up sqlite data including WAL. I think this is a great idea to use sqlite in production for my personal web service (which I don't have now).

there is a good service called "fly.io". they are thinking of using sqlite seriously.

so, I think sqlite will grow more. it is efficient and cheap(this is a main reason for my future web service)

let's take a look. how to use it.

2 code

2.1 deps.edn

{:path ["src"]
 :deps [org.clojure/java.jdbc {:mvn/version "0.7.12"}]}

2.2 core.clj

(ns core
  (:require [clojure.java.jdbc :as jdbc]))

(def db
  {:connection-uri "jdbc:sqlite:db/review.db"})

(defn create-table
  []
  (jdbc/db-do-commands db
		       (jdbc/create-table-ddl :reviews
					      [[:content :text]])))

(defn init-test-data
  []
  (let [data {:content "this is content"}]
    (jdbc/insert! db :reviews data)))

(defn print-all-reviews
  []
  (jdbc/query db ["select rowid, * from reviews"]))

(comment
  (create-table)
  (init-test-data)
  (print-all-reviews))

really simple

3 with next.jdbc

(ns core
  (:require [next.jdbc :as jdbc]))

(def db
  {:connection-url "jdbc:sqlite:b/review.db"})

(defn create-db
  []
  (jdbc/execute! db
		 ["create table reviews (content text)"]))

(defn init-test-data
  []
  (jdbc/execute-one! db ["insert into reviews(content) values ('number 1'),
							      ('number 2'),
							      ('number 3')"]))
(defn print-all-reviews
  []
  (jdbc/execute! db ["select rowid, * from reviews"]))

(comment
  (create-db)
  (init-test-data)
  (print-all-reviews)
  ;;
  )