Writing Migrations¶

Generating a skeleton migration file¶

Let’s start by creating a new Phinx migration. Run Phinx using the create command:

$ vendor/bin/phinx create MyNewMigration

This will create a new migration in the format YYYYMMDDHHMMSS_my_new_migration.php, where the first 14 characters are replaced with the current timestamp down to the second.

If you have specified multiple migration paths, you will be asked to select which path to create the new migration in.

Phinx automatically creates a skeleton migration file with a single method:

<?php

use Phinx\Migration\AbstractMigration;

class MyNewMigration extends AbstractMigration
{
    /**
     * Change Method.
     *
     * Write your reversible migrations using this method.
     *
     * More information on writing migrations is available here:
     * https://book.cakephp.org/phinx/0/en/migrations.html#the-change-method
     *
     * Remember to call "create()" or "update()" and NOT "save()" when working
     * with the Table class.
     *
     */
    public function change()
    {

    }
}

All Phinx migrations extend from the AbstractMigration class. This class provides the necessary support to create your database migrations. Database migrations can transform your database in many ways, such as creating new tables, inserting rows, adding indexes and modifying columns.

The Change Method¶

Phinx 0.2.0 introduced a new feature called reversible migrations. This feature has now become the default migration method. With reversible migrations, you only need to define the up logic, and Phinx can figure out how to migrate down automatically for you. For example:

<?php

use Phinx\Migration\AbstractMigration;

class CreateUserLoginsTable extends AbstractMigration
{
    public function change()
    {
        // create the table
        $table = $this->table('user_logins');
        $table->addColumn('user_id', 'integer')
              ->addColumn('created', 'datetime')
              ->create();
    }
}

When executing this migration, Phinx will create the user_logins table on the way up and automatically figure out how to drop the table on the way down. Please be aware that when a change method exists, Phinx will automatically ignore the up and down methods. If you need to use these methods it is recommended to create a separate migration file.

The following actions are reversible when done through the Table API in Phinx, and will be automatically reversed:

• Creating a table

• Renaming a table

• Adding a column

• Renaming a column

• Adding an index

• Adding a foreign key

If a command cannot be reversed then Phinx will throw an IrreversibleMigrationException when it’s migrating down. If you wish to use a command that cannot be reversed in the change function, you can use an if statement with $this->isMigratingUp() to only run things in the up or down direction. For example:

<?php

use Phinx\Migration\AbstractMigration;

class CreateUserLoginsTable extends AbstractMigration
{
    public function change()
    {
        // create the table
        $table = $this->table('user_logins');
        $table->addColumn('user_id', 'integer')
              ->addColumn('created', 'datetime')
              ->create();
        if ($this->isMigratingUp()) {
            $table->insert([['user_id' => 1, 'created' => '2020-01-19 03:14:07']])
                  ->save();
        }
    }
}

The Up Method¶

The up method is automatically run by Phinx when you are migrating up and it detects the given migration hasn’t been executed previously. You should use the up method to transform the database with your intended changes.

The Down Method¶

The down method is automatically run by Phinx when you are migrating down and it detects the given migration has been executed in the past. You should use the down method to reverse/undo the transformations described in the up method.

The Init Method¶

The init() method is run by Phinx before the migration methods if it exists. This can be used for setting common class properties that are then used within the migration methods.

The Should Execute Method¶

The shouldExecute() method is run by Phinx before executing the migration. This can be used to prevent the migration from being executed at this time. It always returns true by default. You can override it in your custom AbstractMigration implementation.

The Table Object¶

The Table object is one of the most useful APIs provided by Phinx. It allows you to easily manipulate database tables using PHP code. You can retrieve an instance of the Table object by calling the table() method from within your database migration.

<?php

use Phinx\Migration\AbstractMigration;

class MyNewMigration extends AbstractMigration
{
    /**
     * Migrate Up.
     */
    public function up()
    {
        $table = $this->table('tableName');
    }

    /**
     * Migrate Down.
     */
    public function down()
    {

    }
}

You can then manipulate this table using the methods provided by the Table object.

Saving Changes¶

When working with the Table object, Phinx stores certain operations in a pending changes cache. Once you have made the changes you want to the table, you must save them. To perform this operation, Phinx provides three methods, create(), update(), and save(). create() will first create the table and then run the pending changes. update() will just run the pending changes, and should be used when the table already exists. save() is a helper function that checks first if the table exists and if it does not will run create(), else it will run update().

