4 ms·

> No more "models", ORMs / query builders, and crunching data in app code I think you've overstepping there. I definitely think it's great if not essential to

> No more "models", ORMs / query builders, and crunching data in app code I think you've overstepping there. I definitely think it's great if not essential to understand the underpinnings of your RDBMS and how to actually use it, but an ORM is a really useful tool. It keeps a lot of crap from reaching your database (incorrectly cast data, malicious input, etc) and hands you back a format that is easy and logical to work with, as well as managing schema changes for you without destroying your whole database. If your ORM won't let you do your number-crunching in-database, it's a shitty ORM. You were right to throw it straight into the ocean... But mine does. Again, I'm not in any way suggesting you shouldn't know SQL —it's great to be able to understand what your ORM is doing, allowing you to improve it— so this book sounds essential for us Postgres lifers... But don't insist on walking everywhere because your car broke down. Buy a better car.

mmckelvy 7y ago
I'm not sure an ORM adds much in the way of casting data or preventing malicious input. Both of those features are readily available in Postgres itself. As far as formatting, the usual drivers (e.g. node-postgres) return rows as simple arrays of objects with keys corresponding to column names. That format seems easy enough to work with. RE: number-crunching, I was referring to the fact that leaning more heavily on Postgres allows you to skip the number crunching altogether and just output the end results (e.g. using aggregates, window functions, CTEs, lateral joins, etc.).
- oliwarner 7y ago
  > I'm not sure an ORM adds much in the way of casting data or preventing malicious input. My ORM is constantly converting things backwards and forwards using in-database functions. The biggest change here is casting internal datetimes to the right timezone. This and input mitigation is all possible to build into your queries, but a) you have to remember to do it every time and b) you have to do it. I've got better things to do than debug my crappy SQL (at least that's what I tell myself). > using aggregates, window functions, CTEs, lateral joins, etc. Yes, that's what I'm talking about too. A good ORM lets you use those in-database features from the ORM, building queries that use the features of your RBMDS. I think we have very different experiences of what an ORM can do. They're not equal. For some background, I've been using Django for a decade. It hasn't always been as good as it is today, but doing the sorts of things we're talking about here is stupid-simple. Plus you get to stay within a Model-based environment so you can encase this data in business logic, or get raw data, or use that query inside another aggregate. Here's some Django (me playing around in shell_plus on real models with real data, start is a datetime) and the SQL it's running. today = datetime.date.today() Booking.objects.filter(start__date=today).count() SELECT COUNT(*) AS "__count" FROM "bookings_booking" WHERE ("bookings_booking"."start" AT TIME ZONE 'Europe/London')::date = '2019-08-22'::date And if you want to do something a little fruitier like pulling back the average booking.cost for bookings over the next week, it's pretty simple if you understand how things like `.values()` transforms a query. Booking.objects.filter( start__date__range=(today, today + timedelta(days=7)) ).annotate( date=Trunc('start', 'day', output_field=models.DateField()) ).values( 'date' ).annotate( avg_cost=Avg('cost') ) SELECT DATE_TRUNC('day', "bookings_booking"."start") AS "date", AVG("bookings_booking"."cost") AS "avg_cost" FROM "bookings_booking" WHERE ("bookings_booking"."start" AT TIME ZONE 'Europe/London')::date BETWEEN '2019-08-22'::date AND '2019-08-29'::date GROUP BY DATE_TRUNC('day', "bookings_booking"."start") It's not perfect, but it's not far off. There's also enough tooling around the ORM to detect bad queries and fix them.
- oliwarner 7y ago
  I've just spent a few days debugging somebody else's raw SQL. My conviction that good ORM is better is stronger than ever.

mmckelvy 7y ago

I'm not sure an ORM adds much in the way of casting data or preventing malicious input. Both of those features are readily available in Postgres itself. As far as formatting, the usual drivers (e.g. node-postgres) return rows as simple arrays of objects with keys corresponding to column names. That format seems easy enough to work with. RE: number-crunching, I was referring to the fact that leaning more heavily on Postgres allows you to skip the number crunching altogether and just output the end results (e.g. using aggregates, window functions, CTEs, lateral joins, etc.).

oliwarner 7y ago
> I'm not sure an ORM adds much in the way of casting data or preventing malicious input. My ORM is constantly converting things backwards and forwards using in-database functions. The biggest change here is casting internal datetimes to the right timezone. This and input mitigation is all possible to build into your queries, but a) you have to remember to do it every time and b) you have to do it. I've got better things to do than debug my crappy SQL (at least that's what I tell myself). > using aggregates, window functions, CTEs, lateral joins, etc. Yes, that's what I'm talking about too. A good ORM lets you use those in-database features from the ORM, building queries that use the features of your RBMDS. I think we have very different experiences of what an ORM can do. They're not equal. For some background, I've been using Django for a decade. It hasn't always been as good as it is today, but doing the sorts of things we're talking about here is stupid-simple. Plus you get to stay within a Model-based environment so you can encase this data in business logic, or get raw data, or use that query inside another aggregate. Here's some Django (me playing around in shell_plus on real models with real data, start is a datetime) and the SQL it's running. today = datetime.date.today() Booking.objects.filter(start__date=today).count() SELECT COUNT(*) AS "__count" FROM "bookings_booking" WHERE ("bookings_booking"."start" AT TIME ZONE 'Europe/London')::date = '2019-08-22'::date And if you want to do something a little fruitier like pulling back the average booking.cost for bookings over the next week, it's pretty simple if you understand how things like `.values()` transforms a query. Booking.objects.filter( start__date__range=(today, today + timedelta(days=7)) ).annotate( date=Trunc('start', 'day', output_field=models.DateField()) ).values( 'date' ).annotate( avg_cost=Avg('cost') ) SELECT DATE_TRUNC('day', "bookings_booking"."start") AS "date", AVG("bookings_booking"."cost") AS "avg_cost" FROM "bookings_booking" WHERE ("bookings_booking"."start" AT TIME ZONE 'Europe/London')::date BETWEEN '2019-08-22'::date AND '2019-08-29'::date GROUP BY DATE_TRUNC('day', "bookings_booking"."start") It's not perfect, but it's not far off. There's also enough tooling around the ORM to detect bad queries and fix them.
oliwarner 7y ago
I've just spent a few days debugging somebody else's raw SQL. My conviction that good ORM is better is stronger than ever.