SQLite - Nimbus Code

1. Introduction

SQLite is a C-language library that implements a small, fast, self-contained, high-reliability, full-featured, SQL database engine. Unlike most other SQL databases, SQLite does not have a separate server process. SQLite reads and writes directly to ordinary disk files.

Created by D. Richard Hipp in 2000, SQLite is the most widely deployed database engine in the world, found in countless applications including every Android and iOS device, most web browsers, and many other applications. SQLite's source code is in the public domain, making it free to use for any purpose.

2. Syntax Examples

Basic Database Operations

-- Create a table
CREATE TABLE contacts (
    contact_id INTEGER PRIMARY KEY,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    email TEXT,
    phone TEXT,
    created_at TEXT DEFAULT (datetime('now'))
);

-- Insert data
INSERT INTO contacts (first_name, last_name, email, phone)
VALUES 
  ('John', 'Smith', '[email protected]', '555-123-4567'),
  ('Jane', 'Doe', '[email protected]', '555-987-6543');

-- Query data
SELECT * FROM contacts;

-- Update data
UPDATE contacts
SET email = '[email protected]'
WHERE contact_id = 1;

-- Delete data
DELETE FROM contacts
WHERE contact_id = 2;

SQLite-Specific Features

-- SQLite dynamic typing (type affinity)
CREATE TABLE dynamic_example (
    id INTEGER PRIMARY KEY,
    flexible_column  -- No type specified, can hold any data type
);

INSERT INTO dynamic_example (flexible_column)
VALUES 
  (42),                  -- Integer
  (3.14159),             -- Real
  ('text value'),       -- Text
  (NULL),               -- NULL
  (x'DEADBEEF');         -- BLOB (binary data)

-- Using SQLite date/time functions
SELECT 
  date('now'),
  time('now'),
  datetime('now', 'localtime'),
  strftime('%Y-%m-%d %H:%M', 'now');

-- Full-text search (requires FTS extension)
CREATE VIRTUAL TABLE articles USING fts5(
    title, 
    body
);

INSERT INTO articles VALUES
  ('SQLite Tutorial', 'SQLite is a lightweight database...'),
  ('Database Design', 'Proper database design is essential...');

-- Search for content
SELECT * FROM articles
WHERE articles MATCH 'sqlite';

Programming with SQLite

// JavaScript with Node.js (using the sqlite3 package)
const sqlite3 = require('sqlite3').verbose();
const db = new sqlite3.Database('./mydb.sqlite');

// Create table
db.run(`CREATE TABLE IF NOT EXISTS users (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT UNIQUE
)`);

// Insert data
db.run(`INSERT INTO users (name, email) VALUES (?, ?)`, 
  ['Alice', '[email protected]'], 
  function(err) {
    if (err) {
      return console.error(err.message);
    }
    console.log(`User added with ID: ${this.lastID}`);
  }
);

// Query data
db.all(`SELECT * FROM users`, [], (err, rows) => {
  if (err) {
    return console.error(err.message);
  }
  rows.forEach(row => {
    console.log(row);
  });
});

// Close the database connection
db.close();

3. Main Uses

SQLite is used in a wide variety of applications:

Mobile applications (iOS, Android)
Desktop applications
Embedded systems and IoT devices
Web browsers (Firefox, Chrome) for local storage
Application file formats
Development and testing environments
Educational tools for learning SQL
Small to medium-sized websites

4. Pros and Cons

Advantages

Zero configuration required
No server process needed
Cross-platform database file
Extremely lightweight (< 600KB)
Self-contained in a single file
ACID-compliant transactions
Public domain source code (free for any use)

Limitations

Limited concurrency for write operations
Not suitable for high-volume applications
No built-in user management
Limited network access capabilities
Lack of built-in replication
Size limitations (~140TB theoretical, but practical limits much lower)
Limited ALTER TABLE functionality

5. SQLite Evolution

SQLite has evolved significantly since its creation:

2000 - Initial release
2004 - SQLite 3.0 (new file format, UTF-16 support)
2010 - SQLite 3.7 (WAL mode for improved concurrency)
2013 - SQLite 3.8 (significant performance improvements)
2015 - SQLite 3.9 (advanced JSON capabilities)
2016 - SQLite 3.15 (Row Values support)
2018 - SQLite 3.24 (UPSERT support)
2019 - SQLite 3.27 (VACUUM INTO support)
2020 - SQLite 3.32 (RETURNING clause)
2022 - SQLite 3.38 (Native JSON data type)
2023-2025 - Continued stability and performance improvements

SQLite is known for its consistent backward compatibility and reliability. The development team values correctness and long-term stability over adding new features.

6. Learning Resources

Here are some excellent resources for learning SQLite:

SQLite Official Documentation - Comprehensive reference
SQLite Tutorial - Step-by-step guides
SQLite Database Tutorial for Beginners - Video tutorial
DB Browser for SQLite - Visual database tool
Learn SQL on Codecademy - Interactive course

7. Related Technologies

Technologies related to SQLite: