Database Practice: SQLite and PostgreSQL

Database Design

Database is a system to manage data, which is built on file system offered by OS. Broadly speaking, there are two cases in which database is utilized:

1. OLTP, OnLine Transaction Process, for supporting daily business
2. OLAP, OnLine Analysis Process, for supporting data analysis in Data Warehouse

Database design is actually the design of data tables (entities) and their relationships. The design purposes are:

1. store information without unnecessary redundancy
2. retrieve information easily and fast

Normal Form (NF)

NFs are used to eliminate information redundancy. The higher NF, the less redundancy.

• 1NF: No Multi-value Cell. Each cell only contains one value. No multi-value cells. Null value is allowed. Primary key might has more than one columns (composite key).
• 2NF (Whole Key): No Partial Dependency. If primary key only contains one column, it’s 2NF satisfied. All non-key attributes must be fully dependent on the entire primary key.
• 3NF (Nothing but the Key): No Transitive Dependency. Every non-key attribute in a table must directly depend on the key, the whole key, and nothing but the key. For practice, 3NF is good enough! Higher NFs are more academic!
• BCNF: Boyce-Codd NF. No Reverse Dependency.
• 4NF: No Multi-value Dependency.

Example of Multi-value Dependency:

A --> BC
A ->> B  # multi-value dependency
B and C are indenpendent with each other

Decompose:
A --> C
AB --> ∅  # only A and B in table

Due to speed requirement, the design for OLAP system migth be denormalized.

Primary Key (PK)

• Every table needs a PK.
• Non-null and Unique, for all columns in PK
• Immutability (practically, not just in theory): a PK value should never need to change once assigned. If it changes, every foreign key referencing it must cascade.
• Stability (optional): new keys are strictly greater than previous keys. In most engines, the primary key determines physical row order or is the most heavily used index, so its shape affects performance system-wide. That is why PK design matters so much more than picking any unique column as PK.

Natural Key vs. Surrogate Key

Natural Key: A column (or set of columns) that already exists in the real-world data and is inherently unique — e.g., email, SSN, ISBN, (country_code, license_plate).

• Pros:
  • No extra column needed; the key means something.
  • Prevents accidental duplicate entities (two rows can’t exist for the same email).
• Cons:
  • Real-world “unique” things change more often than you’d think (emails change, company names change, government IDs get reissued in some countries).
  • Often wide (a string, or a composite of several columns) — bigger, slower indexes, and every foreign key referencing it repeats that width.
  • Business rules change: “surely two people can’t have the same SSN” turns out to have edge cases (shared family SSNs in some legacy systems, contractors without SSNs, etc.).

Surrogate Key: A system-generated identifier with no business meaning — typically an auto-incrementing integer or a UUID.

• Pros:
  • Never needs to change, regardless of what happens to the business data.
  • Small (if integer) and fast to index and join on.
  • Decouples identity from data — you can fix a typo’d email without touching foreign key relationships everywhere.
• Cons:
  • Meaningless on its own — you always need a separate unique constraint on the natural key anyway (e.g., unique index on email) to prevent duplicate entities.
  • One more column, one more index.

Better Practice

• Use a surrogate key as the PK, but also put a unique constraint on the natural keys (UK, unique key). You get stability for joins and foreign keys, and you still get duplicate-prevention on the real-world identities.
• It might worth considering how to design surrogate key: incremental int or UUID, and depends on your external stability requirement.

Entity Relationship Diagram (ERD)

ERD is the broad category — any diagram showing entities (tables) and their relationships. It’s a concept, not a specific notation.

Crow’s Foot Notation is the most common specific notation style used to draw ERDs — it’s how you draw the relationship lines and cardinality (one-to-many, many-to-many, optional/mandatory) between entities. So the real choice isn’t “ERD or Crow’s Foot” — it’s “which notation do I use to draw my ERD,” and Crow’s Foot is by far the most practical, widely-used answer for real database schema work (as opposed to, say, Chen Notation, which is more academic/theoretical and rarely used in practice for actual schema documentation).

Crow's Foot Notation in Mermaid:

* Entities are tables
* --: represent identifying relationship
* ..: represent non-identifying relationship
* o: zero
* |: one
* { or }: many
* o,|,{,} could be bind with each other accordingly

An example, column types are in SQLite flexible typing style:

# Mermaid ERD
# type name PK|FK|UK "comments"
erDiagram
    PRODUCT {
        IPK id PK
        TEXT barcode UK "unique"
        TEXT name
        TEXT price
        TEXT cost
        TEXT create_time
    }
    PURCHASE_TIME {
        IPK id PK
        TEXT datetime
    }
    SALE_ITEM {
        IPK id PK
        INT purchase FK
        INT product FK
        TEXT price
        TEXT cost
        INT quantity
    }

    SALE_ITEM }o--|| PRODUCT : contain
    SALE_ITEM }|--|| PURCHASE_TIME: happen

