4 ms·

I've consulted at so many places that just wrote stored procedures/functions and put them in the DB but never put those in source control and managed them like

I've consulted at so many places that just wrote stored procedures/functions and put them in the DB but never put those in source control and managed them like code. Can't tell you how many times they'd split the SQL box onto two separate machines to support different clients and growth and then forget to update one copy of procs on one of the machines causing all sorts of hell. I found it a lot in small to medium sized business which were transitioning from the couple of people who wrote all their software to a team needing to do things properly and consistently. But I also found it at 50 person dev teams, which was weird and the worst. I've generally always managed it as code, just easier that way.

mettamage 5y ago
It happened with me as well. My previous employer created stored procedures in a MSSQL database and called it a day.
MikeTV 5y ago
My experience also. This is particularly bad in the SQL Server space, since Microsoft essentially dropped source control integration in SQL Server Management Studio 2016 and later (there's an official workaround[0], but it's not well received). Most likely they're happy to offload version control to add-ins. There are a few, but most of them are way out of budget for small businesses or small IT departments. Which means version control usually doesn't happen. This has been such a problem for me with clients that I started a side project[1] to try to help fill the gap. If anything it's shown me that the problem is more widespread than I originally thought. [0] https://cloudblogs.microsoft.com/sqlserver/2016/11/21/source-control-in-sql-server-management-studio-ssms/ https://cloudblogs.microsoft.com/sqlserver/2016/11/21/source... [1] https://www.versionsql.com/ https://www.versionsql.com/
- fatnoah 5y ago
  In the past, I used the SQL project feature of Visual Studio to great success to manage the DB. Everything except data up/down migrations was well supported, and it enabled us to keep our SQL in source control. I never even knew there was direct integration with SQL server.
  - zip1234 5y ago
    SQL Projects are still nice and still work in the lastest Visual Studio. You can sync the DDL from a current DB to the project and vice versa.
    - fatnoah 5y ago
      That's good to hear. When I started that job, the DB upgrade was a massive, manually curated SQL file that would be run for every upgrade and contained every change from the beginning of time. You can imagine how well that worked. I blew peoples' minds with the idea of "building" a database package
