Dong Nguyen
SQLite

Defaults

-- Set the journal mode to Write-Ahead Logging for concurrency
PRAGMA journal_mode = WAL;

-- Set synchronous mode to NORMAL for performance and data safety balance
PRAGMA synchronous = NORMAL;

-- Set busy timeout to 5 seconds to avoid "database is locked" errors
PRAGMA busy_timeout = 5000;

-- Set cache size to 20MB for faster data access
PRAGMA cache_size = -20000;

-- Enable foreign key constraint enforcement
PRAGMA foreign_keys = ON;

-- Enable auto vacuuming and set it to incremental mode for gradual space reclaiming
PRAGMA auto_vacuum = INCREMENTAL;

-- Store temporary tables and data in memory for better performance
PRAGMA temp_store = MEMORY;

-- Set the mmap_size to 2GB for faster read/write access using memory-mapped I/O
PRAGMA mmap_size = 2147483648;

-- Set the page size to 8KB for balanced memory usage and performance
PRAGMA page_size = 8192;

Foreign key

FOREIGN KEY (foreign_key_columns)
   REFERENCES parent_table(parent_key_columns)
      ON UPDATE action 
      ON DELETE action;

Actions

  • SET NULL -> When the parent key changes, delete or update, the corresponding child keys of all rows in the child table set to NULL.

  • SET DEFAULT -> sets the value of the foreign key to the default value specified in the column definition when you create the table.

  • RESTRICT -> does not allow you to change or delete values in the parent key of the parent table.

  • NO ACTION -> does not mean by-pass the foreign key constraint. It has the similar effect as the RESTRICT

  • CASCADE -> propagates the changes from the parent table to the child table when you update or delete the parent key

Backup/Restore

sqlite3 database.sqlite3 .dump > database.dump
sqlite3 database.sqlite3 < database.dump