• IPK equals INTEGER PRIMARY KEY, which defines an alias of ROWID.
• Price and cost are in TEXT type to support decimal computation!
• Price and cost are repeated in SALE_ITEM table represent the values at that purchase moment.
• Each sale item contains one products. Each product could be included in zero or many sale items.
• Each purchase time contains one or many sale items. Each sale item happens at one purchase time.

Design for OLTP and OLAP

OLTP (supports fast, frequent, small read/write operations) cares about correctness and speed of individual transactions. OLAP (support complex queries over large volumes of historical data) cares about speed of aggregation/join across huge datasets.

OLTP needs 3NF design. The goal is to avoid data duplication and anomalies. Every piece of data lives in exactly one place. OLAP sometimes needs denormalized design for speed. The goal is to minimize joins and make aggregation fast, even if it means repeating data.

In real systems, OLAP schemas are usually derived from OLTP schemas via ETL (Extract Transform Load) pipelines: raw normalized transactional data gets transformed, aggregated, and reshaped into a star/snowflake schema for reporting.

OLTP tables are named after what they are (a customer, an order). OLAP tables are named after what they do in a query (a fact gets aggregated, a dimension is what you group or filter by). It’s really a difference in modeling philosophy driven by the difference in workload.

Star, Snowflake and Constellation Schema

• Star schema: one fact table in the center, surrounded by dimension tables directly connected to it.
• Snowflake schema: like a star schema, but dimension tables are further normalized into sub-dimensions (so it “snowflakes” outward).
• Constellation (galaxy) schema: takes it a step further — multiple fact tables coexist and share some of the same dimension tables. Visually it looks like a galaxy of stars, hence the name.

Transaction

A database transaction symbolizes a unit of works, sometimes made up of multiple operations, performed within a database management system against a database, that is treated in a coherent and reliable way independent of other transactions. This is for keeping Data Integrity! A database transaction, by definition, must be:

• Atomic (no half change, all or nothing)
• Consistent (the change can only happen if the new state is valid, any attempt to commit an invalid change will fail, leaving the system at its previous valid state, from one valid state to another)
• Isolated (nobody sees any part of the transaction until it’s commited)
• Durable (it must get written to persistent storage, no need to flush)

Database practitioners often refer to these properties of database transactions using the acronym ACID.

Connection and Cursor

Before issuing SQL statements to database system, we need to connect it first. That’s database connection. Connection holds transactions and lock state.

A database cursor is a mechanism that enables traversal over the records from a database. It’s similar to the programming language concept of iterator. Cursors are used by programmers to process individual rows returned by database queries. Cursors enable manipulation of whole result sets at once. In this scenario, a cursor enables the sequential processing of rows in a result set. A cursor can be viewed as a pointer to one row in a set of rows. The cursor can only refer to one row at a time, but can move to other rows of the result set as needed.

We can have multi-cursor for one connection. But they share transactions and lock state. They would never be real paralleled. We can use multi-cursor in safe nested queries in one thread, but not recommend for multi-thread cases. Each thread should have its own connection. That’s the standard pattern for both SQLite and PostgreSQL. Concurrent transactions are realized by multi-connection.

SQL

• Structure Query Language, ISO standard
• Case-Insensitive (key words, table and column names)
• Turning Complete since SQL:1999
• SQL does not guarantee the order of rows in a table in any way.
• The results are always a table or empty (zero row), even there is only one row and one column.

Three-Valued Logic (TVL or 3VL)

True, False and Null which means Unknown.

# NOT
not true  = false
not false = true
not null  = null  -- not unknown is unknonwn

# AND
true & true   = true
true & false  = false
false & false = false
true & null   = null
false & null  = false

# OR
true | true   = true
true | false  = true
false | false = false
true | null   = true
false | null  = null

• In WHERE clause, null (unknown) is treated as false.
• Comparison with null (unknown) result in null (unknown).
• Arithmetic operation with null (unknown) result is null (unknown).

null = null, false, this is comparison
null != null, true, therefore unique could be null
null is null, true, this is judgement
null [not] like, false
null [not] in, false
t1 = t2, false (unknown) when one of them is null (unknown)

• SELECT DISTINCT treats null as a fixed value.
• GROUP BY treats null as a fixed value.
• ORDER BY treats null as the biggest value by default.
• CHECK constraint treats null as true.
• In general, aggregation functions would ignore null value.

Execution Order of SQL

1. FROM – identify source table(s), change table names
2. JOIN – merge tables
3. WHERE – filter individual rows
4. GROUP BY – group rows, reduce group into one row
5. HAVING – filter groups
6. SELECT – pick/compute columns, window functions
7. DISTINCT – remove duplicate rows
8. ORDER BY – sort the result
9. LIMIT/OFFSET – restrict row count

JOIN

• [INNER] JOIN
• LEFT [OUTER] JOIN
• RIGHT [OUTER] JOIN
• FULL [OUTER] JOIN
• CROSS JOIN, Cartesian Product

