V1.3: Create Table
Create Table
A table is defined via #create_table. This method accepts two arguments: the name and a block that expresses the design.
Safe operation can be performed via #create_table?. It only creates the table if it doesn’t exist.
Force operation can be performed via #create_table!. It drops the existing table and creates a new one from scratch.
These operations shouldn’t be used in migrations.
Column Definition
To define a column we use #column, followed by the name, the type and options.
The name must be a unique identifier within the table.
The type can be a Ruby type (e.g. String), a symbol that represents a Ruby type (e.g. :string), or a string that represents the raw database type (e.g. "varchar(255)"). The only one exception in case of symbol is :Bignum. Using the symbol :Bignum as a type will use the appropriate 64-bit integer type for the database you are using.
Type Definition
The following Ruby types are supported:
String(varchar(255))Numeric(numeric)Fixnum(integer)Integer(integer):Bignum(bigint) # Note: use this type as a symbol, since Ruby 2.4.0 removed Bignum classFloat(double precision)BigDecimal(numeric)Date(date)DateTime(timestamp)Time(timestamp)TrueClass(boolean)FalseClass(boolean)File(blob)
Their translation from Ruby types to database types may vary from database to database.
Options
It supports the following options:
:default(default value):index(create an index for the column):null(allow NULL values or not):primary_key(make the column primary key for the table):unique(add a uniqueness constraint for the column)
Note that Hanami natively supports PostgreSQL data types. Learn more about them in the dedicated article.
Primary Key
We can define primary keys with the following syntaxes:
column :id, Integer, null: false, primary_key: true
# or just use this shortcut
primary_key :id
Foreign Keys
Foreign keys are defined via #foreign_key, where we specify the name of the column, the referenced table, and a set of options.
The following example creates an author_id column (integer) for books and adds a foreign key.
create_table :books do
# ...
foreign_key :author_id, :authors, on_delete: :cascade, null: false
end
Options
It accepts the following options:
:deferrable(make the constraint check deferrable at the end of a transaction):key(the column in the associated table that this column references. Unnecessary if this column references the primary key of the associated table):null(allow NULL values or not):type(the column type):on_delete(action to take if the referenced record is deleted::restrict,:cascade,:set_null, or:set_default):on_update(action to take if the referenced record is updated::restrict,:cascade,:set_null, or:set_default)
Indexes
Indexes are defined via #index. It accepts the name(s) of the column(s), and a set of options.
create_table :stores do
# ...
column :code, Integer, null: false
column :lat, Float
column :lng, Float
index :code, unique: true
index [:lat, :lng], name: :stores_coords_index
end
Options
It accepts the following options:
:unique(uniqueness constraint):name(custom name):type(the type of index, supported by some databases):where(partial index, supported by some databases)
Constraints
We can define constraints on columns via #constraint. It accepts a name and a block.
create_table :users do
# ...
column :age, Integer
constraint(:adult_constraint) { age > 18 }
end
Please note that the block is evaluated in the context of the database engine, complex Ruby code doesn’t work. Database functions are mapped to Ruby functions, but this reduces the portability of the migration.
create_table :users do
# ...
column :password, String
constraint(:password_length_constraint) { char_length(password) >= 8 }
end
Checks
Checks are similar to constraints, but they accept an anonymous block or a SQL raw string.
create_table :users do
# ...
column :age, Integer
column :role, String
check { age > 18 }
check %(role IN('contributor', 'manager', 'owner'))
end