Learn SQLite

What is SQLite?

SQLite is a C-language library that implements a small, fast, self-contained, high-reliability, full-featured, SQL database engine. It's the most used database engine in the world. SQLite is built into all mobile phones and most computers and comes bundled inside countless other applications that people use every day.

Key characteristics include:

• Serverless: SQLite doesn't require a separate server process. It reads and writes directly to ordinary disk files.
• Self-Contained: Requires minimal support from the operating system or external library.
• Transactional: ACID compliant, ensuring all changes within a transaction either occur completely or not at all.
• Zero-Configuration: No setup or administration needed.
• Cross-Platform: A complete database is stored in a single cross-platform disk file.

Use Cases

• Embedded Devices & IoT: Its small footprint makes it ideal for devices with limited resources.
• Application File Format: Used as the internal data storage format for desktop applications (e.g., browsers, multimedia software).
• Mobile Applications: The standard database for Android and iOS development.
• Website Databases: Suitable for low to medium traffic websites.
• Data Analysis: Convenient for processing and analyzing datasets that fit on a single machine.
• Caching: Used as a local cache for enterprise data.
• Prototyping & Development: Easy to set up and use during development before potentially migrating to a client/server database.
• Teaching & Testing: Excellent for learning SQL concepts without server setup.

Basic SQL Commands

Here are some fundamental SQL commands used in SQLite (and most SQL databases):

Data Definition Language (DDL)

-- Create a new table
CREATE TABLE Users (
    UserID INTEGER PRIMARY KEY AUTOINCREMENT,
    Name TEXT NOT NULL,
    Email TEXT UNIQUE,
    RegistrationDate DATE DEFAULT CURRENT_DATE
);

-- Modify an existing table (e.g., add a column)
ALTER TABLE Users ADD COLUMN IsActive BOOLEAN DEFAULT 1;

-- Delete a table
DROP TABLE Users;

Data Manipulation Language (DML)

-- Insert data into a table
INSERT INTO Users (Name, Email) VALUES ('Alice', 'alice@example.com');
INSERT INTO Users (Name, Email, IsActive) VALUES ('Bob', 'bob@example.com', 0);

-- Select data from a table
SELECT UserID, Name, Email FROM Users WHERE IsActive = 1 ORDER BY Name;
SELECT COUNT(*) FROM Users WHERE RegistrationDate > '2024-01-01';

-- Update existing data
UPDATE Users SET Email = 'alice.updated@example.com' WHERE UserID = 1;

-- Delete data from a table
DELETE FROM Users WHERE IsActive = 0;

Data Types

SQLite uses a more general, dynamic type system. A value stored in a column can be of any of the following storage classes:

• NULL: The value is a NULL value.
• INTEGER: A signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude.
• REAL: A floating-point value, stored as an 8-byte IEEE floating-point number.
• TEXT: A text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16LE).
• BLOB: A blob of data, stored exactly as it was input.

SQLite supports type affinity on columns. This means the preferred storage class for a column is determined by its declared type. Common declared types like VARCHAR, BOOLEAN, DATE, DATETIME are mapped to these storage classes (mostly TEXT, INTEGER, or REAL).

Constraints

Constraints enforce rules on data within tables:

• PRIMARY KEY: Uniquely identifies each row in a table. Often an INTEGER type with AUTOINCREMENT.
• FOREIGN KEY: Establishes a link between two tables, enforcing referential integrity.
• UNIQUE: Ensures that all values in a column (or set of columns) are distinct.
• NOT NULL: Ensures that a column cannot have a NULL value.
• CHECK: Ensures that values in a column satisfy a specific condition.
• DEFAULT: Provides a default value for a column when none is specified during INSERT.

CREATE TABLE Orders (
    OrderID INTEGER PRIMARY KEY AUTOINCREMENT,
    UserID INTEGER NOT NULL,
    OrderDate DATETIME DEFAULT CURRENT_TIMESTAMP,
    Amount REAL CHECK(Amount > 0),
    Status TEXT DEFAULT 'Pending',
    FOREIGN KEY (UserID) REFERENCES Users(UserID) ON DELETE CASCADE
);

Indexes

Indexes are special lookup tables that the database engine can use to speed up data retrieval operations. They are automatically created for PRIMARY KEY and UNIQUE constraints.

-- Create an index on the Email column for faster lookups
CREATE INDEX idx_user_email ON Users(Email);

-- Remove an index
DROP INDEX idx_user_email;

Transactions

Transactions group multiple SQL statements into a single unit of work, ensuring atomicity.

BEGIN TRANSACTION;
-- Or just BEGIN;

UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 1;
UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 2;

-- If everything is okay:
COMMIT;

-- If something went wrong:
-- ROLLBACK;

Pragmas

PRAGMA commands are specific to SQLite and are used to modify its operation or query internal data.

-- Get schema information for a table
PRAGMA table_info(Users);

-- List all tables, indexes, etc.
SELECT name, type, sql FROM sqlite_master WHERE type='table';

-- Check foreign key constraints status (0 = off, 1 = on)
PRAGMA foreign_keys;

-- Enable foreign key constraints
PRAGMA foreign_keys = ON;

Tools

• SQLite Command-Line Shell: A standalone program (`sqlite3` or `sqlite3.exe`) that allows typing SQL commands directly.
• DB Browser for SQLite: A popular, free, open-source visual tool to create, design, and edit SQLite database files.
• SQLiteStudio: Another free, cross-platform SQLite database manager.
• Language Bindings/Libraries: Such as `Microsoft.Data.Sqlite` for C#/.NET, `sqlite3` module for Python, etc.

Using SQLite with C# (.NET)

In .NET applications, the `Microsoft.Data.Sqlite` library (available via NuGet) is commonly used to interact with SQLite databases.

using Microsoft.Data.Sqlite;
using System;

// Connection string points to the database file
string connectionString = "Data Source=mydatabase.db";

using (var connection = new SqliteConnection(connectionString))
{
    connection.Open();

    // Create a table
    var createCmd = connection.CreateCommand();
    // Escape the  symbol for Razor by using @
    createCmd.CommandText = @"CREATE TABLE IF NOT EXISTS Logs (
                                LogID INTEGER PRIMARY KEY AUTOINCREMENT,
                                Message TEXT,
                                Timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
                             );";
    createCmd.ExecuteNonQuery();

    // Insert data
    var insertCmd = connection.CreateCommand();
    insertCmd.CommandText = "INSERT INTO Logs (Message) VALUES ($message)";
    insertCmd.Parameters.AddWithValue("$message", "Application started.");
    insertCmd.ExecuteNonQuery();

    // Read data
    var selectCmd = connection.CreateCommand();
    selectCmd.CommandText = "SELECT Message, Timestamp FROM Logs ORDER BY Timestamp DESC LIMIT 10";

    using (var reader = selectCmd.ExecuteReader())
    {
        while (reader.Read())
        {
            Console.WriteLine($"Log: {reader.GetString(0)} at {reader.GetDateTime(1)}");
        }
    }
} // Connection is automatically closed here