3 ms·

I'm a web developer (React / Node.js) and I bought the first edition of this book (it was called Mastering PostgreSQL in Application Development then). I can s

I'm a web developer (React / Node.js) and I bought the first edition of this book (it was called Mastering PostgreSQL in Application Development then). I can say that it really changed the way I approach building apps. I definitely lean on the database a lot more now. No more "models", ORMs / query builders, and crunching data in app code. Just build your schema, write plain sql queries, and you're off to the races. As a bonus, I'm also much more comfortable with Postgres' built in command line tool, psql. The scope of the book is pretty broad. You'll cover psql setup, a little relational theory, some nuts and bolts about the inner workings of Postgres, indexing strategy, data types, aggregates, window functions, common table expressions, etc. Given the scope, it's difficult to cover everything in depth, so I'd treat this book as more of an introduction to what's possible. Highly recommended.

originalvichy 7y ago
Thanks for the recommendation! Might buy this come payday.
majewsky 7y ago
> No more "models", ORMs / query builders, and crunching data in app code. Just build your schema, write plain sql queries, and you're off to the races. I tend to do it the same way, but how do you deal with the lack of a query builder when you truly need to build a query on the fly? For example, what do you do when implementing a REST endpoint like GET /things where the user can give any number of 10 query parameters to filter the result?
- spiderxxxx 7y ago
  You do "or <parameter> IS NULL". It makes the query plan uncacheable or requiring a godly cache, but it's fine for searches.
- mmckelvy 7y ago
  For cases like this, I use pl/pgsql functions and dynamic queries. Dynamic queries take extra care and aren't the most pleasant things to write, but they can handle the GET /things with 10 parameters endpoints quite well.
- wilmoore 7y ago
  > what do you do when implementing a REST endpoint like GET /things where the user can give any number of 10 query parameters to filter the result? https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-constant-score-query.html https://www.elastic.co/guide/en/elasticsearch/reference/curr...
oliwarner 7y ago
> 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.

originalvichy 7y ago

Thanks for the recommendation! Might buy this come payday.

majewsky 7y ago

> No more "models", ORMs / query builders, and crunching data in app code. Just build your schema, write plain sql queries, and you're off to the races. I tend to do it the same way, but how do you deal with the lack of a query builder when you truly need to build a query on the fly? For example, what do you do when implementing a REST endpoint like GET /things where the user can give any number of 10 query parameters to filter the result?

spiderxxxx 7y ago
You do "or <parameter> IS NULL". It makes the query plan uncacheable or requiring a godly cache, but it's fine for searches.
mmckelvy 7y ago
For cases like this, I use pl/pgsql functions and dynamic queries. Dynamic queries take extra care and aren't the most pleasant things to write, but they can handle the GET /things with 10 parameters endpoints quite well.
wilmoore 7y ago
> what do you do when implementing a REST endpoint like GET /things where the user can give any number of 10 query parameters to filter the result? https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-constant-score-query.html https://www.elastic.co/guide/en/elasticsearch/reference/curr...

oliwarner 7y ago

> 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.