GitHub - babashka/pod-babashka-go-sqlite3: A babashka pod for interacting with sqlite3. (original) (raw)

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