SQLite

SQLite is a complete SQL database that lives in a single file. There's no server to install, no user accounts to create, and no connection settings - your program opens the file and starts running queries.

Who gets this pick

You'll see SQLite when you expect just a handful or under a thousand users, you're hosting on your own server or aren't sure yet, and either your data is structured (or you're not sure), or you're new to databases - even with flexible data. It's never recommended for managed database hosting or for thousands of users and up.

Why SQLite fits

For learning, it removes every obstacle between you and SQL. Python includes it in the standard library (import sqlite3), and a free tool like DB Browser for SQLite lets you see your tables change as your code runs. Everything you learn - tables, keys, joins, indexes - transfers directly to PostgreSQL and MySQL.

For small projects, it's not a toy. SQLite is one of the most widely deployed pieces of software in existence - it's inside phones, browsers and countless desktop apps - and it's fast. For a site with modest traffic it will comfortably outperform a database server on the same machine, because there's no network hop. Backups can be as simple as copying a file (done safely - see below).

A first project

Our Vue + Flask + SQLite guide builds a to-do app whose entire database is one file next to your code. Then practise on it directly: open the file in DB Browser for SQLite, add a due_date column, and write queries for "overdue" and "due this week".

Settings worth turning on

PRAGMA foreign_keys = ON;     -- off by default, per connection!
PRAGMA journal_mode = WAL;    -- readers no longer block the writer

Foreign key enforcement is off by default in SQLite and must be enabled on every connection, so put it in the function that opens your database. WAL mode is persistent once set and makes concurrent reads and writes much smoother for web apps. If you're on SQLite 3.37 or newer, consider STRICT tables, which reject values of the wrong type instead of quietly storing them.

Deployment caveats

• Temporary filesystems. Many platform services wipe local files on every deploy or restart, taking your database with them. Use a host with persistent storage - a VPS, PythonAnywhere, or a platform plan with an attached disk.
• One server only. SQLite lives on one machine's disk, so it doesn't suit apps spread across several servers.
• Back up properly. Copying the file while it's being written can produce a corrupt copy. Use the .backup command in the sqlite3 shell or your language's backup API.

Mistakes to avoid

• Assuming foreign keys are enforced. They aren't until you turn them on.
• Putting the database file in a web-accessible folder, where anyone could download it. Keep it outside the public directory.
• Relative file paths that depend on which folder you ran the app from - build the path from your script's location.
• Long-running write transactions, which block other writers. Keep writes short.

When to move on

Move to PostgreSQL when you need multiple app servers, many simultaneous writers, or a managed database - our PostgreSQL pick covers that step. Your SQL comes with you.

Keep going