Actions

There's three different kind of actions supported by Lobos: create, alter and drop. All actions take an optional first argument which can either be a connection key, a connection spec or a schema. Actions are directly executed against the given or default connection.

create

The create action can be use to insert schemas, tables or indexes.

(create
  (table :users
    (integer :id :primary-key)
    (varchar :name 100)))

alter

The alter action only purpose is the modify a table definition.

(alter (table :users (check :name (> (length :name) 1))))

drop

The drop action can be use to remove schemas, tables or indexes.

(drop (table :users))

Elements

There's a complete set of schema elements which are an abstract representation of SQL objects and their constituent. They've been designed to be easily composed so that you can write you're own helpers to create schemas of arbitrary complexity.

The first one is the Schema element, which can be used to create or drop schemas. It can also be used as the first argument of an action, in which case the schema elements given will produce qualified identifiers once compiled.

[schema-name options? & elements]

It can take an optional map of options which for now only support the :db-spec key. You can specify a number of schema elements that will be created if that schema is passed to the create action.

Schema Elements

Table

The Table element is a macro that compose its given elements. It can take columns, constraints or indexes in any order.

([name & elements])
Macro
  Constructs an abstract table definition containing the given
  elements. Takes an arbitrary number of table elements.

All table elements are functions taking a table element as first argument. You can write your own helpers to construct custom table macro like in the following example.

(defn surrogate-key [table]
  (integer table :id :auto-inc :primary-key))

(defn datetime-tracked [table]
  (-> table
      (timestamp :updated_on)
      (timestamp :created_on (default (now)))))

(defmacro tbl [name & elements]
  `(-> (table ~name
         (surrogate-key)
         (datetime-tracked))
       ~@elements))

Index

Indexes may be created outside a table definition. They need at least a table name and a sequence of column names.

(index :users [:name])

You can also provide an index name and some options. For now only the :unique option is recognized.

(index :users :users_unique_name [:name] :unique)

Take note that at this point, you must provide an index name when you're calling index with options.

Table Elements

Table elements are meant to be used inside the table macro. There are three type of table elements: Column, Constraint and Index.

Column

Columns are contructed using the column function or the typed column function that we'll talked about later. This function take the table it will be attached to, a column name, an optional data-type definition and a set of options.

(column *table* :name (data-type :varchar 100) :unique :not-null)

Here's a list of available options:

  • :unique which construct an unique constraint on that column
  • :primary-key which make the current column the primary key
  • [:refer tname & options] which add a foreign key constraint to the specified table. The options are the same as the foreign-key function with the expection that you can specify only one parent column.
  • :not-null prevents this column from being null
  • :auto-inc (for integers types) which makes it auto-populated with incremented integers
  • [:encoding enc] (for character types) determines which encoding to use if supported by the database. Also see the natianal character types.
  • [:collate type] (for character types) determines how equality is handled
  • :time-zone (for time types) determines if the type includes a time-zone

Constraint

There's three types of constraint: UniqueConstraint, ForeignKeyConstraint and CheckConstraint. Each can be construction using specific helpers with the exception of UniqueConstraint which have two.

(unique *table* [:name])

and

(primary-key *table* [:id])

Foreign key constraint can be defined using the foreign-key functions.

[table name? columns parent-table parent-columns? match? & triggered-actions]

If parent-columns aren't specified, the columns will be used.

The match optional argument can be one of :full, :partial or :simple, but note that this isn't supported by most databases.

You can specify triggered-actions with pairs of keyword, the first of the pairs must be one of :on-delete or :on-update, while the second one can be one of :cascade, :set-null, :restrict, :set-default or :no-action. The actions keywords are directly translated to SQL keywords, so you can specify custom ones if the database you're using provide more.

You can optionally specify the constraint name just after the table for unique and foreign-key contraints.

Index

Indexes can also be defined inside a table definition in the same way as a constraint. See the `Index` schema element documentation above.

Data Types

Columns can be defined using the data-type name as a function. This enable more compact table definition.

Simple Typed Column

Simple typed columns don't take any arguments.

  • smallint
  • integer
  • bigint
  • real
  • double-precision

The double-precision typed column is aliased as double.

Numeric Column

Numeric columns take an optional precision and scale arguments.

[table column-name & [precision scale & options]]
  • numeric
  • decimal

Optional Precision Column

Optional precision columns take an optional precision argument.

[table column-name & [precision & options]]
  • float

Optional Length Column

Optional length columns take an optional length argument.

[table column-name & [length & options]]
  • char
  • nchar
  • clob
  • nclob
  • binary
  • blob

The clob and nclob typed columns are aliased as text and ntext.

Length Bounded Column

Length bounded columns must have a length argument.

[table column-name length & options]
  • varchar
  • nvarchar
  • varbinary

Commented Code

For a better understanding of how this library work you can consult the commented source code: