Database-first scaffolding

Overview

The library is code-first by default: you annotate C# classes and the generator produces the data layer and migrations. For an existing database, the CLI tool can work in the other direction — reading a live schema and scaffolding it back into the same model the analyzer understands.

NEW in 0.3.3

Two sub-commands of the migration tool cover this:

Command	Input	Output
scaffold schema	A live PostgreSQL connection	A structure.json schema manifest
scaffold classes	A live connection or a structure.json	Annotated [Table] C# classes

Both reuse the same schema model and SQL generator as the code-first pipeline, so the result round-trips: scaffolded classes, recompiled and run back through generate, reproduce the same schema.

scaffold schema

Read a database and write a structure.json:

socigydb scaffold schema \
  --connection "Host=localhost;Username=postgres;Password=...;Database=app" \
  --schema public \
  --output ./structure.json

Option	Description
--connection	PostgreSQL connection string (required)
--schema	Database schema to read (default public)
--output	Output path (default ./structure.json)

scaffold classes

Generate annotated C# classes, either directly from a database or from a previously-written structure.json:

# From a live database
socigydb scaffold classes \
  --connection "Host=localhost;Username=postgres;Password=...;Database=app" \
  --output ./Models \
  --namespace MyApp.Data

# From an existing schema manifest
socigydb scaffold classes \
  --from-schema ./structure.json \
  --output ./Models \
  --namespace MyApp.Data

Option	Description
--connection	PostgreSQL connection string (omit when using --from-schema)
--from-schema	Read from an existing structure.json instead of a live database
--schema	Database schema to read (default public)
--output	Output directory for the generated classes (required)
--namespace	Namespace for the generated classes (default: derived from the output directory)

One {ClassName}.cs file is written per table.

What is recovered

The reader maps PostgreSQL metadata back to the attributes the analyzer reads, including:

• Tables and columns, with CLR types inferred from the SQL types.
• Primary keys ([PrimaryKey]) and auto-increment / identity columns ([AutoIncrement]).
• NOT NULL → non-nullable C# types; nullable columns → T?.
• varchar(n) length → [StringLength(n)].
• Recognized defaults mapped back to their DbDefaults token — for example gen_random_uuid() → [Default(DbDefaults.Guid.Random)], timezone('utc', now()) → [Default(DbDefaults.Time.Now)]. Unrecognized defaults are preserved verbatim.
• jsonb columns → [RawJsonColumn].
• Foreign keys → [ForeignKey(typeof(Target), Keys = [...], TargetKeys = [...])], including the ON DELETE action.
• A [Column("…")] override is emitted only when the database column name does not match the default snake_case of the property, keeping the output clean.

Limitations

Some information cannot be reconstructed from the database alone and is scaffolded conservatively:

• [Encrypted] columns are stored as bytea, indistinguishable from a genuine byte[] column. They scaffold as byte[]; re-apply [Encrypted] by hand where appropriate.
• Enum-backed columns scaffold as their underlying primitive.
• CHECK constraints and database enum types are not yet scaffolded.

Treat scaffolding as a starting point: review the generated classes, then continue code-first from there.