How Do You Set Up a Practice Database Locally?

You can go from an empty machine to running real SQL against your own tables in one sitting: install the SQLite command-line shell, create a database file, load a dozen rows, and query them. No server, no account, no configuration files — a practice database is a file on disk and a program to open it.

What do you install, and where does it come from?

SQLite ships as a single executable called sqlite3 (the command-line shell) — that is the whole sandbox. Grab it from the official download page under precompiled binaries, or let your package manager place it on the PATH, whichever route your system prefers:

# Windows (choose one)
winget install SQLite.SQLite
choco install sqlite

# macOS
brew install sqlite

# Debian or Ubuntu
sudo apt install sqlite3

Confirm the shell answers. The exact version date and build hash on your machine will differ — mine was a 3.39 build installed through Chocolatey — but what matters is that the binary is on the PATH:

> sqlite3 --version
3.39.2 2022-07-21 15:24:47 698edb77537b67c41adc68f9b892db56...

Two companion tools are worth knowing about without installing them today. DB Browser for SQLite is a free GUI where you can see rows in a spreadsheet-like grid — helpful for eyeballing results while you learn. Server engines such as MySQL or PostgreSQL become interesting in Module 8 and beyond; they need a daemon process, and you can always run one later inside a short Docker container. Nothing in the first half of the course needs them.

How do you create your first database and load data into it?

A SQLite database grows out of nothing: the first time a query or an import touches a file name, sqlite3 creates the file. Store everything in one tidy folder, starting with a small seed.sql script holding the table you will reuse through the next lessons. Save this as seed.sql in that folder:

CREATE TABLE shop_items (
  id       INTEGER PRIMARY KEY,
  name     TEXT NOT NULL,
  category TEXT,
  price    REAL,
  stock    INTEGER
);

INSERT INTO shop_items (name, category, price, stock) VALUES
  ('Insulated cable 5 m', 'Wiring',     4.90, 40),
  ('UK plug moulded',     'Wiring',     2.35, 80),
  ('Tape measure 3 m',    'Hand tools', 6.75, 15);

From the same folder, run the script into a fresh database file, then ask the shell what it created:

$ sqlite3 practice.db ".read seed.sql"
$ sqlite3 practice.db ".tables"
products

The first command is silent — it loads data. The second prints the one table the script made. Dot-commands like .tables belong to the shell itself, not to SQL; they are conveniences you will retire the moment you learn the underlying query. That underlying query runs against sqlite_master, the catalog table every SQLite database keeps. Filter it by name to ask about one table in particular:

SELECT name, type
FROM sqlite_master
WHERE name = 'shop_items';

name        type
----------  -----
shop_items  table

This is a genuine SQL statement — drop the WHERE clause and the same query lists every table and index in the file, and you can filter it, join it or run it from a GUI, which makes it the portable equivalent of .tables. MySQL carries the same idea as SHOW TABLES, SQL Server queries sys.tables — every engine keeps such a catalog, spelled differently.

How do you know the rows actually landed?

Count them. A check this short is easy to retype, and you will use it habitually whenever a seed script misbehaves:

SELECT COUNT(*) FROM products;

COUNT(*)
7

Single-column answers come back without decoration. If you want the grid view with headers, the shell prints one with the -header -column flags — try sqlite3 -header -column practice.db "SELECT id, name, price FROM products;" and you will see the three seed rows drawn as a table. From here on, the results shown in these lessons come through the command line in exactly that way, so yours should match if your seed rows match mine.

Why is it a file, and when does that stop being enough?

The database is an ordinary file — practice.db, a few kilobytes. Copy it, email it, commit it, delete it: the data is the file, with no process holding it hostage. Exit the shell and reopen the file later, and your rows are exactly where you left them. Open a file name that does not exist, and watch the shell quietly create an empty one — asking practice2.db for its tables prints nothing at all before you have created anything, which is a fast way to lose ten debugging minutes on a typo:

$ sqlite3 practice2.db ".tables"

That silence is also your tip-off that a file-per-task workflow works: give each exercise its own database name and nothing can cross-contaminate. The model wears out when a database needs to serve many simultaneous users — module 8's design work assumes you will graduate eventually — but for a learner's sandbox the single file is a feature worth keeping on purpose.

Quick reference

• Install sqlite3 once through winget, choco, brew, apt or the official download page; verify with sqlite3 --version.
• Opening a non-existent file creates it silently — prefer running .tables first when something seems missing.
• Seed scripts are plain CREATE TABLE and INSERT statements loaded with .read.
• sqlite_master is the portable answer to what tables exist, and works anywhere SQL works.

On your machine: build the folder exactly as shown, but seed a table of five rows you invent — a playlist, a bookshelf, your own toolkit. After loading it, prove it exists with sqlite_master, count the rows, then open the same database file in DB Browser and find your rows there visually. Finish by creating a deliberately misspelled copy of the database name and observing the empty .tables output.