Table builder
The table builder allows you to create, drop, or rename columns on a selected database table.
You get access to the table builder instance by calling one of the following schema builder methods.
class UserSchema extends BaseSchema {
public up() {
this.schema.createTable('users', (table) => {
console.log(table) // 👈 Table builder
})
this.schema.table('users', (table) => {
console.log(table) // 👈 Table builder
})
}
}
Methods/Properties
Following is the list of methods/properties available on the table builder class.
dropColumn
Drop a column by its name.
this.schema.table('users', (table) => {
table.dropColumn('name')
})
dropColumns
Drop more than one column by providing multiple arguments.
this.schema.table('users', (table) => {
table.dropColumns('first_name', 'last_name')
})
renameColumn
Rename a column. The method accepts the existing column name as the first argument and the new name as the second argument.
this.schema.table('users', (table) => {
table.renameColumn('name', 'full_name')
})
increments
Adds an auto-incrementing column. The column is also marked as the primary key unless disabled explicitly.
- In PostgreSQL, the column has the
serial
data type. - In Amazon Redshift, it is an
integer indentity (1,1)
.
this.schema.createTable('users', (table) => {
table.increments('id')
})
Define an incrementing column, but do not mark it as the primary key.
this.schema.createTable('users', (table) => {
table.increments('other_id', { primaryKey: false })
})
integer
Add an integer column.
this.schema.createTable('users', (table) => {
table.integer('visits')
})
bigInteger
Adds a bigint
column in MYSQL and PostgreSQL. For all other database drivers, it defaults to a normal integer.
BigInt column values are returned as a string in query results.
this.schema.createTable('users', (table) => {
table.bigInteger('visits')
})
text
Adds a text column to the database. You can optionally define the text datatype to be mediumtext
or longtext
. The data type is ignored if the underlying driver is not MySQL.
this.schema.createTable('posts', (table) => {
table.text('content_markdown', 'longtext')
})
string
Add a string column with an optional length. The length defaults to 255
, if not specified.
this.schema.createTable('posts', (table) => {
table.string('title')
// Explicit length
table.string('title', 100)
})
float
Adds a float column, with optional precision (defaults to 8) and scale (defaults to 2).
this.schema.createTable('products', (table) => {
table.float('price')
/**
* Explicit precision and scale
*/
table.float('price', 8, 2)
})
decimal
Adds a decimal column, with optional precision (defaults to 8) and scale (defaults to 2).
Specifying null
as precision creates a decimal column that can store numbers of precision and scale. (Only supported for Oracle, SQLite, Postgres)
this.schema.createTable('products', (table) => {
table.decimal('price')
/**
* Explicit precision and scale
*/
table.decimal('price', 8, 2)
})
boolean
Adds a boolean column. Many databases represent true
and false
as 1
and 0
and return the same value during SQL queries.
this.schema.createTable('posts', (table) => {
table.boolean('is_published')
})
date
Adds a date column to the database table.
this.schema.createTable('users', (table) => {
table.date('dob')
})
dateTime
Adds a DateTime column to the database table. The method accepts the column name as the first argument, alongside the options object to configure the precision
and use the timestampz
data type.
- You can enable/disable the
timestampz
data type for PostgreSQL. It is enabled by default. - You can define the column precision for MySQL 5.6+.
this.schema.createTable('users', (table) => {
table
.dateTime('some_time', { useTz: true })
.defaultTo(this.now())
// Or define the precision
table
.dateTime('some_time', { precision: 6 })
.defaultTo(this.now(6))
})
time
Adds a time column with optional precision for MySQL. It is not supported on Amazon Redshift.
this.schema.createTable('users', (table) => {
table.time('some_time', { precision: 6 })
})
timestamp
Adds a timestamp column to the database table. The method accepts the column name as the first argument, alongside the options object to configure the precision
and use the timestampz
data type.
- You can enable/disable the
timestampz
data type for PostgreSQL. It is enabled by default. - Setting
useTz = true
will use theDATETIME2
data type for MSSQL. It is disabled by default. - You can define the column precision for MySQL 5.6+.
this.schema.createTable('users', (table) => {
table.timestamp('created_at')
// Enable timestampz and DATETIME2 for MSSQL
table.timestamp('created_at', { useTz: true })
// Use precision with MySQL
table.timestamp('created_at', { precision: 6 })
})
timestamps
Adds created_at
and updated_at
columns to the database table.
Since AdonisJS uses Knex.js under the hood, your editor autocomplete feature will list the timestamps
method in list of available methods.
However, we recommend not using this method and instead use the timestamp
method for following reasons.
- The
timestamps
method is not chainable. Meaning you cannot add additional constraints likeindex
ornullable
to the column. - You can create columns of type
timestampz
orDatetime2
.
this.schema.createTable('users', (table) => {
table.timestamps()
})
By default, the timestamps
method creates a DATETIME column. However, you can change it to a TIMESTAMP column by passing true
as the first argument.
this.schema.createTable('users', (table) => {
/**
* Creates timestamp column
*/
table.timestamps(true)
})
this.schema.createTable('users', (table) => {
/**
* Creates timestamp column
* +
* Set the default value to "CURRENT_TIMESTAMP"
*/
table.timestamps(true, true)
})
binary
Adds a binary column. The method accepts the column name as the first argument, with an optional length as the second argument (applicable for MySQL only).
this.schema.createTable('users', (table) => {
table.binary('binary_column')
})
enum / enu
Adds an enum column to the database. The method accepts the column name as the first argument, an array of enum options as the second argument, and an optional object of options as the third argument.
- In PostgreSQL, you can use the native enum type by setting the
options.useNative
value to true. Also, make sure to provide a unique name enum name viaoptions.enumName
. - In PostgreSQL, we will create the enum before the column. If the enum type already exists, then you must set
options.existingType
to true. - In Amazon Redshift, unchecked varchar(255) data type is used.
this.schema.createTable('users', (table) => {
table.enu('account_status', ['PENDING', 'ACTIVE', 'SUSPENDED'], {
useNative: true,
enumName: 'user_account_status',
existingType: false,
})
})
You can also specify the PostgreSQL schema for the enum type.
table.enu('account_status', ['PENDING', 'ACTIVE', 'SUSPENDED'], {
useNative: true,
enumName: 'user_account_status',
existingType: false,
schemaName: 'public' // 👈
})
Make sure to drop the enum when dropping the table.
this.schema.raw('DROP TYPE IF EXISTS "user_account_status"')
this.schema.dropTable('users')
json
Adds a JSON column, using the built-in JSON type in PostgreSQL, MySQL and SQLite, defaulting to a text column in older versions or in unsupported databases.
this.schema.createTable('projects', (table) => {
table.json('settings')
})
jsonb
Same as the json
method but uses the native jsonb
data type (if possible).
this.schema.createTable('projects', (table) => {
table.jsonb('settings')
})
uuid
Adds a UUID column. The method accepts the column name as the only argument.
- Uses the built-in UUID type in PostgreSQL
- Uses the
char(36)
for all other databases
this.schema.createTable('users', (table) => {
table.uuid('user_id')
})
Make sure also to create the UUID extension for PostgreSQL. You can also do it inside a dedicated migration file as follows:
import BaseSchema from '@ioc:Adonis/Lucid/Schema'
export default class SetupExtensions extends BaseSchema {
public up() {
this.schema.raw('CREATE EXTENSION IF NOT EXISTS "uuid-ossp"')
}
public down() {
this.schema.raw('DROP EXTENSION IF EXISTS "uuid-ossp"')
}
}
comment
Sets the comment for the table. Accepts the comment value as the only argument.
this.schema.createTable('users', (table) => {
table.comment('Manages the app users')
})
engine
Sets the engine for the database table. The method accepts the engine name as the only argument.
- The method is only available within a
createTable
call. - The engine is only applicable to MySQL and ignored for other databases.
this.schema.createTable('users', (table) => {
table.engine('MyISAM')
})
charset
Sets the charset for the database table. The method accepts the charset value as the only argument.
- The method is only available within a
createTable
call. - The charset is only applicable to MySQL and ignored for other databases.
this.schema.createTable('users', (table) => {
table.charset('utf8')
})
collate
Sets the collation for the database table. The method accepts the collation value as the only argument.
- The method is only available within a
createTable
call. - The collation is only applicable to MySQL and ignored for other databases.
this.schema.createTable('users', (table) => {
table.collate('utf8_unicode_ci')
})
inherits
Set the parent table for inheritance. The method accepts the parent table name as the only argument.
- The method is only available within a
createTable
call. - The
inherits
is only applicable to PostgreSQL and ignored for other databases.
this.schema.createTable('capitals', (table) => {
table.inherits('cities')
})
specificType
Create a column by defining its type as a raw string. The method allows you to create a database column, which is not covered by the standard table builder API.
The first argument is the column name, and the second argument is column type.
this.schema.createTable('users', (table) => {
table.specificType('mac_address', 'macaddr')
})
index
Adds an index to a table over the given columns. You must create the table before defining the index.
- The method accepts an array of columns as the first argument.
- An optional index name as the second argument
- And an optional index type as the third argument. The index type is only applicable for PostgreSQL and MySQL databases.
this.schema.alterTable('users', (table) => {
table.index(['first_name', 'last_name'], 'user_name_index')
})
dropIndex
Drop an existing index from the table columns. The method accepts columns as the first argument and an optional index name as the second argument.
this.schema.alterTable('users', (table) => {
table.dropIndex(['first_name', 'last_name'], 'user_name_index')
})
unique
Adds a unique index to a table over the given columns. A default index name using the columns is used unless indexName
is specified.
this.schema.alterTable('posts', (table) => {
table.unique(['slug', 'tenant_id'])
})
foreign
Adds a foreign key constraint to a table for existing columns. Make sure the table already exists when using the foreign
method.
- The methods one or more column names as the first argument.
- You can define a custom
foreignKeyName
as the second argument. If not specified, the column names are used to generate it.
this.schema.alterTable('posts', (table) => {
table.foreign('user_id').references('users.id')
})
You can also chain the onDelete
and onUpdate
methods to define the triggers.
table
.foreign('user_id')
.references('users.id')
.onDelete('CASCADE')
dropForeign
Drop a pre-existing foreign key constraint. The method accepts one or more columns as the first argument and an optional foreign key name as the second argument.
this.schema.alterTable('posts', (table) => {
table.dropForeign('user_id')
})
dropUnique
Drop a pre-existing unique index. The method accepts an array of string(s) representing column names as the first argument and an optional index name as the second argument.
this.schema.alterTable('posts', (table) => {
table.dropUnique(['email'])
})
dropPrimary
Drop a pre-existing primary key constraint. The method accepts an optional constraint name (defaults to tablename_pkey
).
this.schema.alterTable('posts', (table) => {
table.dropPrimary()
})
setNullable
Set the column to be nullable.
this.schema.alterTable('posts', (table) => {
table.setNullable('full_name')
})
dropNullable
Drop the nullable constraint from the column.
The operation will fail, when the column already has null values.
this.schema.alterTable('posts', (table) => {
table.dropNullable('full_name')
})
Chainable methods
Following is the list of methods you can chain on the schema building methods as modifiers to the column.
alter
Marks the column as an alters/modify instead of the default add. The method is not supported by SQLite or Amazon Redshift drivers.
The alter statement is not incremental. You must redefine the constraints that you want to apply to the column.
this.schema.alterTable('posts', (table) => {
// drops both NOT NULL constraint and the default value (if applied earlier)
table.integer('age').alter()
})
index
Define an index for the current column. The method accepts the following two optional arguments.
- An optional index name as the first argument.
- And an optional index type as the second argument. The index type is only applicable for PostgreSQL and MySQL databases.
this.schema.table('posts', (table) => {
table.string('slug').index('posts_slug')
})
primary
Mark the current column as the primary key. Optionally, you can define the constraint name as the first argument.
On Amazon Redshift, all columns included in a primary key must be not nullable.
this.schema.table('posts', (table) => {
table.integer('id').primary()
})
If you want to define a composite primary key, you must use the table.primary
method.
this.schema.table('posts', (table) => {
table.primary(['slug', 'tenant_id'])
})
unique
Mark the current column as unique. On Amazon Redshift, this constraint is not enforced, but the query planner uses it.
this.schema.table('users', (table) => {
table.string('email').unique()
})
references
Define the column that the current column references as a foreign key.
this.schema.table('posts', (table) => {
table.integer('user_id').references('id').inTable('users')
})
You can also define the tableName.columnName
together and remove the inTable
method all together.
this.schema.table('posts', (table) => {
table.integer('user_id').references('users.id')
})
inTable
Define the table for the foreign key referenced column.
this.schema.table('posts', (table) => {
table.integer('user_id').references('id').inTable('users')
})
onDelete
Define the onDelete
command for the foreign key. The command is expressed as a string value.
this.schema.table('posts', (table) => {
table
.integer('user_id')
.references('id')
.inTable('users')
.onDelete('CASCADE')
})
onUpdate
Define the onUpdate
command for the foreign key. The command is expressed as a string value.
this.schema.table('posts', (table) => {
table
.integer('user_id')
.references('id')
.inTable('users')
.onUpdate('RESTRICT')
})
defaultTo
Define the default value for the column to be used during the insert.
In MSSQL a constraintName option may be passed to ensure a specific constraint name:
this.schema.table('posts', (table) => {
table.boolean('is_published').defaultTo(false)
// For MSSQL
table
.boolean('is_published')
.defaultTo(false, { constraintName: 'df_table_value' })
})
unsigned
Mark the current column as unsigned.
this.schema.table('posts', (table) => {
table
.integer('user_id')
.unsigned() // 👈
.references('id')
.inTable('users')
})
notNullable
Mark the current column as NOT nullable.
Consider using dropNullable method when altering the column.
this.schema.table('users', (table) => {
table.integer('email').notNullable()
})
nullable
Mark the current column as nullable.
Consider using setNullable method when altering the column.
this.schema.table('users', (table) => {
table.text('bio').nullable()
})
first
Sets the column to be inserted on the first position, only used in MySQL alter tables.
this.schema.alterTable('users', (table) => {
table.string('email').first()
})
after
Sets the column to be inserted after another, only used in MySQL alter tables.
this.schema.alterTable('users', (table) => {
table.string('avatar_url').after('password')
})
comment
Sets the comment for a column
this.schema.alterTable('users', (table) => {
table.string('avatar_url').comment('Only relative names are stored')
})
collate
Sets the collation for a column (only works in MySQL).
this.schema.alterTable('users', (table) => {
table
.string('email')
.unique()
.collate('utf8_unicode_ci')
})