A Common Lisp library that provides a user friendly layer over the new jsonb type of PostgreSQL 9.4, allowing trivial storage and retrieval of JSON documents. Thanks to the excellent JSON libraries for Common Lisp, Postgres-JSON thus facilitates easy serialization of lisp data structures to and from a proper database.
-
You have some existing JSON documents you want to store persistently.
-
You want to serialize Common Lisp hash tables, lists, vectors, et cetera to a database.
-
You like the ACID qualities of PostgreSQL but rigid data schema are not suitable for your project.
In some sense Postgres-JSON is a primitive NoSQL document database. It was inspired by the excellent cl-rethinkdb interface to RethinkDB but uses a traditional blocking I/O interface via Postmodern.
-
Marijn Haverbeke's wonderful Postmodern.
-
Any Common Lisp JSON library. Yason is a dependency of this project but you can use whatever library you like: see the comparison by Sabra On The Hill: [JSON libraries] (https://sites.google.com/site/sabraonthehill/home/json-libraries).
As at January 2015 and according to the langauge of Semantic versioning Postgres-JSON is in the "initial development phase". Anything might change.
That said, the interface is relatively stable and the documentation largely complete. So do not use it in production but any and all feedback is most welcome to [email protected] or by raising a GitHub issue.
- [Beginner's guide to JSON with Common Lisp] (doc/beginners.md)
- User's Guide
- API
Most of the library code has docstrings.
You will need a working PostgreSQL 9.4 install. On Debian this may be
as simple as apt-get install postgresql-9.4. If this does not work, see
https://wiki.postgresql.org/wiki/Apt for help updating your apt sources.
Once installed, try pg_lsclusters to see what port your 9.4 install
is on, if it is not 5432 you will need to explicitly supply the port
as I have in the example below. pg_upgradecluster may have been
automatically run for you, in which case your new install may already
be on port 5432.
If this is your first time using Postgres you can setup a database
user to match your unix login (in my case gtod) at the unix shell as
follows:
sudo su postgres
createuser gtod
createdb -O gtod mydb
exit
psql -l or psql -p5433 -l should now list your new database.
For passwordless Postmodern connections I edit the
/etc/postgresql/9.4/main/pg_hba.conf file (which may be elsewhere on
non Debian systems). There is a line:
host all all 127.0.0.1/32 md5
Change md5 to trust. See auth
trust
for the pros and cons of such an approach.
Then sudo service postgresql restart. Again, may be different on
non Debian systems.
Navigate to your ~/quicklisp/local-projects directory and do
git clone https://github.com/gtod/postgres-json.git. Then at your
REPL evaluate:
(ql:register-local-projects)
(ql:quickload :postgres-json)Now:
(defpackage :simple
(:use :cl :postgres-json))
(in-package :simple)
;; Change to suit your Postgres DB
(setf *postmodern-connection* '("mydb" "gtod" "" "localhost" :port 5433))
(ensure-top-level-connection)
;; Create a Postgres-JSON model (and global instance) to store cats
(define-global-model cat -cat- (pgj-object-model))
;; Ensure there is a database backend for our cat model
(ensure-backend -cat-)In the output below I have elided some of the return values for
brevity. obj is a trivial function to turn a list of pairs into a
hash table. pp-json is a trivial function to pretty print a nested
lisp object of hash tables and sequences as JSON.
(insert -cat- (obj "name" "joey" "coat" "tabby"))
1
(pp-json (fetch -cat- 1))
{
"key":1,
"coat":"tabby",
"name":"joey"
}
(insert -cat- (obj "name" "max" "coat" "ginger"))
(insert -cat- (obj "name" "maud" "coat" "tortoiseshell"))
(keys -cat-)
(1 2 3)
(excise -cat- 2)
2
(keys -cat-)
(1 3)
(tally -cat-)
2
(supersede -cat- 3 (obj "name" "maud" "coat" "tortoiseshell" "age" 7
"likes" '("sunshine" 42)))
3
(pp-json (fetch -cat- 3))
{
"age":7,
"key":3,
"coat":"tortoiseshell",
"name":"maud",
"likes":[
"sunshine",
42
]
}See simple for similar code to the above.
There is an extended example in human-1 and
human-2. An example of the simple
customizations available by specializing generic functions is shown in
customize. (ql:quickload :postgres-json-examples) will compile all the examples.
An example user defined query from human-2 and documented in User defined JSON queries:
(define-json-query uncharitable-humans$ ()
(:select (jbuild (human "name") (gift "type" "quantity"))
:from 'human
:inner-join 'gift
:on (:= (j-> human "key") (j-> gift "human-key"))
:where (:= (j-> gift "quantity") (to-jsonb 1))))A reminder that none of this will work unless
*postmodern-connection* is set correctly for your Postgres database.
By writing
(define-global-model cat -cat- (pgj-history-object-model))our cat model descends from a class which maintains history. Now when
you call supersede (which means replace
but is not a Common Lisp standard symbol) or
excise (which means delete...) a JSON
document in a
model, a copy of the current row is
inserted into the <model>_old table before proceeding. So there is
a full history of the document's lifetime.
The whole point is that we are only using a few columns in the PostgreSQL model tables, and just for management purposes: all the goodies are in the JSON. Needless to say it makes sense to keep the objects you end up serializing to a specific model table pretty consistent in their content...
However, I think it may well be practical to support referential integrity, based just on the primary key column in different models. So we should be able to support a CAT owns one or more HUMANS relationship etc. This is the point of using Postgres for JSON documents: we can choose precisely how much of the old fashioned relational database goodness to mix with the new fashioned NoSQL devil may care hedonism...
There is (preliminary) lparallel support in
the postgres-json-parallel system. As at January 2015 you need a
bleeding edge bordeaux-threads to use it. Do a
git clone https://github.com/sionescu/bordeaux-threads.git
in your quicklisp/local-projects directory, register and build, as shown above.
There is a test suite (radically incomplete) which relies upon the lparallel support described above so you cannot run the tests without a recent bordeaux-threads. The same goes for the more informal tests in thread-test.
(ql:quickload :postgres-json-test)
(in-package :postgres-json-test)
(setf *postmodern-connection* '("mydb" "myuname" "" "mydbserver"))
(run-pgj-tests)
It would be nice to have this automated for cl-test-grid but how to surmount the need for a working PostgreSQL 9.4 install?