Database
SQLite3 CLI
SQLite3 CLI commands for embedded database management.
36 commands
Windows
MacOS
Linux
#sqlite
#embedded-database
Connection & Dot Commands
Open or create database file
sqlite3 mydb.db
Open in-memory database
sqlite3 :memory:
List attached databases
.databases
List all tables
.tables
Show CREATE statement for table
.schema tablename
Enable column headers in output
.headers on
Set output to column format
.mode column
Exit SQLite3 shell
.quit
Table Operations
CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT UNIQUE); # Create table
ALTER TABLE users ADD COLUMN age INTEGER; # Add column to table
Rename table
ALTER TABLE users RENAME TO customers;
Delete table if it exists
DROP TABLE IF EXISTS users;
Create index on column
CREATE INDEX idx_email ON users(email);
Show indexes for table
.indices users
Queries
Select rows with condition
SELECT * FROM users WHERE age > 25;
INSERT INTO users (name, email) VALUES ('Alice', '[email protected]'); # Insert row
UPDATE users SET age = 30 WHERE name = 'Alice'; # Update rows
Delete rows by condition
DELETE FROM users WHERE id = 1;
SELECT name, COUNT(*) FROM orders GROUP BY name HAVING COUNT(*) > 5; # Group with having
SELECT * FROM users ORDER BY name LIMIT 10 OFFSET 20; # Paginated query
Import/Export
Set mode to CSV for import/export
.mode csv
Import CSV file into table
.import data.csv users
Redirect output to file
.output results.csv
Dump entire database as SQL
.dump
Dump specific table as SQL
.dump users
Backup database to SQL file
sqlite3 mydb.db .dump > backup.sql
Restore database from SQL file
sqlite3 mydb.db < backup.sql
Configuration
Enable write-ahead logging
PRAGMA journal_mode=WAL;
Enable foreign key enforcement
PRAGMA foreign_keys=ON;
Set cache size to 64MB
PRAGMA cache_size=-64000;
Show column details for table
PRAGMA table_info(users);
Show execution time for queries
.timer on
Check database integrity
PRAGMA integrity_check;
Quick Commands
Open or create a SQLite database file
sqlite3 mydb.db
Import CSV file into a table
.import data.csv users
Enable write-ahead logging for better performance
PRAGMA journal_mode=WAL;