Configure CakePHP to Connect to SQLite

This isn’t going to be an overly involved tutorial. Rather, it will stick to the absolute essentials.

Prerequisites

To follow along with this tutorial, you don’t need a lot, just the following

• Command Line Shell for SQLite
• Composer available globally
• PHP 8.3 (the latest at the time of writing)
• Some familiarity with the command line/terminal
• Some familiarity with CakePHP would also be helpful, but not essential

Configure the database

The first thing you need to do is to update CakePHP's database configuration to use SQLite. To do that, open config/app_local.php and update the driver and database keys of the default array (inside the Datasources array) in the array returned by the file.

'Datasources' => [
    'default' => [
        'driver' => Sqlite::class,
        'database' => __DIR__ . '/../resources/database/database.sqlite',
    ],
]

As shown in the configuration above, driver configures CakePHP to use its SQLite class for database interaction, and database provides the absolute path to the SQLite database file. The configuration above will have CakePHP look for the database file (database.sqlite) in resources/database.

You can also set the entire configuration with the url parameter, as in the following configuration (but there are a couple of steps involved):

'Datasources' => [
    'default' => [
        'url' => env('DATABASE_URL', null),
    ],
]

Then, if config/.env doesn't already exist (which it likely won't), create it from config/.env.example with the following command:

cp -v config/.env.example config/.env

After that, in config/.env, set the value of DATABASE_URL as follows:

export DATABASE_URL="sqlite://127.0.0.1/resources/database/database.sqlite"

Finally, you need to uncomment the following configuration in config/bootstrap.php:

if (!env('APP_NAME') && file_exists(CONFIG . '.env')) {
    $dotenv = new \josegonzalez\Dotenv\Loader([CONFIG . '.env']);
    $dotenv->parse()
        ->putenv()
        ->toEnv()
        ->toServer();
}

There are a few other CakePHP configuration settings that you can make use of, if you’re interested. These are:

• cache: The cache flag to send to SQLite.
• flags: An associative array of PDO constants that should be passed to the underlying PDO instance.
• log: Set to true to enable query logging. When enabled queries will be logged at a debug level with the queriesLog scope.
• mask: Set the (Linux/UNIX) filesystem permissions on the generated database file.
• mode: The mode flag value to send to SQLite.

Of these, flags, log, and mask are likely the most useful.

If you’re not familiar with SQLite, unlike database such as PostgreSQL, MySQL, and MSSQL Server, SQLite is a flat-file or single-file database. Given that, you don’t need a client/server setup, just a single file.

Create a database migration

With the configuration in place, most of the work is now complete. But, you still need to provision the database. This isn't going to be very complex, as we're only going to create a single migration containing a single table named users. To do that, generate a migration file by running the following command:

bin/cake bake migration users

The new migration file will be located in the config/Migrations directory and end with _Users.php. Open it, and update it to match the code below.

<?php

declare(strict_types=1);

use Migrations\AbstractMigration;

class Users extends AbstractMigration
{
    public function change(): void
    {
        $table = $this->table('users');
        $table->addColumn('email', 'string', [
            'default' => null,
            'limit' => 255,
            'null' => false,
        ]);
        $table->addColumn('username', 'string', [
            'default' => null,
            'limit' => 255,
            'null' => false,
        ]);
        $table->addColumn('phone', 'string', [
            'default' => null,
            'limit' => 255,
            'null' => false,
        ]);
        $table->create();
    }
}

Run the database migration

With the migration file created and updated, create the database directory, where the SQLite database file will be stored, and migrate the database with the following command:

mkdir resources/database

bin/cake migrations migrate

You should see output similar to the following from the second command:

using migration paths
 - /opt/cakephp-app/config/Migrations
using seed paths
 - /opt/cakephp-app/config/Seeds
using environment default
using adapter sqlite
using database /opt/cakephp-app/config/../resources/database/database.sqlite
ordering by creation time

 == 20240626050823 Users: migrating
 == 20240626050823 Users: migrated 0.0036s

All Done. Took 0.0064s

Dumps the current schema of the database to be used while baking a diff

using migration paths
 - /opt/cakephp-app/config/Migrations
using seed paths
 - /opt/cakephp-app/config/Seeds
Writing dump file `/opt/cakephp-app/config/Migrations/schema-dump-default.lock`...
Dump file `/opt/cakephp-app/config/Migrations/schema-dump-default.lock` was successfully written

Check the created database

After migrating the database, like any good task, we need to check it. To do that, connect to the SQLite database with SQLite's command line shell by running the following command:

sqlite3 resources/database/database.sqlite

Then, run the following command to print out the schema of the migrated users table:

sqlite> .schema users

You should see output similar to the following:

CREATE TABLE `users` (`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, `email` VARCHAR(255) NOT NULL, `username` VARCHAR(255) NOT NULL, `phone` VARCHAR(255) NOT NULL);

That's how to configure CakePHP to connect to SQLite

Honestly, there isn’t a lot to do to get up and running; mainly because you only need to make a few small changes to CakePHP’s existing configuration, and because SQLite is a flat-file database. However, if you’d read through the CakePHP documentation and weren’t quite sure of the steps you needed to take, I hope that this short tutorial showed you what you needed to do.

What extra configurations would you make?