Lesson 5: SQLite vs PostgreSQL

The everyday picture

SQLite is a notebook in your pocket: always with you, no setup, perfect for one person writing. PostgreSQL is a library with a librarian: many people can read and write at once, the librarian checks who's allowed in, and nothing gets lost in the crowd.

What it really is

• Both are databases: they store information in tables (customers, orders, payments) and both use the same query language, SQL.
• SQLite is a single file that lives inside your app. There's no separate server to install. It's used in phones, desktop apps, small websites and prototypes. It handles reading very well, but only one change can be written at a time.
• PostgreSQL is a separate database server. Your app connects to it. It handles many people changing data at the same time, has detailed permissions (who can see or change what), and offers strong backup and recovery tools. Services like Supabase or Neon can run PostgreSQL for you.

What it means for your idea

For learning, a prototype or a small personal tool, SQLite is perfect: nothing to set up. Once you have real users logging in, booking, paying or editing at the same time, you want PostgreSQL. Moving later is possible, but it's easier to start with PostgreSQL if you already know the app will have many users.

Pick this when...

• SQLite: learning, prototypes, single-user tools, mobile apps, small sites that are mostly read.
• PostgreSQL: anything with many users writing at once, logins, payments, or business records.

Quick check

A booking site where 200 people might book slots at the same moment. Which database? PostgreSQL. Many people writing at once is exactly what it's built for.

Goes deeper in Modules 51–62.