-- other way to do cross join
FROM t1 CROSS JOIN t2
FROM t1, t2  -- comma join
FROM t1 join t2 ON TRUE

• ON conditions
• USING (column,[...]) specifys matching columns as condition
• NATURAL JOIN finds all matching columns as condition, fragile, not recommend

SELECT

Normally, when their is no row, select return empty. We can use SELECT to evalue scalar values, and change the name of columns w/o AS.

sqlite> select 123;
123
---
123
sqlite> select 123 [as] abc;
abc
---
123
sqlite> select 123 abc, 234 bcd, 345 cde;
abc  bcd  cde
---  ---  ---
123  234  345
sqlite> select 1+2;  -- literal column name and evalue it
1+2
---
3

SELECT (...) is called Scalar Subquery.

• Subquery can only return one column.
• Select the first row (one column).
• If subquery returns empty (zero row), select return null.

SELECT DISTINCT is applied on multiple columns and treats the combination of those columns as the unit of uniqueness — not each column separately. GROUP BY is alternative.

-- DISTINCT applies to the whole SELECT list, not just the column right after it
SELECT DISTINCT department, job_title, salary FROM employees;
-- distinct combinations of all three columns

5 Aggregation Functions

count,sum,avg,max,and min.

• count(*), count all row
• count(a), count column a, ignore null
• count(DISTINCT a), count distinct non-null of column a (may not support multi-column)
• count never return null, return 0 when no row
• sum,avg,max and min would ignore null and return null if no input

CASE WHEN … THEN … ELSE … END

Could be used to modify column value just before it is shown up.

CASE 
  WHEN N < 1 OR N IS NULL THEN NULL 
  WHEN N > 100 THEN 100
  ELSE N 
END

CASE department
  WHEN 'Sales' THEN 'S'
  WHEN 'IT' THEN 'I'
  ELSE 'X'
END

GROUP BY … HAVING …

GROUP BY reduces many rows into one row per group (you lose the individual rows). GROUP BY answers: “For each distinct value (or combination) of these columns, give me one summary row.”

-- groups all employees by department, aggregate on each group
SELECT department, COUNT(*) AS employee_count, AVG(salary) AS avg_salary
FROM Employees
GROUP BY department;

-- find customers who spend more than 1000
SELECT customer_id, SUM(amount) AS total_spent
FROM Orders
GROUP BY customer_id
HAVING SUM(amount) > 1000;

-- group by combination of more than one column
SELECT department, YEAR(hire_date) AS hire_year, COUNT(*) AS num_hired
FROM Employees
GROUP BY department, hire_year;

11 Window Functions

A window function performs a calculation across a set of rows that are related to the current row — but without collapsing them into a single row, the way GROUP BY would. Each row keeps its identity, and gets an extra computed value attached to it based on a “window” of related rows.

-- syntax
function_name(...) OVER (
    PARTITION BY column_a  -- optional: split rows into groups
    ORDER BY column_b      -- optional: order rows within each group
)

/*  PARTITION BY — divides the rows into groups
    (like GROUP BY, but doesn't merge them).
    The function resets/restarts for each partition.

    ORDER BY (inside the OVER()) — defines the order
    in which rows are processed for ranking/running
    calculations within each partition.

    If you omit PARTITION BY, the whole table is treated as one big partition.
*/

Ranking Function

• row_number(): unique sequential number, no ties
• rank(): ties share rank, leaves gaps
• dense_rank(): ties share rank, no gaps
• ntile(n): split rows into n roughly equal buckets, (row_number+n-1)/n
• percent_rank(): [0,1], (row_number-1)/(max_row_number-1)
• cume_dist(): cumulative distribution

Offset Function

• lag(): looks at the value from a previous row in the ordered window.
• lead(): looks at the value from a following row.
• first_value(): returns the first value in the window frame.
• last_value(): returns the last value in the window frame.
• nth_value(): returns the value from the Nth row of the window frame.

Aggregation Function as Window Function

Aggregate without collapsing rows.

Running Total and Moving Average

Adding ORDER BY inside OVER() changes the frame from “whole partition” to “up through current row” — this is what gives you a running total.

SELECT name, department, salary,
       SUM(salary) OVER (PARTITION BY department ORDER BY salary DESC) AS running_total
FROM Employees;

/* By default, tied rows get the running total including all tied rows!
   This is RANGE framing. If you want a strict row-by-row running total
   even with ties, use ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW instead. */

Frame Clause

PARTITION BY picks the group. ORDER BY picks the order. The frame clause picks exactly which rows within that ordered group get fed into the function for the current row.

/* This means: "start from the first row of the partition,
   and go all the way down to the current row — nothing after it." */
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

/* Keyword	               Meaning
UNBOUNDED PRECEDING	       Start of the partition
N PRECEDING	               N rows before current
CURRENT ROW	               The current row
N FOLLOWING	               N rows after current
UNBOUNDED FOLLOWING	       End of the partition*/

