1 of 1

Overview

QB ships with a schema builder to help you build your database objects. This provides a few benefits:

The syntax is expressive and fluent, making it easy to understand what is being executed
The syntax is database-agnostic. Specific quirks are isolated in a Grammar file, making it easy to migrate between engines.

You start with a SchemaBuilder object. The SchemaBuilder takes the same Grammar that a QueryBuilder takes.

// manually
var schema = new qb.models.schema.SchemaBuilder(
    new qb.models.grammars.MySQLGrammar()
);

// WireBox
var schema = wirebox.getInstance( "SchemaBuilder@qb" );

Note: the SchemaBuilder is a transient, and a new one should be created for each operation.

The SchemaBuilder has four main methods to start your database object creation:

`create`

Create a new table in the database.

The majority of the work comes from calling methods on the Blueprint object. A Blueprint defines the columns and indexes for your tables.

Example:

SchemaBuilder

schema.create( "users", function( table ) {
    table.increments( "id" );
    table.string( "email" );
    table.string( "password" );
    table.timestamp( "created_date" ).nullable();
    table.timestamp( "modified_date" ).nullable();
} );

SQL (MySQL)

CREATE TABLE `users` (
    `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    `email` VARCHAR(255) NOT NULL,
    `password` VARCHAR(255) NOT NULL,
    `created_date` TIMESTAMP,
    `modified_date` TIMESTAMP,
    CONSTRAINT `pk_users_id` PRIMARY KEY (`id`)
)

`alter`

Alter an existing table in the database.

In addition to using the columns and indexes off of the passed-in Blueprint object, the Blueprint contains helpers such as addConstraint, removeConstraint, addColumn, renameColumn, and dropColumn to assist in altering existing tables.

Example:

SchemaBuilder

schema.alter( "users", function( table ) {
    table.addConstraint( table.unique( "username" ) );
    table.dropColumn( "last_logged_in" );
} );

SQL (MySQL)

ALTER TABLE `users` ADD CONSTRAINT `unq_users_username` UNIQUE (`username`);
ALTER TABLE `users` DROP COLUMN `last_logged_in`;

`drop` and `dropIfExists`

Drop a table from the database.

Example:

SchemaBuilder

schema.drop( "user_logins" );

SQL (MySQL)

DROP TABLE `user_logins`

Additionally, there are a few utility methods defined on `SchemaBuilder` as well:

`rename`

Rename a table from an old name to a new name

Example:

SchemaBuilder

schema.rename( "posts", "blog_posts" );

SQL (MySQL)

RENAME TABLE `posts` TO `blog_posts`

`hasTable`

Check if a table exists in the database.

Example:

SchemaBuilder

schema.hasTable( "users" );

SQL (MySQL)

SELECT 1
FROM `information_schema`.`tables`
WHERE `table_name` = 'users'

`hasColumn`

Check if a column exists in a table in the database.

Example:

SchemaBuilder

schema.hasColumn( "users", "last_logged_in" );

SQL (MySQL)

SELECT 1
FROM `information_schema`.`columns`
WHERE `table_name` = 'users'
    AND `column_name` = 'last_logged_in'

Overview

QB ships with a schema builder to help you build your database objects. This provides a few benefits:

The syntax is expressive and fluent, making it easy to understand what is being executed
The syntax is database-agnostic. Specific quirks are isolated in a Grammar file, making it easy to migrate between engines.

You start with a SchemaBuilder object. The SchemaBuilder takes the same Grammar that a QueryBuilder takes.

// manually
var schema = new qb.models.schema.SchemaBuilder(
    new qb.models.grammars.MySQLGrammar()
);

// WireBox
var schema = wirebox.getInstance( "SchemaBuilder@qb" );

Note: the SchemaBuilder is a transient, and a new one should be created for each operation.

The SchemaBuilder has four main methods to start your database object creation:

`create`

Create a new table in the database.

The majority of the work comes from calling methods on the Blueprint object. A Blueprint defines the columns and indexes for your tables.

Example:

SchemaBuilder

schema.create( "users", function( table ) {
    table.increments( "id" );
    table.string( "email" );
    table.string( "password" );
    table.timestamp( "created_date" ).nullable();
    table.timestamp( "modified_date" ).nullable();
} );

SQL (MySQL)

CREATE TABLE `users` (
    `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    `email` VARCHAR(255) NOT NULL,
    `password` VARCHAR(255) NOT NULL,
    `created_date` TIMESTAMP,
    `modified_date` TIMESTAMP,
    CONSTRAINT `pk_users_id` PRIMARY KEY (`id`)
)

`alter`

Alter an existing table in the database.

Example:

SchemaBuilder

schema.alter( "users", function( table ) {
    table.addConstraint( table.unique( "username" ) );
    table.dropColumn( "last_logged_in" );
} );

SQL (MySQL)

ALTER TABLE `users` ADD CONSTRAINT `unq_users_username` UNIQUE (`username`);
ALTER TABLE `users` DROP COLUMN `last_logged_in`;

`drop` and `dropIfExists`

Drop a table from the database.

Example:

SchemaBuilder

schema.drop( "user_logins" );

SQL (MySQL)

DROP TABLE `user_logins`

Additionally, there are a few utility methods defined on `SchemaBuilder` as well:

`rename`

Rename a table from an old name to a new name

Example:

SchemaBuilder

schema.rename( "posts", "blog_posts" );

SQL (MySQL)

RENAME TABLE `posts` TO `blog_posts`

`hasTable`

Check if a table exists in the database.

Example:

SchemaBuilder

schema.hasTable( "users" );

SQL (MySQL)

SELECT 1
FROM `information_schema`.`tables`
WHERE `table_name` = 'users'

`hasColumn`

Check if a column exists in a table in the database.

Example:

SchemaBuilder

schema.hasColumn( "users", "last_logged_in" );

SQL (MySQL)

SELECT 1
FROM `information_schema`.`columns`
WHERE `table_name` = 'users'
    AND `column_name` = 'last_logged_in'

Overview

create

alter

drop and dropIfExists

Additionally, there are a few utility methods defined on SchemaBuilder as well:

rename

hasTable

hasColumn

Overview

create

alter

drop and dropIfExists

Additionally, there are a few utility methods defined on SchemaBuilder as well:

rename

hasTable

hasColumn

`create`

`alter`

`drop` and `dropIfExists`

Additionally, there are a few utility methods defined on `SchemaBuilder` as well:

`rename`

`hasTable`

`hasColumn`

`create`

`alter`

`drop` and `dropIfExists`

Additionally, there are a few utility methods defined on `SchemaBuilder` as well:

`rename`

`hasTable`

`hasColumn`