SQLite

How does SQLite work? Part 1 | Part 2

SQLite Tutorial

How the SQLite VM works

Swift/iOS

By default, SQLite from Swift/iOS is an exercise in C integration. Numerous wrappers are available.

SQLite.swift: Type-safe Swift wrapper over sqlite3 C API
Sqift: Powerful Swift wrapper for SQLite

Java

SQLite-jdbc: Straight JDBC driver for SQLite

C#/.NET

Microsoft.Data.Sqlite: dotnet add package Microsoft.Data.Sqlite

using (var connection = new SqliteConnection("Data Source=hello.db"))
{
    connection.Open();

    var command = connection.CreateCommand();
    command.CommandText =
    @"
        SELECT name
        FROM user
        WHERE id = $id
    ";
    command.Parameters.AddWithValue("$id", id);

    using (var reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            var name = reader.GetString(0);

            Console.WriteLine($"Hello, {name}!");
        }
    }
}

Native

CG/SQL is a code generation system for the popular SQLite library that allows developers to write stored procedures in a variant of Transact-SQL (T-SQL) and compile them into C code that uses SQLite’s C API to do the coded operations. CG/SQL enables engineers to create highly complex stored procedures with very large queries, without the manual code checking that existing methods require. (Produces C source that can be compiled, and the language is flexible enough to do a fair amount of typical C dev without leaving CQL.)

Docs
Language Guide
Internals Part 1, Part 2: An interesting look into how it works
Source

Plugins/additions/enhancements

Marmot: A distributed SQLite replicator built on top of NATS.

SQLCypher is an extension to SQLite adding encryption. Open-source and commercial.

A fast SQLite PWA notebook for CSV files (Source, uses sql.js (The database is a web-assembly version of SQLite called sql.js that runs in your browser.), VSV (It also uses a SQLite extension called vsv to load delimited text files quickly.), Codemirror (Syntax highlighting), sheet.js (Excel and Openoffice support.), chart.js (Charts), and LocalForage (IndexedDB for efficient local storage.) under the hood.)

SQLStudio (Source): A free, open source, multi-platform SQLite database manager.

WS4SQLite: Query sqlite via http

Interesting articles

"A future for SQL on the Web": Using IndexedDB in the browser as a "file system" underneath a SQLite implementation compiled to WASM. Resulting project is absurd-sql. Makes use of sql.js and a few source changes to it.

"Bashing JSON into Shape with SQLite": "SQLIte added JSON functions to allow you to munge and modify JSON data in whatever creative ways you want."

"JSON improvements in SQLite 3.38.0"

"Experimenting With SQLite in iOS"

Tags: storage relational embedded native library

Last modified 13 January 2024