INSERT INTO

-- insert default value for every column
INSERT INTO purchase_time DEFAULT VALUES;

UPDATE

UPDATE table_name
SET column1 = value1, column2 = value2
WHERE condition;

DELETE FROM

DELETE FROM table_name
WHERE condition;

WHERE …

<, >, =, <=, >=, <>, !=

AND, OR, NOT

IS [NOT] NULL

[NOT] LIKE

• %: zero or more characters
• _: single character
• In SQLite, LIKE is case-insensitive
• In PostgreSQL, LIKE is case-sensitive, so we have a non-standard ILIKE which is case-insensitive.

[NOT] BETWEEN … AND … (inclusive)

-- between text alphabetically
SELECT * FROM Products
WHERE ProductName BETWEEN 'Geitost' AND 'Louisiana Hot Spiced Okra'
ORDER BY ProductName;

[NOT] IN

SELECT * FROM employees
WHERE department IN ('Sales', 'Marketing');

-- IN (SELECT ...), single column
SELECT name FROM employees
WHERE dept_id IN (
    SELECT dept_id FROM departments WHERE budget > 150000
);

/* x IN (1,2,NULL) behaves exactly like x IN (1,2) from
   the WHERE clause's perspective — matches get included,
   everything else gets excluded. The NULL is harmless here.
   
   x NOT IN (1,2,NULL) is always FALSE!!!
     ≡ NOT (x = 1 OR x = 2 OR x = NULL)
        (FALSE or FALSE or UNKNOWN) = UNKNOWN
        NOT UNKNOWN = UNKNOWN = FALSE*/

[NOT] EXISTS

Instead of comparing values, EXISTS just checks whether the subquery returns any rows.

SELECT name FROM employees e
WHERE EXISTS (
    SELECT 1 FROM departments d
    WHERE d.dept_id = e.dept_id AND d.budget > 150000
);

Correlated Subsquery

/* For each employee e1, the subquery computes the
   average salary of just that employee's department,
   then compares. */
SELECT e1.name, e1.salary, e1.dept_id
FROM employees e1
WHERE e1.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.dept_id = e1.dept_id
);

ANY

SELECT ProductName FROM Products
WHERE ProductID = ANY (
  SELECT ProductID
  FROM OrderDetails
  WHERE Quantity = 10
); 

ALL

SOME

Common Table Expression (CTE)

WITH <table_name> AS (...) create a named, temporary result set that only exists for the duration of the query that follows it.

-- basic syntax
-- more readable than subquery
WITH cte_name AS (
    SELECT ...
)
SELECT ...
FROM cte_name

-- CTE can be chained
WITH cte1 AS (
    SELECT ...
),
cte2 AS (
    SELECT ... FROM cte1 ...
)
SELECT * FROM cte2;

-- recursive CTE
WITH RECURSIVE cte_name AS (
    -- 1. Anchor member (base case)
    SELECT ...
    
    UNION ALL -- alwways union all
    
    -- 2. Recursive member (references cte_name)
    SELECT ...
    FROM cte_name
    WHERE ...  -- stopping condition
)
SELECT * FROM cte_name;

-- recursive CTE example (SQL loop)
WITH RECURSIVE cte AS (
    SELECT 1 AS n              -- anchor: start at 1
    UNION ALL
    SELECT n + 1 FROM cte      -- recursive: add 1 each time
    WHERE n < 5                -- stop condition
)
SELECT * FROM cte;
-- It's more like a loop, not recursive function! 
-- recursive here means refering to the same table (itself).
-- In the loop, everytime only deal with new rows.

Set Operations

The queries being combined must have the same number of columns, in the same order, with compatible data types. Column names in the result come from the first query.

• UNION [ALL], all means keep duplications
• INTERSECT [ALL], all means min(a,b)
• EXCEPT [ALL], all means max(a-b,0)

{1,2,3,4,1,3} except {1,2,3,4} --> {}
              except all {1,2,3,4}  --> {1,3}

-- ORDER BY must be at last
SELECT name, department FROM current_employees
UNION
SELECT name, department FROM former_employees
ORDER BY name;

Others

• coalesce(a,b,...): return the first non-null value
• CURRENT_TIMESTAMP: current UTC datetime

SQLite

• https://www.sqlite.org/
• Disk file database, not client-server architecture.
• Lightweight, Fast, Embedded, Zero configuration, Severless.

Concurrency

SQLite uses OS-level file locks on the database file itself to coordinate access between processes and threads. This is fundamentally different from client-server databases (Postgres, MySQL) where a single server process arbitrates access. With SQLite, every process that opens the file is a peer, and they negotiate access through the filesystem’s locking primitives (fcntl in Linux).

Because locking depends on OS-level file locks, the database file must live on a filesystem that correctly implements locking. Network filesystems (NFS, SMB, some cloud-synced folders like Dropbox) often have buggy or incomplete lock implementations, which is why SQLite documentation explicitly warns against using it there for concurrent access.

