Database Setup and Connection
Your database is where data lives permanently. Unlike variables in your code that disappear when the program stops, database data persists across restarts. Setting up this connection correctly is essential for a working application.
SQLite: The Simplest Option
For learning and small projects, SQLite is ideal. It stores everything in a single file — no separate database server to install or configure. Most programming languages include SQLite support built-in.
When your code runs, it opens the SQLite file (creating it if needed), runs queries, and closes the connection. The file sits in your project folder, making it easy to inspect, backup, or delete.
import sqlite3
conn = sqlite3.connect('todos.db')
That's it — you now have a database. SQLite handles the complexity internally.
Creating Tables
Before storing data, you need to create tables. This typically happens once, when your application first runs. A common pattern checks if the table exists and creates it if not:
CREATE TABLE IF NOT EXISTS todos (
id INTEGER PRIMARY KEY AUTOINCREMENT,
text TEXT NOT NULL,
completed BOOLEAN DEFAULT FALSE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
AI-generated code usually includes this setup. Verify the table structure matches your data model.
Basic Operations
Database operations use SQL — a language for querying and modifying data:
-- Insert a new todo
INSERT INTO todos (text) VALUES ('Learn databases');
-- Get all todos
SELECT * FROM todos;
-- Update a todo
UPDATE todos SET completed = TRUE WHERE id = 1;
-- Delete a todo
DELETE FROM todos WHERE id = 1;
Your backend code wraps these SQL statements, passing in values from API requests and returning results as JSON.
Connection Handling
Database connections should be opened when needed and closed when done. Leaving connections open wastes resources; opening too many can crash your application. Most frameworks handle this automatically, but verify your AI-generated code manages connections properly.
Verifying Persistence
Test that data actually persists:
- Create a todo via your API
- Stop your server completely
- Start the server again
- Fetch todos — your item should still be there
If data disappears on restart, something's wrong with your database setup. Check that you're connecting to a file (not an in-memory database) and that writes are committed.
Moving Beyond SQLite
SQLite works great for development and small applications. For production systems with multiple users, you'll eventually want PostgreSQL or MySQL — proper database servers designed for concurrent access and larger datasets. The SQL syntax is nearly identical, making migration straightforward.