Database

FloCMS sites use MySQL or MariaDB. 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#

Database support differs between the website connection and the command-line tools:

The website connection always builds a MySQL DSN. Setting DB_TYPE=sqlite or DB_DSN in .env does not switch App::db(), models, or API controllers that use that connection to SQLite or another engine.

The shared Database class wraps a PDO connection and can be constructed directly with SQLite PDO in custom code or tests. This is separate from the standard website setup. The query builder uses backtick-quoted identifiers, and the CLI migration tools contain MySQL/SQLite-specific SQL, so PDO connectivity alone does not make PostgreSQL or another engine fully supported.

MySQL and MariaDB#

Use MySQL or MariaDB for a standard FloCMS website. Both use the same connection settings below and require PHP's pdo_mysql extension. MariaDB does not need a separate PDO driver.

SQLite for command-line use#

For a separate CLI project or test setup, configure:

DB_TYPE=sqlite
DB_NAME=storage/database.sqlite
DB_USERNAME=
DB_PASSWORD=

The database file path is relative to the project root unless it is absolute. Its parent directory must already exist and be writable, and PHP must have pdo_sqlite enabled. SQLite does not use DB_HOST, DB_PORT, a username, or a password.

php flo db:check

The CLI migration runner supports SQLite, but 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.

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.

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.