Your First Database: SQLite with Claude Code
Design a schema, create tables, insert data, and run queries — all through plain English conversation with Claude Code. No SQL experience required.
What is a database and why you need one
Every application that remembers anything uses a database. When you sign up for a website, your name and email go into a database. When you post a comment, it is stored in a database. When you check your order history, the app reads from a database. A database is simply an organised collection of data that software can read, write, update, and delete efficiently. SQLite is the simplest database to start with because it stores everything in a single file — no server to install, no configuration to manage, no passwords to set up. It is embedded directly into your application. Despite its simplicity, SQLite powers more deployed applications than any other database engine. It runs inside every iPhone, every Android phone, every web browser, and every Mac and Windows computer. When Claude Code creates a SQLite database for you, it generates a single file (typically ending in .db or .sqlite) that contains all your tables, data, and indexes. You can copy this file, back it up, or share it just like any other file. To get started, create a new project folder: mkdir my-first-database && cd my-first-database. Then start Claude Code: claude. Tell it: Initialise a new Node.js project and install the better-sqlite3 package. This gives you the foundation for everything that follows.
Designing your first schema
A schema is the blueprint for your database — it defines what tables exist, what columns each table has, and what type of data each column holds. Think of it like a spreadsheet template: you define the column headers before entering data. Ask Claude Code: Create a SQLite database for a personal bookshelf app. I want to track books with a title, author, genre, page count, rating from 1 to 5, and whether I have finished reading it. Also create a table for reading sessions that records when I started and stopped reading and which book it was for. Claude Code will create a file like init-db.js that defines two tables: books and reading_sessions. The books table will have columns like id (auto-incrementing integer), title (text, required), author (text, required), genre (text), pages (integer), rating (integer between 1 and 5), and finished (boolean, defaults to false). The reading_sessions table will reference books through a foreign key — this is how databases link related data. Run the script: node init-db.js. You should see output confirming the tables were created. If you get an error about better-sqlite3 not being found, run npm install better-sqlite3 first. Verify the database exists: ls -la bookshelf.db. You now have a structured, empty database waiting for data. The schema enforces rules — you cannot accidentally store text in the page count column or forget to include a title.
CRUD operations: Create, Read, Update, Delete
CRUD is the foundation of all database work. Every application performs these four operations. Ask Claude Code: Create a file called crud.js with functions to add a book, list all books, update a book rating, and delete a book. Each function should take appropriate parameters and return the result. Include example usage at the bottom that adds three books, lists them, updates one rating, and deletes one. Run it: node crud.js. You should see output showing three books added, the full list, a rating update confirmation, and a deletion confirmation. The key SQL operations Claude Code generates are INSERT INTO books (title, author, genre, pages) VALUES (?, ?, ?, ?) for creating, SELECT * FROM books for reading, UPDATE books SET rating = ? WHERE id = ? for updating, and DELETE FROM books WHERE id = ? for deleting. The question marks are parameterised queries — they prevent SQL injection attacks where malicious input could damage your database. Ask Claude Code: Add a function to record a reading session for a book with start and end timestamps. Add another function to get total reading time per book. The second function will use a SQL JOIN to combine data from both tables and SUM to aggregate reading time. This is where databases become powerful — combining and summarising data across tables with a single query. If any function throws an error, read the error message carefully. Common issues include: UNIQUE constraint failed (duplicate data), FOREIGN KEY constraint failed (referencing a book that does not exist), and NOT NULL constraint failed (missing required fields).
Querying data with filters and sorting
Raw data is rarely useful on its own. You need to filter, sort, and aggregate it. Ask Claude Code: Create a file called queries.js with the following query functions. Find all books by a specific author. Find all unfinished books sorted by page count ascending. Find the average rating across all finished books. Find the top 3 highest-rated books. Count books per genre. Find books I have spent the most time reading using the reading_sessions table. Run it: node queries.js. Each function demonstrates a different SQL concept. Filtering uses WHERE clauses: SELECT * FROM books WHERE author = ?. Sorting uses ORDER BY: SELECT * FROM books WHERE finished = 0 ORDER BY pages ASC. Aggregation uses functions like AVG, COUNT, and SUM: SELECT AVG(rating) FROM books WHERE finished = 1. Limiting results uses LIMIT: SELECT * FROM books ORDER BY rating DESC LIMIT 3. Grouping uses GROUP BY: SELECT genre, COUNT(*) as count FROM books GROUP BY genre. The reading time query combines JOIN with GROUP BY and ORDER BY — a powerful pattern. Ask Claude Code to add pagination: Modify the list all books function to accept a page number and page size, defaulting to page 1 and 10 books per page. Return the books for that page plus the total count. This uses LIMIT and OFFSET: SELECT * FROM books LIMIT ? OFFSET ?. Pagination is essential for any application displaying database records — you never want to load thousands of rows at once. Test edge cases: what happens when you request a page that does not exist? What about filtering with no matches?
Building a command-line interface
A database is more useful with an interface. Ask Claude Code: Create an interactive command-line interface for the bookshelf database. When I run node cli.js it should show a menu with options to add a book, list all books, search books, update a rating, record a reading session, view reading stats, and quit. Use the readline module built into Node.js so we do not need extra dependencies. After each action, show the result and return to the menu. Run it: node cli.js. You should see a numbered menu. Type 1 to add a book and follow the prompts for title, author, genre, and page count. Type 2 to see all books in a formatted table. Type 3 to search by author or title keyword. The CLI makes your database tangible — you can interact with it naturally instead of writing code for every operation. Ask Claude Code to enhance the CLI: Add colour to the output using ANSI escape codes. Show book ratings as stars. Format the reading stats as a table with columns aligned. Add input validation so rating only accepts numbers between 1 and 5. These enhancements turn a basic script into something that feels like a real tool. If the CLI crashes on invalid input, ask Claude Code: Add error handling to the CLI so that invalid input shows a helpful message instead of crashing. Wrap the menu loop in a try-catch and validate all user input before passing it to database functions. This is a pattern you will use in every application — never trust user input, always validate before processing.
Data integrity and error handling
Databases need safeguards against bad data and failed operations. Ask Claude Code: Add data validation and error handling to the bookshelf database. Add a constraint that rating must be between 1 and 5. Add a constraint that title and author cannot be empty strings. Wrap multi-step operations in transactions so if one step fails, all steps roll back. Add a function that imports books from a JSON file and uses a transaction to ensure either all books are imported or none are. Create a file called test-integrity.js that tests all these scenarios. Run it: node test-integrity.js. You should see tests passing for valid data and descriptive errors for invalid data. Transactions are the key concept here. When you transfer money between bank accounts, you debit one account and credit another. If the credit fails after the debit succeeds, someone loses money. Transactions make the two operations atomic — either both succeed or both roll back. In SQLite, transactions look like: db.transaction(() => { debit(accountA); credit(accountB); }). Ask Claude Code: Add a function to export the entire database to a JSON file for backup, and another function to restore from a backup file. The export should include all tables and their data. The restore should clear existing data and import from the JSON within a transaction. Test by exporting, deleting a few books, and restoring. Common errors you will encounter: SQLITE_BUSY means another process has the database locked — SQLite only allows one writer at a time. SQLITE_CORRUPT means the database file is damaged — restore from backup. SQLITE_CONSTRAINT means you violated a rule like a unique index or foreign key.
Migrations and schema evolution
Real databases change over time. You add new columns, new tables, and new indexes as your application grows. Ask Claude Code: Create a migration system for the bookshelf database. Create a migrations folder. Each migration is a numbered SQL file like 001-create-books.sql, 002-add-reading-sessions.sql, 003-add-book-cover-url.sql. Build a migrate.js script that tracks which migrations have run in a migrations table in the database and only runs new ones. Create the first three migrations including one that adds a cover_url column to the books table. Run it: node migrate.js. You should see output indicating which migrations were applied. Run it again and it should say no new migrations. This is how production applications manage database changes — each change is a numbered, version-controlled migration that runs exactly once. Ask Claude Code: Create a migration that adds a tags table and a book_tags junction table for a many-to-many relationship between books and tags. A book can have multiple tags and a tag can apply to multiple books. Add functions to tag a book, remove a tag, and find all books with a specific tag. Run the migration and test it. The many-to-many pattern is one of the most common database designs. A junction table (book_tags) has two foreign keys — one to books and one to tags. To find all tags for a book, you JOIN book_tags with tags. To find all books with a tag, you JOIN book_tags with books. If a migration fails partway through, the transaction should roll it back so your database is not left in a broken state. Always test migrations on a copy of your database before running them on production data.
From SQLite to production
SQLite is perfect for learning, prototyping, and applications with modest concurrency needs. But knowing when to upgrade is important. SQLite handles one writer at a time. If your application has many simultaneous users writing data, you will hit SQLITE_BUSY errors. This is when you consider PostgreSQL (the most popular production database) or MySQL. The good news: everything you learned here transfers directly. SQL syntax is 95 percent identical across databases. CRUD operations, JOINs, transactions, migrations — all the same concepts, just a different database engine. Ask Claude Code: Create a summary of the differences between SQLite and PostgreSQL. Show me what would change in our bookshelf application if we migrated to PostgreSQL. Claude Code will explain the key differences: PostgreSQL runs as a separate server process, uses a connection string instead of a file path, supports concurrent writers, has more data types (like native JSON and arrays), and requires a running server. The actual SQL changes are minimal — mostly data type adjustments. For your next steps, try extending the bookshelf database. Ask Claude Code: Add a full-text search feature so I can search book titles and authors with partial matches. Add an index on the genre column to speed up genre filtering. Create a stats dashboard that shows books read per month, average rating trend over time, and total pages read. Each of these exercises reinforces a core database skill. Full-text search teaches you about FTS5 (SQLite's full-text search engine). Indexes teach you about query performance. The stats dashboard teaches you about date functions and complex aggregations. You now have the fundamentals to build any data-driven application.
Data and AI Fundamentals
This guide is hands-on and practical. The full curriculum covers the conceptual foundations in depth with structured lessons and quizzes.
Go to lesson