As stated above, when using the change() migration method, you should always use create() or update(), and never save() as otherwise migrating and rolling back may result in different states, due to save() calling create() when running migrate and then update() on rollback. When using the up()/down() methods, it is safe to use either save() or the more explicit methods.

When in doubt with working with tables, it is always recommended to call the appropriate function and commit any pending changes to the database.

Creating a Table¶

Creating a table is really easy using the Table object. Let’s create a table to store a collection of users.

<?php

use Phinx\Migration\AbstractMigration;

class MyNewMigration extends AbstractMigration
{
    public function change()
    {
        $users = $this->table('users');
        $users->addColumn('username', 'string', ['limit' => 20])
              ->addColumn('password', 'string', ['limit' => 40])
              ->addColumn('password_salt', 'string', ['limit' => 40])
              ->addColumn('email', 'string', ['limit' => 100])
              ->addColumn('first_name', 'string', ['limit' => 30])
              ->addColumn('last_name', 'string', ['limit' => 30])
              ->addColumn('created', 'datetime')
              ->addColumn('updated', 'datetime', ['null' => true])
              ->addIndex(['username', 'email'], ['unique' => true])
              ->create();
    }
}

Columns are added using the addColumn() method. We create a unique index for both the username and email columns using the addIndex() method. Finally calling create() commits the changes to the database.

The id option sets the name of the automatically created identity field, while the primary_key option selects the field or fields used for primary key. id will always override the primary_key option unless it’s set to false. If you don’t need a primary key set id to false without specifying a primary_key, and no primary key will be created.

To specify an alternate primary key, you can specify the primary_key option when accessing the Table object. Let’s disable the automatic id column and create a primary key using two columns instead:

<?php

use Phinx\Migration\AbstractMigration;

class MyNewMigration extends AbstractMigration
{
    public function change()
    {
        $table = $this->table('followers', ['id' => false, 'primary_key' => ['user_id', 'follower_id']]);
        $table->addColumn('user_id', 'integer')
              ->addColumn('follower_id', 'integer')
              ->addColumn('created', 'datetime')
              ->create();
    }
}

Setting a single primary_key doesn’t enable the AUTO_INCREMENT option. To simply change the name of the primary key, we need to override the default id field name:

<?php

use Phinx\Migration\AbstractMigration;

class MyNewMigration extends AbstractMigration
{
    public function up()
    {
        $table = $this->table('followers', ['id' => 'user_id']);
        $table->addColumn('follower_id', 'integer')
              ->addColumn('created', 'timestamp', ['default' => 'CURRENT_TIMESTAMP'])
              ->create();
    }
}

In addition, the MySQL adapter supports following options:

By default, the primary key is unsigned. To simply set it to be signed just pass signed option with a true value:

<?php

use Phinx\Migration\AbstractMigration;

class MyNewMigration extends AbstractMigration
{
    public function change()
    {
        $table = $this->table('followers', ['signed' => false]);
        $table->addColumn('follower_id', 'integer')
              ->addColumn('created', 'timestamp', ['default' => 'CURRENT_TIMESTAMP'])
              ->create();
    }
}

The PostgreSQL adapter supports the following options:

To view available column types and options, see Valid Column Types for details.

Determining Whether a Table Exists¶

You can determine whether or not a table exists by using the hasTable() method.

<?php

use Phinx\Migration\AbstractMigration;

class MyNewMigration extends AbstractMigration
{
    /**
     * Migrate Up.
     */
    public function up()
    {
        $exists = $this->hasTable('users');
        if ($exists) {
            // do something
        }
    }

    /**
     * Migrate Down.
     */
    public function down()
    {

    }
}

Dropping a Table¶

Tables can be dropped quite easily using the drop() method. It is a good idea to recreate the table again in the down() method.

Note that like other methods in the Table class, drop also needs save() to be called at the end in order to be executed. This allows phinx to intelligently plan migrations when more than one table is involved.

<?php

use Phinx\Migration\AbstractMigration;

class MyNewMigration extends AbstractMigration
{
    /**
     * Migrate Up.
     */
    public function up()
    {
        $this->table('users')->drop()->save();
    }

    /**
     * Migrate Down.
     */
    public function down()
    {
        $users = $this->table('users');
        $users->addColumn('username', 'string', ['limit' => 20])
              ->addColumn('password', 'string', ['limit' => 40])
              ->addColumn('password_salt', 'string', ['limit' => 40])
              ->addColumn('email', 'string', ['limit' => 100])
              ->addColumn('first_name', 'string', ['limit' => 30])
              ->addColumn('last_name', 'string', ['limit' => 30])
              ->addColumn('created', 'datetime')
              ->addColumn('updated', 'datetime', ['null' => true])
              ->addIndex(['username', 'email'], ['unique' => true])
              ->save();
    }
}

