Hand written SQL

Sometimes no-orm won’t give you what you need. You can write your own SQL but no-orm has some opinions.

Prefer many, simple queries over a single, more complicated query

Imagine you want to write a query to fetch all of the flight attempts for penguins named ‘George’.

You might be tempted to write something like:

const query = `
  SELECT flight_attempts.*
  FROM flight_attempts
  JOIN penguins ON penguins.id = flight_attempts.penguin
  WHERE penguins.name = ${name}`;

Looks pretty simple. And a query like this is relatively simple.

However in general, code and logic written in SQL queries is hard to maintain. You don’t get a lot of the static analysis that comes from other programming languages.

The no-orm approach would be to split this into 2 separate queries:

const penguinsNamedGeorge = await Penguins.getByName({ connection, name: "George" });
const flightAttempts = await PenguinFlightAttempts.getManyByPenguin({
  connection,
  penguins: penguinsNamedGeorge.map((p) => p.id)
});

Yes it’s another round trip to the database, however most of the time your server and database are running in the same data center, so it’s a fast round trip.

The query plan for the 2 simple queries is much simpler meaning your database will be happier.

This approach means you get all of the maintainability from using no-orm’s generated functions.

Know your application

The advice above should always be taken in the context of your application.

Maybe your business has thousands of penguins named George and so storing all of them in a variable would cause your server to crash.

Maybe your database and server are running in separate data centers so the cost of a round trip is high.

Maybe you want a complex SQL query for 1 particular use-case.

At the end of the day, you are the expert on your application and should make these decisions as you see fit.

Writing SQL

As discussed, sometimes you do need to write SQL. An example of this is pagination:

import * as PenguinFlightAttempts from "@no-orm/public/tables/penguin_flight_attempts";
import * as Penguins from "@no-orm/public/tables/penguins";

// ...

export async function paginate({
  connection,
  penguin,
  token,
  limit,
}: PaginateArgs): Promise<readonly PenguinFlightAttempts.Row[]> {
  const query = sql.type(PenguinFlightAttempts.row)`
    SELECT ${PenguinFlightAttempts.columnsFragment}
    FROM ${PenguinFlightAttempts.tableFragment}
    WHERE penguin = ${penguin}
      AND ${token ? sql.fragment`id > ${token}` : sql.fragment`TRUE`}
    ORDER BY id
    LIMIT ${limit}`;

  return connection.any(query);
}

Again, try to keep your SQL queries as simple as possible both for code maintainability but also database performance.