DEV Community

Cover image for How to work with stored procedures and not die trying

How to work with stored procedures and not die trying

Darío Kondratiuk on January 11, 2018

What's wrong with databases? I won't try to sell you the idea that stored procedures are the best and that everybody needs to throw away...
Collapse
 
pesse profile image
Samuel Nitsche

What bothers me most with using Visual Studio for my (very procedure-heavy and db-centric) database project is that Visual Studio encourages you to use a state-based publishing strategy. It works fine as long as you're working with stored procedures only or and are able to create all the tables from scratch if you have to change them.
The moment you try to do migrations like alter table or even data migrations (you might need them at some point if you for example also store metadata in the database or if you want to refactor your data tables) there are massive pitfalls with that approach.

I started using using flyway some months ago for the database migrations and was able to overcome serious problems we previously had with the publish-approach of Visual Studio (also blogged about it: cleandatabase.wordpress.com/2017/1...)

Collapse
 
hardkoded profile image
Darío Kondratiuk

Regarding the migration process, Visual Studio keeps track of refactors in a refactorlog file. So if you rename the table Foo to Var it won't try to drop Foo and create Var, it'll perform a table rename.

But when we start talking about data... yeah, that's a headache.

I'll take a look at your post!

Collapse
 
jfrankcarr profile image
Frank Carr

One thing that has always bugged me about Entity Framework and other .NET ORM's is how they encourage not leveraging the power of the database. Instead, you get monstrously complex layers built on the Repository and Unit of Work pattern to handle transactions and such. This is what SQL Server, Oracle and most other relational DB's have been specifically engineered over decades of development to do very well. It's like buying a bloodhound and then trying to sniff out your quarry yourself while the dog sits on the porch.

Collapse
 
hardkoded profile image
Darío Kondratiuk

Agreed.
Another thing that's hard for me to buy is the Code First approach. I think you need to know and take control over the database your app will use. There is a higher chance of changing your ORM or even the platform you are working on than your database. You have to know your database.

Collapse
 
rafalpienkowski profile image
Rafal Pienkowski

Hi Dario. Very nice article.

I have some additional questions about DB migrations. You've described integration of your tool with CI process in case of integration tests. I assume that you create new database before each test run. My question is how is your approach to database updates on specific environments like test, stage or prod? Do you do it via CI or manually? Did you have a need to perform some data comparisons during such a update?

Collapse
 
hardkoded profile image
Darío Kondratiuk

Hey Rafal, thanks!

Our workflow is something like this. When the CI grabs a branch to be tested it creates a brand new database using the command I shared in the CI section.
When a Pull Request is approved and a branch merged, our CI will test that commit again (to test how it works with the code integrated in the main branch) and then it will run the same command to update our test database, but this time with the arguments “CreateNewDatabase=false,DropObjectsNotInSource=false”.
After that another build will test our master (production) branch against that database to test that we’re not going to break production when that code gets to staging.

We didn’t get to the point to automate the staging and production deploy I think we are close but what we are doing now is using Visual Studio to compare our code with the production server, we would take a look at the diffs, check that everything looks as expected and then we would let Visual Studio do its job. We also have a deploy folder with data scripts we’d use to populate new tables or fields or fix data issues. This part is a manual step but it could be easily automated.

Collapse
 
rafalpienkowski profile image
Rafal Pienkowski

Thanks again for the detailed answer and for sharing your experience.

Collapse
 
minhhungit profile image
Jin

Your post is great! Good to know !

But IMHO, I think everything will be more simple if you use library like FluentMigrator, I also create a custom one for myself here github.com/minhhungit/DatabaseMigr...

Yes, you can track history of stored, function... with source control very easy

And more...

Collapse
 
t4rzsan profile image
Jakob Christensen

Hi Darío.

Nice write-up :)

I wonder why you add your INSERT data SQL as post-publish scripts? I guess it's fine for creating development databases but what happens when you deploy to production?

Collapse
 
hardkoded profile image
Darío Kondratiuk

Thank you Jakob! I like how many of you are finding out the dots I left out :)

So yes, Script.PostDeployment.sql is a script used to populate a brand new database. In order to solve what you mentioned we use different configurations, besides "Debug" and "Release" we created a "deployDB" configuration.

We manually edit the sqlproj file to indicate we want to include Script.PostDeployment.sql only when the configuration is "deployDB".

In the CI instead of executing msbuild in "Release" we call it in "deployDB" mode "/p:Configuration=deployDB"

You could also do that to publish to production. You can create another configuration called, lets say "publishToProd", create your own PROD.PostDeployment.sql and there setup all the scripts you want to be executed when you publish that to prod.

I think this deserves another post :)