Database Forge Class

Database Forge Class

The Database Forge Class contains methods that help you manage yourdatabase.

Initializing the Forge Class
Creating and Dropping Databases
Creating and Dropping Tables
Modifying Tables
Class Reference

Initializing the Forge Class

Important

In order to initialize the Forge class, your databasedriver must already be running, since the forge class relies on it.

Load the Forge Class as follows:

$forge = \Config\Database::forge();

You can also pass another database group name to the DB Forge loader, in casethe database you want to manage isn’t the default one:

$this->myforge = \Config\Database::forge('other_db');

In the above example, we’re passing the name of a different database groupto connect to as the first parameter.

Creating and Dropping Databases

$forge->createDatabase(‘db_name’)

Permits you to create the database specified in the first parameter.Returns TRUE/FALSE based on success or failure:

if ($forge->createDatabase('my_db'))
{
        echo 'Database created!';
}

An optional second parameter set to TRUE will add IF EXISTS statementor will check if a database exists before create it (depending on DBMS).

$forge->createDatabase('my_db', TRUE);
// gives CREATE DATABASE IF NOT EXISTS my_db
// or will check if a database exists

$forge->dropDatabase(‘db_name’)

Permits you to drop the database specified in the first parameter.Returns TRUE/FALSE based on success or failure:

if ($forge->dropDatabase('my_db'))
{
        echo 'Database deleted!';
}

Creating and Dropping Tables

There are several things you may wish to do when creating tables. Addfields, add keys to the table, alter columns. CodeIgniter provides amechanism for this.

Adding fields

Fields are normally created via an associative array. Within the array, you mustinclude a ‘type’ key that relates to the datatype of the field. Forexample, INT, VARCHAR, TEXT, etc. Many datatypes (for example VARCHAR)also require a ‘constraint’ key.

$fields = [
        'users' => [
                'type'       => 'VARCHAR',
                'constraint' => 100,
        ],
];
// will translate to "users VARCHAR(100)" when the field is added.

Additionally, the following key/values can be used:

unsigned/true : to generate “UNSIGNED” in the field definition.
default/value : to generate a default value in the field definition.
null/true : to generate “NULL” in the field definition. Without this,the field will default to “NOT NULL”.
auto_increment/true : generates an auto_increment flag on thefield. Note that the field type must be a type that supports this,such as integer.
unique/true : to generate a unique key for the field definition.

$fields = [
        'id'          => [
                'type'           => 'INT',
                'constraint'     => 5,
                'unsigned'       => true,
                'auto_increment' => true
        ],
        'title'       => [
                'type'           => 'VARCHAR',
                'constraint'     => '100',
                'unique'         => true,
        ],
        'author'      => [
                'type'           =>'VARCHAR',
                'constraint'     => 100,
                'default'        => 'King of Town',
        ],
        'description' => [
                'type'           => 'TEXT',
                'null'           => true,
        ],
        'status'      => [
                'type'           => 'ENUM',
                'constraint'     => ['publish', 'pending', 'draft'],
                'default'        => 'pending',
        ],
];

After the fields have been defined, they can be added using$forge->addField($fields); followed by a call to thecreateTable() method.

$forge->addField()

The add fields method will accept the above array.

Passing strings as fields

If you know exactly how you want a field to be created, you can pass thestring into the field definitions with addField()

$forge->addField("label varchar(100) NOT NULL DEFAULT 'default label'");

Note

Passing raw strings as fields cannot be followed by addKey() calls on those fields.

Note

Multiple calls to addField() are cumulative.

Creating an id field

There is a special exception for creating id fields. A field with typeid will automatically be assigned as an INT(9) auto_incrementingPrimary Key.

$forge->addField('id');
// gives id INT(9) NOT NULL AUTO_INCREMENT

Adding Keys

Generally speaking, you’ll want your table to have Keys. This isaccomplished with $forge->addKey(‘field’). The optional secondparameter set to TRUE will make it a primary key and the thirdparameter set to TRUE will make it a unique key. Note that addKey()must be followed by a call to createTable().

Multiple column non-primary keys must be sent as an array. Sample outputbelow is for MySQL.

$forge->addKey('blog_id', TRUE);
// gives PRIMARY KEY `blog_id` (`blog_id`)
 
$forge->addKey('blog_id', TRUE);
$forge->addKey('site_id', TRUE);
// gives PRIMARY KEY `blog_id_site_id` (`blog_id`, `site_id`)
 
