Class SchemaModule

Alter a table.

Examples

await db.schema
  .alterTable('person')
  .alterColumn('first_name', (ac) => ac.setDataType('text'))
  .execute()
Copy

Create a new index.

Examples

await db.schema
  .createIndex('person_full_name_unique_index')
  .on('person')
  .columns(['first_name', 'last_name'])
  .execute()
Copy

Create a new schema.

Examples

await db.schema
  .createSchema('some_schema')
  .execute()
Copy

Create a new table.

Examples

This example creates a new table with columns id, first_name, last_name and gender:

await db.schema
  .createTable('person')
  .addColumn('id', 'integer', col => col.primaryKey().autoIncrement())
  .addColumn('first_name', 'varchar', col => col.notNull())
  .addColumn('last_name', 'varchar', col => col.notNull())
  .addColumn('gender', 'varchar')
  .execute()
Copy

This example creates a table with a foreign key. Not all database engines support column-level foreign key constraint definitions. For example if you are using MySQL 5.X see the next example after this one.

await db.schema
  .createTable('pet')
  .addColumn('id', 'integer', col => col.primaryKey().autoIncrement())
  .addColumn('owner_id', 'integer', col => col
    .references('person.id')
    .onDelete('cascade')
  )
  .execute()
Copy

This example adds a foreign key constraint for a columns just like the previous example, but using a table-level statement. On MySQL 5.X you need to define foreign key constraints like this:

await db.schema
  .createTable('pet')
  .addColumn('id', 'integer', col => col.primaryKey().autoIncrement())
  .addColumn('owner_id', 'integer')
  .addForeignKeyConstraint(
    'pet_owner_id_foreign', ['owner_id'], 'person', ['id'],
    (constraint) => constraint.onDelete('cascade')
  )
  .execute()
Copy

Create a new type.

Only some dialects like PostgreSQL have user-defined types.

Examples

await db.schema
  .createType('species')
  .asEnum(['dog', 'cat', 'frog'])
  .execute()
Copy

Create a new view.

Examples

await db.schema
  .createView('dogs')
  .orReplace()
  .as(db.selectFrom('pet').selectAll().where('species', '=', 'dog'))
  .execute()
Copy

Drop an index.

Examples

await db.schema
  .dropIndex('person_full_name_unique_index')
  .execute()
Copy

Drop a schema.

Examples

await db.schema
  .dropSchema('some_schema')
  .execute()
Copy

Drop a table.

Examples

await db.schema
  .dropTable('person')
  .execute()
Copy

Drop a type.

Only some dialects like PostgreSQL have user-defined types.

Examples

await db.schema
  .dropType('species')
  .ifExists()
  .execute()
Copy

Drop a view.

Examples

await db.schema
  .dropView('dogs')
  .ifExists()
  .execute()
Copy

Refresh a materialized view.

Examples

await db.schema
  .refreshMaterializedView('my_view')
  .concurrently()
  .execute()
Copy

Returns a copy of this schema module without any plugins.

Returns a copy of this schema module with the given plugin installed.

See QueryCreator.withSchema