Invercargill-Sql

Invercargill-Sql

A Vala SQL library with tight integration into the Invercargill ecosystem, providing a generic database abstraction layer with lazy enumeration support.

Features

• Generic Engine Interface: Abstract away specific SQL server implementations
• SQLite & PostgreSQL Support: First-class SQLite and PostgreSQL implementations; the ORM, migrations and projections run unchanged over either backend
• Invercargill Integration: Query results returned as Enumerable<Properties> for lazy enumeration
• Parameterized Queries: Fluent API for type-safe parameter binding
• Async Support: All operations have async variants for non-blocking I/O
• Transaction Support: Full transaction management with commit/rollback
• Connection String Support: Create connections using URI-style connection strings

Dependencies

• glib-2.0
• gobject-2.0
• gio-2.0
• invercargill-1
• sqlite3 (internal dependency only)
• libpq (PostgreSQL client library, for the postgresql:// adapter)

Building

meson setup build
meson compile -C build

Running Tests

meson test -C build

Database-backed tests run twice: over in-memory SQLite (always) and over PostgreSQL when a server is reachable. The PostgreSQL runs go through scripts/run-postgres-tests.sh, which brings up (or reuses) a local server on socket /tmp/pgrun, port 5433, and skips (exit 77) when it cannot, so default builds stay green without PostgreSQL. Point the suites at another server with PG_TEST_CONN, e.g. PG_TEST_CONN='postgresql://user:pass@db.example.com:5432/mydb' meson test -C build.

Each PostgreSQL test program isolates itself in a disposable schema (t_<seed>_<n>), so programs can share one server; leftover schemas are cleaned up on the next run. The ORM, migration and projection suites are backend-parameterised through src/tests/test-backend.vala (TestBackendContext), exercising the same test code against both dialects.

The ORM demo accepts a connection string argument and picks its dialect from the scheme, e.g. ./build/examples/orm-demo "postgresql://postgres@/sqltest?host=/tmp/pgrun&port=5433".

Installation

meson install -C build

Usage

Basic Connection

Use ConnectionFactory to create connections - no need to reference any database-specific types:

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();
}

Connection String Formats

The library supports URI-style connection strings:

// SQLite connections
"sqlite:///absolute/path/to/db.sqlite"     // Absolute path
"sqlite://./relative/path.db"              // Relative path
"sqlite::memory:"                          // In-memory database
"sqlite://:memory:"                        // In-memory database (URI form)
"sqlite:///db.sqlite?mode=ro"              // Read-only mode
"sqlite:///db.sqlite?mode=rw"              // Read-write (must exist)
"sqlite:///db.sqlite?mode=rwc"             // Read-write, create if needed (default)

PostgreSQL Connections

The library also ships a PostgreSQL adapter built on libpq (scheme postgresql, with postgres accepted as an alias). Both providers are registered automatically — no extra setup is needed:

// TCP connection
var conn = InvercargillSql.ConnectionFactory.create_and_open(
    "postgresql://user:password@localhost:5432/mydb"
);

// Unix socket directory via the host option
var conn = InvercargillSql.ConnectionFactory.create_and_open(
    "postgresql://user@/run/postgresql/mydb?host=/run/postgresql"
);

// Short scheme alias, explicit socket dir and port
var conn = InvercargillSql.ConnectionFactory.create_and_open(
    "postgres://postgres@/mydb?host=/tmp/pgrun&port=5433"
);

// Extra libpq options (connect_timeout, sslmode, application_name, ...)
var conn = InvercargillSql.ConnectionFactory.create_and_open(
    "postgresql://user@db.example.com:5432/mydb?sslmode=require&connect_timeout=10"
);

Notes on the adapter:

• Components left out of the URI are omitted from the conninfo handed to libpq, so the usual environment defaults (PGHOST, PGPORT, PGUSER, ~/.pgpass) still apply.
• Commands keep the same :name placeholder style as SQLite; the adapter translates them to libpq $n positions at execution time. Placeholders inside string literals, quoted identifiers, dollar-quoted strings and comments are ignored, as are PostgreSQL ::type cast operators.
• Values are bound in text format. Booleans bind as t/f, DateTime as ISO 8601 text, and Invercargill.BinaryData as bytea hex (\x...), so bytea columns round-trip without casts.
• connection.last_insert_rowid is backed by SELECT lastval(), valid after any INSERT that used a sequence on that connection.
• DialectFactory also ships a PostgreSQL dialect: DialectFactory.create("postgresql") (or "postgres") returns a shared PostgresDialect instance that generates named-parameter CRUD SQL, identity-column DDL, information_schema introspection and standard projection SELECTs. See src/dialects/postgres-dialect.vala.

