4 ms·

Keeping logic in the database like that means you can’t version those procedures alongside the rest of your code. That’s a pretty big downside.

jsmeaton 9y ago
`create proc if not exists get_customer_v1243(params)` .. I've seen this.
0xCMP 9y ago
I mean you could, but it’d need to be through some kind of migration system to update it.
- other_herbert 9y ago
  Flyway for Java... For stored procedures you use the repeatable syntax that way it checks the checksum of the file and if it doesn't match what is in the migration table it will run it... Easy and always up to date... That's with Java anyway...
- zkomp 9y ago
  There are several such tools, which are specialized and thus tend to perform a better job than orms (who try to do everything) But I would recommend writing, reviewing and deploying migrations by hand, esp for critical parts of the schema (automatic tools are almost guaranteed to get something wrong, with locking etc)
vectorpush 9y ago
Sure you can, just store them in .sql files and have your CI system auto deploy them.
programmarchy 9y ago
Sure you can. SQL is just plain text, so keep all your create table scripts in your repo, along with deploy and rollback scripts that you’d use to extend or migrate your schemas.
sbuttgereit 9y ago
Of course you can, at least I do. In the databases I've worked with extensively (PostgreSQL and, somewhat in the past, Oracle) Store Procedures are routinely versioned controlled as part of the application, just these bits of code are in a different language than the rest. The creation of functions/procedures is not tied to state of the database in quite the same way as tables are; the functions/procedures, where they care about the data, do need to recognize the table structure and changes to that structure, but that's no different than any other of the application code which makes use of the data in the database. I think where many people get caught up on this is that they do something like migrations to get code, including procedural code, into the database... but that's not the only game in town. And really, given what's possible with databases today, I'm not sure migrations are even the best way anymore. Consider a tool like: http://sqitch.org/ http://sqitch.org/ which facilitates not treating stored procedures as migrations, but rather as individual files which change just like any other code. There are ways to accomplish having good version control on the table/structure side as well, which again, is something you lose with the migration tools I've worked with. Anyway, I just don't buy this argument.
GFischer 9y ago
Microsoft has SQL Server Data Tools which can be integrated into your favorite SCM (we use VSTS but we'll switch to Git soon), can have automated deploys (which we don't) and in general can be a part of a modern development lifecycle.

jsmeaton 9y ago

`create proc if not exists get_customer_v1243(params)` .. I've seen this.

0xCMP 9y ago

I mean you could, but it’d need to be through some kind of migration system to update it.

other_herbert 9y ago
Flyway for Java... For stored procedures you use the repeatable syntax that way it checks the checksum of the file and if it doesn't match what is in the migration table it will run it... Easy and always up to date... That's with Java anyway...
zkomp 9y ago
There are several such tools, which are specialized and thus tend to perform a better job than orms (who try to do everything) But I would recommend writing, reviewing and deploying migrations by hand, esp for critical parts of the schema (automatic tools are almost guaranteed to get something wrong, with locking etc)

vectorpush 9y ago

Sure you can, just store them in .sql files and have your CI system auto deploy them.

programmarchy 9y ago

Sure you can. SQL is just plain text, so keep all your create table scripts in your repo, along with deploy and rollback scripts that you’d use to extend or migrate your schemas.

sbuttgereit 9y ago

Of course you can, at least I do. In the databases I've worked with extensively (PostgreSQL and, somewhat in the past, Oracle) Store Procedures are routinely versioned controlled as part of the application, just these bits of code are in a different language than the rest. The creation of functions/procedures is not tied to state of the database in quite the same way as tables are; the functions/procedures, where they care about the data, do need to recognize the table structure and changes to that structure, but that's no different than any other of the application code which makes use of the data in the database. I think where many people get caught up on this is that they do something like migrations to get code, including procedural code, into the database... but that's not the only game in town. And really, given what's possible with databases today, I'm not sure migrations are even the best way anymore. Consider a tool like: http://sqitch.org/ http://sqitch.org/ which facilitates not treating stored procedures as migrations, but rather as individual files which change just like any other code. There are ways to accomplish having good version control on the table/structure side as well, which again, is something you lose with the migration tools I've worked with. Anyway, I just don't buy this argument.

GFischer 9y ago

Microsoft has SQL Server Data Tools which can be integrated into your favorite SCM (we use VSTS but we'll switch to Git soon), can have automated deploys (which we don't) and in general can be a part of a modern development lifecycle.