Renaming a Table¶

To rename a table access an instance of the Table object then call the rename() method.

<?php

use Phinx\Migration\AbstractMigration;

class MyNewMigration extends AbstractMigration
{
    /**
     * Migrate Up.
     */
    public function up()
    {
        $table = $this->table('users');
        $table
            ->rename('legacy_users')
            ->update();
    }

    /**
     * Migrate Down.
     */
    public function down()
    {
        $table = $this->table('legacy_users');
        $table
            ->rename('users')
            ->update();
    }
}

Changing the Primary Key¶

To change the primary key on an existing table, use the changePrimaryKey() method. Pass in a column name or array of columns names to include in the primary key, or null to drop the primary key. Note that the mentioned columns must be added to the table, they will not be added implicitly.

<?php

use Phinx\Migration\AbstractMigration;

class MyNewMigration extends AbstractMigration
{
    /**
     * Migrate Up.
     */
    public function up()
    {
        $users = $this->table('users');
        $users
            ->addColumn('username', 'string', ['limit' => 20, 'null' => false])
            ->addColumn('password', 'string', ['limit' => 40])
            ->save();

        $users
            ->addColumn('new_id', 'integer', ['null' => false])
            ->changePrimaryKey(['new_id', 'username'])
            ->save();
    }

    /**
     * Migrate Down.
     */
    public function down()
    {

    }
}

Changing the Table Comment¶

To change the comment on an existing table, use the changeComment() method. Pass in a string to set as the new table comment, or null to drop the existing comment.

<?php

use Phinx\Migration\AbstractMigration;

class MyNewMigration extends AbstractMigration
{
    /**
     * Migrate Up.
     */
    public function up()
    {
        $users = $this->table('users');
        $users
            ->addColumn('username', 'string', ['limit' => 20])
            ->addColumn('password', 'string', ['limit' => 40])
            ->save();

        $users
            ->changeComment('This is the table with users auth information, password should be encrypted')
            ->save();
    }

    /**
     * Migrate Down.
     */
    public function down()
    {

    }
}

Valid Column Types¶

Column types are specified as strings and can be one of:

• binary

• boolean

• char

• date

• datetime

• decimal

• float

• double

• smallinteger

• integer

• biginteger

• string

• text

• time

• timestamp

• uuid

In addition, the MySQL adapter supports enum, set, blob, tinyblob, mediumblob, longblob, bit and json column types (json in MySQL 5.7 and above). When providing a limit value and using binary, varbinary or blob and its subtypes, the retained column type will be based on required length (see Limit Option and MySQL for details);

In addition, the Postgres adapter supports interval, json, jsonb, uuid, cidr, inet and macaddr column types (PostgreSQL 9.3 and above).

Valid Column Options¶

The following are valid column options:

For any column type:

For decimal columns:

For enum and set columns:

For smallinteger, integer and biginteger columns:

For Postgres, when using identity, it will utilize the serial type appropriate for the integer size, so that smallinteger will give you smallserial, integer gives serial, and biginteger gives bigserial.

For timestamp columns:

You can add created_at and updated_at timestamps to a table using the addTimestamps() method. This method accepts three arguments, where the first two allow setting alternative names for the columns while the third argument allows you to enable the timezone option for the columns. The defaults for these arguments are created_at, updated_at, and false respectively. For the first and second argument, if you provide null, then the default name will be used, and if you provide false, then that column will not be created. Please note that attempting to set both to false will throw a \RuntimeException. Additionally, you can use the addTimestampsWithTimezone() method, which is an alias to addTimestamps() that will always set the third argument to true (see examples below). The created_at column will have a default set to CURRENT_TIMESTAMP. For MySQL only, update_at column will have update set to CURRENT_TIMESTAMP.

<?php

use Phinx\Migration\AbstractMigration;

