# 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 Pattern

For bulk inserts, open a single transaction and loop over instances. All inserts share the same transaction and are committed atomically.

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

foreach (var user in usersToImport)
{
    await user.Insert()
        .WithTransaction(tx)
        .ExcludeAutoFields()
        .ExecuteAsync();
}

await tx.CommitAsync();
```

> **TIP** For very large bulk inserts (thousands of rows), consider using PostgreSQL's `COPY` protocol via `NpgsqlBinaryImporter` for maximum throughput. The builder-based loop above is appropriate for up to a few hundred rows.

## See Also

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