Creating Tables and Inserting Data

// 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();

Querying Data

// Execute a query - results are lazily enumerated
var results = conn.create_command("SELECT * FROM users")
    .execute_query();

// Enumerate results
foreach (var row in results) {
    // Access columns by name using the Properties interface
    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");
}

Parameterized Queries

The library supports fluent parameter binding:

// Chain parameter bindings
var cmd = conn.create_command(
    "SELECT * FROM users WHERE name = :name AND active = :active"
);
cmd.with_parameter("name", "Bob")
   .with_parameter("active", true);

var results = cmd.execute_query();

Transactions

// Start a transaction
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();
    
    // Commit if all succeeds
    txn.commit();
} catch (SqlError e) {
    // Rollback on error
    txn.rollback();
    warning("Transaction failed: %s", e.message);
}

Async Operations

All database operations have async variants:

async void query_async(Connection conn) throws SqlError {
    // Open connection asynchronously
    yield conn.open_async();
    
    // Execute query asynchronously
    var results = yield conn.execute_query_async(
        "SELECT * FROM users WHERE active = :active",
        props => props.set_native("active", true)
    );
    
    foreach (var row in results) {
        print(@"Found: %s\n", row.get("name")?.as_string_or_null() ?? "null");
    }
    
    // Close asynchronously
    yield conn.close_async();
}

Working with Results

Results are returned as Enumerable<Properties>, giving you access to all Invercargill LINQ-style operations:

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

// Filter results
var activeUsers = results.where(row => row.get("active")?.as<bool>() ?? false);

// Project to different types
var names = results.select(row => row.get("name")?.as_string_or_null() ?? "unknown");

// Cache results to prevent re-enumeration
var cachedResults = results.cache();

// Convert to array
var array = results.to_array();

Architecture

The library follows a layered architecture with clean separation between public interfaces and internal implementations:

┌─────────────────────────────────────────┐
│           User Application               │
├─────────────────────────────────────────┤
│         Public API Layer                 │
│   ConnectionFactory  ConnectionString    │
│   Connection  Command  Transaction       │
│   ConnectionFlags  SqlError              │
├─────────────────────────────────────────┤
│         Internal Implementation          │
│   SqliteProvider  SqliteConnection       │
│   SqliteCommand  SqliteResultEnumerable  │
│   PostgresProvider  PostgresConnection   │
│   PostgresCommand  PostgresResult...     │
├─────────────────────────────────────────┤
│         Native Drivers                   │
│   SQLite (via VAPI)   libpq (Postgres)   │
└─────────────────────────────────────────┘

Key Public Interfaces

• ConnectionFactory: Static factory for creating connections from connection strings
• ConnectionString: Parsed connection string with scheme, host, database, and options
• Connection: Database connection management
• Command: SQL command execution with parameter binding
• Transaction: Transaction lifecycle management
• ConnectionFlags: Database-agnostic connection flags
• SqlError: Error domain for all SQL-related errors

Internal Implementations

The following classes are internal to the library and should not be referenced directly:

• SqliteProvider: SQLite connection provider
• SqliteConnection: SQLite-specific connection handling
• SqliteCommand: SQLite statement wrapper with parameter binding
• SqliteResultEnumerable: Lazy enumeration over SQLite result sets
• SqliteTransaction: SQLite transaction management
• PostgresProvider: PostgreSQL connection provider (libpq)
• PostgresConnection: PostgreSQL-specific connection handling
• PostgresCommand: libpq command wrapper translating :name to $n
• PostgresResultEnumerable: Materialised enumeration over PGresult
• PostgresTransaction: PostgreSQL transaction management

Error Handling

The library uses SqlError for all error conditions:

public errordomain SqlError {
    CONNECTION_FAILED,       // Failed to connect to database
    CONNECTION_CLOSED,       // Operation on closed connection
    INVALID_CONNECTION_STRING, // Malformed connection string
    PREPARATION_FAILED,      // SQL syntax error
    EXECUTION_FAILED,        // Runtime execution error
    INVALID_PARAMETER,       // Invalid parameter name or type
    TRANSACTION_FAILED,      // Transaction error
    TYPE_ERROR,              // Type conversion error
    GENERAL_ERROR            // Other errors
}