How to teach your Node.js app to speak SQL without an ORM

Confession: I love SQL. I love its declarative nature: you tell the database what data you need and how it should look, not how it should go around accomplishing that.

I love SQL so much I want to use it in any programming language I happen to be using, and do so directly. No middleman, no abstraction layer, and no ORM. I could write another article discussing the pros and cons of ORMs but let's leave that aside today. Suffice to say, I find them to be a needless intermediary.

I also prefer using types, which is why Typescript is my go-to language when working with Node.js. Granted, it is not a standalone language and compiles back to Javascript, and so its type safety is limited to compile-time. Still, its impact on my developer productivity and happiness is difficult to overstate.

Lastly, my database of choice for pretty much any web app has been PostgreSQL. Let's see how one could ties these three preferences together.

SQL with Typescript

When it comes to accessing a database, a popular choice in the Node.js is Knex. It comes with the usual assortment of features such as a schema builder, a query builder, and migrations. I've used it on client projects and I've got to admit, it is really easy to use.

It includes support for raw queries, and I could almost use normal SQL that way, but looks how it looks in practice:

knex.raw('select * from users where id = ?', [1]).then(function(resp) { ... });

Indeed, I supply an array of parameters to replace the placeholders (?), and I trust Knex will take care of sanitizing the input to prevent SQL injection. What I don't have is type safety.

Introducing pgtyped

I was looking for a Typescript library for an experiment I'm running, and found this wonderful repository: pgtyped. Long story short, it has precisely the feature set I was looking for. Quoting from the README:

PgTyped makes it possible to use raw SQL in TypeScript with guaranteed type-safety.
No need to map or translate your DB schema to TypeScript, PgTyped automatically generates types and interfaces for your SQL queries by using your running Postgres database as the source of type information.

What it means in practice: I write normal SQL queries, put placeholders inside for parameters, and run pgtyped on my code-base. It will examine the database, infer the data types, map them to Javascript datatypes, and create typed "proxies" that I work with in my data-access layer.

Thus I both have and eat my cake!

Practical example

JComments is a headless commenting platform for static websites. I've built it with NestJS and Postgres. Pgtyped came on my radar when I was setting up my stack, and has been there from the very first draft of the app.

The NestJS way of organizing your code makes a heavy use of modules and I quite enjoy its powerful dependency injection container, even though I burned myself many times when getting familiar with the framework.

Inside each module, I have services that provide functionality specific to the module's problem domain, as well as a data access objects. For example, the comments module that serves out comments for a given URL over an API has these files inside:

comments.service that is used by the API controller and mediates the data access
comments.interface that contains domain interfaces
comments.sql that has raw SQL queries used by the service class

When running pgtyped in my project folder, it generates a fourth file: comments.queries. There's generated code inside that contains proxies for query parameter objects as well as proxies for the returned results. For instance, given this SQL query:

/* @name commentsForAccountPaged */
SELECT * FROM comments WHERE account_id=:accountId ORDER BY created_at DESC LIMIT :limit OFFSET :offset;

The proxy for the query parameters looks like this:

export interface ICommentsForAccountPagedParams {
  accountId: string | null | void;
  limit: string | null | void;
  offset: string | null | void;
}

And the proxy for the resultset looks like this:

export interface ICommentsForAccountPagedResult {
  id: string;
  account_id: string;
  page_url: string;
  comment: string;
  reader_name: string;
  reader_email: string | null;
  reader_website: string | null;
  created_at: Date;
  page_title: string | null;
}

The object that runs the query have a name determined by the annotation preceding the SQL statement, in this case commentsForAccountPaged. It has a method run(), which takes the ICommentsForAccountPagedParams interface as its parameter and returns an array of ICommentsForAccountPagedResult.

This is all tied together in the service method:

async commentsForAccountPaged(account: Account, batchSize?: number, page?: number): Promise<CommentWithId[]> {
  const limit = batchSize ?? 10
  const offset = ((page ?? 1) - 1) * limit
  const params = { accountId: account.id, limit: `${limit}`, offset: `${offset}` }
  const pagedComments = await commentsForAccountPaged.run(params, this.client)
  this.logger.debug(interpretedQuery(commentsForAccountPaged, params))
  return pagedComments.map(this.recordToClass)
}

I can't make a mistake by passing it parameters of an incorrect type, and I know exactly what the object coming out of the database looks like. The last statement of the method merely maps the auto-generated object into my domain object.

I find this approach refreshing as it lets me speak SQL. It's an analogy to the "exposed brick" trend in apartment construction. We speak to databases daily, and often hide this fact behind the facades of ORMs and other middlemen. Why? The database is a foundational technology. And anything beyond a simple CRUD app will eventually tie itself to the specific database chosen for the project. We can just as well speak with it using its native tongue.

There's an argument to be made that as a project grows, the number of SQL queries written grows as well. Yes, code duplication can be an issue, if you have multiple queries that kinda do the same thing but not exactly. I just don't perceive this to be a problem: just by looking into a single SQL file, I know exactly how many queries I am using, what they look like, and can make refactoring decisions based on that.

Conclusion

I've been enamored with pgtyped and recommend that you take a look at it if you are developing Typescript apps with Postgres. I found it to be highly usable for a normal web app with standard database needs.

It's a big difference if you're coming over from the ORM world, and my goal is not to talk you out of using ORMs. If you like working with plain SQL queries and statements instead, then you'll be quick to appreciate it.

P.S.

If you feel like we'd get along, you can follow me on Twitter, where I document my journey.

Published on 18 Aug 2021