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.
// manuallyvar schema = new qb.models.schema.SchemaBuilder(new qb.models.grammars.MySQLGrammar());​// WireBoxvar schema = wirebox.getInstance( "[email protected]" );
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.
Argument | Type | Required | Default | Description |
table | string |
| ​ | The name of the table to create. |
callback | function |
| ​ | A callback function used to define the table body. It is passed a |
options | struct |
|
| Options to pass to |
execute | boolean |
|
| Run the query immediately after building it. |
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.
Argument | Type | Required | Default | Description |
table | string |
| ​ | The name of the table to alter. |
callback | function |
| ​ | A callback function used to define the changes to the table. It is passed a |
options | struct |
|
| Options to pass to |
execute | boolean |
|
| Run the query immediately after building it. |
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.
Argument | Type | Required | Default | Description |
table | string |
| ​ | The name of the table to drop. |
options | struct |
|
| Options to pass to |
execute | boolean |
|
| Run the query immediately after building it. |
Example:
SchemaBuilder
schema.drop( "user_logins" );
SQL (MySQL)
DROP TABLE `user_logins`
Rename a table from an old name to a new name
Argument | Type | Required | Default | Description |
from | string |
| ​ | The old table name. |
to | string |
| ​ | The new table name. |
options | struct |
|
| Options to pass to |
execute | boolean |
|
| Run the query immediately after building it. |
Example:
SchemaBuilder
schema.rename( "posts", "blog_posts" );
SQL (MySQL)
RENAME TABLE `posts` TO `blog_posts`
Check if a table exists in the database.
Argument | Type | Required | Default | Description |
name | string |
| ​ | The name of the table to check. |
options | struct |
|
| Options to pass to |
execute | boolean |
|
| Run the query immediately after building it. |
Example:
SchemaBuilder
schema.hasTable( "users" );
SQL (MySQL)
SELECT 1FROM `information_schema`.`tables`WHERE `table_name` = 'users'
Check if a column exists in a table in the database.
Argument | Type | Required | Default | Description |
table | string |
| ​ | The name of the table to check for the column in. |
column | string |
| ​ | The column to check for in the table. |
options | struct |
|
| Options to pass to |
execute | boolean |
|
| Run the query immediately after building it. |
Example:
SchemaBuilder
schema.hasColumn( "users", "last_logged_in" );
SQL (MySQL)
SELECT 1FROM `information_schema`.`columns`WHERE `table_name` = 'users'AND `column_name` = 'last_logged_in'