MySQL vs SQLite: differences, concurrency and when to switch

Short answer: SQLite is a library that keeps the database in one file inside your application: no server, no users, one writer at a time. MySQL is a server that clients reach over a socket or the network, with accounts and many concurrent writers. Use SQLite for one app on one machine, MySQL or MariaDB when many writers share data.

The SQLite behaviour shown below (locking, WAL, type affinity, STRICT tables, foreign keys, backups) was checked on Debian 12 with SQLite 3.40.1, driven through Python 3.11’s sqlite3 module. The MySQL side is written from the MySQL Reference Manual and MariaDB’s documentation, not run for this article.

MySQL vs SQLite at a glance

Embedded file vs client/server

SQLite’s own documentation puts it in one line: “SQLite does not compete with client/server databases. SQLite competes with fopen().” There is no daemon: your program calls the SQLite library, which reads and writes a file on the local disk. Copy the program and the file to another machine and the application works there too. Debian and Ubuntu ship the library as libsqlite3-0 and the command-line shell as sqlite3.

MySQL and MariaDB run as a service. Applications connect through a Unix socket or TCP port 3306, authenticate as a database user and send queries; the server manages files, caching, locking and crash recovery for all of them at once. That costs memory and administration, and buys you concurrent writers, remote clients and replication. Installing and securing it is covered in installing MariaDB or MySQL on Ubuntu and Debian.

Concurrency: one writer, and what WAL changes

SQLite allows exactly one writer per database file at a time. In its default rollback journal mode (journal_mode=DELETE) a writer also cannot commit while another connection has a read transaction open. Write-ahead logging (WAL) removes that second limit: readers keep reading their snapshot while a writer commits. It does not remove the first. This script opens two connections to the same file in each mode, with the busy timeout set to zero so the error appears at once instead of after a wait:

import sqlite3

for mode in ("DELETE", "WAL"):
    db = f"demo-{mode.lower()}.db"
    w = sqlite3.connect(db, timeout=0, isolation_level=None)   # timeout=0: fail at once
    w.execute(f"PRAGMA journal_mode={mode}")
    w.execute("CREATE TABLE IF NOT EXISTS t(x)")

    r = sqlite3.connect(db, timeout=0, isolation_level=None)
    r.execute("BEGIN")
    r.execute("SELECT count(*) FROM t").fetchone()   # a reader holds a snapshot

    w.execute("BEGIN IMMEDIATE")
    w.execute("INSERT INTO t VALUES (1)")
    try:
        w.execute("COMMIT")
        print(mode, "commit while reading: ok")
    except sqlite3.OperationalError as e:
        print(mode, "commit while reading:", e)
        w.execute("ROLLBACK")
    r.execute("COMMIT")

    w.execute("BEGIN IMMEDIATE")
    w2 = sqlite3.connect(db, timeout=0, isolation_level=None)
    try:
        w2.execute("BEGIN IMMEDIATE")
    except sqlite3.OperationalError as e:
        print(mode, "second writer:", e)
    w.execute("ROLLBACK")

DELETE commit while reading: database is locked
DELETE second writer: database is locked
WAL commit while reading: ok
WAL second writer: database is locked

In real applications you set a busy timeout rather than zero, so a second writer waits for the first instead of failing: PRAGMA busy_timeout = 5000; in SQL, or the timeout argument in Python, which defaults to 5 seconds. With WAL on, short write transactions and a busy timeout, a single-server web app that mostly reads rarely notices the single-writer limit; SQLite’s documentation calls 100,000 hits a day a conservative estimate for a website. What it cannot do is let many writers work at the same moment: writes queue up, and long transactions make the queue longer.

WAL has conditions of its own: every process using the database must be on the same machine, because WAL relies on shared memory, and SQLite’s documentation warns against keeping a database on a network file system at all. MySQL has none of these limits: InnoDB locks individual rows, readers see consistent snapshots, and many connections write at once.

Users, permissions and network access

SQLite has no user accounts and no GRANT. Whoever can read the file can read the whole database; whoever can write the file and its directory (SQLite creates journal and WAL files next to it) can change it. Access control is ordinary Linux file permissions, and remote access means writing your own API in front of it.

MySQL has accounts tied to where they connect from ('app'@'localhost' is a different account from 'app'@'%') and privileges per database, table or even column. That lets a web application, a reporting tool and a backup job each get exactly the rights they need. Listing and checking those accounts is covered in listing MySQL users and databases.

Types: SQLite is flexible unless you ask it not to be

A column type in SQLite is a preference, called type affinity, not a rule. MySQL in its default strict mode rejects a value that does not fit the column. Run on the same Debian 12 machine:

import sqlite3
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE loose(n INTEGER)")
db.execute("INSERT INTO loose VALUES ('42'), ('abc')")
print(db.execute("SELECT n, typeof(n) FROM loose").fetchall())