KronisLV 5y ago
Once saw a system like that, however, not only was the system not structured like most are nowadays (e.g. one that allows the app to do most of the CRUD), but it also stored almost everything in the database - not just stored procedures, but information about what sorts of data views in the web interface should have, all of the configuration, all of the validation logic etc. So if it were to be a traditional MVC design (which it quite wasn't), it would have both the model and controller in the DB and the Java bits only handled the view part with JSP and some other outdated tech. Rendering a view basically involved calling a DB stored procedure which prepared everything that should be visible and returned the data to the "fake back end" for further processing, before it got sent off to the browser. And saving new data or editing the existing data involved the same approach, but with numerous function parameters (think 20 to 40). On the bright side: it worked fast. On the not so bright side: everything else was horrible. The Java code was nightmarish and badly maintained, the discoverability of it was horrible since it also had to deal with naming things similarly to how they were in the DB, with unreasonable identifier length limits. The DB code was almost impossible to version well (no automatic migrations to speak of), routinely broke and there was little to no logging in place, as most DBMSes out there handle the concept of "application logs" pretty badly, unless you write your own, which the other devs hadn't done. Oh, and you can forget about putting a breakpoint in those stored procedures, or even having Apache2/Nginx/whatever tell you what was being called for any action. It really taught me a thing or two about whether i should eagerly agree to help with code that other companies have developed and someone now needs to fix/improve. Since then, i've also seen all sorts of systems, some that have automated migrations but don't have a baseline, some that have so much data and complexity that everyone has to share a test DB instance, others where the migrations are automated but there are no seeding scripts so a newly initialized DB instance has the correct structure for local development but the system is not usable because of no data. Of course, i've fixed what i could over the years, but there are very few approaches that truly work. The most functional approach that i've seen: automatic DB migration scripts in the project (versioned in VCS) from day 1, each developer having their local DB instance, with seeding scripts for that as well, so that they can do breaking changes locally and test them out as often as they like, without fearing ruining the schema (after all, locally you can just wipe the schema and data, run the stable migrations/seeding scripts and continue from there again), no manual changes on DB servers to schema/data (unless in an emergency, but even then the same should be done in scripts later). Additionally: it's useful to have scripts that won't fail if you run them more than once (check if data needs to be altered first, do so if necessary, otherwise do nothing, maybe output information in some log table). Optionally: it can be useful to have the ability to reverse migrations but sometimes that's not easy to do and increases the total work ~2X, depending on the complexity of your migrations. Forward only approach has been sufficient for most of my projects. Optionally: i've also explored model driven development with MySQL Workbench, where i made all of the models in ER diagrams and used forward engineering to get the SQL for scripts. It was a wonderfully nice approach, but pgAdmin and SQL Workbench as well as others don't really support anything like that, so it's a niche concept: https://dev.mysql.com/doc/workbench/en/wb-forward-engineering-live-server.html https://dev.mysql.com/doc/workbench/en/wb-forward-engineerin...

mettamage 5y ago

It happened with me as well. My previous employer created stored procedures in a MSSQL database and called it a day.

MikeTV 5y ago

My experience also. This is particularly bad in the SQL Server space, since Microsoft essentially dropped source control integration in SQL Server Management Studio 2016 and later (there's an official workaround[0], but it's not well received). Most likely they're happy to offload version control to add-ins. There are a few, but most of them are way out of budget for small businesses or small IT departments. Which means version control usually doesn't happen. This has been such a problem for me with clients that I started a side project[1] to try to help fill the gap. If anything it's shown me that the problem is more widespread than I originally thought. [0] https://cloudblogs.microsoft.com/sqlserver/2016/11/21/source-control-in-sql-server-management-studio-ssms/ https://cloudblogs.microsoft.com/sqlserver/2016/11/21/source... [1] https://www.versionsql.com/ https://www.versionsql.com/

fatnoah 5y ago
In the past, I used the SQL project feature of Visual Studio to great success to manage the DB. Everything except data up/down migrations was well supported, and it enabled us to keep our SQL in source control. I never even knew there was direct integration with SQL server.
- zip1234 5y ago
  SQL Projects are still nice and still work in the lastest Visual Studio. You can sync the DDL from a current DB to the project and vice versa.
  - fatnoah 5y ago
    That's good to hear. When I started that job, the DB upgrade was a massive, manually curated SQL file that would be run for every upgrade and contained every change from the beginning of time. You can imagine how well that worked. I blew peoples' minds with the idea of "building" a database package

KronisLV 5y ago

Once saw a system like that, however, not only was the system not structured like most are nowadays (e.g. one that allows the app to do most of the CRUD), but it also stored almost everything in the database - not just stored procedures, but information about what sorts of data views in the web interface should have, all of the configuration, all of the validation logic etc. So if it were to be a traditional MVC design (which it quite wasn't), it would have both the model and controller in the DB and the Java bits only handled the view part with JSP and some other outdated tech. Rendering a view basically involved calling a DB stored procedure which prepared everything that should be visible and returned the data to the "fake back end" for further processing, before it got sent off to the browser. And saving new data or editing the existing data involved the same approach, but with numerous function parameters (think 20 to 40). On the bright side: it worked fast. On the not so bright side: everything else was horrible. The Java code was nightmarish and badly maintained, the discoverability of it was horrible since it also had to deal with naming things similarly to how they were in the DB, with unreasonable identifier length limits. The DB code was almost impossible to version well (no automatic migrations to speak of), routinely broke and there was little to no logging in place, as most DBMSes out there handle the concept of "application logs" pretty badly, unless you write your own, which the other devs hadn't done. Oh, and you can forget about putting a breakpoint in those stored procedures, or even having Apache2/Nginx/whatever tell you what was being called for any action. It really taught me a thing or two about whether i should eagerly agree to help with code that other companies have developed and someone now needs to fix/improve. Since then, i've also seen all sorts of systems, some that have automated migrations but don't have a baseline, some that have so much data and complexity that everyone has to share a test DB instance, others where the migrations are automated but there are no seeding scripts so a newly initialized DB instance has the correct structure for local development but the system is not usable because of no data. Of course, i've fixed what i could over the years, but there are very few approaches that truly work. The most functional approach that i've seen: automatic DB migration scripts in the project (versioned in VCS) from day 1, each developer having their local DB instance, with seeding scripts for that as well, so that they can do breaking changes locally and test them out as often as they like, without fearing ruining the schema (after all, locally you can just wipe the schema and data, run the stable migrations/seeding scripts and continue from there again), no manual changes on DB servers to schema/data (unless in an emergency, but even then the same should be done in scripts later). Additionally: it's useful to have scripts that won't fail if you run them more than once (check if data needs to be altered first, do so if necessary, otherwise do nothing, maybe output information in some log table). Optionally: it can be useful to have the ability to reverse migrations but sometimes that's not easy to do and increases the total work ~2X, depending on the complexity of your migrations. Forward only approach has been sufficient for most of my projects. Optionally: i've also explored model driven development with MySQL Workbench, where i made all of the models in ER diagrams and used forward engineering to get the SQL for scripts. It was a wonderfully nice approach, but pgAdmin and SQL Workbench as well as others don't really support anything like that, so it's a niche concept: https://dev.mysql.com/doc/workbench/en/wb-forward-engineering-live-server.html https://dev.mysql.com/doc/workbench/en/wb-forward-engineerin...