5 ms·
I've used SQLalchemy a lot. I've even written a keyset paging extension for SQLalchemy. But recently I've switched to writing stored procedures and calling the
by djrobstep 9y ago
I've used SQLalchemy a lot. I've even written a keyset paging extension for SQLalchemy.
But recently I've switched to writing stored procedures and calling them directly, instead of going through an ORM for everything... And it's so much easier.
- wasted_intel 9y agoKeeping 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 agoI mean you could, but it’d need to be through some kind of migration system to update it.
- other_herbert 9y agoFlyway 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 agoThere 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 agoSure you can, just store them in .sql files and have your CI system auto deploy them.
- programmarchy 9y agoSure 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 agoOf 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 agoMicrosoft 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.
- deleted 9y ago[deleted]
- BurningFrog 9y agoOne problem with having logic in the DB is that it's much harder to scale if you need 10x more computing power.
- ckarmann 9y agoScaling is like optimization in general: not everything will need to be able to scale, and even if they will, they often do not need to scale yet.
- ako 9y agoMoving the definition of your sql from your app to the database doesn't impact scalability, as in both cases the database needs to perform the query.
- BurningFrog 9y agoWe're talking about stored procedures, which are executable code/business logic.
- ako 9y agoYes, and if all your stored procedure is doing is execute a query and return a cursor to the result set, it's using just as much database cpu as with a regular query.