PostgreSQL INTERVAL data type or ORM?

Hey folks,

I was wondering if you did know that there is this cool data type in PostgreSQL which allows us to descriptively tell which time do we want it to return. Like when you wanna say return all products who've been added to inventory at least six month ago. So here is how you can do it.

Very easy and straight forward in PSQL if you ask me with INTERVAL data type:

SELECT *
FROM inventories
WHERE created_at < NOW() - INTERVAL '6 months'

Where as in Prisma you could say something like:

import { DateTime } from 'luxon';
import { PrismaClient } from '@prisma/client';

export const prisma = new PrismaClient();

const now = DateTime.now();
const sixMonthsAgo = now
  .set({ month: now.month - 6 })
  .toJSDate();
prisma.inventory.findMany({
  where: {
    createdAt: { 
      lt: sixMonthsAgo
    }
  }
})

So here you go, it is really nice and lovely but at the same time you can see the difference. Here we needed another lib to makeup for the Date. You can find another example here.

It might not be a big deal bu I guess now you can see how they differ from each other.

A comparison between ORM and SQL. These where the point that came to my mind when I thought about the topic.

	Pros	Cons
ORM	No need to serialize your data*. No need to handle SQL injection. They come with other tools such as data migration and schema migration which can ease a lot of tedious tasks.	Sometimes it is more efficient to just write SQL. Do not support all the features of your RDBMS. Do not support all the features of your RDBMS.
Raw SQL	Leverage built-in features of your RDBMS. More granular control over queries.	Take care of SQL injection and other potential threads. Code duplication if you're not careful. Too much boiler plate code to just get started (CRUD).

*Although we can use AS to define our own alias and prevent an extra step of serializing data in our code. And we might also still need to serialize what prisma returns anyway like what I've done here.

---

So then what?

I guess it is easy to decide what you should do. You can add a repository layer which exposes your prisma client and then in this way you can have your cake and eat it too. Whenever you need to write raw SQL you can just jump in your repository and add a new method.

And for the normal scenarios you can simply use prisma to have full power of both world. I've seen also people who love to add repository pattern even if they are using something like Prisma but to be frank I think it is over engineering stuff.

Lemme know your thoughts and if you like it share your codes with us so we can discuss it even more in depth.

---

Update 08.08.2024

I recently tried to implement a simple data sanitization in SQL with BEFORE UPDATE and BEFORE INSERT. To be frank with you guys I did not find it easy to get rid of trailing white space characters as we do in TS/JS.

You can find it here.