Data · Rows, schemas and migrations

Invercargill-Sql

A generic SQL abstraction built on Invercargill data structures. Connections are provider-neutral, query results return as lazy Enumerable rows, and above that sits a migration system and an ORM with entity mappers and projections. SQLite is the engine beneath today.

Version: 0.1
Licence: LGPL-3.0-or-later
Package: usm install invercargill-sql
Language: Vala

Overview

Why it exists

Databases in Vala usually mean provider-specific APIs and eager row fetching. Invercargill-Sql takes the other road: a small public surface — ConnectionFactory, Connection, Command, Transaction — under which the concrete engine is an internal detail. Query results come back as Enumerable<Properties>, so a result set is not a materialised list you then re-shape but a lazy sequence you query with the same operations as everything else in the stack.

On that ground stands the rest of the data story: a fluent migration system that builds and versions schemas, an ORM that maps GObject classes onto tables with schema introspection, and projections that shape joins and aggregates into purpose-built read models. Spry's user and permission tables are defined here, as migrations.

Features

What it hands you

Generic engine interface. Abstract away specific SQL server implementations; SQLite ships as the first-class engine.
Lazy Enumerable rows. Query results return as Enumerable<Properties> — filter, project and cache them with Invercargill itself.
Connection strings. URI-style strings such as sqlite::memory: or sqlite:///db.sqlite?mode=ro, parsed into scheme, database and options.
Parameterized queries. A fluent API for type-safe parameter binding.
Transactions. Full commit/rollback management.
Async throughout. Every operation has an async variant for non-blocking I/O.
Migrations. A fluent builder for tables, columns, indexes and foreign keys, with dependency-aware ordering and up/down reversibility.
ORM. Entity mappers, sessions, typed queries and projections — including join and aggregate read models.

Installation

Getting the library

usm install invercargill
usm install invercargill-sql

USM resolves SQLite and the other build dependencies itself.

From source:

git clone <the Invercargill-Sql repository>
cd Invercargill-Sql
meson setup build
ninja -C build
ninja -C build install

The test suite runs with meson test -C build.

valac --pkg invercargill-sql my-program.vala

The binding pulls in its .deps chain automatically.

Usage & documentation

Connecting and querying

Use ConnectionFactory to create connections — no database-specific types in sight:

using InvercargillSql;

void main() throws SqlError {
    // Create and open a connection using a connection string
    var conn = ConnectionFactory.create_and_open("sqlite:///mydb.db");

    // Or create and open separately
    var conn2 = ConnectionFactory.create("sqlite:///another.db");
    conn2.open();

    // Execute commands...

    conn.close();
    conn2.close();
}

From the README. Connection strings also cover sqlite://./relative/path.db, sqlite::memory: and read-only or read-write modes via ?mode=.

DDL and inserts go through fluent commands with bound parameters:

// Create a table
conn.create_command("""
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        email TEXT
    )
""").execute_non_query();

// Insert data with parameters
conn.create_command("INSERT INTO users (name, email) VALUES (:name, :email)")
    .with_parameter("name", "Alice")
    .with_parameter("email", "alice@example.com")
    .execute_non_query();

From the README.

Query results are lazy — enumerate them, read columns by name, or keep querying:

var results = conn.create_command("SELECT * FROM users")
    .execute_query();

foreach (var row in results) {
    string? name  = row.get("name")?.as_string_or_null();
    string? email = row.get("email")?.as_string_or_null();
    int64?  id    = row.get("id")?.as<int64?>();

    print(@"User: $name ($email)\n");
}

// Results are Enumerable<Properties> — the whole toolkit applies
var activeUsers = results.where(row => row.get("active")?.as<bool>() ?? false);
var names       = results.select(row => row.get("name")?.as_string_or_null() ?? "unknown");
var cached      = results.cache();

From the README — the second half shows Invercargill's modifiers applied directly to a result set.

Transactions commit or roll back as a unit, and every operation has an async twin:

var txn = conn.begin_transaction();

try {
    conn.create_command("INSERT INTO users (name) VALUES ('Charlie')")
        .execute_non_query();
    conn.create_command("INSERT INTO users (name) VALUES ('Diana')")
        .execute_non_query();

    txn.commit();
} catch (SqlError e) {
    txn.rollback();
    warning("Transaction failed: %s", e.message);
}

