sqlite3

The sqlite3 module is a simple wrapper around the sqlite3 functions.

simple-sqlite3.idio

import sqlite3

;; we'll use a temporary file for this db
db-file := (libc/make-tmp-file)

db = sqlite3-open db-file

;; sqlite3-exec will accept multiple SQL statements separated by ;
sqlite3-exec db "
create table foods   (id integer primary key, name text, type text);
"

;; a prepared statement using named parameters.
stmt := sqlite3-prepare db "insert into foods values (NULL, :name, :type);"

;; bind values to named parameters
sqlite3-bind stmt ":name" "apple" ":type" "fruit"
sqlite3-step stmt
sqlite3-reset stmt

;; old-school knowledge of parameter indices
sqlite3-bind stmt 1 "banana" 2 "fruit"
sqlite3-step stmt
sqlite3-reset stmt

;; individual bindings -- only accepts parameter indices
sqlite3-bind-text stmt 1 "carrot"
sqlite3-bind-text stmt 2 "vegetable"
sqlite3-step stmt
sqlite3-reset stmt

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;

;; sqlite3-exec accepts a callback function for rows retrieved
sqlite3-exec db "select * from foods where type = 'fruit';" (function (cols names) {
  printf "callback: %s\n" cols
})

;; a row at a time
stmt = sqlite3-prepare db "select * from foods where id = ?;"
sqlite3-bind stmt 1 1
printf "id 1: %s\n" (sqlite3-step stmt)
sqlite3-reset stmt

;; pick out columns from the result
stmt = sqlite3-prepare db "select * from foods where id = ?;"
sqlite3-bind stmt 1 3
sqlite3-step stmt
printf "cols: %s is a %s\n" (sqlite3-column stmt 1) (sqlite3-column stmt 2)
sqlite3-reset stmt

;; db (and any associated statements) will be finalized in due course,
;; otherwise
sqlite3-close db

rm db-file

$ idio simple-sqlite3
callback: ("1" "apple" "fruit")
callback: ("2" "banana" "fruit")
id 1: (1 "apple" "fruit")
cols: carrot is a vegetable

Last built at 2024-05-21T06:11:41Z+0000 from 77077af (dev) for Idio 0.3