pod-babashka-go-sqlite3

A babashka pod for interacting with sqlite3.

Implemented using the Go go-sqlite3 and transit libraries.

Usage

Load the pod and pod.babashka.go-sqlite3 namespace:

(ns sqlite3-script
  (:require [babashka.pods :as pods]))

(pods/load-pod 'org.babashka/go-sqlite3 "0.1.0")
(require '[pod.babashka.go-sqlite3 :as sqlite])

The namespace exposes two functions: execute! and query. Both accept a path to the sqlite database and a query vector:

(sqlite/execute! "/tmp/foo.db"
  ["create table if not exists foo (the_text TEXT, the_int INTEGER, the_real REAL, the_blob BLOB)"])

;; This pod also supports storing blobs, so lets store a picture.
(def png (java.nio.file.Files/readAllBytes (.toPath (io/file "resources/babashka.png"))))

(sqlite/execute! "/tmp/foo.db"
  ["insert into foo (the_text, the_int, the_real, the_blob) values (?,?,?,?)" "foo" 1 3.14 png])
;;=> {:rows-affected 1, :last-inserted-id 1}

(def results (sqlite/query "/tmp/foo.db" ["select * from foo order by the_int asc"]))
(count results) ;;=> 1

(def row (first results))
(keys row) ;;=> (:the_text :the_int :the_real :the_blob)
(:the_text row) ;;=> "foo"

;; Should be true:
(= (count png) (count (:the_blob row)))

Additionally, unparameterised queries are supported if a string is passed

(sqlite/query "/tmp/foo.db" "select * from foo")

Passing any other kind of data apart from a string or a vector will throw.

See test/script.clj for an example test script.

HoneySQL

HoneySQL is a babashka-compatible library for turning Clojure data structures into SQL.

(ns honeysql-script
  (:require [babashka.deps :as deps]
            [babashka.pods :as pods]))

;; Load HoneySQL from Clojars:
(deps/add-deps '{:deps {honeysql/honeysql {:mvn/version "1.0.444"}}})

(require '[honeysql.core :as sql]
         '[honeysql.helpers :as helpers])

(pods/load-pod 'org.babashka/go-sqlite3 "0.1.0")
(require '[pod.babashka.go-sqlite3 :as sqlite])

(sqlite/execute! "/tmp/foo.db" ["create table if not exists foo (col1 TEXT, col2 TEXT)"])

(def insert
  (-> (helpers/insert-into :foo)
      (helpers/columns :col1 :col2)
      (helpers/values
       [["Foo" "Bar"]
        ["Baz" "Quux"]])
      sql/format))
;; => ["INSERT INTO foo (col1, col2) VALUES (?, ?), (?, ?)" "Foo" "Bar" "Baz" "Quux"]

(sqlite/execute! "/tmp/foo.db" insert)
;; => {:rows-affected 2, :last-inserted-id 2}

(def sqlmap {:select [:col1 :col2]
             :from   [:foo]
             :where  [:= :col1 "Foo"]})

(def select (sql/format sqlmap))
;; => ["SELECT col1, col2 FROM foo WHERE col1 = ?" "Foo"]

(sqlite/query "/tmp/foo.db" select)
;; => [{:col1 "Foo", :col2 "Bar"}]

See test/honeysql.clj for a HoneySQL example script.

Build

Requirements

Go 1.15+ should be installed.
Clone this repo.
Run go build -o pod-babashka-go-sqlite3 main.go to compile the binary.

License

License: BSD 3-Clause

babashka / pod-babashka-go-sqlite3