/DB

Quickstart

Declare a table, insert a row, query it back, update it, and delete it — the full first-use experience from scratch.

updated 3 May 20263 min readv0.1.82

Prerequisites

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

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.Core.Attributes;
using Socigy.OpenSource.DB.Core.Values;

[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 (e.g. = "anonymous") is not just a C# default — the source generator reads it and emits DEFAULT 'anonymous' in the DDL for that column. This means the database enforces the same default even for rows inserted outside your application.

Build the project once to trigger the source generator:

dotnet build

After a successful build, User.Insert(), User.Query(), User.Update(), and User.Delete() are available as if you had written them yourself.

2. Create the table in PostgreSQL

Run this DDL against your database. The CLI tool generates this automatically from your annotated classes — shown here manually for clarity:

CREATE TABLE "users" (
    "id"         UUID        NOT NULL DEFAULT gen_random_uuid(),
    "username"   TEXT        NOT NULL DEFAULT 'anonymous',
    "email"      TEXT,
    "created_at" TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY ("id")
);

3. Open a connection

using Npgsql;

await using var conn = new NpgsqlConnection(
    "Host=localhost;Database=myapp;Username=postgres;Password=secret");
await conn.OpenAsync();

Pass this open connection to every command builder with .WithConnection(conn).

4. Insert a row

With ExcludeAutoFields

ExcludeAutoFields() skips all columns that carry a [Default] attribute or a C# initializer. This lets PostgreSQL generate Id and CreatedAt on the server side:

var user = new User { Username = "alice", Email = "[email protected]" };

await user.Insert()
    .WithConnection(conn)
    .ExcludeAutoFields()   // omits Id and CreatedAt — generated by the DB
    .ExecuteAsync();

With WithValuePropagation

If you want the DB-generated values (Id, CreatedAt) written back into the C# object after the insert, chain WithValuePropagation():

var user = new User { Username = "alice", Email = "[email protected]" };

await user.Insert()
    .WithConnection(conn)
    .ExcludeAutoFields()
    .WithValuePropagation()   // populates user.Id and user.CreatedAt from the RETURNING clause
    .ExecuteAsync();

Console.WriteLine(user.Id);        // now set to the UUID the DB generated
Console.WriteLine(user.CreatedAt); // now set to the server-side NOW()

Without ExcludeAutoFields

When you pre-generate the primary key in C#, omit ExcludeAutoFields() to send all columns:

var user = new User
{
    Id       = Guid.NewGuid(),
    Username = "bob",
};

await user.Insert().WithConnection(conn).ExecuteAsync();

5. SELECT rows

User.Query() returns an async-enumerable stream. Supply a LINQ expression for the WHERE clause:

await foreach (var u in User.Query(x => x.Username == "alice")
    .WithConnection(conn)
    .ExecuteAsync())
{
    Console.WriteLine($"{u.Id}: {u.Username}");
}

Omit the predicate to fetch all rows:

await foreach (var u in User.Query().WithConnection(conn).ExecuteAsync())
{
    Console.WriteLine(u.Username);
}

6. Update selected fields

Use .WithFields() to restrict the UPDATE to specific columns. The generated UPDATE always uses the primary key in its WHERE clause:

user.Username = "alice-v2";

await user.Update()
    .WithConnection(conn)
    .WithFields(x => new object[] { x.Username })  // UPDATE only the username column
    .ExecuteAsync();

To update all non-primary-key columns, omit .WithFields():

await user.Update().WithConnection(conn).ExecuteAsync();

7. Delete a row

Delete() uses the primary key column(s) for the WHERE clause automatically:

await user.Delete()
    .WithConnection(conn)
    .ExecuteAsync();

Next steps