Submarine is a Common Lisp library that's somewhere between a PostgreSQL library an an object persistency system. It uses Postmodern to communicate with the database. The basic idea is that you create your classes in the metaclass DB-CLASS and submarine cares about creating SQL tables or, if the tables already exist, checking if they conform to the provided specification. Moreover, Submarine supports an intuitive way of expressing both one-to-many and many-to-many relations.

Table of contents

  1. Getting Submarine
  2. Dependencies
  3. Support and mailing lists
  4. License
  5. Introduction for Postmodern users
  6. API reference
  7. Quick start
  8. Caveats and to-do's

Getting submarine

Submarine is ASDF-installable.

You can also download directly both Submarine, and MOP-utils (a small library of MOP related utilities on which Submarine depends). However, submarine isn't very stable at the moment, so I strongly advise you to get the newest version through darcs.

darcs get

You will also need my MOP utilities, which may be incorporated into submarine in the close future:

darcs get

In both cases (downloading the archive or getting it through darcs) you will need to link the .asd files to some place visible by ASDF.

You can also browse the source of submarine and mop-utils.


Submarine depends on Postmodern and Iterate. It uses also my library of MOP utilities, MOP-UTILS, which may become a separate library in the future. On platforms other than SBCL, mop-utils needs Closer-mop.

Support and mailing lists

The submarine-devel mailing list can be used for questions, discussion, bug-reports, patches, or anything else relating to this library. Or mail the author/maintainer directly: Ryszard Szopa.


Submarine is released under a BSD-like license. Which approximately means you can use the code in whatever way you like, except for passing it off as your own or releasing a modified version without indication that it is not the original.

Introduction for Postmodern users

Submarine started as a patch that added, among others, foreign keys support to Postmodern. This meant hacking the DEFTABLE macro and TABLE and TABLEFIELD classes. After some time I realized that my modified Postmodern macros had become far too large and heavy to be easy maintainable. So, I decided to leave Postmodern's code as it was and write a separate library.

Submarine tries to keep as much as possible of Postmodern's original API. I use the same terminology, and functions with similar names will probably do very similar things. My purpose was twofold. First of all, I wanted to make porting programs using Postmodern to Submarine easy. Secondly, this would allow me to use some of Marijn Haverbeke's superb documentation nearly without any changes.

Main differences between Postmodern and Submarine:

API reference

Here you can find some TINAA generated documentation. I document my code a lot, so it should be quite useful (M-. should be your good friend).

Quick start

API references are great, but they are useful only when you already have a basic idea about what can be done with the library. So, here's a poor man's tutorial for Submarine. The code as presented here isn't really suitable for copying and pasting directly into the REPL. However, you may be interested in this file, which is intended to be opened in Emacs with Slime and then C-c'ed form by form.

First, we should create a package for our example.

(in-package :asdf)

(defpackage :submarine-example
  (:use :cl :submarine))
(in-package :submarine-example)

DEFDAO is a wrapper macro, that creates a class inheriting from DAO and with the metaclass set to db-class.

(defdao affiliation ()
  ((name        :type string :initarg :name        :accessor affiliation-name))
  (:connection-spec "submarine-test" "richard" "dupa" "localhost"))

The arguments in the :connection-spec are the following: name of the database, name of the user, password, host. The user should be able to create tables in the given database.

Let's macroexpand last form:

;;           (:CONNECTION-SPEC "submarine-test" "richard" "dupa" "localhost")
;;           (:METACLASS DB-CLASS))
Every slot that is not transient must have a TYPE---it's needed by PostgreSQL. A person may not have an affiliation, but he or she must have a name. (Not-null is by default set to NIL.)
(defdao person ()
  ((name        :initarg :name :accessor person-name :type string :not-null t)
   (affiliation :accessor person-affiliation :type affiliation :foreign t :initform nil
                :initarg :affiliation))
  (:connection-spec "submarine-test" "richard" "dupa" "localhost"))

(defdao club ()
  ((name :initarg :name :accessor club-name :type string))
  (:connection-spec "submarine-test" "richard" "dupa" "localhost"))

There's a many-to-many relationships between persons and clubs: a club can have several members, one person may be the member of a number of clubs.

(def-many-to-many person club :connection-spec ("submarine-test" "richard" "dupa" "localhost"))

This the macroexpansion of the last form. We can see it defines two methods, for retrieving all objects related to its argument.

;; (progn
;;  (defclass person-club (dao)
;;            ((person :type person :accessor person)
;;             (club :type club :accessor club))
;;            (:connection-spec "submarine-test" "richard" "dupa" "localhost")
;;            (:metaclass db-class))
;;  (defmethod club ((submarine::object person))
;;             (mapcar #'club
;;                     (submarine::select-dao-fun 'person-club
;;                                                (list := 'person
;;                                                      (get-id
;;                                                       submarine::object)))))
;;  (defmethod person ((submarine::object club))
;;             (mapcar #'person
;;                     (submarine::select-dao-fun 'person-club
;;                                                (list := 'club
;;                                                      (get-id
;;                                                       submarine::object))))))

Now, we can populate our database by creating some objects:

(let* ((lions (save-dao (make-instance 'club :name "Lion's")))
       (rotary (save-dao (make-instance 'club :name "Rotary")))
       (lodge (save-dao (make-instance 'club :name "The Lodge")))
       (democ (save-dao (make-instance 'affiliation :name "Democrats")))
       (repub (save-dao (make-instance 'affiliation :name "Republicans")))
       (john (make-instance 'person :name "John"))
       (roger (make-instance 'person :name "Roger" :affiliation democ))
       (henry (make-instance 'person :name "Henry" :affiliation repub)))

As save-dao returns its argument after saving it, we can do it at the same time as initializing the variables.

Let's add the last names.

  (setf (person-name henry) "Henry Johnson")
  (setf (person-name roger) "Roger Monroe")

Little Johny declares himself as a Democrat:

  (setf (person-affiliation john) democ)

  (dolist (person (list john roger henry))
    (save-dao person))

Apparently, they are all masons!

  (dolist (person (list john roger henry))
    (relate person lodge))

But they also belong to other clubs:

  (relate john lions)
  (relate roger rotary)
  (relate henry lions)
  (relate henry rotary)

Now, let's see what do we know about our small world.

  (format t "~@{~2&All ~A: ~%~{ * ~A~&~}~}~&"
	  "persons"            (mapcar 'person-name (select-dao 'person))
	  "democrats"          (mapcar 'person-name (get-all 'person democ))
	  "masons"             (mapcar 'person-name (person lodge))
	  "the clubs of Henry" (mapcar 'club-name (club henry))))

The effect of executing the last form:

; All persons: 
;  * John
;  * Roger Monroe
;  * Henry Johnson

; All democrats: 
;  * John
;  * Roger Monroe

; All masons: 
;  * John
;  * Roger Monroe
;  * Henry Johnson

; All the clubs of Henry: 
;  * The Lodge
;  * Lion's
;  * Rotary

We can also retrieve dao's from the database if we have their ID:

(let ((john (make-instance 'person :id 1)))
  (format t "This is number 1: ~A" (person-name john)))

After executing the last form:

;  This is number 1: John

(Well, I knew it.)

Caveats and to-do's