SQLite

Overview

SQLite is a free, open source, lightweight, embedded relational database management system (RDBMS) created by D. Richard Hipp in 2000. It runs as a single-file database without the need for a separate server process, making it suitable for small projects, mobile applications, and local development. SQLite supports the SQL standard (partially compliant with ANSI SQL), provides simple and efficient data storage, and is widely used in Web applications, mobile devices (Android, iOS), and embedded systems.

History and Development

• Origin:
  • SQLite was developed by D. Richard Hipp in 2000 and was originally designed for a US Navy project with the goal of creating an unmanaged embedded database.
  • The design concept is "simple, reliable, and easy to embed", avoiding server dependencies on PostgreSQL or MySQL.
• Development History:
  • 2000-2005: SQLite 1.0 and 2.0 were released, supporting basic SQL functions and widely used in early Web and embedded devices.
  • 2010: SQLite 3.0 introduced dynamic types, transaction support, and B-tree indexes, significantly improving performance.
  • 2015-2020: Added JSON support (3.9, 2015), full-text search (FTS5), and window functions to enhance functionality.
  • 2023-2025: The latest version SQLite 3.46.1 (August 2024), optimizes query performance, supports JSONB and incremental backups, suitable for modern applications.
• Community and support:
  • Maintained by the SQLite team, hosted at sqlite.org and GitHub.
  • Active X community.
  • Support forums (such as Stack Overflow) and documentation (Chinese support, sqlite.org/docs.html).
• Open Source License:
  • SQLite uses a Public Domain license, which has no copyright restrictions and allows free use, modification and distribution.
  • More relaxed than the PostgreSQL license (similar to MIT), no copyright statement is required.

Main Features

SQLite is known for its simplicity, embedded design and high performance, suitable for small to medium-sized projects. Here are its main features:

1. Embedded and single-file database
   • Serverless design:
     • SQLite database is stored as a single .db or .sqlite file, without running a server process.
     • Directly embedded in the application, reducing configuration (such as postgresql.conf without PostgreSQL).
   • Cross-platform:
     • Support Windows, Linux, macOS.
     • Files can be migrated across devices.
2. SQL support and functions
   • SQL standards:
     • Support core SQL operations such as SELECT, INSERT, UPDATE, DELETE.
     • Provides tables, indexes, triggers and views, suitable for structured data.
   • Advanced features:
     • JSON support: store and query JSON data.
     • Full-text search (FTS5): support multi-language search.
     • Dynamic type: columns support multiple data types (such as INTEGER, TEXT), with high flexibility.
3. Transactions and reliability
   • ACID transactions:
     • Support atomicity, consistency, isolation, and persistence to ensure the consistency of donation data.
     • Use BEGIN TRANSACTION and COMMIT to manage transactions.
   • WAL mode:
     • Write-Ahead Logging improves concurrency performance and is suitable for small concurrent scenarios.
4. Lightweight and efficient
   • Small size:
     • The SQLite core library is about 1MB and the running memory is low (about 256KB).
     • Suitable for low-end devices.
   • High performance:
     • Optimize B-tree indexes, fast query speed, suitable for small data sets (<100MB).
5. Tools and Ecosystem
   • Management Tools:
     • SQLite3 CLI: command line tool, run SQL commands.
     • DB Browser for SQLite: graphical interface, manage tables and data.
   • Community Support:
     • SQLite "zero configuration".
     • Provide Chinese documentation (sqlite.org/docs.html).

Advantages and Limitations

Advantages

• Zero configuration:
  • No need to install or run a server, unzip and use, suitable for rapid development.
  • SQLite "newbie friendly".
• Lightweight and efficient:
  • Small memory footprint (256KB vs PostgreSQL's 500MB).
  • Single-file database is easy to backup and migrate.
• Public domain license:
  • No copyright restrictions, more relaxed than the PostgreSQL license (copyright notice must be retained).
  • Suitable for open source or commercial projects.
• Cross-platform:
  • Compatible with multiple operating system environments.
• Widely used:
  • Used in Android, iOS, Firefox, VS Code, etc., proven reliability.

Limitations

• Limited concurrency performance:
  • Does not support high-concurrency write operations (WAL mode supports limited concurrency).
  • Solution: Used for low-concurrency scenarios (such as local testing), use PostgreSQL for production environments.
• Limited functionality:
  • Lack of advanced features of PostgreSQL (such as PostGIS, logical replication).
  • Solution: Use JSON support or extensions (such as FTS5) to meet basic needs.
• Data scale limit:
  • Suitable for small data sets (<1GB), large database performance degrades.
  • Solution: Use SQLite when the data volume is small, and switch to PostgreSQL when it exceeds 1GB.
• No user management:
  • Lack of database-level user permissions, relying on file system permissions.
• Comparison with PostgreSQL:
  • PostgreSQL supports high concurrency, complex queries and enterprise-level functions, suitable for production environments.
  • SQLite is lighter and suitable for local development and small projects.

Summary

SQLite is a lightweight, embedded, open source relational database with a public domain license. It is known for its zero configuration and high efficiency, and is suitable for small projects and local development. Its single-file design and SQL support make it an ideal choice for simple projects. Compared with PostgreSQL (high concurrency, enterprise level) or MySQL (server dependence), SQLite is more suitable for rapid prototyping and low-end devices.