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.
• Indexes → [Index], with their uniqueness, partial filter, covering columns, and sort order. Single-column indexes are emitted on the property, composite ones on the class. The access method comes back as a portable DbIndexMethods constant where one fits, and as RawMethod otherwise. Indexes that implement a primary key or UNIQUE constraint are skipped, since those are already recovered as constraints.
• 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.
• An index name is emitted only when it differs from the name the migration generator would derive, for the same reason.

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.
• Expression indexes (CREATE INDEX ... ON t ((lower(email)))) have no [Index] form. They are reported by name and left out, so re-create them by hand; leaving them undeclared means the next generated migration drops them.

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