Rollback Journal Mode (default)

Under the default rollback journal mode, the database file locking is just like a classic Read-Write Lock (RW-Lock). SQLite databases move through five lock states:

• UNLOCKED – no lock held, process hasn’t touched the file yet
• SHARED – process wants to read; multiple processes can hold SHARED simultaneously
• RESERVED – process intends to write soon (e.g., began a write transaction); one process at a time, but readers with SHARED can continue read
• PENDING – transitional state; writer is waiting for existing readers to finish, and no new SHARED locks are allowed to start
• EXCLUSIVE – writer has sole access; needed to actually commit changes to the file

Key rule: only one writer at a time, and a writer must wait for all readers to release their SHARED locks before it can escalate to EXCLUSIVE and commit. This is why concurrent writes from multiple processes will block or fail with SQLITE_BUSY if not handled properly.

Write-Ahead Logging (WAL) Mode

Under WAL mode, you could get better concurrent performance.

• Writers append changes to a separate -wal file rather than modifying the main database file directly
• Readers read from a consistent snapshot (main db + relevant WAL frames) and are not blocked by writers
• Only one writer at a time is allowed as well, but readers no longer block writers, and writers no longer block readers
• Periodically, a checkpoint operation copies WAL contents back into the main database file
• WAL mode requires a filesystem that supports shared memory (mmap), which is another reason network filesystems are problematic

/* enable WAL mode */
PRAGMA journal_mode=WAL;
/* go back to rollback mode */
PRAGMA journal_mode=DELETE;

Multi-Version Concurrency Control (MVCC)

WAL is one of the implementation of MVCC idea. Instead of making readers and writers fight over the same copy of data, MVCC keeps multiple versions of data around so readers can see a consistent snapshot from a point in time, while writers create new versions without disturbing that snapshot.

1. When a read transaction starts, SQLite records the current end of the WAL file — this defines that reader’s snapshot boundary
2. The reader reads pages by checking: “is there a newer version of this page in the WAL, at or before my snapshot boundary? If yes, use that. If no, use the main .db file.”
3. Writers append new page versions to the end of the WAL — past the boundary any existing reader is using — so they never collide with what a reader is looking at
4. Multiple readers can each have different snapshot boundaries simultaneously, all reading consistent (if slightly different-in-time) views
5. Because old page versions in the WAL can’t be discarded until the last reader referencing them finishes, long-running read transactions directly block WAL reclamation — this is the mechanism behind the “WAL keeps growing” problem

PostgreSQL utilizes the same MVCC idea to improve concurrency!

There’s still Only One Writer at a time in SQLite. MVCC in SQLite solves reader/writer contention, not writer/writer contention. That’s still a plain mutex (the WAL-file write lock).

How Checkpoint Operation Works

In WAL mode, writes don’t touch the main .db file — they’re appended as frames to the -wal file. Checkpointing is the process of taking those WAL frames and writing them back into the main database file, then (potentially) resetting the WAL.

The basic algorithm:

1. Walk through the WAL file frame by frame
2. For each page that was modified, copy the latest version of that page from the WAL into the corresponding position in the main .db file
3. If a page was written multiple times across different transactions in the WAL, only the last version needs to be copied (checkpointing coalesces this automatically)
4. Once all frames are copied, if no readers are still using older WAL frames, the WAL can be reset (truncated or overwritten from the start)

When Checkpoints Happen

By default, SQLite triggers an automatic checkpointing (PASSIVE mode) when the WAL file grows past 1000 pages (roughly 4MB at the default 4KB page size). This happens as part of a regular write transaction commit, on whichever connection happens to trigger the threshold.

/* change the page number threshold,
   0 disables automatic checkpointing entirely,
   useful if you want to control checkpointing manually,
   e.g., during low-traffic windows */
PRAGMA wal_autocheckpoint = N;

There are 4 checkpoint operation modes:

• PASSIVE
  • Checkpoints as many frames as possible without blocking anyone
  • Does not wait for readers to finish, does not block new readers or writers from starting
  • If a reader is holding a snapshot that references old WAL frames, PASSIVE simply stops at the boundary it can’t cross and returns — partial progress is fine, no error
  • Safest, least disruptive, but may leave the WAL file large if there’s a slow/stuck reader
  • This is what runs automatically at the 1000-page threshold
• FULL
  • Blocks new writers from starting (but doesn’t block readers)
  • Waits for all in-progress writers to finish (there’s at most one anyway)
  • Checkpoints all frames up to the point where no active reader still needs them
  • Will still stop short if a reader is holding an old snapshot — it just waits longer / tries harder than PASSIVE, but doesn’t forcibly kick readers out
  • Does not guarantee the WAL is reset back to the beginning afterward
