Schema definition DSL

Schema definition DSL allows you to describe the database structure your application expects.

Currently DbSchema supports tables, fields, primary keys, indexes, foreign keys, check constraints, enum types, and extensions.

Database schema is defined with a block passed to DbSchema.describe method. This block receives a db object on which you can call #table to define a table, #enum to define a custom enum type and #extension to plug a Postgres extension into your database. Everything that belongs to a specific table is described in a block passed to #table.

DbSchema.describe do |db|
  db.extension :hstore

  db.table :users do |t|
    t.serial      :id,       primary_key: true
    t.varchar     :email,    null: false, unique: true
    t.varchar     :password, null: false
    t.varchar     :name,     null: false
    t.integer     :age
    t.user_status :status,   null: false, default: 'registered'
    t.hstore      :tracking, null: false, default: ''
  end

  db.enum :user_status, [:registered, :confirmed_email, :subscriber]

  db.table :posts do |t|
    t.serial      :id,      primary_key: true
    t.integer     :user_id, null: false, index: true, references: :users
    t.varchar     :title,   null: false, length: 50
    t.text        :content
    t.array       :tags,    of: :varchar
  end
end

Tables

Tables are described with the #table method; you pass it the name of the table and describe the table structure in the block:

db.table :users do |t|
  t.varchar :email
  t.varchar :password
end

Fields

You can define a field of any type by calling the corresponding method inside the table block passing it the field name and it's attributes. Most of the attributes are optional.

Here's an example table with various kinds of data:

db.table :people do |t|
  t.varchar     :first_name, length: 50, null: false
  t.varchar     :last_name,  length: 60, null: false
  t.integer     :age
  t.numeric     :salary, precision: 10, scale: 2
  t.text        :about
  t.date        :birthday
  t.boolean     :developer
  t.inet        :ip_address
  t.jsonb       :preferences, default: '{}'
  t.array       :interests, of: :varchar
  t.numrange    :salary_expectations

  t.timestamptz :created_at
  t.timestamptz :updated_at
end

Passing null: false to the field definition makes it NOT NULL; passing some value under the :default key makes it the default value. You can use Strings as SQL strings, Fixnums as integers, Floats as floating point numbers, true & false as their SQL counterparts, Dates as SQL dates and Times as timestamps. A symbol passed as a default is a special case: it is interpreted as an SQL expression so t.timestamp :created_at, default: :'now()' defines a field with a default value of NOW().

Other attributes are type specific, like :length for varchars; the following table lists them all (values in parentheses are default attribute values).

Type	Attributes
smallint
integer
bigint
numeric	precision, scale
real
float
money
smallserial
serial
bigserial
char	length(1)
varchar	length
text
bytea
timestamp
timestamptz
date
time
timetz
interval	fields
boolean
point
line
lseg
box
path
polygon
circle
cidr
inet
macaddr
bit	length(1)
varbit	length
tsvector
tsquery
uuid
json
jsonb
array	of
int4range
int8range
numrange
tsrange
tstzrange
daterange
chkpass
citext
cube
hstore
ean13
isbn13
ismn13
issn13
isbn
ismn
issn
upc
ltree
seg

The of attribute of the array type is the only required attribute (you need to specify the array element type here); other attributes either have default values or can be omitted at all.

You can also use your custom types in the same way: t.user_status :status creates a field called status with user_status type. Custom types are explained in a later section of this document.

Primary keys

Primary keys are defined with a #primary_key method called with a list of columns included in it:

db.table :posts do |t|
  t.serial :id
  t.primary_key :id
end

Since creating a one-column primary key is a very common scenario DbSchema provides a shortcut:

db.table :posts do |t|
  t.serial :id, primary_key: true
end

This differs from the pre-0.5 syntax where you could define both a column and a primary key with t.primary_key :id; this doesn't work anymore.

Keep in mind that a table can have no more than a single primary key.

Indexes

Indexes are created using the #index method: you pass it the field name you want to index:

db.table :users do |t|
  t.varchar :email
  t.index :email
end

Unique indexes are created with unique: true:

t.index :email, unique: true

Simple one-field indexes can be created with index: true and unique: true options passed to the field definition method so

db.table :users do |t|
  t.varchar :name,  index: true
  t.varchar :email, unique: true
end

is essentially the same as

db.table :users do |t|
  t.varchar :name
  t.varchar :email

  t.index :name
  t.index :email, unique: true
end

Passing several field names to #index makes a multiple index:

db.table :users do |t|
  t.varchar :first_name
  t.varchar :last_name

  t.index :first_name, :last_name
end

If you want to specify a custom name for your index, you can pass it in the :name option:

t.index :first_name, :last_name, name: :username_index

