SQLite vs a database server: when to switch

SQLite is a database that lives in a single file and runs inside your app. PostgreSQL and MySQL are database servers: separate processes that your app talks to over a network connection. Both speak SQL, but they solve different problems, and the wrong pick costs you later: slow writes, locked files or a painful migration.

The comparison below assumes the most common case: a bot, API or website that started small and may grow. If you are still choosing a database family, read how to choose a database for your project first.

What is the difference between SQLite and a database server?#

SQLite is a library. Your app opens a file, such as app.db, and reads and writes it directly. There is nothing to install, no port, no password and no separate process. That is why it ships inside phones, browsers and many desktop apps.

A database server (PostgreSQL, MySQL) is a long-running process that owns the data files. Your app connects over TCP, authenticates, and sends queries. The server coordinates every client, so many apps and machines can share one database safely. See what is PostgreSQL and what is MySQL.

How does SQLite handle concurrent writes?#

SQLite lets many connections read at once but only one write at a time. In the default rollback-journal mode, a writer can also block readers while it commits. Switching to WAL (write-ahead log) mode lets readers and a writer run at the same time, though there can still be only one writer.

You only need to enable it once per database, because the setting is stored in the file and persists across connections:

import sqlite3

con = sqlite3.connect("app.db", timeout=10)  # a blocked writer waits up to 10 s
print(con.execute("PRAGMA journal_mode=WAL").fetchone())  # ('wal',)
con.execute("PRAGMA foreign_keys=ON")  # off by default, set it on every connection
con.execute("CREATE TABLE IF NOT EXISTS orders (id INTEGER PRIMARY KEY, total REAL NOT NULL)")
with con:
    con.execute("INSERT INTO orders (total) VALUES (?)", (19.9,))
print(con.execute("SELECT COUNT(*) FROM orders").fetchone())
con.close()

If the pragma cannot switch modes, it returns the previous mode, so check the printed value. Even in WAL mode, a query can return SQLITE_BUSY in rare cases: handle that error in code that writes often.

For a bot that saves settings a few times a minute, one writer at a time is invisible. For an API that takes hundreds of writes per second, every write queues behind the one before it.

How does a database server handle concurrency?#

PostgreSQL uses multiversion concurrency control (MVCC). Each query sees a consistent snapshot of the data, so reading never blocks writing and writing never blocks reading. Writers only wait for each other when they touch the same row. Two requests updating different rows run in parallel.

That row-level locking is the main thing you gain by moving to a server. It is also why a server needs more care: connection limits, pool sizes and credentials become your problem. If you hit them, see the too many connections error.

Can several servers share one SQLite file?#

No, not safely. The SQLite documentation explains that its write locks are known to misbehave on some network filesystems, which can corrupt the database. WAL mode does not work over a network filesystem at all, because all processes must be on the same host.

This is the most common reason to switch. The moment you run a second instance of your app, for load balancing or as a separate worker on another machine, each instance needs to reach the same data over the network. That is what a database server is for.

What size can SQLite reach?#

Larger than most projects will ever need. The theoretical maximum is about 281 TB with the largest page size, and about 17.5 TB at the default 4096-byte page size. A single string or blob is capped at 1,000,000,000 bytes by default.

Size is rarely the reason to leave SQLite. The SQLite team suggests a client/server engine when data may approach terabyte scale, mostly because a single file is hard to distribute, back up and tune at that size.

When should you switch from SQLite to a database server?#

Switch when one or more of these is true:

1. More than one machine needs the data. A second app instance, a separate worker or a load-balanced setup.
2. Writers collide. Logs show database is locked or writes take longer as traffic grows, even with WAL and a timeout.
3. You need roles and permissions. Different services or people should see different tables.
4. You want server features. Extensions, replication, rich index types or specialized data types.
5. The data is growing toward terabytes.

Stay on SQLite when one process owns the data, writes are modest and you value zero setup. That covers many bots, internal tools, prototypes and low-to-medium traffic sites. If you only need fast temporary data, Redis may fit better than either.

How do you move from SQLite to PostgreSQL?#

1. Isolate your queries. Put SQL behind a small data layer or an ORM so only that layer changes.
2. Fix types. SQLite accepts almost any value in any column unless the table is STRICT. PostgreSQL rejects bad data, so check dates, booleans and numbers first.
3. Rewrite the SQL differences. Placeholders (? becomes $1 in many drivers), AUTOINCREMENT becomes GENERATED ... AS IDENTITY, and some functions differ.
4. Create the schema with migrations. See database migrations on deploy.
5. Copy the data, compare row counts, then point the app at the new database.
6. Keep the old file until you have verified the new database and have a backup.

Pick the server by data shape. For relational data, PostgreSQL vs MySQL covers the trade-offs.

How to do this on Square Cloud#

SQLite needs no database hosting: it is a file your app opens itself. An application's disk persists across restarts and deploys, so the file survives. A deploy (a new ZIP or a push) overwrites only the files in the upload, so leave the .db file out of it or it replaces your live data. Each application has its own disk, and apps behind a load balancer do not share files. That is why SQLite stays a single-instance choice.

When you outgrow it, Square Cloud offers hosting for PostgreSQL, MySQL, MongoDB and Redis from the Standard plan. You create the database in the dashboard, and it is provisioned in seconds in the same datacenter as your applications, with about 1 ms of average latency and mandatory TLS. The dashboard hands you the full connection URL and a certificate to download. Copy the URL into your app's environment variables and run the migration yourself.

You take manual snapshots whenever you want, and every plan that includes databases also has an automatic daily snapshot. You can restore a snapshot into the same database, another one of the same engine or a new database. Compare the options on the databases page.