Basics of SQLite Database | Rustcode

Basics of SQLite Database

SQLite is a lightweight, embedded SQL database engine, renowned for its simplicity, portability, and reliability. With no setup or server required, SQLite is found everywhere—from browsers (like Chrome), to mobile devices, to small web apps, IoT devices, and even development prototypes. This article will guide you from the basics to best practices, providing essential learning for modern developers.

What is SQLite?

SQLite is a self-contained, serverless, SQL-compliant database engine. Unlike traditional SQL databases, it stores entire databases in a single disk file, has zero configuration, and requires no separate database server process. Its lightweight nature makes it ubiquitous in mobile apps, desktop tools, browsers, IoT hardware, and even in embedded systems.

Official Website: https://sqlite.org/

Fun fact: SQLite is the world's most deployed database engine, used in billions of devices worldwide!

Key Features of SQLite

Zero Configuration: No server setup, installation, or admin needed. It's just a library!
Cross-platform: Works on Windows, macOS, Linux, iOS, Android, and embedded devices.
Fast and Reliable: Compact code, ACID transactions, reliable even in crashes.
File-based: Each database is a single disk file (easy to copy, move, backup, embed, or version; .sqlite or .db file).
Full SQL Support: Handles most standard SQL queries—SELECT, JOIN, subqueries, indexes, triggers, and more.
Small Footprint: Library size only 500KB–1MB compiled.
Integrated with Popular Languages: Native bindings for Python, JavaScript, Java, C, Go, Node.js, Dart, .NET, and others.
Public Domain License: Free for personal, academic, and commercial use.

Getting Started (Installation & Usage)

Download:
- Official download: https://sqlite.org/download.html
- Preinstalled on most Linux/macOS systems (command: sqlite3)
- For Windows, download sqlite-tools zip, extract, and use sqlite3.exe
Open a new or existing DB:
```
sqlite3 mydatabase.db
```
Basic shell commands:
- .tables — Show all tables
- .schema — Show table SQL
- .exit — Exit shell

Use in Programming Languages:
Python example (with sqlite3 module):


import sqlite3
conn = sqlite3.connect('example.db')
cur = conn.cursor()
cur.execute('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)')
cur.execute('INSERT INTO users (name) VALUES (?)', ('Alice',))
conn.commit()
conn.close()

Tip: Most mobile and cross-platform apps use SQLite behind the scenes for local persistent storage!

Basic Concepts: Database, Tables, Rows

Database: The .db (or .sqlite) file holding tables and their data.
Table: Stores rows (records), each row follows a schema (set of columns, data types, constraints).
Row: Single record in a table—a set of column values (fields).


-- Create a table
CREATE TABLE books (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  title TEXT NOT NULL,
  author TEXT,
  year INTEGER
);

-- Insert a record
INSERT INTO books (title, author, year) VALUES ('1984', 'George Orwell', 1949);

-- Query
SELECT * FROM books WHERE year > 1950;

CRUD Operations in SQLite

Create (INSERT):


INSERT INTO users (name, email) VALUES ('Bob', 'bob@example.com');

Read (SELECT):


SELECT * FROM users;
SELECT name FROM users WHERE email LIKE '%@example.com';

Update (UPDATE):


UPDATE users SET name = 'Robert' WHERE email = 'bob@example.com';

Delete (DELETE):


DELETE FROM users WHERE id = 2;

Schema, Relationships & Data Types

Column Data Types: INTEGER, REAL, TEXT, BLOB, NUMERIC.
Primary Key: Uniquely identifies each row—use INTEGER PRIMARY KEY for auto-increment.
Foreign Key: Maintain relationships between tables (enable foreign keys with PRAGMA foreign_keys = ON;).


CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  user_id INTEGER,
  amount REAL,
  FOREIGN KEY(user_id) REFERENCES users(id)
);

Remember: SQLite enforces foreign key constraints only if explicitly enabled in each connection/session.

Indexing in SQLite

Indexes make SELECT queries faster by allowing quick lookup by one or more columns.

Create index for frequently queried columns:


CREATE INDEX idx_email ON users(email);

Unique indexes avoid duplicate values:


CREATE UNIQUE INDEX idx_unique_email ON users(email);

Transactions & Data Integrity

SQLite is ACID-compliant: Atomic, Consistent, Isolated, Durable.

