Creates a new table instance.

• @param table_name [Symbol] The name of the table
• @param schema [Schema] The schema this table belongs to
• @param as_name [String, nil] An optional alias for the table
• @raise [Error] If the table name is invalid

Example

table = Table.new(:users, schema)
table = Table.new(:users, schema, as: "user_table")

Adds a new column to the table.

• @param columns [Array(Symbol)] the columns to be indexed
• @param unique [Bool] whether the index should be unique (default: false)
• @param table [Table] the table to add the index to (default: self)
• @return [Index] the new index

Example Adding a new index

add_index([:email], unique: true)
add_index([:email, :username], unique: true)
add_index([:email, :username], unique: true, table: users)

Adds a new column to the table.

• @param name [Symbol] the name of the column to be added
• @param as_name [String, nil] an optional alias for the column
• @param null [Bool] whether the column allows null values (default: false)
• @param default [DB::Any, nil] the default value for the column (default: nil)
• @param unique [Bool] whether the column should have a unique constraint (default: false)
• @param index [Bool] whether the column should be indexed (default: false)
• @return [Column] the new column

Example Adding a new column with default options

bigint :age
bigint :age, as: "user_age", null: false, default: 18, unique: true, index: true

Adds a new column to the table. Blob is a column type that can be used to store binary data. It is a wrapper around the Slice(UInt8) type.

• @param name [Symbol] the name of the column to be added
• @param as_name [String, nil] an optional alias for the column
• @param null [Bool] whether the column allows null values (default: false)
• @param default [DB::Any, nil] the default value for the column (default: nil)
• @param unique [Bool] whether the column should have a unique constraint (default: false)
• @param index [Bool] whether the column should be indexed (default: false)
• @return [Column] the new column

Example Adding a new column with default options

blob :data
blob :data, as: "binary_data", null: false, default: nil, unique: true, index: true

Adds a new column to the table.

• @param name [Symbol] the name of the column to be added
• @param as_name [String, nil] an optional alias for the column
• @param null [Bool] whether the column allows null values (default: false)
• @param default [DB::Any, nil] the default value for the column (default: nil)
• @param unique [Bool] whether the column should have a unique constraint (default: false)
• @param index [Bool] whether the column should be indexed (default: false)
• @return [Column] the new column

Example Adding a new column with default options

boolean :active
boolean :active, as: "is_active", null: false, default: false, unique: true, index: true

Adds a CHECK constraint to the table.

• @param condition [String] The SQL condition for the check constraint.
• @param name [String, nil] Optional name for the constraint.
• @return [CheckConstraint] The created check constraint object.

Example

check_constraint "price > 0"
check_constraint "email LIKE '%@%'", name: "chk_email_format"

Adds a new column to the table.

• @param name [Symbol] the name of the column to be added
• @param type [T.class] the data type of the column
• @param as_name [String, nil] an optional alias for the column
• @param null [Bool] whether the column allows null values (default: false)
• @param default [DB::Any, nil] the default value for the column (default: nil)
• @param unique [Bool] whether the column should have a unique constraint (default: false)
• @param size [Int32, nil] the size of the column (default: nil)
• @param index [Bool] whether the column should be indexed (default: false)
• @return [Column] the new column

Example Adding a new column with default options

column :email, String

Creates the table in the database.

• @return [Nil]

Example

table = Table.new(:users, schema)
table.column(:id, Int64, primary: true)
table.column(:name, String)
table.create!
=> nil

Generates the SQL to create the table. Includes column definitions and foreign key constraints.

Adds a new column to the table.

• @param name [Symbol] the name of the column to be added
• @param as_name [String, nil] an optional alias for the column
• @param null [Bool] whether the column allows null values (default: false)
• @param default [DB::Any, nil] the default value for the column (default: nil)
• @param unique [Bool] whether the column should have a unique constraint (default: false)
• @param index [Bool] whether the column should be indexed (default: false)
• @return [Column] the new column

