// databases

What Is SQLite?

SQLite is a lightweight relational database system that stores an entire database in a file. Unlike many database systems, it does not require a separate database server running in the background.

// definition

SQLite is an embedded relational database

SQLite is a relational database management system designed to be embedded directly inside applications.

A complete SQLite database is commonly stored in a single file on disk.

Applications interact with that file using SQL commands such as SELECT, INSERT, UPDATE and DELETE.

// simple idea

A database can be just one file

A SQLite database might look like this:

customers.db

Inside that file could be several tables:

customers orders products payments

The application can open the database file and query those tables directly.

// serverless

SQLite does not need a separate database server

Systems such as MySQL and PostgreSQL normally run as separate database server processes.

Applications connect to those servers over a local connection or network connection.

SQLite works differently.

Application ↓ SQLite library ↓ database.db

The application accesses the database through the SQLite library rather than contacting a separate database server.

// sql

SQLite uses SQL

SQL stands for Structured Query Language.

SQL is used to create, read, update and delete data stored in relational databases.

SELECT * FROM customers;

This query asks SQLite to return all rows from the customers table.

// tables

SQLite stores data in tables

A customer table might contain:

id	name	email
1	Alice	alice@example.com
2	Ben	ben@example.com
3	Carla	carla@example.com

Each row represents a record, while each column represents a particular type of information.

// create table

Tables can be created with SQL

CREATE TABLE customers ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT );

This creates a table containing three columns:

id name email

// insert

Data can be inserted

INSERT INTO customers ( name, email ) VALUES ( 'Alice', 'alice@example.com' );

This adds a new row to the customers table.

// select

Data can be retrieved

SELECT name, email FROM customers;

A condition can also be added:

SELECT * FROM customers WHERE id = 1;

This retrieves the customer whose ID is 1.

// update

Existing data can be changed

UPDATE customers SET email = 'alice.new@example.com' WHERE id = 1;

This changes the email address associated with the selected row.

// delete

Rows can be deleted

DELETE FROM customers WHERE id = 1;

The matching row is removed from the table.

// crud

These operations are often called CRUD

Create

Add new records to the database.

Read

Retrieve existing information.

Update

Change existing records.

Delete

Remove records from the database.

// relational

SQLite is a relational database

Relational databases can connect information stored across different tables.

For example:

customers id | name 1 | Alice orders id | customer_id | total 1001 | 1 | 49.99 1002 | 1 | 18.50

The customer_id value connects the orders to Alice's customer record.

// joins

SQL can combine related tables

SELECT customers.name, orders.total FROM customers JOIN orders ON customers.id = orders.customer_id;

The database can use the relationship between the tables to combine the relevant information.

// files

SQLite databases can use different file extensions

You may encounter files such as:

data.db database.sqlite app.sqlite3 customers.db

The extension does not fundamentally determine whether the file is a SQLite database. It is mainly a naming convention.

// storage classes

SQLite can store several kinds of values

Storage class	Purpose
NULL	No value
INTEGER	Whole numbers
REAL	Floating-point numbers
TEXT	Text strings
BLOB	Binary data

// embedded

SQLite is often embedded inside applications

Software can include SQLite as part of the application itself.

The user may never even realise that a SQLite database is being used.

Desktop Apps Mobile Apps Browsers Local Tools Development Testing

// portable

SQLite databases are highly portable

Because a database can be stored in a single file, it can be straightforward to copy, back up or move.

project/ │ ├── app.py ├── database.db └── settings.json

The database can sit directly alongside the application files.

// sqlite vs csv

SQLite is very different from CSV

CSV	SQLite
Plain-text file	Database file
Usually one table	Can contain many tables
No SQL engine	Supports SQL queries
Limited relationships	Relational structure
Simple data exchange	Application data storage

// sqlite vs mysql

SQLite and MySQL use different architectures

SQLite	MySQL
Embedded database	Database server
Database commonly stored in one file	Server manages database storage
No separate server required	Requires database server software
Excellent for local and embedded use	Common for networked applications

// sqlite vs server database

SQLite is not designed for every database workload

SQLite is extremely useful when simplicity and local storage are important.

A dedicated database server such as PostgreSQL or MySQL may be more appropriate when an application requires many simultaneous writers, centralised access, extensive server administration or large multi-user workloads.

SQLite is not simply a smaller MySQL. Its architecture is deliberately different. It is designed to provide a database engine directly inside an application.

// transactions

SQLite supports transactions

A transaction allows several database operations to be treated as one logical unit.

BEGIN; UPDATE accounts SET balance = balance - 50 WHERE id = 1; UPDATE accounts SET balance = balance + 50 WHERE id = 2; COMMIT;

Transactions help protect data from being left in an incomplete state if an operation fails.

// reliability

SQLite supports ACID transactions

Atomicity

A transaction is completed as a unit or rolled back.

Consistency

Database rules can help preserve valid states.

Isolation

Transactions are managed to avoid unsafe interference.

Durability

Committed changes are designed to survive failures.

// indexes

Indexes can improve query performance

CREATE INDEX idx_customer_email ON customers(email);

An index can allow the database to find certain values more efficiently instead of examining every row.

// programming

Many programming languages can use SQLite

Python PHP JavaScript C C++ Java C# Swift

// example with python

SQLite can be used from application code

For example, Python includes support for SQLite:

import sqlite3 connection = sqlite3.connect("customers.db") cursor = connection.cursor() cursor.execute( "SELECT * FROM customers" ) rows = cursor.fetchall() connection.close()

The program opens the database file, executes a query and retrieves the results.

// uses

What is SQLite used for?

Mobile Apps Desktop Software Local Data Application Settings Prototypes Testing Small Websites Data Analysis Embedded Systems Offline Applications

// advantages

Why is SQLite useful?

Simple

No separate database server needs to be configured.

Portable

An entire database can commonly be stored in one file.

Lightweight

The database engine can be embedded directly inside an application.

Powerful

It still provides SQL, transactions, indexes, relationships and many database features.

// important distinction

A SQLite database is more than an ordinary data file

A CSV file stores rows of text.

A JSON file stores structured text.

A SQLite file contains a database managed by an actual database engine.

CSV → data file JSON → structured data file SQLite → database stored in a file

That distinction is important.

// summary

A database inside one file.

SQLite is an embedded relational database system that provides SQL, tables, relationships, transactions and indexes without requiring a separate database server. Its simplicity and portability make it useful for mobile apps, desktop software, local tools, prototypes and many other applications.