class MyNewMigration extends AbstractMigration
{
    /**
     * Migrate Change.
     */
    public function change()
    {
        // Use defaults (without timezones)
        $table = $this->table('users')->addTimestamps()->create();
        // Use defaults (with timezones)
        $table = $this->table('users')->addTimestampsWithTimezone()->create();

        // Override the 'created_at' column name with 'recorded_at'.
        $table = $this->table('books')->addTimestamps('recorded_at')->create();

        // Override the 'updated_at' column name with 'amended_at', preserving timezones.
        // The two lines below do the same, the second one is simply cleaner.
        $table = $this->table('books')->addTimestamps(null, 'amended_at', true)->create();
        $table = $this->table('users')->addTimestampsWithTimezone(null, 'amended_at')->create();

        // Only add the created_at column to the table
        $table = $this->table('books')->addTimestamps(null, false);
        // Only add the updated_at column to the table
        $table = $this->table('users')->addTimestamps(false);
        // Note, setting both false will throw a \RuntimeError
    }
}

For boolean columns:

For string and text columns:

For foreign key definitions:

You can pass one or more of these options to any column with the optional third argument array.

Limit Option and MySQL¶

When using the MySQL adapter, there are a couple things to consider when working with limits:

• When using a string primary key or index on MySQL 5.7 or below, or the MyISAM storage engine, and the default charset of utf8mb4_unicode_ci, you must specify a limit less than or equal to 191, or use a different charset.

• Additional hinting of database column type can be made for integer, text, blob, tinyblob, mediumblob, longblob columns. Using limit with one the following options will modify the column type accordingly:

For binary or varbinary types, if limit is set greater than allowed 255 bytes, the type will be changed to the best matching blob type given the length.

<?php

use Phinx\Db\Adapter\MysqlAdapter;

//...

$table = $this->table('cart_items');
$table->addColumn('user_id', 'integer')
      ->addColumn('product_id', 'integer', ['limit' => MysqlAdapter::INT_BIG])
      ->addColumn('subtype_id', 'integer', ['limit' => MysqlAdapter::INT_SMALL])
      ->addColumn('quantity', 'integer', ['limit' => MysqlAdapter::INT_TINY])
      ->create();

Custom Column Types & Default Values¶

Some DBMS systems provide additional column types and default values that are specific to them. If you don’t want to keep your migrations DBMS-agnostic you can use those custom types in your migrations through the \Phinx\Util\Literal::from method, which takes a string as its only argument, and returns an instance of \Phinx\Util\Literal. When Phinx encounters this value as a column’s type it knows not to run any validation on it and to use it exactly as supplied without escaping. This also works for default values.

You can see an example below showing how to add a citext column as well as a column whose default value is a function, in PostgreSQL. This method of preventing the built-in escaping is supported in all adapters.

<?php

use Phinx\Migration\AbstractMigration;
use Phinx\Util\Literal;

class AddSomeColumns extends AbstractMigration
{
    public function change()
    {
        $this->table('users')
              ->addColumn('username', Literal::from('citext'))
              ->addColumn('uniqid', 'uuid', [
                  'default' => Literal::from('uuid_generate_v4()')
              ])
              ->addColumn('creation', 'timestamp', [
                  'timezone' => true,
                  'default' => Literal::from('now()')
              ])
              ->create();
    }
}

User Defined Types (Custom Data Domain)¶

Building upon the base types and column options you can define your custom user defined types. Custom user defined types are configured in the data_domain root config option.

data_domain:
    phone_number:
        type: string
        length: 20
    address_line:
        type: string
        length: 150

Each user defined type can hold any valid type and column option, they are just used as “macros” and replaced at the time of migration.

<?php

//...

$table = $this->table('user_data');
$table->addColumn('user_phone_number', 'phone_number')
      ->addColumn('user_address_line_1', 'address_line')
      ->addColumn('user_address_line_2', 'address_line', ['null' => true])
      ->create();

Specifying a data domain at the beginning of your project is crucial to have a homogeneous data model. It avoids mistakes like having many contact_name columns with different lengths, mismatched integer types (long vs. bigint, etc).

# Some examples of custom data types
data_domain:
    file:
        type: blob
        limit: BLOB_LONG    # For MySQL DB. Uses MysqlAdapter::BLOB_LONG
    boolean:
        type: boolean       # Customization of the boolean to be unsigned
        signed: false
    image_type:
        type: enum          # Enums can use YAML lists or a comma separated string
        values:
            - gif
            - jpg
            - png

Get a column list¶

To retrieve all table columns, simply create a table object and call getColumns() method. This method will return an array of Column classes with basic info. Example below:

<?php

use Phinx\Migration\AbstractMigration;

class ColumnListMigration extends AbstractMigration
{
    /**
     * Migrate Up.
     */
    public function up()
    {
        $columns = $this->table('users')->getColumns();
        ...
    }

