Philosophy

Non-opinionated

Crane doesn't drink the ORM Kool Aid: You won't spend a single minute struggling with an interface that claims to be “simple” yet forces you into a limited vision of how databases should work.

To make simple queries, Crane has a simple interface. For more complex stuff, use the SQL query DSL. If you like plain old fashioned SQL, you can use that too — Crane doesn't judge.

Explicit without being verbose

Crane doesn't create tables you don't know about, implicitly follow relations, or generate any unexpected SQL. There are no surprises here.

Usage

Getting it

Crane is available on Quicklisp, just one quick quickload away.

cl-user> (ql:quickload :crane)
  To load "crane":
    Load 1 ASDF system:
      crane
  ; Loading "crane"
  ...
  (:crane)

Configuring and Connecting

First things first: Setup. All Crane needs to get going is a list of databases to connect to, authentication information, and a directory where it will store migrations.

(setup
   :migrations-directory
   (asdf:system-relative-pathname :myapp #p"migrations/")
   :databases
   '(:main
     (:type :postgres
      :name "myapp_db"
      :user "user"
      :pass "user")))

  (connect)

Defining Tables

Crane has a very simple table definition syntax, inspired by that of the Django ORM. This, combined with automatic migrations, lets you rapidly prototype and experiment with table definitions.

(deftable ship ()
    (name :type text :uniquep t)
    (tonnage :type integer))

Automatic Migrations

Migrations are just another part of your development cycle — not an exceptional situation. Just change your table definitions and let Crane figure things out. You don't even have to leave your editor to run a command, locally or in a remote server.

(deftable ship ()
    (name :type text)
    (flag :type text)
    (tonnage :type integer))

  ;; Oops

  (deftable ship ()
    (name :type text :indexp t)
    (flag :type text :nullp nil)
    (tonnage :type integer))

Creating, Saving, and Deleting Objects

Just these three functions. To create an object, use the create function. save and del do exactly what you'd expect.

(let ((instance (create 'ship :name "Dalliance"
                                :tonnage "77")))
    ;; FIXME: It's back luck to rename a ship
    (setf (name instance) "Serenity")
    ;; Expand the cargo hold
    (incf (tonnage instance) 25)
    ;; Save these changes!
    (save instance)
    ;; Time to retire
    (del instance))

High-Level Interface

Simple things should be simple, and the interface is no exception. The majority of database queries in your application will probably be simple filter filter calls.

(filter 'user) ;; Returns everything

  (filter 'user :name "Eudoxia")

  (filter 'user (:> :age 21))

  ;; Returns a single object
  (single 'user :name "Eudoxia")

  ;; Throws an error if this returns more
  ;; than one object
  (single! 'user (:< age 35))

  ;; t if a match exists, nil otherwise
  (exists 'user :name "Eudoxia")

  ;; If this record doesn't exist create it
  (get-or-create 'user :name "Eudoxia" :age 19)

SxQL: Functional, Composable SQL

Most ORMs provide a simple interface that looks simple enough in the examples — but quickly becomes painful to use in real-world cases. The Django ORM tries to get around this by adding various extensions to its basic filter method that allow it to express more complicated queries, but this rapidly becomes cumbersome. Crane's philosophy is: If it's more complicated than a filter, use the SQL DSL.

cl-user> (query (select :tonnage
                    (from :ship)
                    (where (:and (:> :tonnage 125)
                                 (:<= :tonnage 500)))
                    (order-by :tonnage)
                    (limit 10)))
  ;; => ((:|tonnage| 445))

Transactions

Crane provides a macro – with-transaction – that automatically takes care of setting up a transaction and aborting when conditions (Exceptions) are signalled. A manual interface is also provided to provide more fine-grained control over your transactions.

;;;; Automatic
  (with-transaction ()
    (let ((restaurants (filter 'restaurant ...)))
      (loop for restaurant in restaurants do
            ...
            (save restaurant))))

  ;;;; Manual
  (progn
    (begin-transaction)
    (let ((restaurants (filter 'restaurant ...)))
      (loop for restaurant in restaurants do
            ...
            (save restaurant)))
    (commit))

Fixtures

Fixtures are provided through the clos-fixtures library, and can be used for anything from loading mostly unchanging data (A list of countries, for example) to setting up massive datasets for testing.

;;;; initial-data.lisp
  (app:user
    (:name "eudoxia"
     :groups (:admin :staff))
    (:name "joe"
     :groups (:admin)))
  (app:company
    (:name "Initech"
     :city "Denver"))

  ;;;; myapp.asd
  (asdf:defsystem myapp
    :defsystem-depends-on (:clos-fixtures)
    :components ((:module "src"
                  :components
                  ((:fixture "initial-data")))))

Inflation/Deflation

Crane supports inflating values returned by the database into more complex CLOS objects, and deflating those same objects back to an SQL representation. This can be useful for accessing database extensions that provide complex types for columns, like Postgres' PostGIS.

(definflate (stamp 'timestamp)
    ;; Inflate a timestamp value
    ;; into a timestamp object
    (local-time:universal-to-timestamp stamp))

  (defdeflate (stamp local-time:timestamp)
    ;; Deflate a timestamp object
    ;; into a string
    (local-time:format-timestring nil stamp))