Schema builder

The schema builder allows you create, alter, drop, and perform other SQL DDL operations.

You can access the schema builder instance using the this.schema property in your migration files.

import BaseSchema from '@ioc:Adonis/Lucid/Schema'

class UserSchema extends BaseSchema {
  public up() {
    console.log(this.schema)
  }
}

Methods/Properties

Following is the list of methods/properties available on the schema builder class.

createTable

Creates a new database table. The method accepts the table name and a callback that receives the table builder instance to create table columns.

class UserSchema extends BaseSchema {
  public up() {
    this.schema.createTable('users', (table) => {
      table.increments()
      table.string('name')
      table.timestamp('created_at', { useTz: true })
      table.timestamp('updated_at', { useTz: true })
    })
  }
}

createSchema

Create the PostgreSQL schema. It accepts the schema name.

class FoundationSchema extends BaseSchema {
  public up() {
    this.schema.createSchema('public')
  }
}

table/alterTable

Select a SQL table to alter its columns. The method accepts the table name and a callback that receives the table builder instance to modify the table columns.

class UserSchema extends BaseSchema {
  public up() {
    this.schema.alterTable('user', (table) => {
      /**
       * Drop the name column
       */
      table.dropColumn('name')

      /**
       * Add first_name and last_name columns
       */
      table.string('first_name')
      table.string('last_name')
    })
  }
}

renameTable

Rename a table. The method accepts the existing table name as the first argument and the new name as the second argument.

class UserSchema extends BaseSchema {
  public up() {
    this.schema.renameTable('user', 'app_users')
  }
}

dropTable

Drop an existing SQL table. The method accepts the table name as the only argument.

class UserSchema extends BaseSchema {
  public down() {
    this.schema.dropTable('users')
  }
}

dropTableIfExists

Similar to the dropTable method, but conditionally drop the table if it exists.

class UserSchema extends BaseSchema {
  public down() {
    this.schema.dropTableIfExists('users')
  }
}

dropSchema

Drop an existing PostgreSQL schema. The method accepts the schema name as the only argument.

class FoundationSchema extends BaseSchema {
  public down() {
    this.schema.dropSchema('public')
  }
}

dropSchemaIfExists

Similar to the dropSchema method, but conditionally drop the schema if it exists.

class FoundationSchema extends BaseSchema {
  public down() {
    this.schema.dropSchemaIfExists('public')
  }
}

raw

Run a SQL query from the raw string. Unlike the raw query builder , the schema.raw method does not accept bindings separately.

class UserSchema extends BaseSchema {
  public up() {
    this.schema
      .raw("SET sql_mode='TRADITIONAL'")
      .table('users', (table) => {
        table.dropColumn('name')
        table.string('first_name')
        table.string('last_name')
      })
  }
}

withSchema

Specify the schema to select when running the SQL DDL statements. The method accepts the schema name as the only argument.

class UserSchema extends BaseSchema {
  public up() {
    this.schema
      .withSchema('public')
      .createTable('users', (table) => {
        table.increments()
        table.string('name')
        table.timestamp('created_at', { useTz: true })
        table.timestamp('updated_at', { useTz: true })
      })
  }
}