SQLite on a Server: sqlite3 Basics, PHP PDO, Permissions, Backups

This article is a localized edition of the Japanese original. Read the Japanese original

SQLite is an embedded database that stores an entire database in a single file. Unlike MySQL or PostgreSQL, it has no resident server process. Applications (PHP, Python and so on) read and write the file directly through a library. So there is no "start the SQLite server" or "stop it" step; when you use it on a server, the three things to think about are the file's location and permissions, how concurrent writes are handled, and backups.

The steps in this article were tested on Ubuntu 24.04 (SQLite 3.45.1, PHP 8.3) as of November 2026.

What SQLite is and is not suited for

SQLite can handle reads concurrently, but writes go one at a time for the whole database. For sites with heavy write traffic, choose MySQL or MariaDB.

Step 1: Install the sqlite3 command

Install sqlite3, the command-line tool for working with databases.

# Ubuntu / Debian
sudo apt update
sudo apt install sqlite3

# RHEL / AlmaLinux / Rocky Linux / Fedora
sudo dnf install sqlite

# Check the version
sqlite3 --version

libsqlite3-dev (Debian family) and sqlite-devel (RHEL family) are development files used when compiling C programs. You do not need them just to use the command.

To use SQLite from PHP, also install PHP's SQLite extensions. On Ubuntu, the php-sqlite3 package includes both the sqlite3 and pdo_sqlite extensions.

sudo apt install php-sqlite3
php -m | grep -i sqlite

If pdo_sqlite and sqlite3 appear, you are ready. PHP served through the web server (PHP-FPM and so on) picks them up after the service restarts.

Step 2: Create and work with a database

Passing a file name to sqlite3 opens that database. If the file does not exist, it is created when you first create a table.

sqlite3 app.db

At the sqlite> prompt, type SQL. SQL statements end with ;.

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT UNIQUE
);
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');
SELECT * FROM users;

Separately from SQL, "dot commands" starting with . control sqlite3 itself. Dot commands do not end with ;.

You can also run a command in one line from the shell, which is handy in scripts and cron jobs.

sqlite3 app.db "SELECT COUNT(*) FROM users;"
sqlite3 app.db ".tables"

Step 3: Connect from PHP (PDO)

With PDO, PHP code looks the same as it does for MySQL. In this example, the database file sits outside the public directory (document root).

<?php
// Keep the database file outside the public directory
$dbFile = '/var/lib/myapp/app.db';

$pdo = new PDO('sqlite:' . $dbFile, null, null, [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_TIMEOUT            => 5, // seconds to wait for a lock
]);
$pdo->exec('PRAGMA journal_mode = WAL');
$pdo->exec('PRAGMA foreign_keys = ON');

$pdo->exec('CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE
)');

// Pass values through placeholders (prevents SQL injection)
$stmt = $pdo->prepare('INSERT INTO users (name, email) VALUES (:name, :email)');
$stmt->execute([':name' => 'Bob', ':email' => 'bob@example.com']);

$stmt = $pdo->prepare('SELECT id, name, email FROM users WHERE email = :email');
$stmt->execute([':email' => 'bob@example.com']);
print_r($stmt->fetch());

• PDO::ATTR_TIMEOUT sets how many seconds to wait when another process is writing and the lock cannot be acquired. Without it, you are more likely to hit "database is locked" errors right away.
• PRAGMA journal_mode = WAL switches to "WAL mode", where reads are not blocked during a write. The setting is stored in the database file, so running it once keeps it in effect.
• PRAGMA foreign_keys = ON is needed for each connection. By default SQLite does not enforce foreign key constraints.

Step 4: Decide the file location and permissions

Permissions are the most common stumbling block when using SQLite from a web server (PHP).

• Keep it outside the document root: If you put it in a public directory such as /var/www/html/data.db, anyone who learns the URL may be able to download the whole file. Put it in a non-public location such as /var/lib/myapp/.
• The directory needs write permission too: When writing, SQLite creates temporary files (app.db-journal, or app.db-wal and app.db-shm in WAL mode) in the same directory as the database. Making only the file writable fails if the directory is not writable.

With the directory owned by root and only the file owned by www-data, a write produced this error.

SQLSTATE[HY000]: General error: 8 attempt to write a readonly database

Here is an example for when PHP-FPM or Apache runs as the www-data user (the default on Ubuntu / Debian).

