# INSERT

How to insert rows into a database table using the generated Insert() builder in Socigy.OpenSource.DB v0.2.0.

## Overview

Every generated entity instance exposes an `.Insert()` method that returns a fluent builder. You choose which fields to include, optionally request that DB-generated values be propagated back to the instance, then execute.

## Basic Insert

The simplest insert includes all columns, including any `[Default]` columns (those whose values the database generates on insert, such as `id`, `created_at`, or sequence numbers).

```csharp
// C# — insert a new user, including all fields
var user = new User { Id = Guid.NewGuid(), Name = "Alice", Status = "active", Score = 0 };

bool ok = await user.Insert()
    .WithConnection(conn)
    .WithAllFields()
    .ExecuteAsync();
```

## Field Selection Strategies

The builder supports five mutually exclusive strategies for choosing which columns appear in the `INSERT` statement. Pick exactly one per insert.

### ExcludeAutoFields — Skip All [Default] Columns

Omits every column marked `[Default]` (columns whose value the database supplies automatically). This is the most common strategy because it lets PostgreSQL handle `id`, `created_at`, sequences, etc.

```csharp
// C# — let the DB generate Id and CreatedAt
var user = new User { Name = "Bob", Status = "active", Score = 0 };

bool ok = await user.Insert()
    .WithConnection(conn)
    .ExcludeAutoFields()
    .ExecuteAsync();
```

### ExcludeAutoFields With Exceptions — Skip All [Default] Except Listed

Skips all `[Default]` columns **except** those you list explicitly. Useful when you want to override one default (e.g., supply your own `Id`) while still letting the database control others.

```csharp
// C# — supply Id manually; let DB handle CreatedAt
var user = new User { Id = Guid.NewGuid(), Name = "Carol", Status = "active" };

bool ok = await user.Insert()
    .WithConnection(conn)
    .ExcludeAutoFields(x => new object[] { x.Id })
    .ExecuteAsync();
```

### WithAllFields — Include Everything

Forces all columns (including `[Default]` ones) into the `INSERT`. Use when you want to supply every value yourself.

```csharp
// C# — insert with all fields, including Id and timestamps
var user = new User
{
    Id = Guid.NewGuid(),
    Name = "Dave",
    Status = "active",
    CreatedAt = DateTimeOffset.UtcNow
};

bool ok = await user.Insert()
    .WithConnection(conn)
    .WithAllFields()
    .ExecuteAsync();
```

> **ERROR** `WithAllFields()` and `ExcludeAutoFields()` cannot be used in the same call chain. Combining them is a programming error and will throw at runtime.

### WithFields — Include Only Listed Columns

Inserts only the columns you specify. Every other column is omitted from the statement.

```csharp
// C# — insert only Name and Status
var user = new User { Name = "Eve", Status = "active" };

bool ok = await user.Insert()
    .WithConnection(conn)
    .WithFields(x => new object[] { x.Name, x.Status })
    .ExecuteAsync();
```

### ExcludeFields — Exclude Listed Columns

Includes all columns except those you specify. The inverse of `WithFields`.

```csharp
// C# — insert everything except AuditLog
bool ok = await user.Insert()
    .WithConnection(conn)
    .ExcludeFields(x => new object[] { x.AuditLog })
    .ExecuteAsync();
```

## Value Propagation (RETURNING *)

`.WithValuePropagation()` appends `RETURNING *` to the SQL statement. The database returns the complete row after insert (with all generated values filled in), and the library writes those values back into the C# instance. This is the cleanest way to obtain `Id`, `CreatedAt`, sequence numbers, and any other DB-generated columns after an insert.

```csharp
// C# — write generated values back to the instance
var user = new User { Name = "Frank", Status = "active" };

bool ok = await user.Insert()
    .WithConnection(conn)
    .ExcludeAutoFields()
    .WithValuePropagation()
    .ExecuteAsync();

// user.Id and user.CreatedAt are now populated from the database
Console.WriteLine(user.Id);
Console.WriteLine(user.CreatedAt);
```

## Returning a Specific Column Value

When you only need one DB-generated value (the most common case being the new primary key), use `ExecuteReturningAsync<T>()` instead of `ExecuteAsync()`.

```csharp
// C# — return the generated Id
var user = new User { Name = "Grace", Status = "active" };

Guid? newId = await user.Insert()
    .WithConnection(conn)
    .ExcludeAutoFields()
    .ExecuteReturningAsync<Guid>("id");

if (newId is not null)
    Console.WriteLine($"Created user {newId}");
```

The string argument is the column name in the database (snake_case), not the C# property name.

## Static Shorthand

`User.InsertAsync(instance, conn)` is a static convenience method that inserts the row using a plain connection. It is the quickest path when you do not need field selection or value propagation.

```csharp
// C# — static shorthand
bool ok = await User.InsertAsync(user, conn);
```

> **NOTE** The static shorthand does not expose field selection or value propagation options. If you need `ExcludeAutoFields`, `WithValuePropagation`, or any other option, use the instance builder instead.

## Using With Transactions

Pass a `DbTransaction` instead of a `DbConnection` when the insert is part of a larger unit of work.

```csharp
// C# — insert inside a transaction
await using var tx = await conn.BeginTransactionAsync();

bool ok = await user.Insert()
    .WithTransaction(tx)
    .ExcludeAutoFields()
    .WithValuePropagation()
    .ExecuteAsync();

await tx.CommitAsync();
```

## Bulk Insert — `InsertMultipleAsync`

To insert a whole collection, use the static `InsertMultipleAsync` instead of a command per row. It builds **batched multi-row `INSERT ... VALUES (…),(…),…` commands** — one command per chunk — which is dramatically faster than looping.

```csharp
// C# — insert many rows in one (or a few) round-trips
var users = new List<User>
{
    new() { Name = "Alice", Status = "active" },
    new() { Name = "Bob",   Status = "active" },
    // ... thousands more ...
};

int inserted = await User.InsertMultipleAsync(users, conn);
```

- Returns the **total number of rows inserted**.
- Uses the entity's default insert plan, so **auto-increment / `[Default]` sequence columns are skipped** (the database generates them) — same as `ExcludeAutoFields()`.
- Automatically **chunks** so each command stays under PostgreSQL's 65,535-parameter limit (`maxRowsPerBatch ≈ 65535 / columnCount`); a 10,000-row insert of a 6-column table becomes a handful of commands rather than 10,000.
- `[Encrypted]` and JSON columns are handled (bound as `bytea` / `jsonb`).
- Each command is traced/logged through the diagnostics pipeline as a single `INSERT`.

Pass a transaction to make the whole batch atomic with other work:

```csharp
await using var tx = await conn.BeginTransactionAsync();
await User.InsertMultipleAsync(users, conn, tx);
await tx.CommitAsync();
```

> **NOTE** `InsertMultipleAsync` uses the default insert plan (no per-row field selection or value propagation). If you need `WithValuePropagation` or custom field selection, use the per-instance `.Insert()` builder.

> **TIP** For extreme volumes (hundreds of thousands of rows) PostgreSQL's binary `COPY` protocol via `NpgsqlBinaryImporter` is still the throughput champion; `InsertMultipleAsync` is the sweet spot for everyday batches up to tens of thousands.

## See Also

- [UPDATE](update) — updating existing rows
- [DELETE](delete) — removing rows
- [SELECT / Query](select) — reading rows