Skip to content
Tutorials Center
My learning

Topics

Lesson 2 of 33 ยท Meet SQLite

Running SQLite: Browser and the sqlite3 Shell

Learn the two ways to run SQLite: the live in-browser editor on this site and the sqlite3 command-line shell, with dot-commands like .tables, .schema and .mode.

You can learn SQLite in two places. The first is the live editor you are already using on this site, which needs no installation. The second is the sqlite3 command-line shell, a small program that lets you open database files on your own computer. This lesson explains how both work, so you know what to expect from each.

The SQL you write is identical in both. What differs is the surroundings: where the database lives, which commands exist beside SQL, and how results are displayed.

The live editor in this tutorial

Every example marked Try it runs in your browser. The site loads sql.js, which is the real SQLite engine compiled to WebAssembly. When you press Run:

  1. A fresh in-memory database is created.
  2. Your whole editor text is executed, statement by statement, from top to bottom.
  3. Each statement that returns columns shows its result as a table.
  4. The database is thrown away.

What happens when you press Run

New database Every run starts empty.
Run all statements In order, top to bottom.
Show result tables One table per statement that returns rows.
Discard Nothing is saved between runs.

Your example must create its own tables, or choose a sample dataset.

1 / 2

Because nothing is saved, each example must be self-contained: it creates its tables and inserts its data every time. That is also a nice property when you experiment. You cannot damage anything, and pressing Run again resets the world.

SQL Several statements in one run

Press Run (or Ctrl + Enter) to see the result here.

You get two result tables: the three rows, then the count. The CREATE and INSERT statements return no columns, so they show a short "OK" style message instead of a table.

Sample datasets

To practice queries without building tables first, some examples start with a ready-made database. They declare it with db="bookstore", db="shop", db="company" or db="school". The data is loaded fresh before each run, so you can run UPDATE or DELETE freely.

SQL Using the bookstore dataset ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

This lists the tables in the dataset. The special table sqlite_master is SQLite's own catalog of everything in the database. It is explained in Altering Schema Safely, and it is the reason you can inspect any database with plain SQL.

What the browser editor cannot do

sql.js is SQLite without a file system. Keep these limits in mind:

  • No persistent files. Anything involving ATTACH on disk, WAL files or VACUUM INTO is shown as read-only code in this tutorial.
  • Extensions that were not compiled in are missing. For example, the full-text search module FTS5 is not available here.
  • sql.js includes a few extra helper functions (such as median and stdev) that stock SQLite lacks. Lessons point these out when they matter, and teach portable SQL first.
  • There are no dot-commands. Lines such as .tables belong to the command-line shell, not to SQL.

The sqlite3 command-line shell

The sqlite3 program is the official interactive shell. It is a single executable, and it is the best tool for opening real database files, importing data and inspecting a schema.

Installing it

  • macOS ships sqlite3 already. Check with sqlite3 --version.
  • Linux: install the package, for example sudo apt install sqlite3 on Debian and Ubuntu.
  • Windows: download the "sqlite-tools" zip from the SQLite website, unzip it and put sqlite3.exe in a folder on your PATH. Or install it with a package manager such as winget.

