How to run raw SQL to deploy database changes

Question

We intend to create DACPAC files using SQL database projects and distribute them automatically to several environments, DEV/QA/PROD, using Azure Pipeline. I can make changes to the schema for a table, view, function, or procedure, but I'm not sure how we can update specific data in a table. I am sure this is very common use case but unfortunately I am having hard time implementing it.

Any idea how can I automate creating/updating/deleting a row for a table?

E.g.: update myTable set myColumn = 5 where someColumn = 'condition'

Answer 1

In your database project you can add a Post Deployment Script

Answer 2

Do not. Seriously. I found DACPAC always to be WAY too limiting for serious operations. Look how the SQL is generated and - realize how little control you have.

The standard approach is to have deployment scripts that you generate and that do the changes in the database, plus a table in the db tracking which have executed (possibly with a checksum so you do not need t change the name to update them).

You can easily generate them partially by schema compare (and then generate the change script), but those also allow you to do things like data scrubbing and multi step transformations that DACPAC by design cannot efficiently and easily do.

There are plenty of frameworks for this around. They generally belong in the category of developer tools.