sudo mkdir -p /var/lib/myapp
sudo chown www-data:www-data /var/lib/myapp
sudo chmod 750 /var/lib/myapp
# When placing an existing file
sudo chown www-data:www-data /var/lib/myapp/app.db
sudo chmod 640 /var/lib/myapp/app.db

On the RHEL family, Apache and PHP-FPM run as the apache user by default. Check the actual user in the first column of ps aux | grep -E 'php-fpm|httpd|apache2' (on the child process lines, not the parent). Where SELinux is enabled, the directory also needs a writable label (such as httpd_sys_rw_content_t).

Step 5: Concurrent writes and locking

• Only one write happens at a time. If another process is writing, you get "database is locked" once the wait time (PDO::ATTR_TIMEOUT from Step 3) runs out.
• In WAL mode, reads are not blocked during a write. WAL mode suits web applications.
• Long transactions hold the lock for a long time. When writing in bulk, use a single transaction and finish it quickly.
• The official SQLite documentation states that WAL mode does not work over a network file system. Keep the database on the server's local disk.

Step 6: Backups and integrity checks

Copying a database with cp while it is being written can capture an in-between state or, in WAL mode, miss the contents of the -wal file, leaving you with a broken backup. Copy it with SQLite's own features.

# Method 1: the .backup command (uses the online backup API)
sqlite3 /var/lib/myapp/app.db ".backup /backup/app-$(date +%Y%m%d).db"

# Method 2: VACUUM INTO (SQLite 3.27.0 or later; creates a compacted copy)
sqlite3 /var/lib/myapp/app.db "VACUUM INTO '/backup/app-vacuum-$(date +%Y%m%d).db'"

# Check that the backup is not corrupted (ok means it is healthy)
sqlite3 /backup/app-$(date +%Y%m%d).db "PRAGMA integrity_check;"

The target of VACUUM INTO must not exist or must be an empty file, or the command fails. If you run it twice on the same day, include the time in the file name.

To keep the backup as SQL statements, use .dump. The output can be loaded into another SQLite database as is.

sqlite3 /var/lib/myapp/app.db .dump | gzip > /backup/app-$(date +%Y%m%d).sql.gz
# Restore
gunzip -c /backup/app-20261110.sql.gz | sqlite3 /var/lib/myapp/restored.db

For daily backups from cron, put the .backup line in a script and register it together with a step that deletes old files.

SQLite and WordPress

The databases WordPress core officially requires are MySQL or MariaDB. The "SQLite Database Integration" plugin published by WordPress.org (version 3.1.3 as of November 2026) lets WordPress run on SQLite, but the plugin's description says that for production you should keep reliable backups, and that MySQL or MariaDB suit sites with many writes better. Activating it creates a new, empty database; existing posts are not migrated. For use in a local development environment, see "Building a lightweight local development environment with the WordPress SQLite driver".

FAQ

How do I start or stop the SQLite server?

You do not need to. SQLite has no server process; it is used only when an application opens the file. Steps that keep it running with nohup sqlite3 … or stop it with kill are unnecessary.

Where are errors logged?

SQLite does not write to log files or syslog on its own. Errors are returned to the application using it, so for PHP, check PHP's error log (the PHP-FPM log or the web server's error log).

I keep getting “database is locked”

Set a lock wait time (PDO::ATTR_TIMEOUT, or .timeout 5000 in the sqlite3 command), switch to WAL mode, and look for long transactions. If it still happens often, your write volume may exceed what SQLite suits, and it is worth considering a move to MySQL or similar.

Summary

• SQLite is a single-file database with no server process. Work with it through the sqlite3 command using SQL and dot commands.
• From PHP, connect with PDO (a DSN starting with sqlite:) and set a lock wait time and WAL mode.
• On a web server, keep the file outside the document root and make both the file and its directory writable by the user the server runs as. Take backups with .backup or VACUUM INTO, not cp.

Related articles:

• SQLite basics and command reference
• Building a lightweight local development environment with the WordPress SQLite driver

References: SQLite – Command Line Shell For SQLite, SQLite – Write-Ahead Logging, SQLite – Temporary Files Used By SQLite, SQLite – VACUUM, PHP Manual – PDO_SQLITE, WordPress.org – SQLite Database Integration

Revision history: 2026-11-10 full revision (removed nonexistent steps for "starting and stopping an SQLite server", code samples mixing shell and SQL, and checking logs in syslog; replaced them with steps for connecting from PHP, permissions, locking and backups).

Questions about WordPress? The publisher of this site is based in Japan and replies in Japanese. Contact (Japanese)