Question: Interest in EF Core reverse engineering + drift detection tooling?

[nmummau]

Would there be interest in adding optional tooling around the built .dacpac for:

• EF Core reverse engineering (generate models from the schema)
• Post-deploy drift detection (validate a deployed DB matches the project)

These would be opt-in, post-build features and would NOT change .sqlproj semantics—just additional workflows that consume the existing artifact.

I’ve built working prototypees (Docker-based) for both of these things as "sidecars" to my database project, but think the patterns are so valuable that it would be beneficial to share with others using the SDK. If this direction sounds useful / aligned, I’m happy to share more details on implementation.

Brief implementation details

• Reverse engineering would use EFCorePowerTools
• Drift detection would use sqlpackage DeployReport

[ErikEJ]

@nmummau someone did a lot of similar work in that direction:

https://github.com/JerrettDavis/JD.Efcpt.Build

But not from the perspective of the Database project.

Important feature (implemented in the linked project) would be to only regenerate when required (I did some complex detection logic for that)

[ErikEJ]

Would driftdetection not be a publish concern?

[jmezach]

@nmummau What do we mean with EF Core reverse engineering? Generating a DbContext and associated entities with DbSet's? Or do we mean generating an MSBuild.Sdk.SqlProj project based on an existing DbContext?

[nmummau]

@nmummau What do we mean with EF Core reverse engineering? Generating a DbContext and associated entities with DbSet's? Or do we mean generating an MSBuild.Sdk.SqlProj project based on an existing DbContext?

I mean "Generating a DbContext and associated entities with DbSet's" from the MSBuild.Sdk.SqlProj database project. If you ever worked with EDMX you might remember in VisualStudio you could right click on the .sqlproj and choose "generate model from database". It would be a similar mental model to that, except it would use EF Core Power Tools CLI to control it all.

As I type this out I'm wondering if maybe it's better just to leave it out of the SDK entirely. I really do feel like it would be very nice to bake into the SDK though cause any team reaching for this SDK and who are database-first using EF Core, the next thing they need to reach for is generating the EF Core DbContext & models. And having everything in the database project to me makes the most sense for database-first. hmm.

[nmummau]

Would driftdetection not be a publish concern?

I guess it could be a concern at publish-time. However what I'm picturing would be after a database was published. Consider this, we publish a new database table to production. Database Reliability Engineers (ops) get a notification over the weekend and notice that the new table is missing an index that the development team didn't create. They manually create the index to the production database to get the database healthy. Communication breaks down and we never get that index definition added into the database project. This is the drift that we are able to detect with sqlpackage DeployReport. Dev teams can monitor DriftReport results to be alerted that their database project doesn't match the database. At my company we run these tools on intervals that make sense to be sure our database project is trustworthy as the source of truth.

[ErikEJ]

@nmummau Thanks for your proposals.

For EF Core reverse engineering, EF Core Power Tools offer a variety of options:

VS extension add "Create DbContext from SQL Project" and the command line version has full .dacpac support

Given this, maybe we can close this as not planned?

[ErikEJ]

@nmummau Ping?