SQLite
Lightweight File Based SQL Database
For full documentation visit sqlite.org.
Over 10 Years | Multiple Projects | Preferred
SQLite has been a favorite of mine for a local file based database.
SQLite is a lightweight, serverless, and self-contained relational database engine that stores an entire database as a single ordinary file on disk. Unlike traditional database systems like MySQL or PostgreSQL, SQLite does not require a separate server process to run, meaning it reads and writes directly to ordinary disk files.
Key Characteristics
-
Serverless: It runs inside the same process as your application, eliminating network lag.
-
Zero-Configuration: There is no installation, setup, or administrative overhead needed.
-
Single-File: The entire database schema, definitions, and data are packed into one cross-platform file.
-
ACID Compliant: It ensures secure and reliable data transactions even during power losses.
-
Highly Portable: It is the default embedded storage engine for Android, iOS, web browsers, and countless desktop applications.
Step-by-Step Example using Python
Python comes pre-packaged with a built-in module called sqlite3, making it an ideal language to demonstrate SQLite functionality.
- 1. Connect and Create a Table
This step establishes a connection file named company.db and generates a table structure.
import sqlite3
# Connect to database (creates company.db if it doesn't exist)
connection = sqlite3.connect("company.db")
cursor = connection.cursor()
# Create a table for employees
cursor.execute("""
CREATE TABLE IF NOT EXISTS employees (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
role TEXT NOT NULL,
salary REAL
)
""")
connection.commit()
- 2. Insert Data
We now insert sample record sets into the freshly created employees table.
# Insert a single row
cursor.execute("INSERT INTO employees (name, role, salary) VALUES ('Alice Smith', 'Developer', 85000.00)")
# Insert multiple rows simultaneously
sample_data = [
('Bob Jones', 'Designer', 70000.00),
('Charlie Brown', 'Manager', 95000.00)
]
cursor.executemany("INSERT INTO employees (name, role, salary) VALUES (?, ?, ?)", sample_data)
# Commit changes to save them to the disk file
connection.commit()
- 3. Query the Database
You can easily fetch data back from the database file using standard SELECT syntax.
# Fetch all records
cursor.execute("SELECT * FROM employees")
rows = cursor.fetchall()
for row in rows:
print(f"ID: {row[0]} | Name: {row[1]} | Role: {row[2]} | Salary: ${row[3]:,.2f}")
# Close the database connection when done
connection.close()
- Expected Console Output:
ID: 1 | Name: Alice Smith | Role: Developer | Salary: $85,000.00
ID: 2 | Name: Bob Jones | Role: Designer | Salary: $70,000.00
ID: 3 | Name: Charlie Brown | Role: Manager | Salary: $95,000.00
Common Use Cases
-
Embedded Apps: Storing localized data on mobile apps (Android/iOS) and desktop software.
-
Web Browsers: Managing history, cookies, and local caches (Chrome, Firefox, Safari).
-
IoT Devices: Resource-constrained environments like smart-home appliances and drones.
-
Prototypes: Quick application testing before porting code to enterprise systems like PostgreSQL.
When to Avoid SQLite
-
High-Volume Write Environments: SQLite locks the whole file during database writes, meaning it only handles one write application sequentially.
-
Massive Datasets: While it supports up to 281 Terabytes theoretically, performance degrades heavily over multi-million-row analytics without meticulous indexing.
Modern Databases
Social Media
---
title: SQLite
text: Lightweight File Based SQL Database
image: /assets/svg/sqlite-svgrepo-com.svg
link:
target:
---