SQLite vs Postgres — which one for your first project

Let's start with a fact that breaks intuition: SQLite is the most widely deployed database on the planet. It's in your phone right now — in your messengers, your browser, your photo gallery. Not a "toy version of a real database," but an industrial-grade thing with billions of installations.

So "SQLite or Postgres" is not "toy versus serious." It's a choice of where your data lives: in a file next to your code, or on a separate server. The answer decides how your project deploys, what it costs, and what happens when users show up.

The core difference — a file or a server

Both are real relational databases, both speak SQL. The difference is in the design:

• SQLite is a single file on disk, say app.db. No server: your program opens the file directly, like a document. Install the library — the database already exists.
• Postgres is a separate server program. It runs on its own, and your code talks to it over the network: connection, login, password. The server can live on your machine or in the cloud.

Everything else grows out of this one difference.

The honest comparison

| Criterion | SQLite | Postgres | |---|---|---| | Setup | none: the file creates itself | needs a server: yours or cloud | | Where the data lives | one file next to the code | on the database server | | Several apps at once | poor: one file, writes take turns | great: a server built for a crowd | | Deploying to hosting | a trap (see below) | standard: the database lives apart from the code | | Starting price | zero | zero (cloud free tiers) | | Backup | copy the file | a command or a button in the cloud panel | | Users and permissions | none: whoever has the file has everything | yes: roles, access levels, read-only |

The key row is "several apps at once." In SQLite, effectively one process writes to the file at a time — the rest wait. For a site with a dozen simultaneous users that's already a bottleneck. Postgres was built from day one as a server for many clients at once.

The deploy trap people discover too late

The classic scenario: you build an app on SQLite, everything works, you deploy to Vercel or Netlify — and a day later the database is empty.

The reason: on serverless hosting the filesystem is temporary. Your code runs in a disposable environment: request handled — environment thrown away, along with everything the code wrote to disk. The app.db file with your users' data simply vanishes. That's not a hosting bug — that's how serverless works: the code is disposable, so the data must live outside.

Hence the iron rule: user data lives outside the code. On serverless hosting your database must be a separate service, not a file in the project.

Who should pick what — a straight answer

• A script, a prototype, a local tool, a bot for yourself — SQLite. Zero setup, the database is just a file, backup is a file copy. Dragging a server into this is extra work.
• A site or app with users — Postgres, and specifically cloud Postgres: Supabase, Neon and friends give you a free database in minutes — here's the short guide. Your code deploys anywhere; the data lives separately and doesn't vanish.
• Not sure, "what if it grows" — pick Postgres now. Migrating off SQLite later is real work: the SQL is similar but not identical, and above all you'll have to move live data carefully. An hour of setup today is cheaper than a day of migration next month.

In one line: local and for yourself — SQLite; on the internet and for people — Postgres.

Is SQLite not serious enough for production?

It's serious — in its niche: apps where the database lives next to the program — mobile, desktop, embedded. On the web with a persistent server SQLite works too — but on serverless, and under many simultaneous writes, you need the server kind.

Is Postgres hard?

The hard part moved to the cloud. Cloud services hide the installation and tuning: you sign up, click "create project" — and get a working database, a table dashboard, and a connection string. From there it's the same SQL.