A humble database library for janet
jpm install https://github.com/joy-framework/db
You also need one of the following libs for a database driver:
# for postgres
jpm install http://github.com/andrewchambers/janet-pq
# or this for sqlite
jpm install http://github.com/janet-lang/sqlite3
After installing, it should create an executable janet file named db
in (dyn :syspath)/bin
Make sure that directory is in your PATH
db reads your current os environment for the connection string in the variable DATABASE_URL
, which can either start with postgres
or if it doesn't, db defaults to sqlite3.
# your environment variables
# for postgres
DATABASE_URL=postgres://user3123:[email protected]:6212/db982398
# for sqlite
DATABASE_URL=db982398.sqlite3
db supports two databases
- sqlite
- postgres
Run this command to create a sqlite database in the current directory
db create database:sqlite todos_dev.sqlite3
todos_dev.sqlite3
can be any name
Run this command to create a postgres database, assuming a running postgres server and a createdb
cli script in the current PATH
db create database:postgres todos_dev
Creating migrations happens with the same cli program which should get installed when you run jpm install
Note: Make sure you have the janet module bin
folder in your PATH
.
db create migration 'create-table-todos'
This should create a new folder in your current directory named db/migrations
and in that folder, there should be an empty .sql
file named <long-number>-create-table-todos.sql
:
-- up:
-- down:
We can do a little better than that though:
db create table 'todos' 'name text not null' 'completed-at datetime'
This should create a new sql file that looks like this:
-- up:
create table todos (
name text not null,
completed_at datetime
);
-- down:
drop table todos;
Kebab-case gets converted to snake_case automatically.
Run that migration:
db migrate
Roll that migration back just because
db rollback
(import db)
(db/connect (os/getenv "DATABASE_URL"))
; # or
(db/connect) ; # uses the DATABASE_URL environment variable implicitly
(db/disconnect)
Given a table that looks like this:
create table todos (
id integer primary key,
name text
);
insert
(db/insert {:db/table :todos :name "mow the lawn"}) ; # => {:name "mow the lawn" :db/table :todos :id 1}
; # or
(db/insert :todos {:name "mow the lawn"}) ; # => {:name "mow the lawn" :db/table :todos :id 1}
update
(db/update :todos {:id 1 :name "mow the lawn!"}); # => {:name "mow the lawn!" :db/table :todos :id 1}
; # or
(db/update {:db/table :todos :id 1 :name "mow the lawn!"}); # => {:name "mow the lawn!" :db/table :todos :id 1}
delete
(db/delete {:db/table :todos :id 1}) ; # => {:name "mow the lawn!" :db/table :todos :id 1}
; # or
(db/delete :todos 1) ; # => {:name "mow the lawn!" :db/table :todos :id 1}
There are a few ways in db to do queries, the first way is to find a row by primary key
(db/find :todos 1) ; # => {:name "mow the lawn!" :db/table :todos :id 1}
Another way is to "fetch" by primary key, the main difference being is that you can "scope" things by foreign key.
(db/fetch [:todos 1]) ; # => {:name "mow the lawn!" :db/table :todos :id 1}
Let's say there was an accounts table and you wanted to get only the todos for a given account row, so with this schema:
create table accounts (
id integer primary key,
name text
);
create table todos (
id integer primary key,
accounts_id integer references accounts(id),
name text
);
You would get the todos by account like so:
(db/insert :accounts {:name "account #1"})
(db/insert :accounts {:name "account #2"})
(db/insert :todos {:name "todo #1" :account-id 1})
(db/insert :todos {:name "todo #2" :account-id 1})
(db/insert :todos {:name "todo #3" :account-id 2})
(db/insert :todos {:name "todo #4" :account-id 2})
(db/fetch [:accounts 1 :todos 1]) ; # return only the todo row for that account
To return all the rows from an account, use fetch-all
(db/fetch-all [:accounts 1 :todos]) ; # returns all todos for that account
; # you can apply sql options like so:
(db/fetch-all [:accounts 1 :todos] :limit 10 :offset 0 :order "todos.id desc")
That's scoping in db. The next trick is a more flexible way of querying, not by primary key, from
(db/from :accounts
:where ["name like ?" "%#1"]) ; # => [{:id 1 :name "account #1"}]
The same thing applies there with the sql options, :limit
, :order
, :offset
and :join
should all work. Here's another example with a few options:
(db/from :accounts
:join :todos
:where ["id = ?" 1])
; # => [{:id 1 :name "account #1" :todos/id 2 :todos/name "todos #2" ...} ...]
There's one more thing that make from
and find-by
a little special, :join/one
and :join/many
:
(db/from :accounts :join/many :todos)
; # returns
[{:id 1
:name "account #1"
:todos [{:id 1
:name "todo #1"}
{:id 2
:name "todo #2"}]}]
There are a few other things db can do that haven't been documented, check the tests for a more complete look if you're interested.