Use transactions to ensure data consistency:


BEGIN TRANSACTION;
UPDATE users SET name = 'Alice Smith' WHERE id = 1;
DELETE FROM orders WHERE id = 7;
COMMIT;

If anything fails, use ROLLBACK;

SQLite Security Golden Rules

File Permissions: Protect database file from unauthorized users (OS-level security).
Input Validation: Always use parameterized queries or ORM to avoid SQL Injection.
Encryption: Use third-party SQLite extensions for on-disk encryption if storing sensitive data (e.g., SQLCipher).
Backup Regularly: Copy or export the .db file securely.
Prefer In-Memory DB (for sensitive ephemeral data): Start with :memory: to avoid disk writes.

Performance and Optimization Tips

Use EXPLAIN QUERY PLAN ... to check how queries run.
Deploy needed indexes, but avoid too many.
Keep transactions short and fast especially in concurrent environments.
Avoid storing huge blobs/files—store files elsewhere and only store path/metadata in SQLite.
Vacuum the DB regularly with VACUUM; to optimize file size and performance.

SQLite vs Other SQL Databases

Aspect	SQLite	MySQL/PostgreSQL/Oracle
Server Process	None (embedded library)	Separate server/daemon process
Setup	Zero config; file-based	Install/configure server
Size/Footprint	Tiny (<1MB code, file grows as needed)	Larger (10MB+ server, logs, configs)
Concurrency	Single process writes, multi-process reads	Concurrent reads/writes supported
Scalability	Per-device, local apps, small-scale	Servers, clusters, large-scale enterprise
Use Case	Mobile, local, prototypes, IoT, websites, learning	Web, cloud, banking, analytics, big data
License	Public domain, free	Varies—open source and commercial

Real World Applications of SQLite

Mobile Apps: iOS (CoreData), Android (default database), React Native, Flutter apps (SQFlite).
Browsers: Chrome, Firefox (storage for cookies, cache, history).
IoT/Embedded: Routers, smart devices, sensors store logs/configuration/data.
Websites: Many small to medium size web apps store content or logs in SQLite (e.g. Django development, Hugo/WordPress portable, Ghost, etc.).
Desktop Software: Data analysis tools, editors, CAD, email clients (Thunderbird), etc.
Prototyping & Education: Instant SQL learning and quick prototyping for students.

Best Rules

Use INTEGER PRIMARY KEY as row IDs for fastest access.
Keep schema simple and normalize as needed, but denormalize for performance where appropriate.
Keep sensitive data encrypted if stored on user devices.
Don’t forget to enable/verify foreign key constraints per connection if needed.
Optimize queries with appropriate indexes and EXPLAIN QUERY PLAN.
Consider file-based or WAL mode for higher concurrency if multi-process writes are needed.

Official Website & Documentation

Official Website: https://sqlite.org/
Documentation: SQLite Documentation
Download: Download Page
SQL Syntax Reference: SQL Language Manuals

Conclusion

SQLite’s balance of power, reliability, tiny footprint, and zero-configuration has made it the standard for single-file, embedded databases in apps world-wide. Whether you’re building a mobile app, prototyping, or just learning databases, SQLite is a modern essential for developers and students alike.

FAQs about SQLite

What makes SQLite different from MySQL or PostgreSQL?

SQLite requires no server process and stores everything in a single file, making it incredibly lightweight and portable—unlike MySQL/PostgreSQL, which need a running server and are designed for large-scale/multi-user environments.

Can SQLite handle large databases and many users?

SQLite works well for databases up to several GB and apps with mostly single-writer or many-readers. For heavy concurrent writes or enterprise scale, a client-server DB like PostgreSQL or MySQL is preferred.

How do I back up a SQLite database?

Just copy the database .db file to a safe location or use sqlite3 mydb.db .backup backup.db—the whole database is in a single file, making backup and restore trivial.

Does SQLite support SQL JOINs and complex queries?

Yes—SQLite supports inner/outer joins, subqueries, aggregation, views, triggers, CTEs, and most standard SQL features used in mainstream SQL databases.

Where is SQLite commonly used today?

SQLite is integrated into Android, iOS, Firefox, Chrome, Windows 10, Skype, WhatsApp, and countless desktop/mobile apps—perfect for offline storage, app data, prototyping, and lightweight production needs.