$forge->addKey('blog_name');
// gives KEY `blog_name` (`blog_name`)
 
$forge->addKey(['blog_name', 'blog_label']);
// gives KEY `blog_name_blog_label` (`blog_name`, `blog_label`)
 
$forge->addKey(['blog_id', 'uri'], FALSE, TRUE);
// gives UNIQUE KEY `blog_id_uri` (`blog_id`, `uri`)

To make code reading more objective it is also possible to add primaryand unique keys with specific methods:

$forge->addPrimaryKey('blog_id');
// gives PRIMARY KEY `blog_id` (`blog_id`)
 
$forge->addUniqueKey(['blog_id', 'uri']);
// gives UNIQUE KEY `blog_id_uri` (`blog_id`, `uri`)

Adding Foreign Keys

Foreign Keys help to enforce relationships and actions across your tables. For tables that support Foreign Keys,you may add them directly in forge:

$forge->addForeignKey('users_id','users','id');
// gives CONSTRAINT `TABLENAME_users_foreign` FOREIGN KEY(`users_id`) REFERENCES `users`(`id`)

You can specify the desired action for the “on delete” and “on update” properties of the constraint:

$forge->addForeignKey('users_id','users','id','CASCADE','CASCADE');
// gives CONSTRAINT `TABLENAME_users_foreign` FOREIGN KEY(`users_id`) REFERENCES `users`(`id`) ON DELETE CASCADE ON UPDATE CASCADE

Creating a table

After fields and keys have been declared, you can create a new tablewith

$forge->createTable('table_name');
// gives CREATE TABLE table_name

An optional second parameter set to TRUE adds an “IF NOT EXISTS” clauseinto the definition

$forge->createTable('table_name', TRUE);
// gives CREATE TABLE IF NOT EXISTS table_name

You could also pass optional table attributes, such as MySQL’s ENGINE:

$attributes = ['ENGINE' => 'InnoDB'];
$forge->createTable('table_name', FALSE, $attributes);
// produces: CREATE TABLE `table_name` (...) ENGINE = InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci

Note

Unless you specify the CHARACTER SET and/or COLLATE attributes,createTable() will always add them with your configured charset_and _DBCollat values, as long as they are not empty (MySQL only).

Dropping a table

Execute a DROP TABLE statement and optionally add an IF EXISTS clause.

// Produces: DROP TABLE table_name
$forge->dropTable('table_name');
 
// Produces: DROP TABLE IF EXISTS table_name
$forge->dropTable('table_name',TRUE);

Dropping a Foreign Key

Execute a DROP FOREIGN KEY.

// Produces: ALTER TABLE 'tablename' DROP FOREIGN KEY 'users_foreign'
$forge->dropForeignKey('tablename','users_foreign');

Renaming a table

Executes a TABLE rename

$forge->renameTable('old_table_name', 'new_table_name');
// gives ALTER TABLE old_table_name RENAME TO new_table_name

Modifying Tables

Adding a Column to a Table

$forge->addColumn()

The addColumn() method is used to modify an existing table. Itaccepts the same field array as above, and can be used for an unlimitednumber of additional fields.

$fields = [
        'preferences' => ['type' => 'TEXT']
];
$forge->addColumn('table_name', $fields);
// Executes: ALTER TABLE table_name ADD preferences TEXT

If you are using MySQL or CUBIRD, then you can take advantage of theirAFTER and FIRST clauses to position the new column.

Examples:

// Will place the new column after the `another_field` column:
$fields = [
        'preferences' => ['type' => 'TEXT', 'after' => 'another_field']
];
 
// Will place the new column at the start of the table definition:
$fields = [
        'preferences' => ['type' => 'TEXT', 'first' => TRUE]
];

Dropping Columns From a Table

$forge->dropColumn()

Used to remove a column from a table.

$forge->dropColumn('table_name', 'column_to_drop'); // to drop one single column

Used to remove multiple columns from a table.

$forge->dropColumn('table_name', 'column_1,column_2'); // by proving comma separated column names
$forge->dropColumn('table_name', ['column_1', 'column_2']); // by proving array of column names

Modifying a Column in a Table

$forge->modifyColumn()

The usage of this method is identical to addColumn(), except italters an existing column rather than adding a new one. In order tochange the name, you can add a “name” key into the field defining array.

$fields = [
        'old_name' => [
                'name' => 'new_name',
                'type' => 'TEXT',
        ],
];
$forge->modifyColumn('table_name', $fields);
// gives ALTER TABLE table_name CHANGE old_name new_name TEXT

