Getting Started with SQLite

Setting up SQLite is faster than any other database system. There is no server to install or configure — you simply download the command-line shell, and you are ready to create databases and run queries immediately.

Installing the SQLite CLI

macOS

macOS ships with SQLite pre-installed. Open Terminal and run:

sqlite3 --version

To install the latest version via Homebrew:

brew install sqlite

Ubuntu / Debian Linux

sudo apt update
sudo apt install sqlite3
sqlite3 --version

Windows

Download the precompiled binaries from the official SQLite website. Extract the zip and place sqlite3.exe in a directory on your PATH. Open PowerShell or Command Prompt and verify:

sqlite3 --version

Opening and Creating a Database

To create a new database (or open an existing one), pass a filename to sqlite3:

sqlite3 myapp.db

If myapp.db does not exist, SQLite creates it. If it does exist, SQLite opens it. You are now inside the interactive shell.

To open an in-memory database (useful for experiments — it is discarded when you exit):

sqlite3 :memory:

The Interactive Shell

Once inside the shell, you can run SQL statements terminated by a semicolon, or dot-commands (meta-commands that control the shell itself):

-- Show all tables in the current database
.tables

-- Show the schema (CREATE statements) for a table
.schema users

-- Enable column headers in query output
.headers on

-- Set output mode to a readable column format
.mode column

-- Exit the shell
.quit

Running a First Query

CREATE TABLE greetings (id INTEGER PRIMARY KEY, message TEXT);
INSERT INTO greetings (message) VALUES ('Hello, SQLite!');
SELECT * FROM greetings;

The output should show:

id  message
--  -------------
1   Hello, SQLite!

Running SQL from a File

You can save SQL in a .sql file and execute it from the shell or the command line:

# From inside the SQLite shell:
.read setup.sql

# From the system shell (non-interactive):
sqlite3 myapp.db < setup.sql

Useful Shell Settings

These settings improve readability during exploration:

.headers on
.mode column
.nullvalue NULL
.timer on

Place these in a file called .sqliterc in your home directory and the shell will apply them automatically every time you start.

Summary