How to connect to SQLite from the command line

What is SQLite?

A database is an organised place to keep data. SQLite is a small database that lives in one file on your disk. It needs no separate server program. Many apps use it for simple storage.

The command line is a screen where you type commands instead of clicking. On a server you reach it over SSH, a safe way to type commands on a remote computer.

Step 1: Install SQLite

1. Log in to your server over SSH. This guide needs a VPS or dedicated server, or a plan with SSH access.
2. Check if SQLite is installed.

sqlite3 --version

3. If it is missing, install it. On Ubuntu or Debian use this command.

sudo apt install -y sqlite3

On CentOS or RHEL use sudo yum install -y sqlite.

Step 2: Open a database

1. Go to the folder that holds your database file with cd.
2. Open the file.

sqlite3 mydata.db

If the file does not exist, SQLite creates it when you save something. Your prompt changes to sqlite>.

Step 3: Try some commands

Commands that start with a dot are SQLite helpers. Normal SQL commands end with a semicolon.

1. List the tables. A table is like a spreadsheet inside the database.

.tables

2. See how a table is built.

.schema users

3. Create a table.

CREATE TABLE notes (id INTEGER PRIMARY KEY, title TEXT);

4. Add a row.

INSERT INTO notes (title) VALUES ('First note');

5. Read the rows.

SELECT * FROM notes;

6. Make the output easier to read.

.headers on
.mode column

7. Leave SQLite.

.quit

Run one command without opening it

sqlite3 mydata.db "SELECT * FROM notes;"

This runs the query and shows the result right away.

Be careful

Commands like DELETE and DROP TABLE remove data for good. Copy the file first.

cp mydata.db mydata-backup.db

Quick recap

• SQLite stores data in one file.
• Open it with sqlite3 filename.
• Use .tables, .schema and SELECT to look around.
• Leave with .quit and back up before deleting.