    /**
     * Migrate Down.
     */
    public function down()
    {
        ...
    }
}

Get a column by name¶

To retrieve one table column, simply create a table object and call the getColumn() method. This method will return a Column class with basic info or NULL when the column doesn’t exist. Example below:

<?php

use Phinx\Migration\AbstractMigration;

class ColumnListMigration extends AbstractMigration
{
    /**
     * Migrate Up.
     */
    public function up()
    {
        $column = $this->table('users')->getColumn('email');
        ...
    }

    /**
     * Migrate Down.
     */
    public function down()
    {
        ...
    }
}

Checking whether a column exists¶

You can check if a table already has a certain column by using the hasColumn() method.

<?php

use Phinx\Migration\AbstractMigration;

class MyNewMigration extends AbstractMigration
{
    /**
     * Change Method.
     */
    public function change()
    {
        $table = $this->table('user');
        $column = $table->hasColumn('username');

        if ($column) {
            // do something
        }

    }
}

Renaming a Column¶

To rename a column, access an instance of the Table object then call the renameColumn() method.

<?php

use Phinx\Migration\AbstractMigration;

class MyNewMigration extends AbstractMigration
{
    /**
     * Migrate Up.
     */
    public function up()
    {
        $table = $this->table('users');
        $table->renameColumn('bio', 'biography')
              ->save();
    }

    /**
     * Migrate Down.
     */
    public function down()
    {
        $table = $this->table('users');
        $table->renameColumn('biography', 'bio')
               ->save();
    }
}

Adding a Column After Another Column¶

When adding a column with the MySQL adapter, you can dictate its position using the after option, where its value is the name of the column to position it after.

<?php

use Phinx\Migration\AbstractMigration;

class MyNewMigration extends AbstractMigration
{
    /**
     * Change Method.
     */
    public function change()
    {
        $table = $this->table('users');
        $table->addColumn('city', 'string', ['after' => 'email'])
              ->update();
    }
}

This would create the new column city and position it after the email column. The \Phinx\Db\Adapter\MysqlAdapter::FIRST constant can be used to specify that the new column should be created as the first column in that table.

Dropping a Column¶

To drop a column, use the removeColumn() method.

<?php

use Phinx\Migration\AbstractMigration;

class MyNewMigration extends AbstractMigration
{
    /**
     * Migrate up.
     */
    public function up()
    {
        $table = $this->table('users');
        $table->removeColumn('short_name')
              ->save();
    }
}

Specifying a Column Limit¶

You can limit the maximum length of a column by using the limit option.

<?php

use Phinx\Migration\AbstractMigration;

class MyNewMigration extends AbstractMigration
{
    /**
     * Change Method.
     */
    public function change()
    {
        $table = $this->table('tags');
        $table->addColumn('short_name', 'string', ['limit' => 30])
              ->update();
    }
}

Changing Column Attributes¶

To change column type or options on an existing column, use the changeColumn() method. See Valid Column Types and Valid Column Options for allowed values.

<?php

use Phinx\Migration\AbstractMigration;

class MyNewMigration extends AbstractMigration
{
    /**
     * Migrate Up.
     */
    public function up()
    {
        $users = $this->table('users');
        $users->changeColumn('email', 'string', ['limit' => 255])
              ->save();
    }

    /**
     * Migrate Down.
     */
    public function down()
    {

    }
}

Selecting Fields¶

Adding fields to the SELECT clause:

<?php
$builder->select(['id', 'title', 'body']);

// Results in SELECT id AS pk, title AS aliased_title, body ...
$builder->select(['pk' => 'id', 'aliased_title' => 'title', 'body']);

// Use a closure
$builder->select(function ($builder) {
    return ['id', 'title', 'body'];
});

Where Conditions¶

Generating conditions:

// WHERE id = 1
$builder->where(['id' => 1]);

// WHERE id > 1
$builder->where(['id >' => 1]);

As you can see you can use any operator by placing it with a space after the field name. Adding multiple conditions is easy as well:

<?php
$builder->where(['id >' => 1])->andWhere(['title' => 'My Title']);

// Equivalent to
$builder->where(['id >' => 1, 'title' => 'My title']);

// WHERE id > 1 OR title = 'My title'
$builder->where(['OR' => ['id >' => 1, 'title' => 'My title']]);

For even more complex conditions you can use closures and expression objects:

<?php
// Coditions are tied together with AND by default
$builder
    ->select('*')
    ->from('articles')
    ->where(function ($exp) {
        return $exp
            ->eq('author_id', 2)
            ->eq('published', true)
            ->notEq('spam', true)
            ->gt('view_count', 10);
    });

Which results in:

SELECT * FROM articles
WHERE
    author_id = 2
    AND published = 1
    AND spam != 1
    AND view_count > 10

Combining expressions is also possible:

<?php
$builder
    ->select('*')
    ->from('articles')
    ->where(function ($exp) {
        $orConditions = $exp->or_(['author_id' => 2])
            ->eq('author_id', 5);
        return $exp
            ->not($orConditions)
            ->lte('view_count', 10);
    });

It generates:

SELECT *
FROM articles
WHERE
    NOT (author_id = 2 OR author_id = 5)
    AND view_count <= 10

When using the expression objects you can use the following methods to create conditions:

• eq() Creates an equality condition.

• notEq() Create an inequality condition

• like() Create a condition using the LIKE operator.

• notLike() Create a negated LIKE condition.

• in() Create a condition using IN.

• notIn() Create a negated condition using IN.

• gt() Create a > condition.

• gte() Create a >= condition.

• lt() Create a < condition.

• lte() Create a <= condition.

• isNull() Create an IS NULL condition.

• isNotNull() Create a negated IS NULL condition.

Aggregates and SQL Functions¶

<?php
// Results in SELECT COUNT(*) count FROM ...
$builder->select(['count' => $builder->func()->count('*')]);

A number of commonly used functions can be created with the func() method:

• sum() Calculate a sum. The arguments will be treated as literal values.

• avg() Calculate an average. The arguments will be treated as literal values.

• min() Calculate the min of a column. The arguments will be treated as literal values.

• max() Calculate the max of a column. The arguments will be treated as literal values.

• count() Calculate the count. The arguments will be treated as literal values.

• concat() Concatenate two values together. The arguments are treated as bound parameters unless marked as literal.

• coalesce() Coalesce values. The arguments are treated as bound parameters unless marked as literal.

• dateDiff() Get the difference between two dates/times. The arguments are treated as bound parameters unless marked as literal.

• now() Take either ‘time’ or ‘date’ as an argument allowing you to get either the current time, or current date.

When providing arguments for SQL functions, there are two kinds of parameters you can use, literal arguments and bound parameters. Literal parameters allow you to reference columns or other SQL literals. Bound parameters can be used to safely add user data to SQL functions. For example:

<?php
// Generates:
// SELECT CONCAT(title, ' NEW') ...;
$concat = $builder->func()->concat([
    'title' => 'literal',
    ' NEW'
]);
$query->select(['title' => $concat]);

Getting Results out of a Query¶

Once you’ve made your query, you’ll want to retrieve rows from it. There are a few ways of doing this:

<?php
// Iterate the query
foreach ($builder as $row) {
    echo $row['title'];
}

// Get the statement and fetch all results
$results = $builder->execute()->fetchAll('assoc');

Creating an Insert Query¶

Creating insert queries is also possible:

<?php
$builder = $this->getQueryBuilder('insert');
$builder
    ->insert(['first_name', 'last_name'])
    ->into('users')
    ->values(['first_name' => 'Steve', 'last_name' => 'Jobs'])
    ->values(['first_name' => 'Jon', 'last_name' => 'Snow'])
    ->execute();

For increased performance, you can use another builder object as the values for an insert query:

<?php

$namesQuery = $this->getQueryBuilder('select');
$namesQuery
    ->select(['fname', 'lname'])
    ->from('users')
    ->where(['is_active' => true]);

$builder = $this->getQueryBuilder('insert');
$st = $builder
    ->insert(['first_name', 'last_name'])
    ->into('names')
    ->values($namesQuery)
    ->execute();

var_dump($st->lastInsertId('names', 'id'));

The above code will generate:

INSERT INTO names (first_name, last_name)
    (SELECT fname, lname FROM USERS where is_active = 1)

Creating an update Query¶

Creating update queries is similar to both inserting and selecting:

<?php
$builder = $this->getQueryBuilder('update');
$builder
    ->update('users')
    ->set('fname', 'Snow')
    ->where(['fname' => 'Jon'])
    ->execute();

Creating a Delete Query¶

Finally, delete queries:

<?php
$builder = $this->getQueryBuilder('delete');
$builder
    ->delete('users')
    ->where(['accepted_gdpr' => false])
    ->execute();