• RESTART
  • Does everything FULL does, plus: once the checkpoint completes, it ensures that the next write will start writing the WAL from the beginning again (logically resets the WAL sequence)
  • Blocks new readers from starting partway through, briefly, to safely perform this reset
  • Still doesn’t shrink the file on disk — the WAL file’s size stays the same, just its logical content resets
• TRUNCATE
  • Does everything RESTART does, plus: actually truncates the -wal file on disk back to 0 bytes
  • The strongest mode — most likely to briefly block concurrent connections to complete
  • Best for reclaiming disk space, e.g., after a bulk import or during a maintenance window

/* manually trigger checkpoint operation,
   you may need to check return values. */
PRAGMA wal_checkpoint(TRUNCATE);

Better Practice

• set WAL as default. Don’t mix journal modes.
• set Busy Timeout. PRAGMA busy_timeout = 5000;, so that when a process hits a lock conflict, it retries for up to N milliseconds instead of immediately erroring with SQLITE_BUSY. (could be set by connect API in Python sqlite3 module)
• Short Transaction. Keep write transactions as short as possible to minimize the window where other writers are blocked. Keep read transaction short could help WAL checkpointing and avoid “WAL keeps growing” issue.

Threading Modes

SQLite could be compiled into 3 different threading modes:

• Single-thread: no mutexes at all, using it from more than one thread is undefined behavior.
• Multi-thread: safe to use different connections from different threads simultaneously, but a single connection must not be used by more than one thread at a time.
• Serialized (the default in most builds, including Python’s sqlite3 module): SQLite wraps connection-level operations in an internal mutex, so sharing one connection across threads won’t corrupt data. But it means threads are queued/serialized the moment they touch that connection, and transaction’s might be interleaved with each other. Serialization is in SQL statement level, or low C API call level.

>>> import sqlite3
# 3 means serialized threading mode in Python
>>> sqlite3.threadsafety
3

Transaction Modes

Transactions can be DEFERRED, IMMEDIATE, or EXCLUSIVE. The default transaction behavior is DEFERRED.

DEFERRED means that the transaction does not actually start until the database is first accessed. Internally, the BEGIN DEFERRED statement merely sets a flag on the database connection that turns off the automatic commit that would normally occur when the last statement finishes. This causes the transaction that is automatically started to persist until an explicit COMMIT or ROLLBACK or until a rollback is provoked by an error or an ON CONFLICT ROLLBACK clause. If the first statement after BEGIN DEFERRED is a SELECT, then a read transaction is started. Subsequent write statements will upgrade the transaction to a write transaction if possible, or return SQLITE_BUSY. If the first statement after BEGIN DEFERRED is a write statement, then a write transaction is started.

IMMEDIATE causes the database connection to start a new write immediately, without waiting for a write statement. The BEGIN IMMEDIATE might fail with SQLITE_BUSY if another write transaction is already active on another database connection.

EXCLUSIVE is similar to IMMEDIATE in that a write transaction is started immediately. EXCLUSIVE and IMMEDIATE are the same in WAL mode, but in other journaling modes, EXCLUSIVE prevents other database connections from reading the database while the transaction is underway.

Flexible Typing

Unlike most SQL databases, SQLite doesn’t enforce that a column can only holds one data type — any column can store any value of any Storage Class: NULL, INTEGER, REAL, TEXT, or BLOB, regardless of the declared type in CREATE TABLE. Tpye affinity is SQLite’s middle ground: it’s a recommendation for what type of data a column prefers to store, and SQLite will try to coerce inserted values toward that preference, but it won’t refuse to store a mismatched type.

• Datatype names on column definitions are optional. A column definition can consist of just the column name and nothing else.
• SQLite began as a TCL extension that later escaped into the wild. TCL is a dynamic typing language in the sense that the programmer does not need to be aware of datatypes.
• When datatype names are provided, they can be just about Any Text. SQLite attempts to deduce the preferred datatype for the column based on the datatype name in the column definition, but that preferred datatype is advisory, not mandatory. The preferred datatype is known as the Column Affinity.
• An attempt is made to transform incoming data into the preferred datatype of the column. (All SQL database engines do this, not just SQLite.) If this transformation is successful, all is well. But if unsuccessful, instead of raising an error, SQLite just stores the content using its original datatype.

SQLite is less restrictive!

Five Affinities

Every column gets assigned exactly one of affinities based on its declared type:

• TEXT
• NUMERIC
• INTEGER
• REAL
• BLOB (also called “no affinity” — no coercion attempted at all)

1. If the declared type contains the substring “INT” → INTEGER affinity
2. Else if it contains “CHAR”, “CLOB”, or “TEXT” → TEXT affinity
3. Else if it contains “BLOB”, or no type is specified at all → BLOB affinity
4. Else if it contains “REAL”, “FLOA”, or “DOUB” → REAL affinity
5. Else → NUMERIC affinity (the catch-all default)

