Database

In the previous tutorial, we learned about HTTP requests and APIs. Now let’s learn about databases — how to store, read, update, and delete data using Python. We will cover two approaches: Python’s built-in sqlite3 module (no installation needed) and SQLAlchemy (the most popular Python ORM). By the end of this tutorial, you will know how to build data-driven applications. What is SQLite? SQLite is a database that stores everything in a single file. It comes built into Python — no installation, no server, no configuration. It is perfect for: ...

In the Ktor tutorial, we loaded data from the internet. But what happens when the user has no internet? The app shows nothing. You need a local database — and in KMP, that means SQLDelight. SQLDelight is the opposite of Room. Room generates SQL from Kotlin. SQLDelight generates Kotlin from SQL. You write real SQL queries, and SQLDelight generates type-safe Kotlin code. It works on Android, iOS, Desktop, and Web — all from the same .sq files. ...

Your app loads data from an API. But what happens when the user has no internet? The screen goes blank. Room database fixes this. It saves data on the device so your app works offline. And it integrates perfectly with Compose — when data changes in the database, the UI updates automatically. What is Room? Room is Google’s database library for Android. It sits on top of SQLite and gives you a clean Kotlin API instead of raw SQL. ...

Bookmark this page. Use Ctrl+F (or Cmd+F on Mac) to find what you need. This cheat sheet covers SQL from basic queries to window functions and performance. Try examples at db-fiddle.com. Last updated: March 2026 SELECT Basics SELECT * FROM users; -- all columns SELECT name, email FROM users; -- specific columns SELECT DISTINCT city FROM users; -- unique values SELECT name AS full_name FROM users; -- column alias SELECT * FROM users LIMIT 10; -- first 10 rows SELECT * FROM users LIMIT 10 OFFSET 20; -- rows 21-30 (pagination) WHERE — Filtering Rows SELECT * FROM users WHERE age > 18; SELECT * FROM users WHERE city = 'Berlin'; SELECT * FROM users WHERE age BETWEEN 18 AND 30; SELECT * FROM users WHERE city IN ('Berlin', 'Munich', 'Hamburg'); SELECT * FROM users WHERE name LIKE 'A%'; -- starts with A SELECT * FROM users WHERE name LIKE '%son'; -- ends with son SELECT * FROM users WHERE name LIKE '%alex%'; -- contains alex SELECT * FROM users WHERE email IS NULL; -- null check SELECT * FROM users WHERE email IS NOT NULL; SELECT * FROM users WHERE age > 18 AND city = 'Berlin'; SELECT * FROM users WHERE age > 65 OR age < 18; SELECT * FROM users WHERE NOT city = 'Berlin'; ORDER BY and LIMIT SELECT * FROM users ORDER BY name; -- ascending (default) SELECT * FROM users ORDER BY age DESC; -- descending SELECT * FROM users ORDER BY city, name; -- multiple columns SELECT * FROM users ORDER BY age DESC LIMIT 5; -- top 5 oldest INSERT, UPDATE, DELETE -- Insert one row INSERT INTO users (name, email, age) VALUES ('Alex', 'alex@example.com', 25); -- Insert multiple rows INSERT INTO users (name, email, age) VALUES ('Sam', 'sam@example.com', 30), ('Jordan', 'jordan@example.com', 22); -- Update UPDATE users SET age = 26 WHERE name = 'Alex'; -- Update multiple columns UPDATE users SET city = 'Munich', age = 27 WHERE id = 1; -- Delete DELETE FROM users WHERE id = 5; -- Delete all rows (use with caution!) DELETE FROM users; -- Faster delete all (resets table) TRUNCATE TABLE users; -- UPSERT — insert or update if exists (PostgreSQL) INSERT INTO users (email, name) VALUES ('alex@example.com', 'Alex') ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name; -- MySQL equivalent INSERT INTO users (email, name) VALUES ('alex@example.com', 'Alex') ON DUPLICATE KEY UPDATE name = VALUES(name); Warning: Always use WHERE with UPDATE and DELETE. Without it, every row is affected. ...