db.execute("CREATE TABLE tight(n INTEGER) STRICT")
try:
    db.execute("INSERT INTO tight VALUES ('abc')")
except sqlite3.IntegrityError as e:
    print(e)

[(42, 'integer'), ('abc', 'text')]
cannot store TEXT value in INTEGER column tight.n

The text '42' was converted to the integer 42, but 'abc' was stored as text in an INTEGER column without complaint. Declaring the table STRICT (SQLite 3.37.0 and later) makes SQLite reject it, which is what MySQL does by default with error 1366, “Incorrect integer value”.

Two more differences that bite

• Foreign keys are off by default in SQLite. In our check, PRAGMA foreign_keys returned 0 and an orphan row was accepted; after PRAGMA foreign_keys = ON the same insert failed with FOREIGN KEY constraint failed. The setting is per connection, so the application has to switch it on every time it connects. InnoDB enforces foreign keys unless a session explicitly turns the checks off.
• ALTER TABLE is limited in SQLite. It can rename a table, rename, add or drop a column (drop since 3.35.0), but changing a column’s type or constraints means creating a new table and copying the data. MySQL’s ALTER TABLE ... MODIFY does it in one statement.

Backups: never copy a live SQLite file with cp

A SQLite database is just a file, which makes it tempting to back it up with cp. While the application writes, that copy can be inconsistent, and in WAL mode the latest commits may not be in the main file at all yet. With a write just committed, the directory looked like this:

app.db  app.db-shm  app.db-wal

Use SQLite’s own online backup instead. The sqlite3 shell has .backup, and any SQLite 3.27.0 or later can write a clean, compacted copy with VACUUM INTO. We ran the second one on the WAL database above, and the copy passed PRAGMA integrity_check with ok:

sqlite3 /srv/app/app.db ".backup '/backup/app-$(date +%F).db'"

# or the same as plain SQL, which any SQLite client can run
sqlite3 /srv/app/app.db "VACUUM INTO '/backup/app-$(date +%F).db'"

For MySQL and MariaDB the standard logical backup is a dump. With InnoDB tables, --single-transaction takes a consistent snapshot without locking the application out:

mysqldump --single-transaction --routines --triggers appdb > appdb-$(date +%F).sql
mariadb-dump --single-transaction --routines --triggers appdb > appdb-$(date +%F).sql

Point-in-time recovery, replaying the binary log up to the second before a bad DELETE, is built into MySQL and MariaDB; SQLite has nothing comparable built in.

Typical uses

• SQLite: mobile and desktop applications, embedded devices, test suites, prototypes, small and medium websites served by one machine, and self-hosted tools that run as a single process. Uptime Kuma offers SQLite or MariaDB at setup, and Headscale uses SQLite by default.
• MySQL or MariaDB: anything where several application servers, cron workers or people write to the same data, PHP applications built for it, and every WordPress site: wordpress.org recommends MySQL 8.0 or later or MariaDB 10.11 or later, and SQLite support exists only as a plugin.
• Applications that support both: Drupal 11 runs on MySQL, MariaDB, PostgreSQL or SQLite 3.45 and later, so Debian 12’s SQLite 3.40.1 is too old for it while Debian 13 (3.46.1) is new enough. n8n starts on a SQLite file and recommends PostgreSQL, not MySQL, for an instance you keep. The usual pattern is SQLite to try it, a server database to run it.

When to move from SQLite to MySQL, MariaDB or PostgreSQL

Any one of these is a good reason:

• database is locked errors keep appearing although WAL is on, transactions are short and a busy timeout is set.
• A second application server, or a worker on another machine, needs to write.
• Other people or tools need their own logins with limited rights, or remote access.
• The database would have to live on network storage.
• You need replication or point-in-time recovery.

Pick the target by the application: MySQL or MariaDB for PHP stacks and software built around it, PostgreSQL when you want stricter SQL, richer types and the extensions ecosystem. Migrate through the application’s own export and import or its migration tooling where it has one. For PostgreSQL, pgloader can read a SQLite file directly. A raw sqlite3 app.db .dump does not load into MySQL unchanged: the SQL dialects differ in details such as AUTOINCREMENT vs AUTO_INCREMENT, identifier quoting and the PRAGMA lines.

On a VPS

Both run well on a small Linux VPS. SQLite costs nothing while idle and benefits directly from fast NVMe storage; keep the file on the VPS’s own disk, not on a mounted network share such as NFS or SMB, and include it in your backups through .backup or VACUUM INTO, not a plain file copy. MySQL or MariaDB adds a service to secure and size: bind it to 127.0.0.1 unless something remote really needs it, and give InnoDB a buffer pool that fits next to your application in RAM.

How to choose

• One application, one machine, mostly reads: SQLite with WAL on.
• Several writers, several servers or remote clients: MySQL or MariaDB (or PostgreSQL).
• The software names its database: use that. WordPress means MySQL or MariaDB.
• Unsure and starting small: SQLite, through a database layer that lets you switch later.

Frequently asked