Example Adding a new column with default options

date :birthday
date :birthday, as: "date_of_birth", null: false, default: Time.local, unique: true, index: true

Adds a new column to the table.

• @param name [Symbol] the name of the column to be added
• @param as_name [String, nil] an optional alias for the column
• @param null [Bool] whether the column allows null values (default: false)
• @param default [DB::Any, nil] the default value for the column (default: nil)
• @param unique [Bool] whether the column should have a unique constraint (default: false)
• @param index [Bool] whether the column should be indexed (default: false)
• @return [Column] the new column

Example Adding a new column with default options

double :age
double :age, as: "user_age", null: false, default: 18.0, unique: true, index: true

Drops the table from the database.

• @return [Nil]

Example

table = Table.new(:users, schema)
table.drop!
=> nil

Generates the SQL to drop the table.

• @return [String] the SQL query

Example

table = Table.new(:users, schema)
table.drop_sql

=> "DROP TABLE users;"

Gets table expression for Sql query generation

• @return [Expression::Table] the table expression

Example

table = Table.new(:users, schema)
table.expression
=> #<Expression::Table:0x00007f8e7a4e1e80>

Adds a new column to the table.

• @param name [Symbol] the name of the column to be added
• @param as_name [String, nil] an optional alias for the column
• @param null [Bool] whether the column allows null values (default: false)
• @param default [DB::Any, nil] the default value for the column (default: nil)
• @param unique [Bool] whether the column should have a unique constraint (default: false)
• @param index [Bool] whether the column should be indexed (default: false)
• @return [Column] the new column

Example Adding a new column with default options

float :age
float :age, as: "user_age", null: false, default: 18.0, unique: true, index: true

Adds a foreign key constraint to the table.

• @param columns [Array(Symbol), Symbol] The column(s) in this table.
• @param references_table [Symbol] The table the foreign key references.
• @param references_columns [Array(Symbol), Symbol, nil] The column(s) in the referenced table. Defaults to the primary key of the referenced table if nil.
• @param name [String, nil] Optional name for the constraint.
• @param on_delete [Symbol] Action on delete (:cascade, :restrict, :set_null, :no_action). Default :no_action.
• @param on_update [Symbol] Action on update (:cascade, :restrict, :set_null, :no_action). Default :no_action.
• @return [ForeignKey] The created foreign key object.

Example

# Simple foreign key referencing the primary key of 'users'
foreign_key :user_id, references: :users

# Foreign key with explicit referenced column and ON DELETE CASCADE
foreign_key :author_id, references: :authors, references_columns: :id, on_delete: :cascade

# Composite foreign key
foreign_key [:order_id, :product_id], references: :order_items, references_columns: [:o_id, :p_id]

Overload to accept a single column symbol

Adds a new column to the table.

• @param name [Symbol] the name of the column to be added
• @param as_name [String, nil] an optional alias for the column
• @param null [Bool] whether the column allows null values (default: false)
• @param default [DB::Any, nil] the default value for the column (default: nil)
• @param unique [Bool] whether the column should have a unique constraint (default: false)
• @param index [Bool] whether the column should be indexed (default: false)
• @return [Column] the new column

Example Adding a new column with default options

integer :age
integer :age, as: "user_age", null: false, default: 18, unique: true, index: true

Adds a new column to the table. Interval is a column type that can be used to store a duration of time. It is a wrapper around the Time::Span type.

• @param name [Symbol] the name of the column to be added
• @param as_name [String, nil] an optional alias for the column
• @param null [Bool] whether the column allows null values (default: false)
• @param default [DB::Any, nil] the default value for the column (default: nil)
• @param unique [Bool] whether the column should have a unique constraint (default: false)
• @param index [Bool] whether the column should be indexed (default: false)
• @return [Column] the new column

Example Adding a new interval column

interval :duration
interval :duration, as: "time_span", null: false, default: Time.local, unique: true, index: true

