How to Use SQLite CLI: Complete Beginner’s Guide

SQLite is a lightweight, serverless database engine widely used in countless applications. Its Command Line Interface (CLI) is a powerful tool that allows you to interact with SQLite databases directly and efficiently. This tutorial will guide you through using the SQLite CLI from setup to executing commands, with tips and a final checklist to get you confident in managing your databases.

Prerequisites

A computer with SQLite installed. You can check by typing sqlite3 --version in your terminal or command prompt.
Basic understanding of SQL commands (like SELECT, INSERT, and CREATE).
A terminal or command prompt access on your operating system.

Step 1: Starting the SQLite CLI

Open your terminal or command prompt. To start SQLite CLI, run the command:

sqlite3

This opens the SQLite interactive shell. To open a specific database (or create it if it doesn’t exist), type:

sqlite3 your_database_name.db

Replace your_database_name.db with your desired file name.

Step 2: Basic Commands to Know

.help – Lists all available commands within the CLI.
.databases – Shows databases currently attached.
.tables – Lists all tables in the current database.
.schema [table_name] – Displays the structure of the entire database or a specific table.
.exit or .quit – Exits the CLI.

Step 3: Creating Tables and Inserting Data

Use standard SQL to create a table:

CREATE TABLE users (
   id INTEGER PRIMARY KEY,
   name TEXT NOT NULL,
   email TEXT UNIQUE NOT NULL
);

Insert data into the table:

INSERT INTO users (name, email) VALUES ('Alice', '[email protected]');
INSERT INTO users (name, email) VALUES ('Bob', '[email protected]');

Step 4: Querying Data

Query your data with:

SELECT * FROM users;

You can filter results:

SELECT name, email FROM users WHERE id = 1;

Step 5: Saving Changes

SQLite autocommits by default. If you start a transaction manually with BEGIN TRANSACTION;, you need to save changes with COMMIT; or roll them back with ROLLBACK;.

Troubleshooting Common Issues

Command not found error: Ensure SQLite is installed and added to your system PATH. You can download it from SQLite Official site.
Permission errors: Check file permissions where your database file resides.
Syntax errors: Check your SQL syntax carefully. The CLI will show error messages pointing to issues.
Exiting unexpectedly: Save transactions or use .save filename to save your in-memory data.