Documentation
Inspect and export database schemas
On this page
Schema inspection reads catalogs into the existing schema domain model. The same application service backs the CLI and JSON-RPC worker. Discovery is independent of SQL rendering: a dialect can render schemas without a registered discovery reader.
CLI workflow
Add --read-only to require the shared native read-only access mode on supported file routes. Unsupported routes fail before inspection; --allow-partial cannot relax this requirement. See read-only connections for the provider matrix and the difference from transaction defaults or read-replica routing.
Use a named connection from the existing ConnectionStrings configuration, or an environment variable containing the URL. Specify exactly one selector; URLs are not command-line arguments.
duburl schema inspect --connection-env DATABASE_URL --table public.customer --output schema.yaml
duburl schema inspect --connection source --table public.customer --table public.orders --include-references --output schema.json
duburl schema inspect --connection source --basic --output-format json
duburl schema validate schema.yaml
duburl schema render schema.yaml --dialect pgsql --output schema.sql
# Optional: deploy only after reviewing the generated SQL.
duburl schema deploy schema.yaml --connection destination
Omitting –table selects all visible user tables. Repeat –table or –schema for multiple literal identifiers. Table identifiers support up to four components (catalog.database.schema.name); quote components containing dots or spaces with SQL double quotes, square brackets, or backticks. Comparisons are ordinal and case sensitive. No user identifier is inserted into a catalog SQL query. Readers collect visible metadata and the inspector applies filters to structured identities.
The default inspection requests every feature. –basic requests tables, columns, primary keys and foreign keys only. –include-references includes the transitive closure of visible foreign-key target tables, even outside the filters. Without it, keys targeting excluded tables are omitted with SDI005 and the result is incomplete. Unavailable targets also produce incomplete results. Missing selected tables produce SDI004; absence cannot distinguish a nonexistent object from one hidden by catalog visibility.
| –output writes only the document to a file; otherwise only document bytes go to stdout. –output-format yaml | json takes precedence over the filename. Without it, .yaml/.yml/.json determine the file format; unknown extensions are rejected. Stdout defaults to YAML. Diagnostics always go to stderr. |
Strict export is the default: incomplete inspection does not write a document. –allow-partial deliberately permits an incomplete export and still prints diagnostics and returns exit code 7. Existing output files are untouched when discovery or conversion fails. Exit codes are 0 for complete export, 2 for invalid input, 7 for incomplete inspection, 8 for unsupported discovery, 9 for inspection/export failure, and 130 for cancellation. A failed strict request containing an unsupported requested feature returns 8; other incomplete strict requests return 7.
A document has no discovery-completeness assertion. Retain application/worker results and diagnostics alongside exported documents. A partial document must not be treated as a complete database specification.
Public extension contracts
Core contains DubUrl.Discovery metadata records and ISchemaMetadataReader. It references neither DubUrl.Schema nor concrete drivers. A custom provider can implement the reader and attach new SchemaDiscoveryCapability(reader) through ProviderRegistration.WithCapabilities, or annotate its mapper with SchemaDiscovery(typeof(MyReader)). Attributes apply to the explicit mapper only; sharing a driver/protocol does not opt other databases into catalog compatibility. Native connector packs load these same provider-owned readers.
The reader receives an open, borrowed DbConnection, options, and cancellation token. It must issue only metadata reads and must not close/dispose the connection. Capabilities describes support before inspection; SchemaMetadata.Coverage and Diagnostics describe the actual result. ConfigureOwnedConnection can restrict a closed, owned connection before opening it. SQLite uses this to force read-only mode, avoiding accidental creation of missing database files.
SchemaInspector owns public inspection and mapping. Its ConnectionUrl overload creates/opens/disposes the connection on success, failure and cancellation. Its DbConnection overload requires an already-open connection and leaves it owned by the caller. ISchemaInspectionService/SchemaInspectionService adds domain-to-v1 document conversion. Both overloads propagate cancellation. Provider exception text is replaced with stable, credential-free diagnostics; provider extension authors must keep their own returned metadata diagnostics free of secrets.
MetadataCoverage distinguishes Available, Unsupported, Inaccessible, Absent and Unrepresentable. Only available/absent feature coverage with no loss diagnostics is complete. Column and key order is preserved; tables, indexes, constraints, and views are ordered deterministically using ordinal identities/names. Unknown logical types are rejected instead of guessed.
Inspection maps UNIQUE declarations to table-level UniquenessConstraint objects with the name reported by the reader and the columns in declared order. The v1 exporter writes these as uniqueConstraints, including any explicit NULL policy reported by the reader. Otherwise the constraint uses native NULL behavior. Independent indexes stay separate. When a reader also reports a backing index, inspection omits it from the independent index list only if the reader confirms its ownership. Inspection reports SDI010 for ambiguous ownership or when a unique index on the same columns as a constraint with an explicit policy may enforce different NULL behavior.
Stable inspection codes:
| Code | Meaning |
|---|---|
| SDI001 | Provider has no discovery capability |
| SDI002 | Metadata or connection is inaccessible |
| SDI003 | Requested feature has incomplete coverage |
| SDI004 | Selected table is missing or invisible |
| SDI005 | Foreign-key target lies outside the selected/visible model |
| SDI007 | Metadata cannot form a valid domain schema |
| SDI008 | View definition is not a portable projection |
| SDI009 | Catalog construct is not representable |
| SDI010 | Domain schema cannot be exported losslessly to v1 |
| SDI011 | Catalog default is unknown or incompatible with the destination |
| SDI012 | Structured type metadata is incomplete or unrepresentable |
Inspection runs several catalog queries and does not establish a transaction or a database-wide snapshot. Concurrent DDL may cause inconsistent results and validation failure. For consistent exports, inspect a stable database or supply a borrowed connection with an appropriate caller-managed snapshot/transaction environment. Catalog permission filtering can conceal objects entirely; completeness is relative to visible objects and requested features, not proof of unrestricted database access.
Provider coverage and limits
| Provider | Initial coverage | Additional metadata and limits |
|---|---|---|
| SQL Server | User tables, ordered columns/facets/nullability, primary/foreign keys | Literal defaults, identity seed/increment, simple generated expressions, ordered B-tree indexes, simple comparison checks, MS_Description comments, view inventory/simple projections. Filtered/included/specialized indexes are diagnosed. |
| PostgreSQL | Ordinary/partitioned user tables, ordered columns/facets/nullability, primary/foreign keys | Literal defaults, identity seed/increment, simple generated expressions, B-tree indexes, simple comparison checks, comments, view inventory/simple projections. Expression/partial/included/non-B-tree indexes are diagnosed. Other databases using Npgsql are not automatically qualified. |
| DuckDB | Current database user tables, ordered columns/facets/nullability, primary/foreign keys | Literal defaults, dedicated sequence-backed identities, representable virtual generated expressions, ordinary/unique secondary indexes in column order, simple checks, comments, and view inventory/simple projections. DuckDB does not retain index ASC/DESC directions; discovery leaves direction unspecified. Foreign keys render inline with dependency-ordered tables; cycles are rejected. |
| SQLite | User tables in main and databases attached to the inspected connection, ordered columns/declared facets/nullability, primary/foreign/unique keys | Literal defaults, ordinary indexes/directions, representable checks and generated expressions, and view inventory/simple projections. Foreign-key names are synthesized. SQLite lacks native table and column comments. |
SQLite uses main and each attachment alias as the database qualifier. For example, main.customer and archive.customer remain separate objects. To inspect attachments, run ATTACH DATABASE on an open connection and pass that same connection to SchemaInspector.InspectAsync. Use DiscoveryOptions.Databases to filter by alias or set the Database field on a table filter. When deploying the rendered SQL on another connection, attach the destination databases under the same aliases first.
SQLite reports UNIQUE names through its backing indexes. A declaration such as CONSTRAINT uq_customer UNIQUE (...) can therefore appear in the export with a generated name such as sqlite_autoindex_customer_1.
Expression and partial indexes, unsupported types, arbitrary SQL expressions and arbitrary views produce diagnostics. Discovery recognizes literal expressions and a limited set of column and function expressions. Portable views support simple single-source projections with optional AS aliases. Unsupported views retain their identities and column inventories in SchemaInspectionResult.ViewInventory.
Native types are validated through NativeDatabaseType and remain dialect scoped when discovered as native overrides (for example PostgreSQL JSONB). v1 cannot encode explicit native dialect scopes: export rejects such domain schemas with SDI010 instead of widening their scope or changing the document contract. Logical mappings of ordinary scalar types are portable approximations; source vendor storage details beyond the model are not a promise of identical physical DDL. SQL Server unbounded column facets are diagnosed.
Providers can implement IStructuredColumnMetadataReader to return structured evidence with the same catalog snapshot, keyed by qualified table and literal column name. When the shape and storage are known, the structured descriptor takes precedence over the catalog’s scalar type or native hint. A known descriptor with unknown nested nullability is retained with SDI012 and requires partial inspection to succeed. If the shape or storage is unknown, inspection fails with SDI012 because the column cannot be represented faithfully. Coverage reports what the catalog revealed. The descriptor does not certify database enforcement.
Use least-privilege metadata visibility for inspection: SQL Server catalog visibility/VIEW DEFINITION as appropriate, PostgreSQL catalog and information_schema visibility for the selected objects, DuckDB local catalog access, or SQLite read permission. Fixture creation/deployment requires extra DDL permissions only in disposable QA databases.
Offline export
var document = new SchemaDocumentExporter().Map(schema);
using var stream = File.Create("schema.json");
new SchemaDocumentWriter().Write(stream, document, SchemaDocumentFormat.Json);
The writer leaves the stream open, emits deterministic UTF-8 without timestamps or connection state, validates v1 semantics before writing, and supports JSON/YAML. Export rejects unsupported custom constraints, expressions, column subclasses, literal types and native scopes instead of discarding them. Schema-only consumers do not acquire a serializer dependency; writing lives in DubUrl.Schema.Documents.
Worker example
Configure ConnectionStrings:source on the worker, then send:
{"jsonrpc":"2.0","id":1,"method":"schema.inspect","params":{"connection":"source","tables":[{"schema":"public","name":"customer"}],"includeReferences":true,"basic":true}}
The result includes success, document, isComplete, coverage and diagnostics. allowPartial defaults to false; schemas supplies literal schema-name filters. Only configured connection references are accepted. The worker’s existing request cancellation/timeouts apply. worker.capabilities advertises schemaDiscovery separately from rendering/deployment. Serialize the document with SchemaDocumentWriter when saving a standalone file.
Qualification
The live database QA suite in DubUrl.Providers runs against disposable databases. Its inspection fixtures cover defaults, identity and generated values, indexes, constraints and views. The SQLite fixtures exercise inspection, rendering, deployment and reinspection across main and an attached database.
Catalog defaults
Column.DefaultValue stores a typed literal. A provider that recognizes a typed expression reports a ColumnDefaultExpression in MetadataColumn.Default. Schema preserves the expression symbolically, including any external sequence identity. Providers use DBNull.Value for an explicit NULL default. An unrecognized catalog expression should be reported as a Defaults diagnostic or a MetadataExpression marker, which Schema diagnoses with SDI011. Plain strings are treated as literal values. The inspector maps metadata without evaluating catalog SQL or advancing sequences.
Generated columns have a separate structured expression model. Discovery currently recognizes column references, literals, and nested Length/Upper/Lower calls. Operators such as amount * 2 remain unrepresentable by the current expression contract.
DuckDB identities, generated columns and indexes
Verified with DuckDB.NET / engine 1.5.6:
- DuckDB rejects native GENERATED ALWAYS AS IDENTITY. DubUrl renders portable identities with a deterministic, dedicated CREATE SEQUENCE and a DEFAULT nextval(…) reference. Seed and positive/negative increment are preserved. DropIfExists recreates the generated sequence after dropping the table. Sequence names are derived from the table/column identity within the table’s namespace; the document does not preserve source sequence names or current counter state.
- Discovery recognizes a direct nextval(‘qualified-sequence’) default on an integer column when catalog dependencies establish exclusive column/table use, the sequence does not cycle, and its bounds are representable. Shared sequences, cycling/custom bounds, ambiguous references and expressions around nextval are diagnosed instead of flattened into independent identities. Inspection does not advance sequences.
- Virtual generated expressions are read from canonical table DDL because information_schema’s generation fields are empty and column_default also contains generated expressions. Those expressions are not mistaken for ordinary defaults. Stored generation is unsupported by this DuckDB engine.
- Ordinary and unique secondary index names, owning tables, and column declaration order are discovered. Primary/unique-constraint backing indexes remain separate. Expression or specialized index forms are diagnosed.
- DuckDB accepts ASC/DESC in CREATE INDEX but discards directions in its catalog. Discovery preserves column order and sets direction to unspecified; it cannot recover discarded declarations. Explicit model directions can be emitted in SQL, but DuckDB does not retain them.
- CREATE INDEX uses an unqualified index name with a qualified ON table; DuckDB derives the index namespace from its table. Conflicting explicit index/table namespaces are rejected.
Reference: DuckDB generated columns, sequences, and catalog functions.