Class Reference

CodeIgniter\Database\Forge
- addColumn($table[, $field = []])

Parameters:

- **$table** (_string_) – Table name to add the column to
- **$field** (_array_) – Column definition(s)Returns:

TRUE on success, FALSE on failureReturn type:bool

Adds a column to a table. Usage: See Adding a Column to a Table.

addField($field)

Parameters:

- **$field** (_array_) – Field definition to addReturns:

CodeIgniterDatabaseForge instance (method chaining)Return type:CodeIgniterDatabaseForge

Adds a field to the set that will be used to create a table. Usage: See Adding fields.

addKey($key[, $primary = FALSE[, $unique = FALSE]])

Parameters:

- **$key** (_mixed_) – Name of a key field or an array of fields
- **$primary** (_bool_) – Set to TRUE if it should be a primary key or a regular one
- **$unique** (_bool_) – Set to TRUE if it should be a unique key or a regular oneReturns:

CodeIgniterDatabaseForge instance (method chaining)Return type:CodeIgniterDatabaseForge

Adds a key to the set that will be used to create a table. Usage: See Adding Keys.

addPrimaryKey($key)

Parameters:

- **$key** (_mixed_) – Name of a key field or an array of fieldsReturns:

CodeIgniterDatabaseForge instance (method chaining)Return type:CodeIgniterDatabaseForge

Adds a primary key to the set that will be used to create a table. Usage: See Adding Keys.

addUniqueKey($key)

Parameters:

- **$key** (_mixed_) – Name of a key field or an array of fieldsReturns:

CodeIgniterDatabaseForge instance (method chaining)Return type:CodeIgniterDatabaseForge

Adds a unique key to the set that will be used to create a table. Usage: See Adding Keys.

createDatabase($dbName[, $ifNotExists = FALSE])

Parameters:

- **$db_name** (_string_) – Name of the database to create
- **$ifNotExists** (_string_) – Set to TRUE to add an ‘IF NOT EXISTS’ clause or check if database existsReturns:

TRUE on success, FALSE on failureReturn type:bool

Creates a new database. Usage: See Creating and Dropping Databases.

createTable($table[, $if_not_exists = FALSE[, array $attributes = []]])

Parameters:

- **$table** (_string_) – Name of the table to create
- **$if_not_exists** (_string_) – Set to TRUE to add an ‘IF NOT EXISTS’ clause
- **$attributes** (_string_) – An associative array of table attributesReturns:

Query object on success, FALSE on failureReturn type:mixed

Creates a new table. Usage: See Creating a table.

dropColumn($table, $column_name)

Parameters:

- **$table** (_string_) – Table name
- **$column_names** (_mixed_) – Comma-delimited string or an array of column namesReturns:

TRUE on success, FALSE on failureReturn type:bool

Drops single or multiple columns from a table. Usage: See Dropping Columns From a Table.

dropDatabase($dbName)

Parameters:

- **$dbName** (_string_) – Name of the database to dropReturns:

TRUE on success, FALSE on failureReturn type:bool

Drops a database. Usage: See Creating and Dropping Databases.

dropTable($table_name[, $if_exists = FALSE])

Parameters:

- **$table** (_string_) – Name of the table to drop
- **$if_exists** (_string_) – Set to TRUE to add an ‘IF EXISTS’ clauseReturns:

TRUE on success, FALSE on failureReturn type:bool

Drops a table. Usage: See Dropping a table.

modifyColumn($table, $field)

Parameters:

- **$table** (_string_) – Table name
- **$field** (_array_) – Column definition(s)Returns:

TRUE on success, FALSE on failureReturn type:bool

Modifies a table column. Usage: See Modifying a Column in a Table.

renameTable($table_name, $new_table_name)

Parameters:

- **$table** (_string_) – Current of the table
- **$new_table_name** (_string_) – New name of the tableReturns:

Query object on success, FALSE on failureReturn type:mixed

Renames a table. Usage: See Renaming a table.

Database Manipulation with Database Forge

Database Forge Class

Initializing the Forge Class

Creating and Dropping Databases

Creating and Dropping Tables

Adding fields

Passing strings as fields

Creating an id field

Adding Keys

Adding Foreign Keys

Creating a table

Dropping a table

Dropping a Foreign Key

Renaming a table

Modifying Tables

Adding a Column to a Table

Dropping Columns From a Table

Modifying a Column in a Table

Class Reference