/DB

Quickstart

Declare a table, wire up the generated context, then insert, read, update, and delete rows through ISocigyDatabaseFactory.

updated 5 Jun 20264 min readv0.3.2View as Markdown

Prerequisites

  • .NET 10 SDK installed and on your PATH.
  • A running PostgreSQL 14+ instance, currently the only supported engine.
  • Socigy.OpenSource.DB added to your project. See Installation.

This quickstart uses the recommended context API: you inject a factory, run work inside a scope, and the connection is managed for you. For raw connection access see Connections & DI.

1. Declare a table

Annotate a partial class with [Table] and column attributes. The class must be partial so the source generator can augment it with query methods in a companion file.

using Socigy.OpenSource.DB.Attributes;

[Table("users")]
public partial class User
{
    [PrimaryKey, Default(DbDefaults.Guid.Random)]
    public Guid Id { get; set; }

    public string Username { get; set; } = "anonymous";

    public string? Email { get; set; }

    [Default(DbDefaults.Time.Now)]
    public DateTime CreatedAt { get; set; }
}
NOTE
A C# property initializer such as = "anonymous" is more than a C# default. The generator reads it and emits DEFAULT 'anonymous' in the DDL, so the database enforces the same default even for rows inserted outside your application.
NOTE
All schema attributes and the DbDefaults constants come from Socigy.OpenSource.DB.Attributes, a single using.

Build the project once to run the source generator:

dotnet build

2. Generate the schema

Build the model project in the DB_Migration configuration to produce the PostgreSQL DDL from your annotated classes:

dotnet build MyApp.DB/MyApp.DB.csproj -c DB_Migration

The migration for the User class above resolves to:

CREATE TABLE IF NOT EXISTS "users" (
    "id"         UUID      NOT NULL DEFAULT gen_random_uuid(),
    "username"   TEXT      NOT NULL DEFAULT 'anonymous',
    "email"      TEXT,
    "created_at" TIMESTAMP NOT NULL DEFAULT timezone('utc', now()),
    PRIMARY KEY ("id")
);

See CLI tool and Applying migrations for the full workflow.

3. Register the database and context

With databaseName: "AppDb" in socigy.json, the generator emits AddAppDb(), AddAppDbContext(), and EnsureLatestAppDbMigration(). Wire them up at startup:

using Socigy.OpenSource.DB.AppDb.Extensions;

var builder = WebApplication.CreateBuilder(args);

builder.AddAppDb();                  // registers IDbConnectionFactory
builder.Services.AddAppDbContext();  // registers ISocigyDatabaseFactory<IAppDb>

var app = builder.Build();

await app.EnsureLatestAppDbMigration();  // applies pending migrations on startup

The connection string lives in appsettings.json under ConnectionStrings:AppDb:Default. Do not include Database=, the factory appends it from databaseName:

{
  "ConnectionStrings": {
    "AppDb": {
      "Default": "Host=localhost;Port=5432;Username=postgres;Password=secret"
    }
  }
}

See Configuration for every field.

4. Inject the factory

Inject ISocigyDatabaseFactory<IAppDb> and run work inside a scope. ExecuteAsync opens a non-transactional scope, ExecuteTransactionAsync commits when the callback returns and rolls back if it throws. Table sets are pluralized: User becomes db.Users.

using Socigy.OpenSource.DB.AppDb.Context;
using Socigy.OpenSource.DB.Core.Context;

public class UserService(ISocigyDatabaseFactory<IAppDb> db)
{
    // ...
}

5. Insert a row

InsertAsync skips columns with a [Default] attribute or a C# initializer by default, letting PostgreSQL generate Id and CreatedAt:

await db.ExecuteAsync(d => d.Users.InsertAsync(
    new User { Username = "alice", Email = "[email protected]" }));

InsertAsync returns Task<bool>. To write the auto fields yourself (for example a pre-generated primary key), pass includeAutoFields: true:

await db.ExecuteAsync(d => d.Users.InsertAsync(
    new User { Id = Guid.NewGuid(), Username = "bob" },
    includeAutoFields: true));

Batch many rows with InsertMultipleAsync, which emits multi-row INSERT statements:

await db.ExecuteTransactionAsync(d => d.Users.InsertMultipleAsync(newUsers));

6. Read rows

ToListAsync materializes matching rows, and FirstOrDefaultAsync returns a single row or null. Both run inside the scope, so nothing lazy escapes the connection:

var alice = await db.ExecuteAsync(d =>
    d.Users.FirstOrDefaultAsync(u => u.Username == "alice"));

var recent = await db.ExecuteAsync(d =>
    d.Users.ToListAsync(u => u.CreatedAt > DateTime.UtcNow.AddDays(-7)));

Omit the predicate on ToListAsync to fetch every row. Use ExistsAsync and CountAsync for existence and tallies:

bool taken  = await db.ExecuteAsync(d => d.Users.ExistsAsync(u => u.Username == "alice"));
long total  = await db.ExecuteAsync(d => d.Users.CountAsync());

For streaming large result sets row by row, see ForEachAsync in Database context. For richer reads, see SELECT and WHERE.

7. Update a row

UpdateAsync matches on the primary key and returns the number of affected rows:

alice.Username = "alice-v2";
int updated = await db.ExecuteAsync(d => d.Users.UpdateAsync(alice));

See UPDATE for partial-column updates.

8. Delete rows

DeleteAsync takes a predicate and returns the number of deleted rows:

int deleted = await db.ExecuteAsync(d =>
    d.Users.DeleteAsync(u => u.Username == "alice-v2"));

See DELETE for more.

Next steps