What is SQLite?
SQLite is the world's most widely deployed database. Unlike PostgreSQL or MySQL, SQLite has no separate server process. Instead, it's a single file that your application reads and writes directly. Think of it like JSON in database form: zero installation, zero configuration, everywhere.
Where is SQLite used? Your phone (iOS, Android). Your browser (Firefox, Chrome). Embedded systems, IoT devices, smart watches, game engines, and millions of applications worldwide. If you've ever used a mobile app, you've used SQLite.
When should you use SQLite?
- Embedded applications: Desktop apps, mobile apps, games that need their own database.
- Testing: Fast, no setup. Perfect for unit tests that need real database behavior.
- Small datasets: Millions of rows are fine; SQLite handles them well on a single machine.
- Read-heavy workloads: Excellent performance for queries on one machine.
When to use PostgreSQL instead:
- Multiple users: PostgreSQL handles concurrent connections from many clients. SQLite has limited concurrency (one write at a time).
- Large datasets: Billions of rows spread across multiple servers. PostgreSQL scales horizontally.
- Complex transactions: Need MVCC (multi-version concurrency control) and advanced locking. PostgreSQL is built for this.
- Complex features: Full-text search, JSON operators, advanced types. PostgreSQL is more feature-rich.
Creating Tables in SQLite
SQLite table creation is simpler than PostgreSQL, but with some quirks. Let's create the QuickCart schema:
Q6: Create the Customers Table
Key differences from PostgreSQL:
AUTOINCREMENT with INTEGER PRIMARY KEY— Instead of SERIAL. This auto-increments the ID.TEXT— SQLite uses TEXT for strings (not VARCHAR with a length limit). SQLite is flexible about type hints.BOOLEAN DEFAULT 0— SQLite stores booleans as 0 (false) or 1 (true).
Q7: Create the Products Table
Highlights:
REAL— SQLite's decimal/float type. For exact money, use REAL (not DECIMAL like PostgreSQL).CHECK (price > 0)— Constraints work the same as PostgreSQL.CURRENT_TIMESTAMP— Defaults to the current timestamp.
Q8: Create the Orders Table
Q9: Create the Order Items Table
Note: SQLite supports FOREIGN KEY but doesn't enforce them by default. To enable: PRAGMA foreign_keys = ON;
Querying SQLite
Standard SQL works in SQLite. Here are practical queries from the QuickCart data:
Q13: Filter — Premium Customers
Q14: JOIN — Delivered Orders with Customer Names
Q15: Aggregation — Product Stats by Category
Q16: CTE (WITH Clause) — Top 5 Customers by Order Count
SQLite Limitations
SQLite is powerful, but it's not PostgreSQL. Here are the key constraints:
Q11: No RIGHT JOIN (only LEFT JOIN, INNER JOIN, CROSS JOIN)
Workaround: Use LEFT JOIN and swap table order. SQLite doesn't support RIGHT JOIN or FULL OUTER JOIN.
Q10: No ALTER COLUMN Type Change
Limitation: You can't do ALTER TABLE products ALTER COLUMN price TYPE DECIMAL; SQLite will reject it.
Workaround: Create a new table with the new schema, insert data from the old table, drop the old table, and rename the new one.
Q17: String Functions Differ Slightly
Key difference: SQLite uses SUBSTR() instead of PostgreSQL's SUBSTRING(). Check your dialect.
Q18: Date Functions Use strftime()
Key function: Use STRFTIME(format, date) instead of PostgreSQL's TO_CHAR() or DATE_FORMAT().
Summary: SQLite has no RIGHT/FULL JOIN, can't change column types with ALTER, and uses different string/date functions. But for embedded apps and testing, these trade-offs are worth it.
Python + SQLite
The magic of SQLite is that you can use it directly from Python — no server to start, no installation needed. Python includes the sqlite3 module in the standard library.
Basic Connection and Query
Q20: Parameterized Queries (Safe from SQL Injection)
CRITICAL: Never do string concatenation like f"WHERE city = '{city}'". Use ? placeholders instead. This protects against SQL injection attacks.
Q21: INSERT with Parameters
Q22: Get Last Inserted Row ID
Q23: fetchone() vs fetchall()
Use fetchone(): When you only need one row (e.g., fetching a user by ID).
Use fetchall(): When you need all rows (e.g., listing all orders).
Best Practice: Always use context managers (with statement) to ensure connections are closed:
Test Your Knowledge
Zero install, zero excuses.
Join the Best AI Program: The AI-Era Data Engineer — 5 months, fully online, with placement assistance. Week 1 is free.
Explore The AI-Era Data Engineer →