5 ms·

How would somebody implement this for a team with versioning, source code view, and deployment?

msvalkon 7y ago
One can create the stored procedure and store it in an .sql file which can be version controlled like any piece of code. These can then be deployed in a number of ways.
gigatexal 7y ago
I was this guy years ago in a waterfall style shop. We were a SQLServer c# all MS vertical and we did just this. All stored procedures, table definitions, etc were checked in. There was a whole release process and QA to bundle the right versions of things into deployables which I did. It didn’t work flawlessly but it worked and when it failed it was straightforward enough that figure out why was easy.
magicalhippo 7y ago
We keep our schema in an XML file which is kept our source control repository alongside the application code. We have a tool to generate the XML and to take the XML and compare with a database and altering it to match the XML schema. Though we never delete data due to backwards compatibility, so no dropping of objects, reduction of column widths etc. When deploying a new version the database is processed first, if successful the new executable is made available. If not it calls home so we can take a look. To make changes in development, we commit a new XML to dev branch, we have a commit hook to upgrade the dev database.
wayneftw 7y ago
If you are using SQL Server, you can create a SQL Server project in Visual Studio and keep all of the source code for your database objects (tables, views, functions, procedures, etc) in git. The tools in Visual Studio will also help you create migration scripts and do schema/data diffs.
ibotty 7y ago
sqitch is great! https://github.com/sqitchers/sqitch https://github.com/sqitchers/sqitch
ako 7y ago
Why are you asking this? Just wondering why think it would not be doable. Sql and stored procedures are just a textual source code. You can put it into version control, do code reviews, or build CICD pipelines for sql and stored procedures just like for any other language... The only challenge is the deployment environment, as databases offer a stateful runtime engine, so you'll want to consider it's state. But state can also be managed in CICD pipelines, with scripting, sql-loader, backups, or similar.

One can create the stored procedure and store it in an .sql file which can be version controlled like any piece of code. These can then be deployed in a number of ways.

gigatexal 7y ago

I was this guy years ago in a waterfall style shop. We were a SQLServer c# all MS vertical and we did just this. All stored procedures, table definitions, etc were checked in. There was a whole release process and QA to bundle the right versions of things into deployables which I did. It didn’t work flawlessly but it worked and when it failed it was straightforward enough that figure out why was easy.

magicalhippo 7y ago

We keep our schema in an XML file which is kept our source control repository alongside the application code. We have a tool to generate the XML and to take the XML and compare with a database and altering it to match the XML schema. Though we never delete data due to backwards compatibility, so no dropping of objects, reduction of column widths etc. When deploying a new version the database is processed first, if successful the new executable is made available. If not it calls home so we can take a look. To make changes in development, we commit a new XML to dev branch, we have a commit hook to upgrade the dev database.

wayneftw 7y ago

If you are using SQL Server, you can create a SQL Server project in Visual Studio and keep all of the source code for your database objects (tables, views, functions, procedures, etc) in git. The tools in Visual Studio will also help you create migration scripts and do schema/data diffs.

ibotty 7y ago

sqitch is great! https://github.com/sqitchers/sqitch https://github.com/sqitchers/sqitch

ako 7y ago

Why are you asking this? Just wondering why think it would not be doable. Sql and stored procedures are just a textual source code. You can put it into version control, do code reviews, or build CICD pipelines for sql and stored procedures just like for any other language... The only challenge is the deployment environment, as databases offer a stateful runtime engine, so you'll want to consider it's state. But state can also be managed in CICD pipelines, with scripting, sql-loader, backups, or similar.