From the README. Async: conn.open_async(), conn.execute_query_async(...), conn.close_async().

Migrations — schemas as code

Subclass Migration, declare a namespace, serial and name, and build your schema with the fluent builder:

using InvercargillSql;
using InvercargillSql.Migrations;

public class V001_CreateUsers : Migration {
    public override string migration_namespace { get { return "app"; } }
    public override uint64 serial { get { return 1; } }
    public override string name { get { return "CreateUsers"; } }

    public override void up(MigrationBuilder b) throws SqlError {
        b.create_table("users", t => {
            t.column<int64?>("id")
                .primary_key()
                .auto_increment();
            t.column<string>("name")
                .not_null();
            t.column<string>("email")
                .unique();
            t.column<int64?>("age");
            t.column<bool?>("is_active");

            // Create index on email for fast lookups
            t.index("idx_users_email").on_column("email");
        });
    }

    public override void down(MigrationBuilder b) throws SqlError {
        b.drop_table("users");
    }
}

Verbatim from examples/migrations/v001-create-users.vala.

var runner = new MigrationRunner(conn, dialect);
runner.register_migration(new V001_CreateUsers());
runner.register_migration(new V002_CreateProducts());
runner.register_migration(new V003_CreateOrders());

runner.migrate_to_latest();
var applied = runner.get_applied_migrations();

From examples/demo.vala — the runner orders migrations and remembers what is applied.

The ORM — entities, sessions, projections

Map a GObject class onto a table once; sessions do the rest:

public class User : Object {
    public int64 id { get; set; }
    public string name { get; set; }
    public string email { get; set; }
    public int64 age { get; set; }
    public bool is_active { get; set; }

    public static void configure_mapper(EntityMapperBuilder<User> b) {
        b.table("users")
            .column<int64?>("id", u => u.id, (u, v) => u.id = v)
            .column<string>("name", u => u.name, (u, v) => u.name = v)
            .column<string>("email", u => u.email, (u, v) => u.email = v)
            .column<int64?>("age", u => u.age, (u, v) => u.age = v)
            .column<bool?>("is_active", u => u.is_active, (u, v) => u.is_active = v);
    }
}

From examples/entities/user.vala — the mapper introspects the live schema at registration.

var registry = new TypeRegistry();
registry.register_with_schema<User>("users", conn, dialect, User.configure_mapper);
var session = new OrmSession(conn, registry, dialect);

// Insert
var alice = new User();
alice.name = "Alice";
alice.email = "alice@example.com";
session.insert(alice);

// Query with typed predicates and ordering
var users_over_25 = session.query<User>()
    .where(expr("age > $0", new NativeElement<int?>(25)))
    .materialise();

var active_users = session.query<User>()
    .where(expr("is_active == $0", new NativeElement<bool?>(true)))
    .order_by(expr("age"))
    .materialise();

// Update and delete
alice.age = 31;
session.update(alice);
session.delete(alice);

From examples/demo.vala — expr() builds parameterised predicates; pagination is .limit(2).offset(1).

Projections complete the ORM: purpose-built read models — simple, joined and aggregate — registered per type and queried through the same sessions, as UserSummary, OrderDetail and SalesReport in the demo show.

The repository's examples/demo.vala runs the whole story — migrations, registration, inserts, queries, updates, deletes, projections — against an in-memory database; its docs/ARCHITECTURE.md and plans/ directory record how the layered design was reached.

Package & source

The ledger

Version: 0.1 (from MANIFEST.usm)
Licence: LGPL-3.0-or-later — free to run, study, share and improve; the LGPL keeps it free to link from any program.
Package: usm install invercargill-sql
Provides: libinvercargill-sql.so, invercargill-sql.h, invercargill-sql.vapi + .deps, invercargill_sql-1.0.gir, invercargill_sql-1.0.typelib, invercargill-sql.pc
Needs at runtime: GLib, GObject, Gio, libinvercargill-1.so and libsqlite3.so.0.
Source: The repository Tilo15/Invercargill-Sql on the Astrologue forge (git over ssh, port 222). Free software: the licence guarantees your right to the source.