Nilesh Kumar Singh

Posted on May 23

The Ultimate SQLite Cheatsheet - Commands, Tips & Recovery Guide

#sqlite #itforbeginners #cheatsheet

SQLite is one of the most widely used database engines in the world, powering everything from mobile apps (Android, iOS) to web browsers (Chrome, Firefox) and embedded systems. Its lightweight, serverless architecture makes it a favorite among developers.

Whether you're a beginner learning SQL or an experienced developer looking for a quick reference, this comprehensive SQLite cheatsheet covers everything from basic queries to advanced optimizations—plus what to do if your database gets corrupted.

1. Getting Started with SQLite

What is SQLite?

SQLite is a self-contained, zero-configuration, transactional SQL database engine. Unlike MySQL or PostgreSQL, it doesn’t require a separate server process, making it perfect for:

Mobile apps (Android, iOS)
Local caching in web apps
Embedded systems (IoT devices)
Small to medium-sized desktop applications

Installing SQLite

Most Linux/macOS systems come with SQLite pre-installed. To check:

sqlite3 --version

If not installed, download it from SQLite’s official website.

2. Essential SQLite Commands

Opening & Managing Databases

Start SQLite and open a database:

  sqlite3 my_database.db

Exit SQLite shell:

  .exit

Get help (lists all commands):

  .help

Viewing Database Structure

List all tables:

  .tables

View schema of a table:

  .schema table_name

Show database settings:

  .dbconfig

3. Basic SQL Queries

SELECT – Retrieving Data

Fetch all records from a table:

  SELECT * FROM employees;

Filter with WHERE:

  SELECT name, salary FROM employees WHERE salary > 50000;

Sort with ORDER BY:

  SELECT * FROM employees ORDER BY hire_date DESC;

INSERT – Adding New Data

Insert a single record:

  INSERT INTO employees (name, salary) VALUES ('John Doe', 60000);

Insert multiple records at once:

  INSERT INTO employees (name, salary) VALUES 
    ('Alice Smith', 70000),
    ('Bob Johnson', 55000);

UPDATE & DELETE – Modifying Data

Update existing records:

  UPDATE employees SET salary = 65000 WHERE id = 1;

Delete records:

  DELETE FROM employees WHERE id = 5;

4. Working with Tables

Creating & Modifying Tables

Create a new table:

  CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    salary REAL,
    hire_date DATE
  );

Add a new column:

  ALTER TABLE employees ADD COLUMN department TEXT;

Delete a table:

  DROP TABLE employees;

Indexes for Faster Queries

Create an index:

  CREATE INDEX idx_salary ON employees(salary);

Remove an index:

  DROP INDEX idx_salary;

5. Advanced SQLite Operations

Transactions (ACID Compliance)

Start a transaction:

  BEGIN TRANSACTION;

Commit changes:

  COMMIT;

Rollback if something goes wrong:

  ROLLBACK;

Joins (Combining Tables)

INNER JOIN (only matching rows):

  SELECT e.name, d.department_name 
  FROM employees e 
  INNER JOIN departments d ON e.department_id = d.id;

LEFT JOIN (all rows from left table + matches from right):

  SELECT e.name, d.department_name 
  FROM employees e 
  LEFT JOIN departments d ON e.department_id = d.id;

Aggregate Functions (GROUP BY, HAVING)

Count employees per department:

  SELECT department, COUNT(*) as employee_count 
  FROM employees 
  GROUP BY department;

Filter groups with HAVING:

  SELECT department, AVG(salary) as avg_salary 
  FROM employees 
  GROUP BY department 
  HAVING AVG(salary) > 60000;

6. Importing & Exporting Data

Exporting a Database

Save entire database to a .sql file:

  sqlite3 my_database.db .dump > backup.sql

Export a table to CSV:

  .mode csv
  .output employees.csv
  SELECT * FROM employees;
  .output stdout

Importing Data

Restore from a .sql dump:

  sqlite3 new_database.db < backup.sql

Import CSV into a table:

  .mode csv
  .import employees.csv employees

7. SQLite Optimization Tips

Use transactions for bulk inserts (much faster than individual inserts).
Create indexes on frequently queried columns.
Avoid SELECT *—only fetch columns you need.
Use VACUUM to reduce file size after deletions:

  VACUUM;

8. SQLite Recovery: What to Do When Your Database is Corrupt

Despite its reliability, SQLite databases can get corrupted due to:

Sudden power failures
File system errors
Improper shutdowns

Signs of Corruption

Error: "database disk image is malformed"
Incomplete or missing data
SQLite fails to open the file

How to Recover a Corrupt SQLite Database

Try Built-in Repair Commands

   PRAGMA integrity_check;

If errors are found, attempt:

   PRAGMA quick_check;

Use .dump to Salvage Data

   sqlite3 corrupt.db ".recover" | sqlite3 recovered.db

Use a Professional SQLite Recovery Tool If manual methods fail, specialized tools like SQLite Database Recovery can:
- Recover deleted records
- Fix corrupted tables
- Extract data even from severely damaged .db files

Pro Tip: Always keep backups! Use .dump regularly to export your data.

Final Thoughts

This ultimate SQLite cheatsheet covers everything from basic queries to advanced optimizations and recovery techniques. Whether you're building an app, managing local data, or troubleshooting a corrupted database, these commands will save you time and effort.

Need more help?

Check the official SQLite documentation
Explore SQLite GUI tools like DB Browser for SQLite

Did I miss anything? Add it in the comments!

Goglides Dev 🌱