# How to test database - sqlite

**URL:** <https://discourse.cakephp.org/t/how-to-test-database-sqlite/11551>\
**Category:** Testing\
**Created:** [October 4, 2023, 4:44pm UTC](https://discourse.cakephp.org/t/how-to-test-database-sqlite/11551 "2023-10-04T16:44:21Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![shadowx.jb](https://avatars.discourse-cdn.com/v4/letter/s/a88e4f/32.png) [@shadowx.jb](https://discourse.cakephp.org/u/shadowx.jb)\
**Post date:** [October 4, 2023, 4:44pm UTC](https://discourse.cakephp.org/t/how-to-test-database-sqlite/11551/1 "2023-10-04T16:44:21Z")

</div>

I have config for test database

```auto
        '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

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

```

schema.sql

```auto
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.

```auto
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.

---

<div class="post-metadata">

**Author:** ![Zuluru](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.cakephp.org/zuluru/32/1230_2.png) [@Zuluru](https://discourse.cakephp.org/u/Zuluru)\
**Post date:** [October 4, 2023, 7:40pm UTC](https://discourse.cakephp.org/t/how-to-test-database-sqlite/11551/2 "2023-10-04T19:40:30Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![shadowx.jb](https://avatars.discourse-cdn.com/v4/letter/s/a88e4f/32.png) [@shadowx.jb](https://discourse.cakephp.org/u/shadowx.jb)\
**Post date:** [October 5, 2023, 4:54am UTC](https://discourse.cakephp.org/t/how-to-test-database-sqlite/11551/3 "2023-10-05T04:54:47Z")

</div>

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](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

---

<div class="post-metadata">

**Author:** ![Zuluru](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.cakephp.org/zuluru/32/1230_2.png) [@Zuluru](https://discourse.cakephp.org/u/Zuluru)\
**Post date:** [October 5, 2023, 1:57pm UTC](https://discourse.cakephp.org/t/how-to-test-database-sqlite/11551/4 "2023-10-05T13:57:19Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![shadowx.jb](https://avatars.discourse-cdn.com/v4/letter/s/a88e4f/32.png) [@shadowx.jb](https://discourse.cakephp.org/u/shadowx.jb)\
**Post date:** [October 5, 2023, 4:26pm UTC](https://discourse.cakephp.org/t/how-to-test-database-sqlite/11551/5 "2023-10-05T16:26:01Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Zuluru](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.cakephp.org/zuluru/32/1230_2.png) [@Zuluru](https://discourse.cakephp.org/u/Zuluru)\
**Post date:** [October 5, 2023, 6:30pm UTC](https://discourse.cakephp.org/t/how-to-test-database-sqlite/11551/6 "2023-10-05T18:30:02Z")

</div>

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