Cakephp: How to use migration to insert records

Question

I'm using CakePHP v3.x and I'm trying to figure out how to insert some records via the migrations tool. The documentation only lists methods for modifying the schema. Will I need to insert records manually with raw SQL?

Lead

Answer 1

CakePHP 3's Migration plugin is a Phinx wrapper plugin, so adding records can be done using the up() method:-

public function up() {
    // Save records to the newly created schema
}

public function down() {
    // Remove records
}

For example, you could add a new user using TableRegistry on up:-

public function up() {
    // Save records to the newly created schema
    $UsersTable = TableRegistry::get('Users');
    $user = $UsersTable->newEntity();

    $user->name = 'Joe Bloggs';
    $user->email = '[email protected]';

    $UsersTable->save($user);
}

If using TableRegistry don't forget to include use Cake\ORM\TableRegistry; at the top of the migration file.

For CakeDC's Migration plugin you can insert records using the callbacks in the relevant migration file:-

public function after($direction) {
    if ($direction === 'up') {
        // Save records to the newly created schema
    } elseif ($direction === 'down') {
        // Remove records
    }
}

NOTE: If you are using the Postgres driver, there is currently a bug which requires a small workaround to make this work.

Kizzykjersti

CakePHp 3.x ships with its own migrations plugin (github.com/cakephp/migrations), which is a Phinx wrapper, are you sure that this applies here? – Geerts

My mistake, I like working with CakeDC's plugin. I've updated my answer to show how to do it with the default plugin too. – Kizzykjersti

kk, just wasn't sure as I haven't used the migrations plugin yet. I think it should be noted that in that case one indeed has to use raw SQL, as there seem to be no insert (wrapper) methods. github.com/robmorgan/phinx/issues/96 – Geerts

@Geerts my understanding was that you could use Cake's ORM in the migrations for inserting data. However, every implementation of migrations I've seen has used raw SQL for handling the migration which doesn't seem correct to me. – Kizzykjersti

Mmm, that might be true... I'll give it a try later on before I'll continue spreading potential false information :) – Geerts

Ok, it indeed works, you can use the ORM or the underlying lower level database layer to insert rows. Not sure though if there might be any side effects with regards to cached schemas. – Geerts

@Geerts do I simply add the "uses" at the top and then $this->loadModel(...) ? – Lead

@SDP No, the loadModel() method is part of the ModelAwareTrait trait, it's not used in genereated migration files. If you'd wanted to use that method, then you'd have to incorporate the trait. However I'd probably simply directly use the TableRegistry class to retrieve a table instance. – Geerts

@Geerts gotcha. Should this be it's own answer? – Lead

@SDP Not neccessarily, drmonkeyninja could add it to his answer. – Geerts

@SDP I've added an example using TableRegistry to my answer. – Kizzykjersti

Looks like some time ago, an insert() method was added, however It's not documented yet github.com/robmorgan/phinx/pull/457 – Geerts

Answer 2

in cake php 5, you can execute raw queries in up and down methods like so:

 public function up(): void {
     $this->execute("INSERT INTO `table_name` (col_names) VALUES ('your val')");
 }

Try it out in cake 3x to see if it works or check docs to see when the function was added.

Psychotherapy