NUMERIC affinity: attempts to convert text to INTEGER or REAL if the text looks losslessly convertible (e.g., ‘123’ → integer, ‘3.5’ → real). If conversion would lose information (like ‘123abc’), the value is stored as-is in its original TEXT form. NULL and BLOB pass through unchanged.

sqlite> CREATE TABLE demo (
  a INTEGER,   -- INTEGER affinity
  b TEXT,      -- TEXT affinity
  c BLOB,      -- BLOB affinity (none)
  d NUMERIC,   -- NUMERIC affinity
  e REAL       -- REAL affinity
);
sqlite> INSERT INTO demo VALUES ('5', '5', '5', '5', '5');
sqlite> SELECT typeof(a), typeof(b), typeof(c), typeof(d), typeof(e) FROM demo;
integer|text|text|integer|real
sqlite> INSERT INTO demo VALUES ('5', '5', '5', null, '5');
sqlite> SELECT typeof(a), typeof(b), typeof(c), typeof(d), typeof(e) FROM demo WHERE rowid=2;
integer|text|text|null|real

Max-Length for TEXT and BLOB

The real ceiling on how much you can store in a single TEXT or BLOB value is a compile-time setting:

• Default: 1,000,000,000 bytes (1 billion bytes, ~954 MiB)
• This is a compile-time constant (SQLITE_MAX_LENGTH), configurable up to a hard ceiling of 2^31 - 1 (about 2.1 GB) — it cannot be raised beyond that even by recompiling, since the internal length field is a signed 32-bit integer
• It can also be lowered at runtime (but never raised beyond the compiled-in max) via sqlite3_limit(db, SQLITE_LIMIT_LENGTH, newValue), or PRAGMA isn’t used for this one — it’s purely a C API call, not exposed as a PRAGMA

Unless whoever built your SQLite binary changed the default, you can store up to ~1GB in a single TEXT or BLOB column value, per row, per column. If you really need length constraint, use CHECK on table definition.

Strict Table

Introduced in SQLite 3.37.0 (2021), STRICT is an opt-in table-level modifier that turns off SQLite’s normally-flexible type system for that table and enforces actual type checking on insert/update. With STRICT, an insert that can’t be sensibly converted to the declared type is rejected with an error instead of silently stored as an affinity type. There are 6 types in strict table:

1. INT
2. INTEGER (for ROWID column)
3. REAL
4. TEXT
5. BLOB
6. ANY (dynamic type column in strict table)

CREATE TABLE t (a INTEGER, b TEXT, c REAL) STRICT;

ROWID (Default PK)

Every row in an ordinary SQLite table has a 64-bit signed integer key called the rowid, which uniquely identifies that row within the table. This exists whether or not you ever reference it explicitly — it’s the physical key SQLite’s underlying B-tree structure uses to store and look up rows. Unless a table is declared WITHOUT ROWID, this key is always there.

sqlite> CREATE TABLE aa (name TEXT);
sqlite> INSERT INTO aa(name) VALUES('tom');
sqlite> INSERT INTO aa(name) VALUES('jacky');
sqlite> SELECT * FROM aa;
tom
jacky
sqlite> SELECT rowid,* FROM aa; /* show rowid column */
1|tom
2|jacky

Create ROWID’s Alias

sqlite> CREATE TABLE bb (id INTEGER PRIMARY KEY, name TEXT);
sqlite> INSERT INTO bb(name) VALUES('tom'),('jacky');
sqlite> SELECT * FROM bb;
1|tom
2|jacky
sqlite> INSERT INTO bb(id,name) VALUES(5,'xinlin');
sqlite> SELECT rowid,* FROM bb;
1|1|tom
2|2|jacky
5|5|xinlin

Only literal INTEGER PRIMARY KEY works.

ROWID Allocation

If you insert a row without specifying the rowid column (or explicitly insert NULL into it), SQLite auto-assigns one:

• Default algorithm: current max rowid in the table + 1
• If the table is empty, it starts at 1
• If the max rowid is already 9223372036854775807 (the largest signed 64-bit int), SQLite instead searches for an unused value at random — this is a fallback edge case you’ll essentially never hit in practice, but it’s documented behavior, not an error

Important consequence: rowids get reused after deletion under this default scheme. If your table has rows with rowids 1, 2, 3 and you delete row 3, the next insert gets rowid 3 again (since max+1 logic just looks at current max, which is now 2). This is fine for most uses but dangerous if your application logic assumes rowids are permanently unique across the lifetime of the table (e.g., using them as external references, foreign keys held outside the transaction, or cache keys).

AUTOINCREMENT — Preventing Reuse

CREATE TABLE t (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT);

Keyword AUTOINCREMENT changes the allocation algorithm: instead of max(rowid)+1 at insert time, SQLite tracks the highest rowid ever used in a hidden system table called sqlite_sequence, and always allocates strictly higher than that historical high-water mark — even if rows with high rowids were later deleted. This guarantees monotonically increasing, never-reused rowids for the lifetime of the table.

