# Quickstart

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

## 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](/database/0.3.2/getting-started/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](/database/0.3.2/core-concepts/connections-and-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.

```csharp
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:

```bash
dotnet build
```

---

## 2. Generate the schema

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

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

The migration for the `User` class above resolves to:

```sql
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](/database/0.3.2/migration/cli-tool) and [Applying migrations](/database/0.3.2/migration/applying) 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:

```csharp
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`:

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

See [Configuration](/database/0.3.2/getting-started/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`.

```csharp
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`:

```csharp
await db.ExecuteAsync(d => d.Users.InsertAsync(
    new User { Username = "alice", Email = "alice@example.com" }));
```

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

```csharp
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:

```csharp
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:

```csharp
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:

```csharp
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](/database/0.3.2/core-concepts/database-context). For richer reads, see [SELECT](/database/0.3.2/querying/reading/select) and [WHERE](/database/0.3.2/querying/reading/where).

---

## 7. Update a row

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

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

See [UPDATE](/database/0.3.2/querying/writing/update) for partial-column updates.

---

## 8. Delete rows

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

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

See [DELETE](/database/0.3.2/querying/writing/delete) for more.

---

## Next steps

- Build the same concepts into a full application in the [Tutorial](/database/0.3.2/tutorial/overview).
- Learn every modeling attribute in [Tables](/database/0.3.2/defining-models/tables) and [Columns](/database/0.3.2/defining-models/columns).
- Group multiple writes atomically with [Transactions](/database/0.3.2/core-concepts/transactions).
- Explore joins and set operations in [Joins](/database/0.3.2/querying/reading/joins) and [Set operations](/database/0.3.2/querying/reading/set-operations).
- Solve common problems with [Recipes](/database/0.3.2/recipes/audit-columns).