Overview

Search…

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.  It can additionally take a struct of default query options forwarded on to queryExecute.
1
// manually
2
var schema = new qb.models.schema.SchemaBuilder(
3
    grammar = new qb.models.grammars.MySQLGrammar(),
4
    defaultOptions = { datasource: "my_datasource" }
5
);
6
​
7
// WireBox
8
var schema = wirebox.getInstance( "[email protected]" );
Copied!
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
true
​
The name of the table to create.
callback
function
true
​
A callback function used to define the table body. It is passed a Blueprint as the only argument.
options
struct
false
{}
Options to pass to queryExecute.
execute
boolean
false
true
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
1
schema.create( "users", function( table ) {
2
    table.increments( "id" );
3
    table.string( "email" );
4
    table.string( "password" );
5
    table.timestamp( "created_date" ).nullable();
6
    table.timestamp( "modified_date" ).nullable();
7
} );
Copied!
SQL (MySQL)
1
CREATE TABLE `users` (
2
    `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
3
    `email` VARCHAR(255) NOT NULL,
4
    `password` VARCHAR(255) NOT NULL,
5
    `created_date` TIMESTAMP,
6
    `modified_date` TIMESTAMP,
7
    CONSTRAINT `pk_users_id` PRIMARY KEY (`id`)
8
)
Copied!
​alter​
Alter an existing table in the database.
Argument
Type
Required
Default
Description
table
string
true
​
The name of the table to alter.
callback
function
true
​
A callback function used to define the changes to the table. It is passed a Blueprint as the only argument.
options
struct
false
{}
Options to pass to queryExecute.
execute
boolean
false
true
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
1
schema.alter( "users", function( table ) {
2
    table.addConstraint( table.unique( "username" ) );
3
    table.dropColumn( "last_logged_in" );
4
} );
Copied!
SQL (MySQL)
1
ALTER TABLE `users` ADD CONSTRAINT `unq_users_username` UNIQUE (`username`);
2
ALTER TABLE `users` DROP COLUMN `last_logged_in`;
Copied!
​drop and dropIfExists​
Drop a table from the database.
Argument
Type
Required
Default
Description
table
string
true
​
The name of the table to drop.
options
struct
false
{}
Options to pass to queryExecute.
execute
boolean
false
true
Run the query immediately after building it.
Example:
SchemaBuilder
1
schema.drop( "user_logins" );
Copied!
SQL (MySQL)
1
DROP TABLE `user_logins`
Copied!
Additionally, there are a few utility methods defined on SchemaBuilder as well:
rename
Rename a table from an old name to a new name
Argument
Type
Required
Default
Description
from
string
true
​
The old table name.
to
string
true
​
The new table name.
options
struct
false
{}
Options to pass to queryExecute.
execute
boolean
false
true
Run the query immediately after building it.
Example:
SchemaBuilder
1
schema.rename( "posts", "blog_posts" );
Copied!
SQL (MySQL)
1
RENAME TABLE `posts` TO `blog_posts`
Copied!
hasTable
Check if a table exists in the database.
Argument
Type
Required
Default
Description
name
string
true
​
The name of the table to check.
options
struct
false
{}
Options to pass to queryExecute.
execute
boolean
false
true
Run the query immediately after building it.
Example:
SchemaBuilder
1
schema.hasTable( "users" );
Copied!
SQL (MySQL)
1
SELECT 1
2
FROM `information_schema`.`tables`
3
WHERE `table_name` = 'users'
Copied!
hasColumn
Check if a column exists in a table in the database.
Argument
Type
Required
Default
Description
table
string
true
​
The name of the table to check for the column in.
column
string
true
​
The column to check for in the table.
options
struct
false
{}
Options to pass to queryExecute.
execute
boolean
false
true
Run the query immediately after building it.
Example:
SchemaBuilder
1
schema.hasColumn( "users", "last_logged_in" );
Copied!
SQL (MySQL)
1
SELECT 1
2
FROM `information_schema`.`columns`
3
WHERE `table_name` = 'users'
4
    AND `column_name` = 'last_logged_in'
Copied!