# Quickstart

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

## 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](/database/0.3.0/getting-started/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.

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

```bash
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](/database/0.3.0/migration/cli-tool) generates this automatically from your annotated classes — shown here manually for clarity:

```sql
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

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

```csharp
var user = new User { Username = "alice", Email = "alice@example.com" };

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()`:

```csharp
var user = new User { Username = "alice", Email = "alice@example.com" };

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:

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

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

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

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

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

---

## 7. Delete a row

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

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

---

## Next steps

- Learn every attribute available for defining models in [Tables & classes](/database/0.3.0/defining-models/tables) and [Columns & types](/database/0.3.0/defining-models/columns).
- Explore joins and set operations in [Joins](/database/0.3.0/querying/joins) and [Set operations](/database/0.3.0/querying/set-operations).
- Set up the migration CLI to generate DDL from your C# classes automatically in [CLI tool](/database/0.3.0/migration/cli-tool).
- Configure DI and connection factory generation in [Configuration](/database/0.3.0/getting-started/configuration).