Index variants

Below we will go over some variants of indexes and explain how no-orm will handle them.

We will be looking at these tables.

penguins.sql

CREATE TABLE penguins (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  species TEXT NOT NULL,
  waddle_speed_kph NUMERIC NOT NULL,
  favourite_snack TEXT,
  date_of_birth TIMESTAMP WITH TIME ZONE NOT NULL,

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

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()
);

Single column

A single column index will generate the following:

CREATE INDEX ON flight_attempts (penguin);

A getMany function:

export async function getManyByPenguin({
  connection,
  columns, // List of penguin ids.
}: GetManyByPenguinArgs): Promise<readonly Row[]> {
  // ...
}

A get function:

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

A getManyMap function:

export async function getManyByPenguinMap({
  connection,
  columns,
}: GetManyByPenguinArgs): Promise<Map<PenguinsRow["id"], Row[]>> {
  // ...
}

Multi-column index

CREATE INDEX ON penguins (name, species);

This gives you the same functions as a single column index except the function arguments are tuples of the columns:

export type GetManyBySpeciesAndDateOfBirth = BaseArgs & {
  columns: {
    name: string;
    species: string;
  }[];
};

// ...

Unique indexes

Adding a UNIQUE constraint to your indexes changes some of the return types of the functions.

CREATE UNIQUE INDEX ON penguins (name);

The return type is Row | null rather than Row[].

export async function getByName({
  connection,
  name,
}: GetByNameArgs): Promise<Row | null> {
  // ...
}

Partial indexes

An index with a predicate, for example:

CREATE INDEX ON flight_attempts (failure_reason)
WHERE failure_reason IS NOT NULL;

To leverage this index, your SQL query needs to include a WHERE failure_reason IS NOT NULL filter.

no-orm is not smart enough to understand what SQL filter to create when generating code for partial indexes. As such, no-orm will ignore partial indexes when generating functions.

To fully leverage partial indexes with predicates, you will have to hand-write your own SQL.

Functional indexes

An index with a function applied to it, for example:

CREATE INDEX ON penguins (LOWER(name));

Similar to a partial index to leverage this index, your SQL needs to include a WHERE LOWER(name) = ? filter.

Again, no-orm is not smart enough and so will ignore these indexes when generating code.