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 {
async up() {
// highlight-start
this.schema.createTable('users', (table) => {
console.log(table) // 👈 Table builder
})
this.schema.table('users', (table) => {
console.log(table) // 👈 Table builder
})
// highlight-end
}
}
Drop a column by its name.
this.schema.table('users', (table) => {
table.dropColumn('name')
})
Drop more than one column by providing multiple arguments.
this.schema.table('users', (table) => {
table.dropColumns('first_name', 'last_name')
})
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')
})
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 })
})
Add an integer column.
this.schema.createTable('users', (table) => {
table.integer('visits')
})
Adds a bigint
column in MYSQL and PostgreSQL. For all other database drivers, it defaults to a normal integer.
:::note BigInt column values are returned as a string in query results. :::
this.schema.createTable('users', (table) => {
table.bigInteger('visits')
})
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')
})
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)
})
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)
})
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)
})
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')
})
Adds a date column to the database table.
this.schema.createTable('users', (table) => {
table.date('dob')
})
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))
})
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 })
})
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 })
})
Adds created_at
and updated_at
columns to the database table.
:::warning
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)
})
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')
})
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')
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')
})
Same as the json
method but uses the native jsonb
data type (if possible).
this.schema.createTable('projects', (table) => {
table.jsonb('settings')
})
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 '@adonisjs/lucid/schema'
export default class SetupExtensions extends BaseSchema {
async up() {
this.schema.raw('CREATE EXTENSION IF NOT EXISTS "uuid-ossp"')
}
async down() {
this.schema.raw('DROP EXTENSION IF EXISTS "uuid-ossp"')
}
}
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')
})
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')
})
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')
})
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')
})
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')
})
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')
})
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')
})
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')
})
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'])
})
Adds a foreign key constraint to a table for existing columns. Make sure the table already exists when using the foreign
method.
- The methods accepts 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')
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')
})
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'])
})
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()
})
Set the column to be nullable.
this.schema.alterTable('posts', (table) => {
table.setNullable('full_name')
})
Drop the nullable constraint from the column.
:::warning The operation will fail, when the column already has null values. :::
this.schema.alterTable('posts', (table) => {
table.dropNullable('full_name')
})
Following is the list of methods you can chain on the schema building methods as modifiers to the column.
Marks the column as an alters/modify instead of the default add. The method is not supported by SQLite or Amazon Redshift drivers.
:::note
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()
})
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')
})
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'])
})
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()
})
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')
})
Define the table for the foreign key referenced column.
this.schema.table('posts', (table) => {
table.integer('user_id').references('id').inTable('users')
})
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')
})
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')
})
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' })
})
Mark the current column as unsigned.
this.schema.table('posts', (table) => {
table
.integer('user_id')
.unsigned() // 👈
.references('id')
.inTable('users')
})
Mark the current column as NOT nullable.
:::note Consider using dropNullable method when altering the column. :::
this.schema.table('users', (table) => {
table.integer('email').notNullable()
})
Mark the current column as nullable.
:::note Consider using setNullable method when altering the column. :::
this.schema.table('users', (table) => {
table.text('bio').nullable()
})
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()
})
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')
})
Sets the comment for a column
this.schema.alterTable('users', (table) => {
table.string('avatar_url').comment('Only relative names are stored')
})
Sets the collation for a column (only works in MySQL).
this.schema.alterTable('users', (table) => {
table
.string('email')
.unique()
.collate('utf8_unicode_ci')
})