News for hackers. Stuff that matters.
Re:What ORMs have taught me: just learn SQL (2014) by djrobstep on Sunday December 17, 2017 @11:00PM (#15949297)
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.

Re:What ORMs have taught me: just learn SQL (2014) by wasted_intel on Sunday December 17, 2017 @11:07PM (#15949332)
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.
Re:What ORMs have taught me: just learn SQL (2014) by sbuttgereit on Sunday December 17, 2017 @11:35PM (#15949458)
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/ 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.

Re:What ORMs have taught me: just learn SQL (2014) by vectorpush on Sunday December 17, 2017 @11:14PM (#15949365)
Sure you can, just store them in .sql files and have your CI system auto deploy them.
Re:What ORMs have taught me: just learn SQL (2014) by programmarchy on Sunday December 17, 2017 @11:18PM (#15949376)
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.
Re:What ORMs have taught me: just learn SQL (2014) by jsmeaton on Sunday December 17, 2017 @11:12PM (#15949357)
`create proc if not exists get_customer_v1243(params)`

.. I've seen this.

Re:What ORMs have taught me: just learn SQL (2014) by GFischer on Monday December 18, 2017 @10:17AM (#15952160)
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.
Re:What ORMs have taught me: just learn SQL (2014) by 0xCMP on Sunday December 17, 2017 @11:14PM (#15949364)
I mean you could, but it’d need to be through some kind of migration system to update it.
Re:What ORMs have taught me: just learn SQL (2014) by other_herbert on Sunday December 17, 2017 @11:25PM (#15949414)
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...
Re:What ORMs have taught me: just learn SQL (2014) by zkomp on Monday December 18, 2017 @02:37AM (#15950094)
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)

Re:What ORMs have taught me: just learn SQL (2014) by BurningFrog on Sunday December 17, 2017 @11:31PM (#15949437)
One problem with having logic in the DB is that it's much harder to scale if you need 10x more computing power.
Re:What ORMs have taught me: just learn SQL (2014) by ako on Monday December 18, 2017 @01:10AM (#15949782)
Moving 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.
Re:What ORMs have taught me: just learn SQL (2014) by BurningFrog on Monday December 18, 2017 @01:24AM (#15949841)
We're talking about stored procedures, which are executable code/business logic.
Re:What ORMs have taught me: just learn SQL (2014) by ako on Monday December 18, 2017 @04:45AM (#15950542)
Yes, 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.
Re:What ORMs have taught me: just learn SQL (2014) by ckarmann on Sunday December 17, 2017 @11:42PM (#15949486)
Scaling 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.