The Database Forge Class contains methods that help you manage your database. Important In order to initialize the Forge class, your database driver must already be running, since the Forge class relies on it. Load the Forge Class as
follows: <?php
$forge = \Config\Database::forge();
You can also pass another database group name to the DB Forge loader, in case the database you want to manage isn’t the default one: <?php
$this->myforge = \Config\Database::forge('other_db');
In the above example, we’re passing the name of a different database group to connect to as the first parameter. Permits you to create the database specified in the first parameter. Returns true/false based on success or failure: <?php
if ($forge->createDatabase('my_db')) {
echo 'Database created!';
}
An optional second parameter set to true will add IF EXISTS statement or will check if a database exists before create it (depending on DBMS). <?php
$forge->createDatabase('my_db', true);
/*
* gives CREATE DATABASE IF NOT EXISTS `my_db`
* or will check if a database exists
*/
Permits you to drop the database specified in the first parameter. Returns true/false based on success or failure: <?php
if ($forge->dropDatabase('my_db')) {
echo 'Database deleted!';
}
CodeIgniter supports creating databases straight from your favorite terminal using the dedicated db:create command. By using this command it is
assumed that the database is not yet existing. Otherwise, CodeIgniter will complain that the database creation has failed. To start, just type the command and the name of the database (e.g., foo): > php spark db:create foo
If everything went fine, you should expect the Database "foo" successfully created. message displayed. If you are on a testing environment or you are using the SQLite3 driver, you may pass in the file extension for the file where the database will be created using the --ext option.
Valid values are db and sqlite and defaults to db. Remember that these should not be preceded by a period. > php spark db:create foo --ext sqlite
// will create the db file in WRITEPATH/foo.sqlite
Note When using the special SQLite3 database name :memory:, expect that the command will still produce a success message but no database file is created. This is because SQLite3 will just use an in-memory database. There are several things you may wish to do when creating tables. Add fields, add keys to the table, alter columns. CodeIgniter provides a mechanism for this. Fields are normally created via an associative array. Within the array, you must include a type key that relates to the datatype of the field. For example, INT, VARCHAR, TEXT, etc. Many datatypes (for example VARCHAR) also require a constraint
key. <?php
$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: <?php
$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 the createTable() method. The add fields method will accept the above array. Since v4.2.0, $forge->addField() accepts a CodeIgniter\Database\RawSql instance, which expresses raw SQL strings.Initializing the Forge Class
Creating and Dropping
Databases
$forge->createDatabase(‘db_name’)
$forge->dropDatabase(‘db_name’)
Creating Databases in the Command Line
Creating and Dropping Tables
Adding Fields
$forge->addField()
Raw Sql Strings as Default Values
<?php use CodeIgniter\Database\RawSql; $fields = [ 'id' => [ 'type' => 'INT', 'constraint' => 5, 'unsigned' => true, 'auto_increment' => true, ], 'created_at' => [ 'type' => 'TIMESTAMP', 'default' => new RawSql('CURRENT_TIMESTAMP'), ], ]; $forge->addField($fields); /* gives: "id" INT(5) UNSIGNED NOT NULL AUTO_INCREMENT, "created_at" TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL */
Warning
When you use RawSql, you MUST escape the data manually. Failure to do so could result in SQL injections.
Passing Strings as Fields
If you know exactly how you want a field to be created, you can pass the string into the field definitions with addField():
<?php $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 type id will automatically be assigned as an INT(9) auto_incrementing Primary Key.
<?php $forge->addField('id'); // gives `id` INT(9) NOT NULL AUTO_INCREMENT
Adding Keys
Generally speaking, you’ll want your table to have Keys. This is accomplished with $forge->addKey('field'). The optional second parameter set to true will make it a primary key and the third parameter 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 output below is for MySQL.
<?php $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 primary and unique keys with specific methods:
<?php $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:
<?php $forge->addForeignKey('users_id', 'users', 'id'); // gives CONSTRAINT `TABLENAME_users_foreign` FOREIGN KEY(`users_id`) REFERENCES `users`(`id`) $forge->addForeignKey(['users_id', 'users_name'], 'users', ['id', 'name']); // gives CONSTRAINT `TABLENAME_users_foreign` FOREIGN KEY(`users_id`, `users_name`) REFERENCES `users`(`id`, `name`)
You can specify the desired action for the “on delete” and “on update” properties of the constraint:
<?php $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 $forge->addForeignKey(['users_id', 'users_name'], 'users', ['id', 'name'], 'CASCADE', 'CASCADE'); // gives CONSTRAINT `TABLENAME_users_foreign` FOREIGN KEY(`users_id`, `users_name`) REFERENCES `users`(`id`, `name`) ON DELETE CASCADE ON UPDATE CASCADE
Creating a Table
After fields and keys have been declared, you can create a new table with
<?php $forge->createTable('table_name'); // gives CREATE TABLE table_name
An optional second parameter set to true will create the table only if it doesn’t already exist.
<?php $forge->createTable('table_name', true); // creates table only if table does not exist
You could also pass optional table attributes, such as MySQL’s ENGINE:
<?php $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.
<?php // Produces: DROP TABLE `table_name` $forge->dropTable('table_name'); // Produces: DROP TABLE IF EXISTS `table_name` $forge->dropTable('table_name', true);
A third parameter can be passed to add a CASCADE option, which might be required for some drivers to handle removal of tables with foreign keys.
<?php // Produces: DROP TABLE `table_name` CASCADE $forge->dropTable('table_name', false, true);
Dropping a Foreign Key
Execute a DROP FOREIGN KEY.
<?php // Produces: ALTER TABLE `tablename` DROP FOREIGN KEY `users_foreign` $forge->dropForeignKey('tablename', 'users_foreign');
Dropping a Key
Execute a DROP KEY.
<?php // Produces: DROP INDEX `users_index` ON `tablename` $forge->dropKey('tablename', 'users_index');
Renaming a Table
Executes a TABLE rename
<?php $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. It accepts the same field array as above, and can be used for an unlimited number of additional fields.
<?php $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 their AFTER and FIRST clauses to position the new column.
Examples:
<?php // 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.
<?php $forge->dropColumn('table_name', 'column_to_drop'); // to drop one single column
Used to remove multiple columns from a table.
<?php $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 it alters an existing column rather than adding a new one. In order to change the name, you can add a “name” key into the field defining array.
<?php $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
class CodeIgniter\Database\Forge addColumn($table[, $field = []])
|
true on success, false on failure |
bool |
Adds a column to a table. Usage: See Adding a Column to a Table.
addField($field)
|
CodeIgniterDatabaseForge instance (method chaining) |
CodeIgniterDatabaseForge Adds a field to the set that will be used to create a table. Usage: See Adding Fields. |
|
CodeIgniterDatabaseForge instance (method chaining) |
CodeIgniterDatabaseForge |
Adds a foreign key to the set that will be used to create a table. Usage: See Adding Foreign Keys.
addKey($key[, $primary = false[, $unique = false]])
|
CodeIgniterDatabaseForge instance (method chaining) |
CodeIgniterDatabaseForge |
Adds a key to the set that will be used to create a table. Usage: See Adding Keys.
addPrimaryKey($key)
|
CodeIgniterDatabaseForge instance (method chaining) |
CodeIgniterDatabaseForge |
Adds a primary key to the set that will be used to create a table. Usage: See Adding Keys.
addUniqueKey($key)
|
CodeIgniterDatabaseForge instance (method chaining) |
CodeIgniterDatabaseForge |
Adds a unique key to the set that will be used to create a table. Usage: See Adding Keys.
createDatabase($dbName[, $ifNotExists = false])
|
true on success, false on failure |
bool |
Creates a new database. Usage: See Creating and Dropping Databases.
createTable($table[, $if_not_exists = false[, array $attributes = []]])
|
Query object on success, false on failure |
mixed |
Creates a new table. Usage: See Creating a Table.
dropColumn($table, $column_name)
|
true on success, false on failure |
bool |
Drops single or multiple columns from a table. Usage: See Dropping Columns From a Table.
dropDatabase($dbName)
|
true on success, false on failure |
bool |
Drops a database. Usage: See Creating and Dropping Databases.
dropTable($table_name[, $if_exists = false])
|
true on success, false on failure |
bool |
Drops a table. Usage: See Dropping a Table.
modifyColumn($table, $field)
|
true on success, false on failure |
bool |
Modifies a table column. Usage: See Modifying a Column in a Table.
renameTable($table_name, $new_table_name)
|
Query object on success, false on failure |
mixed |
Renames a table. Usage: See Renaming a Table.