Search…
8.9.0
Introduction
What's New?
Installation & Usage
Migration Guide
Contributing & Filing Issues
Query Builder
Getting a New Query
Building Queries
Executing Queries
Options and Utilities
Debugging
Schema Builder
Overview
Create
Columns
Column Modifiers
Column Constraints
Creating Table Constraints
Alter
Drop
External Links
API Docs
Source Code
Issue Tracker
Powered By GitBook
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.
// manually
var schema = new qb.models.schema.SchemaBuilder(
grammar = new qb.models.grammars.MySQLGrammar(),
defaultOptions = { datasource: "my_datasource" }
);
​
// WireBox
var 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
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
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
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
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
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
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
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
schema.rename( "posts", "blog_posts" );
SQL (MySQL)
RENAME TABLE `posts` TO `blog_posts`

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
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.
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
schema.hasColumn( "users", "last_logged_in" );
SQL (MySQL)
SELECT 1
FROM `information_schema`.`columns`
WHERE `table_name` = 'users'
AND `column_name` = 'last_logged_in'
Query Builder - Previous
Debugging
Next - Schema Builder
Create
Last modified 1mo ago
Copy link
Outline
create
alter
drop and dropIfExists
Additionally, there are a few utility methods defined on SchemaBuilder as well:
rename
hasTable
hasColumn