EP-REF: NuGet package references for .pgpkg (consume/publish reusable schema packages)

[ilijevicdenis]

Summary

PgProj's ReferenceResolver only resolves local ArtifactReferences today; NuGet restore of .pgpkg packages is explicitly flagged as a "planned follow-up". This blocks the recommended SSDT composition model where a large database is split into reusable, versioned schema packages consumed by id+version. Adding NuGet PackageReference support (consume) and dotnet pack of a project into a .nupkg (publish) closes this parity gap.

SSDT / SqlPackage reference

In SSDT, a database project can declare a PackageReference to a .dacpac distributed as a NuGet package, restore it via dotnet add package / dotnet restore, and itself be produced as a consumable package via dotnet pack. This lets teams factor a monolithic schema into a shared "common" project plus dependent projects that reference it by package id and version, with build-time resolution feeding the deploy/compare model. Cross-project references contribute objects to the composite model used at build and publish time.

Proposed PgProj approach

Extend the existing artifact pipeline rather than inventing a new format:

• Pack: reuse the SDK packaging path that already packs the CLI as a tool to also pack the project's built .pgpkg into a .nupkg (a dotnet pack target emitting the .pgpkg as TfmSpecificPackageFile content under a known folder, with id/version metadata).
• Restore + resolve: teach ReferenceResolver to accept PackageReference entries, run NuGet restore, locate the restored .pgpkg in the global packages folder, and feed its objects into the same composite model that ArtifactReference already populates — so SchemaCompare, IdentityDiffEngine, DeployScriptGenerator, RiskAnalyzer, and PhasedDeployer consume referenced objects transparently.
• Scope to same-database composition: because PG has no three-part names (cross-DB is FDW/dblink only), the deliverable is build-time composite models and reference resolution for a shared "common schema" project, not runtime cross-DB calls. Referenced objects are visible to the build/compare model but the resolver must not emit deploy DDL for objects owned by a referenced package (reference-only, mirroring SSDT "same database" reference semantics).
• Validate via the shadow-DB harness: a consumer project that references a packed common-schema package must build, compare, and deploy identically to the inlined-objects baseline, and profiles must round-trip unchanged.

Acceptance criteria

• dotnet pack on a PgProj project produces a .nupkg containing the project's .pgpkg with correct id/version metadata.
• A PackageReference in a .pgproj is restored via NuGet and its .pgpkg is located and loaded by ReferenceResolver.
• Objects from a referenced package populate the composite model consumed by SchemaCompare/IdentityDiffEngine but are treated as reference-only (no deploy DDL is generated for them).
• A consumer project referencing a packed common-schema package builds and deploys via DeployScriptGenerator/PhasedDeployer with output equivalent to the inlined-objects baseline (verified on the shadow-DB harness).
• Missing/unresolvable package id+version fails the build with a clear, actionable diagnostic.
• Round-trip and parity tests stay green; a regression test covers the package-reference resolution path end to end.

References

• MicrosoftDocs/sql-docs: docs/tools/sql-database-projects/concepts/package-references.md
• Priority: medium

[ilijevicdenis]

Broken into implementable children for the next wave: #147 (pack → .nupkg), #148 (restore + resolve PackageReference, reference-only), #149 (consumer parity vs inlined baseline, shadow-DB).

[ilijevicdenis]

EP-REF complete on main: pack (#147, 7344705) + restore-aware reference-only resolution (#148, b110bc3) + shadow-DB parity (#149, c445f8d). dotnet pack produces a consumable .nupkg; <PackageReference> after dotnet restore resolves the embedded .pgpkg reference-only.