An index-first approach

So far we’ve only seen how to query a table based on its primary key. But what about querying by other columns?

For example, what if wanted to query for all of the flight attempts relating to a penguin?

flight-attempts.sql

CREATE TABLE flight_attempts (
  id SERIAL PRIMARY KEY,
  penguin INT NOT NULL REFERENCES penguins(id),
  method flight_attempt_method NOT NULL,
  attempted_at TIMESTAMP WITH TIME ZONE NOT NULL,
  altitude_cm INT NOT NULL,
  success BOOLEAN NOT NULL,
  failure_reason TEXT,

  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
);

Use an index

Creating a database index on this column will tell no-orm to generate a query on that column for you.

CREATE INDEX ON flight_attempts (penguin);

export async function getByPenguin({
  connection,
  penguin,
}: GetByPenguinArgs): Promise<readonly Row[]> {
  // ...
}