Adds a new JSON column to the table.

• @param name [Symbol] the name of the column to be added
• @param type [T.class] the Crystal type to map the JSON to (default: JSON::Any)
• @param as_name [String, nil] an optional alias for the column
• @param null [Bool] whether the column allows null values (default: false)
• @param default [DB::Any, nil] the default value for the column (default: nil)
• @param unique [Bool] whether the column should have a unique constraint (default: false)
• @param index [Bool] whether the column should be indexed (default: false)
• @return [Column] the new column

Example Adding a new column with default options

# Defaults to JSON::Any
json :metadata

# Using a custom type
class MySettings
  include JSON::Serializable
  property theme : String
end

json :settings, MySettings

json :metadata, JSON::Any, as: "meta", null: false, default: nil, unique: true, index: true

Adds a lock_version column for optimistic locking. Lock version is used to prevent race conditions when multiple users are updating the same record. It is a counter that is incremented each time the record is updated. If the record is updated by another user, the lock version will be different and the update will fail. The user will then need to retry the operation.

• @param name [Symbol] the name of the column to be added (default: :version)
• @param as_name [String, nil] an optional alias for the column
• @param null [Bool] whether the column allows null values (default: false)
• @param default [DB::Any] the default value for the column (default: 1)
• @param index [Bool] whether the column should be indexed (default: false)
• @return [Column] the new version column

Example Adding a version column for optimistic locking

lock_version :version

Adds a new primary key column to the table.

• @param name [Symbol] the name of the column to be added (default: :id)
• @param type [T.class] the data type of the column (default: Int32)
• @param auto_increment [Bool] whether the column should auto increment (default: true)
• @param as_name [String, nil] an optional alias for the column
• @param unique [Bool] whether the column should have a unique constraint (default: true)
• @return [PrimaryKey(T)] the new primary key column

Example Adding a new primary key column

primary :id, Int32
primary :id, Int32, auto_increment: false

Adds a new column to the table.

• @param name [Symbol] the name of the column to be added
• @param as_name [String, nil] an optional alias for the column
• @param null [Bool] whether the column allows null values (default: false)
• @param default [DB::Any, nil] the default value for the column (default: nil)
• @param unique [Bool] whether the column should have a unique constraint (default: false)
• @param index [Bool] whether the column should be indexed (default: false)
• @return [Column] the new column

Example Adding a new column with default options

decimal :price
decimal :price, as: "product_price", null: false, default: 0.0, unique: true, index: true

Adds a new column to the table.

• @param name [Symbol] the name of the column to be added
• @param as_name [String, nil] an optional alias for the column
• @param null [Bool] whether the column allows null values (default: false)
• @param default [DB::Any, nil] the default value for the column (default: nil)
• @param unique [Bool] whether the column should have a unique constraint (default: false)
• @param index [Bool] whether the column should be indexed (default: false)
• @return [Column] the new column

Example Adding a new column with default options

timestamp :created_at
timestamp :created_at, as: "created_at", null: false, default: Time.local, unique: true, index: true

Adds a new column to the table.

Example Adding timestamps to the table

timestamps

Truncates the table in the database.

• @return [Nil]

Example

table = Table.new(:users, schema)
table.truncate!
=> nil

Generates the SQL to truncate the table.

• @return [String] the SQL query

Example

table = Table.new(:users, schema)
table.truncate_sql
=> "TRUNCATE TABLE users;"

Adds a UNIQUE constraint to the table.

• @param columns [Array(Symbol)] The column(s) to include in the constraint.
• @param name [String, nil] Optional name for the constraint.
• @return [UniqueConstraint] The created unique constraint object.

Example

unique_constraint [:email]
unique_constraint [:first_name, :last_name], name: "uk_person_name"

Adds a new column to the table.

• @param name [Symbol] the name of the column to be added

Returns all columns in the table that are marked as version columns

• @return [Array(Column)] an array of version columns