Database

FloCMS sites use MySQL, MariaDB or, since core 2.9, SQLite. A site doesn't need a database to run: the welcome page, static pages and the documentation you're reading work without one. You add a database when your site stores data.

Supported databases#

The query builder uses backtick-quoted identifiers, which MySQL, MariaDB and SQLite all accept. PDO connectivity alone does not make PostgreSQL or another engine supported.

MySQL and MariaDB#

Use MySQL or MariaDB for most websites, and on shared hosting such as cPanel. Both use the same connection settings below and require PHP's pdo_mysql extension. MariaDB does not need a separate PDO driver.

SQLite#

SQLite keeps the whole database in one file. It suits small sites, local development and tests. Set:

DB_TYPE=sqlite
DB_NAME=storage/database.sqlite

DB_NAME is the file, relative to the project root unless it is a full path. SQLite doesn't use DB_HOST, DB_PORT, a username or a password, and PHP needs pdo_sqlite. Create the file with the migrations:

php flo migrate
php flo db:check

• The file's folder must exist and be writable by PHP and the web server, since SQLite also writes a journal file next to it.
• The website connection never creates the file. When it is missing, the page says so and names DB_NAME, so a typo can't look like an empty site.
• Foreign keys are switched on, and a write waits up to 5 seconds for another one to finish.
• Each migration's SQL must also work on SQLite. The migration helpers id() and tableOptions() adapt to MySQL and SQLite; application and package migrations may still contain engine-specific SQL. See migrations.
• Keep the file out of the web root. storage/ is a good place: the web server serves only public/.

Configuration#

The connection is set in .env:

DB_HOST=127.0.0.1
DB_PORT=3306
DB_NAME=my_site
DB_USERNAME=my_user
DB_PASSWORD=secret
DB_CHARSET=utf8mb4

config/config.php copies these into the db.* settings, which the connection reads. While DB_NAME or DB_USERNAME is empty, the site runs without a database. With SQLite, only DB_NAME is needed.

The connection#

FloCMS opens the connection the first time a query needs it, and every model and query in the request shares it. Pages that never query the database never connect.

In a model, the connection is $this->db. Anywhere else:

use FloCMS\Core\App;

$db = App::db();   // FloCMS\Core\Database, or null when not configured

if ($db !== null) {
    $count = $db->table('posts')->count();
}

The connection uses real prepared statements, throws an exception on every SQL error, and gives up connecting after 2 seconds. When the connection fails, the failure is remembered for the rest of the request, so a slow or missing server costs that time only once.

Build queries with the query builder, or run SQL with query(). See models.

When the database is missing#

You don't need to catch these exceptions; the error handler shows the page. In debug mode the page also shows the driver's own message. See error pages.

Checking the connection#

App::dbStatus() reports the state without throwing, for setup screens and health checks:

$status = App::dbStatus();
// ['configured' => true, 'connected' => false, 'reason' => 'unknown_database',
//  'code' => 1049, 'message' => 'Database not found.', 'detail' => '...']

reason is not_configured, server_unavailable, access_denied, unknown_database or other. message is safe to show to visitors. detail is the driver's message, which names hosts and users, so only show it in debug mode.

From the command line:

php flo db:check    # is the database configured and reachable?
php flo db:show     # the connection and every table with its row count
php flo doctor      # the whole installation, the database included

Creating tables#

Tables are created with migrations: PHP files in database/migrations/ that every copy of the site runs the same way. A new site's migrations create the users table and the API tables:

php flo migrate

Starting data, such as categories or a demo account, comes from seeders.