PHPUnit: PDOException while running Unit test, CakePHP 4

Hi folks,
newbie here. I am trying to write some tests, but I keep banging my head to a metaphorical and a literal wall with it.
I am trying to test add user function, here’s the test:

/**
     * Test add method
     *
     * @return void
     */
    public function testAdd(): void
    {
        
        $this->get('/users/add');
        $this->assertResponseOk();


    $data = [
        'firstname' => 'Rio',
        'lastname' => 'Grande',
        //'username' => 'riogrande@gmail.com',
        //'email' => 'riogrande@gmail.com',
        //'password' => 'riorio'
    ];

    $this->post('/users/add', $data);
    //$this->assertResponseContains('The user has been saved.');

    $expected = [
        [
        'firstname' => 'Rio',
         'lastname' => 'Grande',
        // 'username' => 'riogrande@gmail.com',
        'email' => 'riogrande@gmail.com',
        // 'password' => 'riorio'
        ]
    ];
     
    $users = TableRegistry::get('users');
    $query = $users->find('all', [
    'fields' => ['Users.firstname', 'Users.lastname', 'Users.username', 'Users.email', 'Users.password'],
    'conditions' => ['Users.email' => 'riogrande@gmail.com'],
    ]);

    $result = $query->enableHydration(false)->toArray();
    $this->assertEquals($expected, $result);
}

Here is the add function in UsersController:

/**
     * Add method
     *
     * @return \Cake\Http\Response|null Redirects on successful add, renders view otherwise.
     */
    public function add()
    {
        $user = $this->Users->newEmptyEntity();
        if ($this->request->is('post')) {
            $user = $this->Users->patchEntity($user, $this->request->getData());
            $user->user_id = $this->Auth->user('id');
            if ($this->Users->save($user)) {
                $this->Flash->success(__('The user has been saved.'));

                return $this->redirect(['action' => 'index']);
            }
            $this->Flash->error(__('The user could not be saved. Please, try again.'));
        }

    }

And the problem while running phpunit test: PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘Users.firstname’ in ‘field list’

The app is functioning otherwise and I can add new users and data goes to database, but since testing with Cake is something new for me it would be nice to start with kind of easy test and get it to work.

Please: explain to me in plain English, why the test can’t find my Users.firstname (I tried with Users.email and Users.password and got the same error). Do I have to select the table/database somewhere? What can I do to continue with this?

All the code is stolen from other cake users (thanks and sorry) and it’s probably from Cake version 3 or prior, does it affect?

I really appreciate all input. Have a nice weekend!

What does the fixture look like for initializing the users table for your test?

Thanks for your answer Zuluru!
I actually realized that my test db is empty! I copied the structure from actual db there now, but still doesn’t work. I assume that I have to run migrations to Cake same way as I ran them with actual db?

And then I went to read documentation about the fixtures… As you might guess, I didn’t read 'til fixture part and I don’t have fixtures. So, back to documentation!

Of course I had fixtures since CakePHP makes stuff ready :slight_smile:

This is my fixture for Users:

public $fields = [
        'id' => ['type' => 'integer', 'length' => null, 'unsigned' => false, 'null' => false, 'default' => null, 'comment' => '', 'autoIncrement' => true, 'precision' => null],
        'firstname' => ['type' => 'string', 'length' => 255, 'null' => false, 'default' => null, 'collate' => 'utf8mb4_general_ci', 'comment' => '', 'precision' => null],
        'lastname' => ['type' => 'string', 'length' => 255, 'null' => false, 'default' => null, 'collate' => 'utf8mb4_general_ci', 'comment' => '', 'precision' => null],
        'email' => ['type' => 'string', 'length' => 255, 'null' => false, 'default' => null, 'collate' => 'utf8mb4_general_ci', 'comment' => '', 'precision' => null],
       
        'user_group' => ['type' => 'integer', 'length' => null, 'unsigned' => false, 'null' => false, 'default' => null, 'comment' => '', 'precision' => null, 'autoIncrement' => null],
        
        '_constraints' => [
            'primary' => ['type' => 'primary', 'columns' => ['id'], 'length' => []],
            'UNIQUE_EMAIL' => ['type' => 'unique', 'columns' => ['email'], 'length' => []],
        ],
        '_options' => [
            'engine' => 'InnoDB',
            'collation' => 'utf8mb4_general_ci'
        ],
    ];

did you load fixture?
ie

class ArticlesControllerTest extends TestCase
{

public $fixtures = ['app.Articles'];

you can also check your phpunit config and if you have any test data

https://book.cakephp.org/3/en/development/testing.html#phpunit-configuration

You said you have these fixtures in ControllerTest.php. Is that where the test that’s causing the error is running from?

They are like this:
protected $fixtures = [
‘app.Users’
];

Stupidest thing, but I can’t figure out where to find this .xml!

Yes. I only have tests in this one ControllerTest.php and when I run tests, it says that "There were 2 errors:

  1. App\Test\TestCase\Controller\UsersControllerTest::testAdd
    PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘Users.firstname’ in ‘field list’ "
    and the another error is similar except the test is another.

its in here https://github.com/cakephp/app/blob/61ae10539f3b41c61852a4ba1f660047bfb63fdd/phpunit.xml.dist you need to rename it thou

Jeez I’m blind :frowning: Thanks!

It should be right:

<!-- Setup a listener for fixtures -->
    <listeners>
        <listener class="Cake\TestSuite\Fixture\FixtureInjector">
            <arguments>
                <object class="Cake\TestSuite\Fixture\FixtureManager"/>
            </arguments>
        </listener>
    </listeners>

ok this is kind of odd

$users = TableRegistry::get('users');
$query = $users->find('all', [
    'fields' => ['Users.firstname'

you need to take care of letter cases, so your table is Users or users?

users with a small letter. I followed an example by my colleague and they’ve written the table name with capital letter, ie.
$query
->select([‘Tablestuff.id’
although the table is called tablestuff.

I’m confused with this! And I actually think I have tried this with both small and capital letters.

https://book.cakephp.org/3/en/intro/conventions.html#model-conventions

in simple terms in cake you should use PascalCase, and in db snake_case

you can try to quickly bin/cake bake fixture Users just copy your $records before! and then use the proper casing