Trade-offs of AUTOINCREMENT:

• Extra overhead: every insert now needs to check/update the sqlite_sequence table, so it’s measurably slower than plain INTEGER PRIMARY KEY — the SQLite docs explicitly recommend avoiding it unless you actually need the no-reuse guarantee
• Can fail with SQLITE_FULL once the sequence is exhausted at the 64-bit max, since it won’t fall back to the random-search behavior plain rowid tables use
• Only valid combined with INTEGER PRIMARY KEY — you cannot apply AUTOINCREMENT to a rowid alias declared any other way

When to actually use it: if externally-visible IDs need to never repeat, even across deletes (e.g., you’re handing out these IDs in URLs, API tokens, references stored in other systems) — otherwise, skip it.

Explicit ROWID Assignment

You can specify any rowid value yourself:

INSERT INTO t (id, name) VALUES (100, 'x');
-- negative rowids are legal
-- rowid literal name could be directly used
INSERT INTO aa (rowid, name) VALUES (-5, 'y');

Rowids can be negative — the auto-assignment algorithm never picks a negative value on its own, but nothing stops you from inserting one explicitly. Ordering, comparisons, and lookups all work correctly with negative rowids since it’s just a signed 64-bit integer.

Without ROWID Table

CREATE TABLE t (id TEXT PRIMARY KEY, name TEXT) WITHOUT ROWID;

This opts a table out of the rowid mechanism entirely. Instead, the table’s declared PRIMARY KEY becomes the actual B-tree key directly — there’s no hidden 64-bit integer sitting underneath.

ROWID and INSERT … RETURNING

-- return the most recent auto-assigned rowid
INSERT INTO t (name) VALUES ('x') RETURNING id;

Foreign Key

-- have to explicitly turn on
PRAGMA foreign_keys=on;

Memory SQLite :memory:

Creates a database that exists entirely in RAM, with no backing file on disk at all. It behaves like a fully-featured SQLite database — same SQL support, same transaction semantics — except nothing is ever persisted, and it disappears the moment the connection closes.

An in-memory database’s lifetime is exactly the lifetime of the database connection that created it. The moment that connection is closed, the entire database is gone, irrecoverably. There is no implicit “attach to an existing in-memory db by name” the way you might expect from a shared resource. Therefore, each :memory: connection is independent and private. Opening :memory: twice — even in the same process — gives you two completely separate, unrelated databases, not two handles to the same one. And WAL mode doesn’t apply to in-memory databases.

conn1 = sqlite3.connect(":memory:")
conn2 = sqlite3.connect(":memory:")
conn1.execute("CREATE TABLE t (x)")
conn2.execute("SELECT * FROM t")  # ERROR: no such table — conn2 has its own empty db

Use cases:

1. for test
2. caching for SQL queries
3. ETL staging step, query and transform data entirely in RAM before writing final results elsewhere

sqlite3 Module in Python

connect

If connect a non-existed database file, SQLite would create it. This interface has a few important parameters:

• check_same_thread=True, prevent the connection sharing among multiple threads in Python level
• timeout=5.0, equals PRAGMA busy_timeout = 5000;, 5 seconds
• autocommit, should be set to False, and use commit() and rollback() to close transaction explicitly. If it is True, commit() and rollback() callings have no effect. (>=3.12)

# compatible style
if sys.version_info >= (3, 12):
    # implicit BEGIN, explicit commit and rollback
    self.conn = sqlite3.connect('items.db', autocommit=False)
else:
    # default isolation level: DEFERRED
    # no implicit BEGIN for SELECT, commit is harmless!
    self.conn = sqlite3.connect('items.db')
# turn on foreing key
self.conn.execute("PRAGMA foreign_keys = ON;")

execute

execute could be called on both connection and cursor. Under the hood, they are the same. If it is called on connection, a brand-new cursor would be returned. You can throw it away, or chain it with fetchall(). It can only issue one single SQL statement, and support ? placeholders for parameter binding. Control transaction by commit() and rollback().

executemany

Repeatedly execute the single SQL statement with an iterable parameter.

executescript

Take a SQL string to execute. If autocommit=False, no implicit transaction control.

PostgreSQL

• https://www.postgresql.org/
• Client-server Architecture.

PL/pgSQL

PL: Procedure Language

Plain SQL is declarative — great for querying, but it has no loops, no variables, no branching logic. PL/pgSQL adds procedural programming capability inside the database, so you can write complex logic that runs server-side (faster — no round trips to the app) instead of pulling data out, processing it in application code, and pushing it back. (SQLite doesn’t have it since it is serverless and it is binded with app.)

-- no transaction can be inside Function
CREATE OR REPLACE FUNCTION function_name(param1 TYPE, param2 TYPE)
RETURNS return_type AS $$
DECLARE
    -- variable declarations
    my_var INT;
BEGIN
    -- procedural logic here
    RETURN some_value;
END;
$$ LANGUAGE plpgsql;