Otherwise the index name will be generated as "#{table_name}_#{field_names.join('_')}_index" so the index above will be called users_first_name_last_name_index.

If you need an index with a custom order on some fields you can specify your fields as a hash with keys as field names and values representing the order - it's either ASC (:asc, the default), DESC (:desc), ASC NULLS FIRST (:asc_nulls_first), or DESC NULLS LAST (:desc_nulls_last). It looks like this:

db.table :some_table do |t|
  t.integer :col1
  t.integer :col2
  t.integer :col3
  t.integer :col4

  t.index col1: :asc, col2: :desc, col3: :asc_nulls_first, col4: :desc_nulls_last
end

By default B-tree indexes are created; if you need an index of a different type you can pass it in the :using option:

db.table :users do |t|
  t.array :interests, of: :varchar
  t.index :interests, using: :gin
end

You can also create a partial index if you pass some condition as SQL string in the :where option:

db.table :users do |t|
  t.varchar :email
  t.index :email, unique: true, where: 'email IS NOT NULL'
end

If you need an index on expression you can use the same syntax replacing column names with SQL strings containing the expressions:

db.table :users do |t|
  t.timestamp :created_at
  t.index 'date(created_at)'
end

Expression indexes syntax allows specifying an order exactly like in a common index on table fields - just use a hash form like t.index 'date(created_at)' => :desc. You can also use expressions in a multiple index.

Foreign keys

The #foreign_key method defines a foreign key. In it's minimal form it takes a referencing field name and referenced table name:

db.table :users do |t|
  t.serial  :id, primary_key: true
  t.varchar :name
end

db.table :posts do |t|
  t.integer :user_id
  t.varchar :title

  t.foreign_key :user_id, references: :users
end

The syntax above assumes that this foreign key references the primary key. If you need to reference another field you can pass a 2-element array in :references option, the first element being table name and the second being field name:

db.table :users do |t|
  t.varchar :name
  t.index :name, unique: true # you can only reference either primary keys or unique columns
end

db.table :posts do |t|
  t.varchar :username
  t.foreign_key :username, references: [:users, :name]
end

DbSchema also provides a short syntax for simple one-column foreign keys - just pass the :references option to the field definition:

db.table :posts do |t|
  t.integer :user_id,  references: :users
  t.varchar :username, references: [:users, :name]
end

As with indexes, you can pass your custom name in the :name option; default foreign key name looks like "#{table_name}_#{fkey_fields.first}_fkey".

You can also define a composite foreign key consisting of (and referencing) multiple columns; just list them all:

db.table :table_a do |t|
  t.integer :col1
  t.integer :col2
  t.index :col1, :col2, unique: true
end

db.table :table_b do |t|
  t.integer :a_col1
  t.integer :a_col2
  t.foreign_key :a_col1, :a_col2, references: [:table_a, :col1, :col2]
end

There are 3 more options to the #foreign_key method: :on_update, :on_delete and :deferrable. First two define an action that will be taken when a referenced column is changed or the whole referenced row is deleted, respectively; you can set these to one of :no_action (the default), :restrict, :cascade, :set_null or :set_default. See PostgreSQL documentation for more information.

Passing deferrable: true defines a foreign key that is checked at the end of transaction.

Check constraints

A check constraint is like a validation on the database side: it checks if the inserted/updated row has valid values.

To define a check constraint you can use the #check method passing it the constraint name (no auto-generated names here, sorry) and the condition that must be satisfied, in a form of SQL string.

db.table :users do |t|
  t.serial  :id, primary_key: true
  t.varchar :name
  t.integer :age, null: false

  t.check :valid_age, 'age >= 18'
end

As with indexes and foreign keys, DbSchema has a short syntax for simple check constraints - a :check option in the method definition:

db.table :products do |t|
  t.serial  :id,    primary_key: true
  t.text    :name,  null: false
  t.numeric :price, check: 'price > 0'
end

Enum types

PostgreSQL allows developers to create custom enum types; value of enum type is one of a fixed set of values stored in the type definition.

Enum types are declared with the #enum method (note that you must call it from the top level of your schema and not from within some table definition):

db.enum :user_status, [:registered, :confirmed_email]

Then you can create fields of that type exactly as you would create a field of any built-in type - just call the method with the same name as the type you defined:

db.table :users do |t|
  t.user_status :status, default: 'registered'
end

Arrays of enums are also supported - they are described just like arrays of any other element type:

db.enum :user_role, [:user, :manager, :admin]

db.table :users do |t|
  t.array :roles, of: :user_role, default: '{user}'
end

Extensions

PostgreSQL has a wide variety of extensions providing additional data types, functions and operators. You can use DbSchema to manage extensions in your database:

db.extension :hstore

Note that adding and removing extensions in Postgres requires superuser privileges.