Many languages also bundle the engine (Python's sqlite3 module, for example), but the shell is a separate and very useful tool.

Opening and creating databases

Terminal
sqlite3                 # starts with a temporary in-memory database
sqlite3 shop.db         # opens shop.db, creating the file on first write
sqlite3 shop.db "SELECT count(*) FROM products;"   # run one statement and exit

If the file does not exist, SQLite creates it as soon as you first save something into it. Inside the shell you will see a prompt:

TEXT
SQLite version 3.46.0 2024-05-23 13:25:27
Enter ".help" for usage hints.
sqlite> CREATE TABLE notes (id INTEGER PRIMARY KEY, body TEXT);
sqlite> INSERT INTO notes (body) VALUES ('first note');
sqlite> SELECT * FROM notes;
1|first note
sqlite> .quit

Two details to remember. SQL statements must end with a semicolon, otherwise the shell waits for more input (the prompt changes to ...>). Lines starting with a dot are dot-commands: they are handled by the shell itself, they do not need a semicolon and they only exist in this program.

The dot-commands you will use most

Command What it does
.help Lists all dot-commands
.tables Lists tables (and views)
.schema Prints the CREATE statements; .schema notes shows one table
.headers on Shows column names above results
.mode column Aligns output in columns (other modes: box, table, csv, json, line, markdown)
.open file.db Opens another database file
.read script.sql Runs the SQL in a script file
.import file.csv table Loads a CSV file into a table
.dump Prints SQL that recreates the whole database
.timer on Shows how long each statement took
.quit Leaves the shell (also Ctrl-D)

A typical session after turning on friendlier output:

TEXT
sqlite> .headers on
sqlite> .mode box
sqlite> SELECT * FROM notes;
โ”Œโ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ id โ”‚    body    โ”‚
โ”œโ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ 1  โ”‚ first note โ”‚
โ””โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜
sqlite> .tables
notes
sqlite> .schema notes
CREATE TABLE notes (id INTEGER PRIMARY KEY, body TEXT);

The exact box drawing depends on your version of the shell. Older versions default to a plain 1|first note format, so setting .mode explicitly makes the output predictable.

Put your preferred settings in a file called .sqliterc in your home folder. The shell runs it at startup, so .headers on and .mode box are always active.

Scripts and the command line

The shell is also good for automation. These forms are common in shell scripts and deployment tools:

Terminal
sqlite3 shop.db < schema.sql                 # run a whole script
sqlite3 -header -csv shop.db "SELECT * FROM products;" > products.csv
sqlite3 shop.db .dump > backup.sql           # text backup
sqlite3 :memory: ".read demo.sql"            # test a script on a throwaway database

:memory: is the special file name for an in-memory database. It is the same kind of database as in the live editor.

Reproducing a shell session in the browser

The dot-commands have SQL equivalents for looking at structure. Since .tables and .schema are not available in the editor, you can ask sqlite_master instead:

SQL Inspect the schema with SQL

Press Run (or Ctrl + Enter) to see the result here.

The first result lists long_notes (a view) and notes (a table). The second prints the exact CREATE TABLE text you wrote. This is what .schema does under the hood.

SQLite also has table-valued helper functions that return a table's columns. They behave like tables you can SELECT from:

SQL Columns of a table

Press Run (or Ctrl + Enter) to see the result here.

You get one row per column with its declared type, whether it is NOT NULL, its default value and whether it is part of the primary key. This is the same data that PRAGMA table_info(contacts); gives in the shell.

Which one should you use?

Use both. The browser editor is perfect for learning and trying ideas because it cannot break anything. The shell is the tool for real work on your own files: importing CSVs, running scripts, checking a production database, and making backups. Graphical tools exist as well (DB Browser for SQLite, DBeaver and others), and they all use the same engine.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What happens to the database when you press Run in the live editor?

    Each run starts with an empty in-memory database, so every example must create its own tables or use a sample dataset.
  2. 2. Which of these is a dot-command?

    Lines starting with a dot are handled by the sqlite3 shell itself. The others are SQL statements.
  3. 3. What does sqlite3 app.db do when app.db does not exist yet?

    SQLite creates the database file on demand. Opening a non-existent name is the usual way to start a new database.
  4. 4. Which query shows the same information as the .schema dot-command?

    sqlite_master stores the original CREATE statements. SHOW and DESCRIBE belong to other databases and do not exist in SQLite.

Summary

  • The live editor runs real SQLite (sql.js) in your browser, with a fresh in-memory database for every run and optional sample datasets.
  • It has no files, no dot-commands and no extensions beyond what is compiled in.
  • sqlite3 is the official shell. Open or create a database with sqlite3 file.db, and use :memory: for a temporary one.
  • Dot-commands (.tables, .schema, .headers on, .mode, .import, .dump, .quit) are shell features and need no semicolon. SQL statements do.
  • sqlite_master and pragma_table_info() let you inspect a schema with plain SQL anywhere.

Next: Your First Database creates a table, adds rows and reads them back.