How to test database - sqlite

Testing
1

I have config for test database

        'test' => [
            'host' => 'localhost',
            'username' => '',
            'password' => '',
            'database' => '',
            'url' => env('DATABASE_TEST_URL', 'sqlite://127.0.0.1/tmp/tests.sqlite'),
        ],

How to create - tests.sqlite database?

If exported schema from phpmyadmin and store to /tests/schema.sql and uncomment code from /tests/bootstrap.php

use Cake\TestSuite\Fixture\SchemaLoader;
(new SchemaLoader())->loadSqlFiles('./tests/schema.sql', 'test');

schema.sql

DROP TABLE IF EXISTS `albums`;
CREATE TABLE IF NOT EXISTS `albums` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`albums_type_id` int(11) NOT NULL,
`file` varchar(250) NOT NULL,
`name` varchar(150) NOT NULL,
`description` text DEFAULT NULL,
`created` datetime NOT NULL,
`modified` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `albums___fk1` (`albums_type_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Doesnt work, i get error eg.

Error in bootstrap script: PDOException:
SQLSTATE[HY000]: General error: 1 near "KEY": syntax error
#0 E:\projects\www\vendor\cakephp\cakephp\src\Database\Driver.php(257): PDO->exec('DROP TABLE IF E...')
#1 E:\projects\www\vendor\cakephp\cakephp\src\TestSuite\Fixture\SchemaLoader.php(91): Cake\Database\Driver->exec('DROP TABLE IF E...')
#2 E:\projects\www\tests\bootstrap.php(64): Cake\TestSuite\Fixture\SchemaLoader->loadSqlFiles('./tests/schema....', 'test')
#3 E:\projects\www\vendor\phpunit\phpunit\src\TextUI\Application.php(299): include_once('E:\\projects\\www...')
#4 E:\projects\www\vendor\phpunit\phpunit\src\TextUI\Application.php(98): PHPUnit\TextUI\Application->loadBootstrapScript('E:\\projects\\www...')
#5 E:\projects\www\vendor\phpunit\phpunit\phpunit(99): PHPUnit\TextUI\Application->run(Array)
#6 {main}

Please help.

2

I’m no sqlite expert, but from a quick search it appears that they support PRIMARY KEY but not other KEYs, hence why your SQL is causing the near "KEY" syntax error.

3

So I think that the instructions are wrong? Where does it say that an sql schema is enough and not a specially modified schema?

https://book.cakephp.org/5/en/development/testing.html#creating-schema-with-sql-dump-files

I don’t want to use a test database, but I would like it to be tested right from that schema = sqlite if possible

4

The instructions are presumably assuming that you’re going to run your tests on the same type of database as you’re targeting for deployment. Why are you using MySQL for one and sqlite for the other? They are not, as you’ve just found, completely compatible, so testing on one may not find problems that will arise when you deploy on the other.

1 Like
5

Thank you for the explanation, so do you recommend having one more identical test database? If I set it to the same as production, the tests will delete or overwrite my data.

6

I would recommend using the test database setup, where unit tests run on a different database, but the same engine.

1 Like