When you're working on a DotNet or DotNetCore Project and it comes to persisting data, the obvious choice is to use MS SQL Server.
If you are working in a team of more than two Developers, its likely you are using Visual Studio Database Projects, now known as SQL Server Data Tools for Visual Studio to manage the database changes, add them to version control and deploy the changes using DacPac.
But what if for reasons beyond your control you cannot use MS SQL and have to use a different database server ?
For a new development project at work, Management had decreed we were to use MySql on a cloud provider.
The Team, I was on, also had to accomodate a couple of other "challenging" requirements imposed upon us :-
- Use Stored Procedures over ORM generated Sql for queries.
- the Database Migrations need to run on windows for Developers but integrate with Linux for DevOps servers, so would need to run from a command or script file
- Preferably keep the database code in a DBA Friendly format.
- Keep it as cheap as possible.
I am not going to discuss the pros and cons of Stored procs vs ORM generated queries. There is plenty of information out on the web about this subject.
I wanted to try and find a solution that gave the team I was on as familiar experience to SQL Server Data Tools for Visual Studio as possible but I was prepared for some compromise. I knew that we may not have all the features we had become accustomed to.
The key things we needed the solution to do was avoid the team having to author ALTER scripts for database object changes. we had got used to the declaritve approach of having all the Database objects as Create Statements in SQL Server Data Tools for Visual Studio
The options considered were:-
If you can stay with MsSql and SQL Server Data Tools for Visual Studio then do so. But if you do not have that luxury maybe something in the next couple of posts can help.