Question 247067 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Standard conventions on querying with SQL ORM

I’m using an ORM to access my SQL database from my Node JS application, and was wondering what the industry standard best practices are to perform SQL operations (some of which might be too advanced for the ORM.)

Scenario 1

Say I have User -hasmany- Order -hasmany- Product. For this simple left join scenario since ORMs are expected to manage without an issue if I want to fetch all products order by a user. However, would it be frowned upon to do something like:

const user = await User.getByPk(1);
const orders = await user.getOrders();
const products = await Promise.all( /* for each order call order.getProducts */ )

Of course this is a dead-simple example, but I was wondering if the principle of making procedural queries are considered de facto code smell (maybe wrap in a transaction would be ok?)

Scenario 2

With Postgres I’d like to utilize the TSVECTOR functionality, which I don’t believe ORMs like Sequelize have support for. In that case, should I even consider trying to use the ORM (maybe through the raw query functionalities), but then what’s the point, should we run these queries with just the vanilla client like postgres.js?

Tags:

2

Answers


  1. 0

    Posting a comment as an answer due to length and formatting.

    You might want to try this question at SoftwareEngineering – that board might be more adequate for discussing designs, standards, good practices and opinions.

    1. If those 3 steps is your entire operation, you could do it in one, single round trip. But if there’s something after step 1 that could potentially abort or alter step 2, you might need them separate. You might afford the round trip but not the refactor and vice versa. You’ll always find someone displeased with the smell of you code and plenty more who couldn’t care less: as long as it performs according to spec and maintains fine, discussing it becomes premature optimisation.
    2. If tsvector isn’t natively supported by an ORM of your choice, raw querying still might not be the only alternative (Python SQLAlchemy example, Node.JS Sequelize example). Even if you use an ORM as a de-facto raw, plain client library, you could still find some of the other, additional built-in functionalities useful.
    Login or Signup to reply.
  2. 0

    I am not sure if this is a proper answer. But you can you do hierarchical fetches with RDB, rdbjs.org .
    There will be one query for each hierarchical level – based on foreign keys to parent.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search