Building a sample app

In this section, we will walk through the process of creating of API server. Here you can get an overall idea of how Orchid ORM looks and feels, what problem it solves, and see the benefits and possible drawbacks.

Feel free to skip it, or briefly scroll it, as it turned out to be embarrassingly long. Still, it can be useful to peek how one or another case can be handled with Orchid ORM.

We are going to build an API for a blog site with users, articles, and tags, users can follow each other. It is inspired by realworld API spec.

Full code is available here.

API routes

POST /users: register new user
- JSON payload:
  - username: string
  - email: string
  - password: string
- Responds with user object and auth token
POST /users/auth: login
- JSON payload:
  - email: string
  - password: string
- Responds with user object and auth token
POST /users/:username/follow: follow a user
- No payload and no response needed
DELETE /users/:username/follow: unfollow a user
- No payload and no response needed
GET /articles: get a list of articles
- URI params:
  - author: filter articles by the username of the author
  - tag: filter articles by tag
  - feed: list articles only from authors which the current user is following
  - favorite: list only articles favorited by the current user
  - limit: limit articles
  - offset: offset articles
- Responds with article data
POST /articles: create a new article
- JSON payload:
  - slug: string
  - title: string
  - body: string
  - tags: array of strings
- Responds with article data
PATCH /articles/:slug: update article
- JSON payload:
  - slug?: string
  - title?: string
  - body?: string
  - tags?: array of strings
- Responds with article data
POST /articles/:slug/favorite
- JSON payload:
  - favorite: true to make favorite, false to un-favorite the article
- No response is needed
DELETE /articles/:slug: delete article
- No response is needed

type AuthResponse = {
  user: {
    id: number;
    username: string;
    email: string;
  };
  token: string;
};

type ArticleResponse = {
  slug: string;
  title: string;
  body: string;
  // how much users have favorited this article
  favoritesCount: number;
  // whether requesting user has favorited this article
  favorited: boolean;
  tags: string[];
  author: {
    username: string;
    // following means if the user who performs the request is following this user
    following: boolean;
  };

  // Postgres is returning dates in such format: `2022-11-04 10:53:02.129306 +00:00`
  // but this format is not supported by all browsers
  // As a bonus, both transferring and parsing date as an epoch number is more efficient, so let's use numbers for dates:
  createdAt: number;
  udpatedAt: number;
};

initialize the project

Let's init the project:

mkdir blog-api
cd blog-api
npm init
git init .
mkdir src

Add a .gitignore:

text

node_modules

# local environment variables
.env.local

The first thing we need in every node.js project is a TypeScript:

npm i -D typescript @types/node

The second thing to add in every node.js project is eslint with prettier, it takes quite a long list of dependencies and few file changes, check this commit for an example configuration.

We won't get stuck here on the topic of configuring the server and test framework, here is a commit for sample server configuration, and here is commit for configuring tests.

For the sample application, I chose fastify as a server framework because it is easier to set up (async error handling out of the box, unlike express), has more concise syntax for routes, and it includes a very nice utility for testing out of the box. Of course, you can use Orchid ORM with any other framework.

setup Orchid ORM

You can follow quickstart to initialize the project instead of this section. Here it's described how to set up everything from scratch.

Install Orchid ORM dependencies:

npm i orchid-orm pqb orchid-orm-schema-to-zod
# dev dependencies:
npm i -D rake-db orchid-orm-test-factory

See details for each dependency in a Quickstart.

Place database URLs to .env.local file (which should be listed in .gitignore), one database for development and a second for tests:

text

DATABASE_URL=postgres://user:password@localhost:5432/blog-api
DATABASE_URL_TEST=postgres://user:password@localhost:5432/blog-api-test

Load these variables in the app, this is a good practice to validate variables so our team members can see if something is missing, we can use zod for validation:

// src/config.ts
import { z } from 'zod';
import dotenv from 'dotenv';

dotenv.config({ path: '.env.local' });
dotenv.config({ path: '.env' });

const env = z
  .object({
    PORT: z.number().default(3000),
    NODE_ENV: z
      .literal('development')
      .or(z.literal('production'))
      .or(z.literal('test'))
      .default('development'),
    DATABASE_URL: z.string(),
    // DATABASE_URL_TEST is optional because production may not have it
    DATABASE_URL_TEST: z.string().optional(),
  })
  .parse(process.env);

export const config = {
  ...env,
  currentDatabaseUrl:
    env.NODE_ENV === 'test' ? env.DATABASE_URL_TEST : env.DATABASE_URL,
};

Now, config has DATABASE_URL for the dev database, DATABASE_URL_TEST for the test database, and currentDatabaseUrl with the database for the current environment.

Create the main file for the database instance:

// src/db.ts
import { orchidORM } from 'orchid-orm';
import { config } from './config';

export const db = orchidORM(
  {
    databaseURL: config.currentDatabaseUrl,
    log: true,
  },
  {
    // tables will be listed here
  },
);

Define a base table class which will be used later to extend tables from.

By default, timestamps are returned as strings, the same as when loading timestamps from databases directly.

For this API let's agree to return timestamps as epoch numbers (it's efficient and simple to use), but if you prefer to deal with Date objects write t.timestamp().asDate() instead.

The text column type requires min and max values to be passed, they are required to ensure that empty strings or strings of enormous length won't be allowed by your API backend.

Setting min and max for each text column may be tiresome, let's override a text method to have default min and max.

// src/lib/baseTable.ts
import { createBaseTable } from 'orchid-orm';

export const BaseTable = createBaseTable({
  columnTypes: (t) => ({
    ...t,
    // set default min and max for all text columns
    text: (min = 3, max = 100) => t.text(min, max),
    // parse timestamps to numbers
    timestamp: () => t.timestamp().asNumber(),
  }),
});

Create a script that we will use from a terminal to generate and run migrations:

// src/scripts/db.ts
import path from 'path';
import { rakeDb } from 'rake-db';
import { config } from '../config';
import { appCodeUpdater } from 'orchid-orm/codegen';
import { BaseTable } from '../lib/baseTable';

const options = [{ databaseURL: config.DATABASE_URL }];

// when running in production we don't need to test the database
if (config.NODE_ENV !== 'production') {
  const url = config.DATABASE_URL_TEST;
  if (!url) {
    throw new Error('DATABASE_URL_TEST env variable is missing');
  }
  options.push({ databaseURL: url });
}

// pass options and migrationPath to `rakeDb`
export const change = rakeDb(options, {
  baseTable: BaseTable,
  migrationsPath: '../migrations',
  appCodeUpdater: appCodeUpdater({
    tablePath: (tableName) => `../app/tables/${tableName}.table.ts`,
    ormPath: '../db.ts',
  }),
});

appCodeUpdater is optional. When configured, this will automatically add and update table files in the project.

Add the script to package.json scripts section:

json

{
  "scripts": {
    "db": "ts-node src/scripts/db.ts"
  }
}

Now we can create databases from the command line:

npm run db create

If the database user specified in .env.local is not a superuser, this command will ask for a superuser username/password to create databases.

After successfully running it will print:

text

Database blog-api successfully created
Created versions table
Database blog-api-test successfully created
Created versions table

So we can see it created two databases. Each of them has a special table to track which migrations were already applied and which were not.

Add a jest-setup.ts to the root of the project.

The following makes every test case that makes db queries wrapped in a transaction with rollback, so that every change will seamlessly disappear.

import { testTransaction } from 'orchid-orm';
import { db } from './src/db';

beforeAll(async () => {
  await testTransaction.start(db);
});

beforeEach(async () => {
  await testTransaction.start(db);
});

afterEach(async () => {
  await testTransaction.rollback(db);
});

afterAll(async () => {
  await testTransaction.close(db);
});

Add it to package.json "jest" section:

json

{
  "jest": {
    "setupFilesAfterEnv": ["./jest-setup.ts"]
  }
}

user endpoints

Usually, each table should have a primary key column. We will use t.identity().primaryKey() for this purpose, it is an autoincrementing integer type. Another available option for primary keys is to use t.uuid().primaryKey().

It is a good idea to have createdAt and updatedAt columns in every table, even if it is not asked in the requirements, these columns may come in handy later, for displaying, and sorting by them, updatedAt may be used for cache invalidation. Add them to the table by writing: ...t.timestamps().

Each column has a type, which is used to get a TypeScript type and a database type when running a migration. Some column methods have an effect only in migration, some methods are for validation.

add migration

Generate a new migration file by running:

npm run db new createUser

In the newly added file such content appears:

// src/migrations/*timestamp*_createUser.ts
import { change } from '../scripts/db';

change(async (db) => {
  await db.createTable('user', (t) => ({}));
});

Add user columns and timestamps:

import { change } from '../scripts/db';

change(async (db) => {
  await db.createTable('user', (t) => ({
    id: t.identity().primaryKey(),
    username: t.text().unique(),
    email: t.text().unique(),
    password: t.text(),
    ...t.timestamps(),
  }));
});

Apply the migration by running:

npm run db migrate

This will create a new table in the database. If case you need to roll it back, run:

npm run db rollback

table class

Migration script was configured to generate table files by itself.

Check src/app/tables/user.table.ts - it should have the following content:

// src/app/tables/user.ts
import { BaseTable } from '../../lib/baseTable';

export class UserTable extends BaseTable {
  readonly table = 'user';
  columns = this.setColumns((t) => ({
    id: t.identity().primaryKey(),
    username: t.text().unique(),
    email: t.text().unique(),
    password: t.text(),
    ...t.timestamps(),
  }));
}

src/app/tables is only a temporary destination, feel free to move files from here to where it feels better.

Let it be in src/app/user/user.table.ts.

There is an entry in src/db.ts for UserTable, it was also added automatically by running migration.

The columns of tables can serve as a validation schema. We can define that the username column is of text type, set a minimum and maximum length to it, and the table definition becomes our source of knowledge for how to validate data. To define once and use in various controllers.

Here we add a max validation to the username, email validation to the email, and min validation to the password columns:

// src/app/user/user.table.ts
import { BaseTable } from '../../lib/baseTable';

export class UserTable extends BaseTable {
  readonly table = 'user';
  columns = this.setColumns((t) => ({
    id: t.identity().primaryKey(),
    // min length is still 3, as defined in BaseTable configuration, overriding max value here
    username: t.text().unique().max(30),
    email: t.text().unique().email(),
    // overriding min value, max value defaults to 100
    password: t.text().min(8),
    // add `createdAt` and `updatedAt` timestamps
    ...t.timestamps(),
  }));
}

Consider the email column:

t.text() // this is a column type
  .unique() // mark the column as `unique`, this is used by migration and by test factory
  .email(); // validates email

Now that we have table class, and it is registered in db.ts, we can write queries like db.user.count(), db.user.select(...), and many others.

Define a test factory that we will use very soon:

// src/lib/test/testFactories.ts
import { ormFactory } from 'orchid-orm-test-factory';
import { db } from '../../db';

export const factory = ormFactory(db);

writing tests for registering user endpoint

Let's write tests for the first endpoint POST /users:

// src/app/user/user.controller.test.ts
import { testRequest } from '../../lib/test/testRequest';
import { factory } from '../../lib/test/testFactories';
import { db } from '../../db';

describe('user controller', () => {
  describe('POST /users', () => {
    // pick params to use for this request
    const params = factory.user.pick({
      username: true,
      email: true,
      password: true,
    });

    it('should register a new user, save it with hashed password, return a user and a token', async () => {
      // build an object with randomly generated data
      const data = params.build();

      // perform a POST request to the /users endpoint with the data
      const res = await testRequest.post('/users', data);

      // ensure that response has a correct data
      const json = res.json();
      expect(json).toMatchObject({
        user: {
          username: data.username,
          email: data.email,
        },
        token: expect.any(String),
      });

      // check that the user was saved to the database with the correct fields
      const savedUser = await db.user.findBy({ username: data.username });
      expect(savedUser).toMatchObject({
        username: data.username,
        email: data.email,
      });

      // ensure that we don't store plain text passwords to the database
      expect(savedUser.password).not.toBe(data.password);
    });

    it('should return error when username is taken', async () => {
      // build new randomly generated params
      const data = params.build();
      // create a new user with this specific username
      await factory.user.create({ username: data.username });

      // perform request
      const res = await testRequest.post('/users', data);

      // expect error because a user with such username was created before the request
      expect(res.json()).toMatchObject({
        message: 'Username is already taken',
      });
    });

    // similar to username test
    it('should return error when email is taken', async () => {
      const data = params.build();
      await factory.user.create({ email: data.email });

      const res = await testRequest.post('/users', data);

      expect(res.json()).toMatchObject({
        message: 'Email is already taken',
      });
    });
  });
});

testRequest is a custom helper around app.inject from fastify to perform a fake request without the app running.

express doesn't have such tools and can be tested with real requests, it's recommended to use axios for this purpose.

register user endpoint

On real projects, the auth will be more sophisticated, but for demo purposes, let's do a simple token-based auth.

Add JWT_SECRET to the .env file and config.ts:

// src/config.ts

const env = z.object({
  // ...snip
  JWT_SECRET: z.string(),
});

Here are utility functions for JSON web tokens:

// src/lib/jwt.ts
import { JwtPayload, sign, verify } from 'jsonwebtoken';
import { config } from '../config';

export const createToken = ({ id }: { id: number }): string => {
  return sign({ id }, config.JWT_SECRET);
};

export const verifyToken = (token: string): string | JwtPayload => {
  return verify(token, config.JWT_SECRET);
};

Utility functions for hashing and comparing passwords:

// src/lib/password.ts
import { genSalt, hash, compare } from 'bcrypt';

export const encryptPassword = async (password: string): Promise<string> => {
  const salt = await genSalt(10);
  return await hash(password, salt);
};

export const comparePassword = async (
  password: string,
  encrypted: string,
): Promise<boolean> => {
  return await compare(password, encrypted);
};

Now that we have verifyToken and comparePassword, we can use them in the test to check the token and the password:

it('should register a new user, save it with hashed password, return a user and a token', async () => {
  // ...snip

  expect(verifyToken(json.token)).toMatchObject({ id: savedUser.id });

  expect(comparePassword(data.password, savedUser.password));
});

Every node.js framework and even specific project usually have own custom way of validating request parameters, some people use middleware for this, and some use decorators like in the Nest.js framework. No matter how it is implemented, it should serve the purposes of:

request query, body, and route params must be validated properly
validated query, body, and params should have proper types
it is nice to have response validation in the dev/test environment, so you won't leak sensitive data by accident, and no need to write tedious tests to ensure that the response has correct types and shape.

Usually, out-of-the-box validation utility fails to satisfy all 3 points, I'm using a custom utility routeHandler to validate parameters and results by using zod schemas, here is the source. Not that I recommend using it in your project, as it looks quite messy, better to find a good library or a framework for this.

Main point here is that we have Zod schemas derived from Orchid table classes, and we should use them for validations of our request parameters.

Simple express.js example:

import { UserTable } from './user.table';

app.patch('/users/:id', (req, res) => {
  // picking id from UserTable.schema(), it could be an integer or uuid,
  // we don't have to know that in the controller
  const { id } = UserTable.schema().pick({ id: true }).parse(req.params);

  // parsing req.body by using UserTable.schema()
  const data = UserTable.schema().omit({ id: true }).parse(req.body);

  // using id and data to update user
});

To validate the incoming data of requests, and responds we are sending to user, let's create user.dto.ts file (dto stands for Data Transfer Object) with all the validation schemas:

// src/user/user.dto.ts
import { z } from 'zod';
import { UserTable } from './user.table';

// input data to register user
export const userRegisterDTO = UserTable.schema().pick({
  username: true,
  email: true,
  password: true,
});

// input data to login
export const userLoginDTO = UserTable.schema().pick({
  email: true,
  password: true,
});

// response data of register and login endpoints
export const authDTO = z.object({
  user: UserTable.schema().pick({
    id: true,
    username: true,
    email: true,
  }),
  token: z.string(),
});

// parameters to follow a user by username
export const usernameDTO = UserTable.schema().pick({
  username: true,
});

// will be used later in `articleDTO` for the article author object
export const userDTO = UserTable.schema()
  .pick({
    username: true,
  })
  .and(
    z.object({
      following: z.boolean(),
    }),
  );

And, finally, we can write the registering endpoint itself:

// src/app/user/user.controller.ts

import { routeHandler } from '../../lib/routeHandler';
import { db } from '../../db';
import { encryptPassword } from '../../lib/password';
import { createToken } from '../../lib/jwt';
import { ApiError } from '../../lib/errors';
import { userRegisterDTO, authDTO } from './user.dto';

export const registerUserRoute = routeHandler(
  {
    body: userRegisterDTO,
    result: authDTO,
  },
  async (req) => {
    try {
      const user = await db.user.select('id', 'email', 'username').create({
        ...req.body,
        password: await encryptPassword(req.body.password),
      });

      return {
        user,
        token: createToken({ id: user.id }),
      };
    } catch (err) {
      if (err instanceof db.user.error && err.isUnique) {
        if (err.columns.username) {
          throw new ApiError('Username is already taken');
        }
        if (err.columns.email) {
          throw new ApiError('Email is already taken');
        }
      }
      throw err;
    }
  },
);

Consider the code for creating a user:

const user = await db.user.select('username', 'email').create({
  ...req.body,
  password: await encryptPassword(req.body.password),
});

select before create changes RETURNING SQL statement, if we use create without select it will return a full record.

It is safe to use ...req.body because body was validated and all unknown keys were stripped out of it.

Inside of error handler, first, we check err instanceof db.user.error to know if this error belongs to the user table, then we check err.isUnique to ensure this is a unique violation error. And then we check err.columns.username and err.columns.email to determine which column has failed uniqueness to throw the corresponding error.

Add the route function to the router:

// src/routes.ts
import { FastifyInstance } from 'fastify';
import * as user from './app/user/user.controller';

export const routes = async (app: FastifyInstance) => {
  app.post('/users', user.registerUserRoute);
};

I'm skipping some framework-specific details: how to configure a server, and configure routing, this depends on the framework and your preferences.

Add corresponding tests:

// src/app/user/user.controller.test.ts

describe('user controller', () => {
  // ...snip

  describe('POST /users/auth', () => {
    it('should authorize user, return user object and auth token', async () => {
      const password = 'password';
      const user = await factory.user.create({
        password: await encryptPassword(password),
      });

      const res = await testRequest.post('/users/auth', {
        email: user.email,
        password,
      });

      const json = res.json();
      expect(json).toMatchObject({
        user: {
          username: user.username,
          email: user.email,
        },
        token: expect.any(String),
      });

      expect(verifyToken(json.token)).toMatchObject({ id: user.id });
    });

    it('should return error when email is not registered', async () => {
      const res = await testRequest.post('/users/auth', {
        email: 'not-registered@test.com',
        password: 'password',
      });

      expect(res.json()).toMatchObject({
        message: 'Email or password is invalid',
      });
    });

    it('should return error when password is invalid', async () => {
      const user = await factory.user.create();

      const res = await testRequest.post('/users/auth', {
        email: user.email,
        password: 'invalid password',
      });

      expect(res.json()).toMatchObject({
        message: 'Email or password is invalid',
      });
    });
  });
});

Controller code:

// src/app/user/user.controller.ts

export const loginUser = routeHandler(
  {
    body: userLoginDTO,
    result: authDTO,
  },
  async (req) => {
    const user = await db.user
      .select('id', 'email', 'username', 'password')
      .findByOptional({
        email: req.body.email,
      });

    if (!user || !(await comparePassword(req.body.password, user.password))) {
      throw new ApiError('Email or password is invalid');
    }

    return {
      // omit is an utility defined somewhere else
      user: omit(user, 'password'),
      token: createToken({ id: user.id }),
    };
  },
);

In the user query note that we use findByOptional method, which returns undefined when not found.

There is a similar findBy method that would throw a NotFoundError when not found, but here we want to check it manually.

Add the route function to the router:

// src/routes.ts
import { FastifyInstance } from 'fastify';
import * as user from './app/user/user.controller';

export const routes = async (app: FastifyInstance) => {
  app.post('/users', user.registerUserRoute);
  app.post('/users/auth', user.loginUserRoute);
};

follow and unfollow

Add a migration:

npm run db new createUserFollow

// src/migrations/*timestamp*_createUserFollow.ts
import { change } from '../scripts/db';

change(async (db) => {
  await db.createTable('userFollow', (t) => ({
    followingId: t.integer().foreignKey('user', 'id'),
    followerId: t.integer().foreignKey('user', 'id'),
    ...t.primaryKey(['followingId', 'followerId']),
  }));
});

This table has followingId for the user who is being followed, and the followerId for the one who follows. Both these columns have foreignKey which connects it with an id of user to ensure that the value always points to an existing user record.

With such syntax ...t.primaryKey([column1, column2]) we define a composite primary key. Internally Postgres will add a multi-column unique index and ensure that all of these columns are not null.

Apply it with npm run db migrate, and src/tables/userFollow.table.ts will appear.

Move it to src/app/user directory and modify:

// src/app/user/userFollow.table.ts
import { BaseTable } from '../../lib/baseTable';
import { UserTable } from './user.table';

export class UserFollowTable extends BaseTable {
  readonly table = 'userFollow';
  columns = this.setColumns((t) => ({
    // in the migration we have a string argument for the foreign table
    // in the model it can be a string as well, or as a callback with table class
    followingId: t.integer().foreignKey(() => UserTable, 'id'),
    followerId: t.integer().foreignKey(() => UserTable, 'id'),
    ...t.primaryKey(['followingId', 'followerId']),
  }));
}

Make sure the table file is linked in db.ts.

Adding followers and followings relations to the user table:

// src/app/user/user.table.ts
import { BaseTable } from '../../lib/baseTable';
import { UserFollowTable } from './userFollow.table';

export class UserTable extends BaseTable {
  // ...snip

  relations = {
    follows: this.hasMany(() => UserFollowTable, {
      columns: ['id'],
      references: ['followingId'],
    }),

    followings: this.hasMany(() => UserFollowTable, {
      columns: ['id'],
      references: ['followerId'],
    }),
  };
}

Tests for the follow/unfollow endpoints:

// src/app/user/user.controller.test.ts

describe('POST /users/:username/follow', () => {
  it('should follow a user', async () => {
    // create a user to perform the request from
    const currentUser = await factory.user.create();
    // create a user to follow
    const userToFollow = await factory.user.create();

    // perform request as a provided user
    await testRequest
      .as(currentUser)
      .post(`/users/${userToFollow.username}/follow`);

    // check that the userFollow record exists in the database
    const follows = await db.userFollow.where({
      followingId: userToFollow.id,
    });
    expect(follows).toEqual([
      {
        followerId: currentUser.id,
        followingId: userToFollow.id,
      },
    ]);
  });

  it('should return not found error when no user found by username', async () => {
    const currentUser = await factory.user.create();

    const res = await testRequest.as(currentUser).post(`/users/lalala/follow`);

    expect(res.json()).toEqual({
      message: 'Record is not found',
    });
  });
});

describe('DELETE /users/:username/follow', () => {
  it('should unfollow a user', async () => {
    const currentUser = await factory.user.create();
    const userToFollow = await factory.user.create({
      follows: { create: [{ followerId: currentUser.id }] },
    });

    await testRequest
      .as(currentUser)
      .delete(`/users/${userToFollow.username}/follow`);

    const follows = await db.userFollow.where({
      followingId: userToFollow.id,
    });
    expect(follows).toEqual([]);
  });

  it('should return not found error when no user found by username', async () => {
    const currentUser = await factory.user.create();

    const res = await testRequest.as(currentUser).post(`/users/lalala/follow`);

    // check that such userFollow record doesn't exist
    const exists = await db.userFollow
      .where({
        followingId: userToFollow.id,
      })
      .exists();
    expect(exists).toEqual(false);
  });
});

Follow user controller:

// src/app/user/user.controller.ts

export const followUserRoute = routeHandler(
  {
    params: usernameDTO,
  },
  async (req) => {
    const userId = getCurrentUserId(req);

    await db.user
      .findBy({
        username: req.params.username,
      })
      .follows.create({
        followerId: userId,
      });
  },
);

getCurrentUserId is a function to get the user id from the JWT token, leaving it beyond this tutorial, here is its source.

After defining the follows relation in the user table, db.user receives a follows property which allows doing different queries, and the code above shows the use of such chained create method.

If there is a need to do multiple queries it will wrap them in a transaction to prevent unexpected race conditions.

Orchid ORM strives to perform as few queries as possible to gain the maximum performance, and in this case, it does a single INSERT ... SELECT ... query, so it inserts userFollow from selecting the user record to use user id.

The findBy method will throw NotFoundError in case the record is not found, add a such section to the global error handler of your app to report such errors to the user:

if (error instanceof NotFoundError) {
  res.status(404).send({
    message: 'Record is not found',
  });
}

Unfollow user controller:

// src/app/user/user.controller.ts

export const unfollowUserRoute = routeHandler(
  {
    params: usernameDTO,
  },
  async (req) => {
    const userId = getCurrentUserId(req);

    await db.user
      .findBy({
        username: req.params.username,
      })
      .follows.findBy({
        followerId: userId,
      })
      .delete();
  },
);

Similarly to the code in follow controller, this code building query to delete userFollow.

Orchid ORM will perform one DELETE query by this code.

Add route functions to the router:

// src/routes.ts
import { FastifyInstance } from 'fastify';
import * as user from './app/user/user.controller';

export const routes = async (app: FastifyInstance) => {
  // ...snip
  app.post('/users/:username/follow', user.followUserRoute);
  app.delete('/users/:username/follow', user.unfollowUserRoute);
};

Create migration files:

npm run db new createArticle
npm run db new createTag
npm run db new createArticleTag
npm run db new createArticleFavorite

Article table migration:

// src/migrations/*timestamp*_createArticle.ts
import { change } from '../scripts/db';

change(async (db) => {
  await db.createTable('article', (t) => ({
    id: t.identity().primaryKey(),
    userId: t.integer().foreignKey('user', 'id').index(),
    slug: t.text().unique(),
    title: t.text(),
    body: t.text(),
    favoritesCount: t.integer(),
    ...t.timestamps(),
  }));
});

Tag table migration:

// src/migrations/*timestamp*_createTag.ts
import { change } from '../scripts/db';

change(async (db) => {
  await db.createTable('tag', (t) => ({
    id: t.identity().primaryKey(),
    name: t.text(),
    ...t.timestamps(),
  }));
});

Article tag join table migration:

// src/migrations/*timestamp*_createArticleTag.ts
import { change } from '../scripts/db';

change(async (db) => {
  await db.createTable('articleTag', (t) => ({
    articleId: t.integer().foreignKey('article', 'id'),
    tagId: t.integer().foreignKey('tag', 'id'),
    ...t.primaryKey(['tagId', 'articleId']),
  }));
});

Article favorite join table migration:

// src/migrations/*timestamp*_createArticleFavorite.ts
import { change } from '../scripts/db';

change(async (db) => {
  await db.createTable('articleFavorite', (t) => ({
    userId: t.integer().foreignKey('user', 'id'),
    articleId: t.integer().foreignKey('article', 'id'),
    ...t.primaryKey(['userId', 'articleId']),
  }));
});

Run migrations with npm run db migrate.

Move the tables from src/app/tables to src/app/... directories, and modify them to have relations.

Tag table:

// src/app/tag/tag.table.ts
import { BaseTable } from '../../lib/baseTable';
import { ArticleTagTable } from './articleTag.table';

export class TagTable extends BaseTable {
  readonly table = 'tag';
  columns = this.setColumns((t) => ({
    id: t.identity().primaryKey(),
    name: t.text().min(3).max(20),
    ...t.timestamps(),
  }));

  relations = {
    articleTags: this.hasMany(() => ArticleTagTable, {
      columns: ['id'],
      references: ['tagId'],
    }),
  };
}

The tag table has no relations in the example above, but only because they're not needed in future queries. Orchid ORM is designed to deal with circular dependencies without problems, so TagTable can use ArticleTable in the relation, and ArticleTable can have TagTable in the relation at the same time.

Article tag table:

// src/app/article/articleTag.table.ts
import { BaseTable } from '../../lib/baseTable';
import { TagTable } from '../tag/tag.table';

export class ArticleTagTable extends BaseTable {
  readonly table = 'articleTag';
  columns = this.setColumns((t) => ({
    articleId: t.integer().foreignKey('article', 'id'),
    tagId: t.integer().foreignKey('tag', 'id'),
    ...t.primaryKey(['tagId', 'articleId']),
  }));

  relations = {
    // this `tag` relation name is used in the article table `tags` relation in the `source` option
    tag: this.belongsTo(() => TagTable, {
      columns: ['tagId'],
      references: ['id'],
    }),
  };
}

Article favorite table:

// src/app/article/articleFavorite.table.ts
import { BaseTable } from '../../lib/baseTable';

export class ArticleFavoriteTable extends BaseTable {
  readonly table = 'articleFavorite';
  columns = this.setColumns((t) => ({
    userId: t.integer().foreignKey('user', 'id'),
    articleId: t.integer().foreignKey('article', 'id'),
    ...t.primaryKey(['userId', 'articleId']),
  }));
}

Article table:

// src/app/article/article.table.ts
import { BaseTable } from '../../lib/baseTable';
import { UserTable } from '../user/user.table';
import { ArticleTagTable } from './articleTag.table';
import { TagTable } from '../tag/tag.table';
import { ArticleFavoriteTable } from './articleFavorite.table';

export class ArticleTable extends BaseTable {
  readonly table = 'article';
  columns = this.setColumns((t) => ({
    id: t.identity().primaryKey(),
    userId: t.integer().foreignKey('user', 'id').index(),
    // it is important to set `min` and `max` for text fields
    // to make sure that the user won't submit empty strings or billion chars long strings:
    slug: t.text().unique().min(10).max(200),
    title: t.text().min(10).max(200),
    body: t.text().min(100).max(100000),
    favoritesCount: t.integer(),
    ...t.timestamps(),
  }));

  relations = {
    author: this.belongsTo(() => UserTable, {
      columns: ['userId'],
      references: ['id'],
    }),

    favorites: this.hasMany(() => ArticleFavoriteTable, {
      columns: ['id'],
      references: ['articleId'],
    }),

    articleTags: this.hasMany(() => ArticleTagTable, {
      columns: ['id'],
      references: ['articleId'],
    }),

    tags: this.hasMany(() => TagTable, {
      through: 'articleTags',
      source: 'tag',
    }),
  };
}

Make sure all tables are linked in db.ts.

list articles endpoint

I write one test for one feature, one by one, and this helps me a lot when writing backends and libraries.

For this tutorial, I'm listing the whole test suite for endpoint only to keep the tutorial a bit more compact.

Here are all tests for the GET /articles endpoint:

// src/app/article/article.controller.test
import { factory } from '../../lib/test/testFactories';
import { testRequest } from '../../lib/test/testRequest';
import { itShouldRequireAuth } from '../../lib/test/testUtils';

describe('article controller', () => {
  describe('GET /articles', () => {
    it('should load articles for public request, favorited and author following fields must be false, newer articles should go first', async () => {
      const author = await factory.user.create();
      await factory.article.createList(2, { userId: author.id });

      const res = await testRequest.get('/articles');

      const data = res.json();
      expect(data.length).toBe(2);

      const [first, second] = data;
      expect(first.favorited).toBe(false);
      expect(first.author.following).toBe(false);
      expect(first.createdAt).toBeGreaterThan(second.createdAt);
    });

    it('should load articles for authorized user, favorited and author following fields must have proper values, newer articles should go first', async () => {
      const currentUser = await factory.user.create();

      const notFollowedAuthor = await factory.user.create();
      await factory.article.create({ userId: notFollowedAuthor.id });

      const followedAuthor = await factory.user.create({
        follows: {
          create: [
            {
              followerId: currentUser.id,
            },
          ],
        },
      });

      await factory.article.create({
        userId: followedAuthor.id,
        favorites: {
          create: [
            {
              userId: currentUser.id,
            },
          ],
        },
      });

      const res = await testRequest.as(currentUser).get('/articles');

      const data = res.json();
      const [first, second] = data;

      expect(second.favorited).toBe(false);
      expect(second.author.following).toBe(false);

      expect(first.favorited).toBe(true);
      expect(first.author.following).toBe(true);
    });

    describe('query params', () => {
      describe('author', () => {
        it('should filter articles by username of author', async () => {
          const [author1, author2] = await factory.user.createList(2);

          await factory.article.create({ userId: author1.id });
          await factory.article.create({ userId: author2.id });

          const res = await testRequest.get('/articles', {
            query: {
              author: author1.username,
            },
          });

          const data = res.json();
          expect(data.length).toBe(1);
          expect(data[0].author.username).toBe(author1.username);
        });
      });

      describe('tag', () => {
        it('should filter articles by tag', async () => {
          const author = await factory.user.create();

          // create article with matching tag
          await factory.article.create({
            userId: author.id,
            articleTags: {
              create: ['one', 'two'].map((name) => ({
                tag: {
                  create: {
                    name,
                  },
                },
              })),
            },
          });

          // create article with different tags
          await factory.article.create({
            userId: author.id,
            articleTags: {
              create: ['three', 'four'].map((name) => ({
                tag: {
                  create: {
                    name,
                  },
                },
              })),
            },
          });

          // create article without tags
          await factory.article.create({ userId: author.id });

          const res = await testRequest.get('/articles', {
            query: {
              tag: 'one',
            },
          });

          const data = res.json();
          expect(data.length).toBe(1);
          expect(data[0].tags).toEqual(['one', 'two']);
        });
      });

      describe('feed', () => {
        itShouldRequireAuth(() =>
          testRequest.get('/articles', {
            query: {
              feed: 'true',
            },
          }),
        );

        it('should return articles from followed authors for authorized user', async () => {
          const currentUser = await factory.user.create();
          const unfollowedAuthor = await factory.user.create();
          const followedAuthor = await factory.user.create({
            follows: {
              create: [
                {
                  followerId: currentUser.id,
                },
              ],
            },
          });

          const expectedArticles = await factory.article.createList(2, {
            userId: followedAuthor.id,
          });

          await factory.article.createList(2, {
            userId: unfollowedAuthor.id,
          });

          const res = await testRequest.as(currentUser).get('/articles', {
            query: {
              feed: 'true',
            },
          });

          const data = res.json();
          expect(data.length).toBe(2);
          expect(data).toMatchObject(
            expectedArticles
              .reverse()
              .map((article) => ({ slug: article.slug })),
          );
        });
      });

      describe('favorite', () => {
        itShouldRequireAuth(() =>
          testRequest.get('/articles', {
            query: {
              favorite: 'true',
            },
          }),
        );

        it('should returns only articles favorited by current user', async () => {
          const [currentUser, author] = await factory.user.createList(2);

          const favoritedArticles = await factory.article.createList(2, {
            userId: author.id,
            favorites: {
              create: [
                {
                  userId: currentUser.id,
                },
              ],
            },
          });

          await factory.article.create({ userId: author.id });

          const res = await testRequest.as(currentUser).get('/articles', {
            query: {
              favorite: 'true',
            },
          });

          const data = res.json();
          expect(data).toMatchObject(
            favoritedArticles
              .reverse()
              .map((article) => ({ slug: article.slug })),
          );
        });
      });
    });
  });
});

Note that all nested create code of the factory.user and factory.article is also applicable to the db.user and db.article.

itShouldRequireAuth is a utility for tests to save some lines of code when testing protected routes.

// src/lib/test/testUtils.ts
export const itShouldRequireAuth = (
  req: () => Promise<{ statusCode: number; json(): unknown }>,
) => {
  it('should require authorization', async () => {
    const res = await req();
    expectUnauthorized(res);
  });
};

export const expectUnauthorized = (res: {
  statusCode: number;
  json(): unknown;
}) => {
  expect(res.statusCode).toBe(401);
  expect(res.json()).toEqual({
    message: 'Unauthorized',
  });
};

Define the articleDTO schema, it will be used for response in GET /articles, PATCH /articles/:id, POST /articles, so better to have it separately:

// src/app/article/article.dto.ts
import { userDTO } from '../user/user.table';
import { z } from 'zod';

const tagListDTO = TagTable.schema().shape.name.array();

export const articleDTO = ArticleTable.schema()
  .pick({
    slug: true,
    title: true,
    body: true,
    favoritesCount: true,
    createdAt: true,
    updatedAt: true,
  })
  .and(
    z.object({
      tags: tagListDTO,
      favorited: z.boolean(),
      author: userDTO,
    }),
  );

Controller code:

import { routeHandler } from '../../lib/routeHandler';
import { db } from '../../db';
import { getOptionalCurrentUserId } from '../user/user.service';
import { z } from 'zod';
import { UnauthorizedError } from '../../lib/errors';
import { articleDTO } from './article.dto';

export const listArticlesRoute = routeHandler(
  {
    query: z.object({
      author: z.string().optional(),
      tag: z.string().optional(),
      feed: z.literal('true').optional(),
      favorite: z.literal('true').optional(),
      limit: z
        .preprocess((s) => parseInt(s as string), z.number().min(1).max(20))
        .default(20),
      offset: z
        .preprocess((s) => parseInt(s as string), z.number().min(0))
        .optional(),
    }),
    result: articleDTO.array(),
  },
  (req) => {
    // currentUserId will be an id for authorized, undefined for not authorized
    const currentUserId = getOptionalCurrentUserId(req);

    let query = db.article
      .select(
        'slug',
        'title',
        'body',
        'favoritesCount',
        'createdAt',
        'updatedAt',
        {
          // `pluck` method collects a column into an array
          // order is ASC by default
          tags: (q) => q.tags.order('name').pluck('name'),
          favorited: (q) =>
            currentUserId
              ? // if currentUserId is defined, return exists query
                q.favorites.where({ userId: currentUserId }).exists()
              : // if no currentUserId, return raw 'false' SQL of boolean type
                q.sql`false`.type((t) => t.boolean()),
          author: (q) =>
            q.author.select('username', {
              // we load the following similar to the favorited above
              following: currentUserId
                ? (q) => q.follows.where({ followerId: currentUserId }).exists()
                : q.sql((t) => t.boolean())`false`,
            }),
        },
      )
      .order({
        createdAt: 'DESC',
      })
      // limit has default 20 in the params schema above
      .limit(req.query.limit)
      // offset parameter is optional, and it is fine to pass `undefined` to the .offset method
      .offset(req.query.offset);

    // filtering articles by author, tag, and other relations by using `whereExists`
    if (req.query.author) {
      query = query.whereExists('author', (q) =>
        q.where({ username: req.query.author }),
      );
    }

    if (req.query.tag) {
      query = query.whereExists('tags', (q) =>
        q.where({ name: req.query.tag }),
      );
    }

    if (req.query.feed || req.query.favorite) {
      if (!currentUserId) throw new UnauthorizedError();

      if (req.query.feed) {
        query = query.whereExists('author', (q) =>
          // `whereExists` can be nested to filter by the relation of the relation
          q.whereExists('follows', (q) =>
            q.where({ followerId: currentUserId }),
          ),
        );
      }

      if (req.query.favorite) {
        query = query.whereExists('favorites', (q) =>
          q.where({ userId: currentUserId }),
        );
      }
    }

    // query is Promise-like and will be awaited automatically
    return query;
  },
);

// src/routes.ts
import * as article from './app/article/article.controller';

export const routes = async (app: FastifyInstance) => {
  // ...snip
  app.get('/articles', article.listArticlesRoute);
};

refactoring code by using repo

Currently, the controller code for listing articles looks messy: too many things are happening, too many query details to read the code quickly and clearly.

Here I want to tell about one special feature of the Orchid ORM which doesn't exist in other node.js ORMs. There are similar capabilities in Objection.js and Openrecord, but they aren't type-safe.

Let's start from the article's author field: querying author includes some nuances specific to the user table, better to keep such queries encapsulated inside the related feature folder.

Create a new file user.repo.ts with the content:

// src/app/user/user.repo.ts
import { createRepo } from 'orchid-orm';
import { db } from '../../db';

export const userRepo = createRepo(db.user, {
  queryMethods: {
    selectDTO(q, currentUserId: number | undefined) {
      return q.select('username', {
        following: currentUserId
          ? (q) => q.follows.where({ followerId: currentUserId }).exists()
          : q.sql((t) => t.boolean())`false`,
      });
    },
  },
});

And now we can simplify querying the author object in the articles` controller:

// src/article/article.controller.ts
import { userRepo } from '../user/user.repo';

export const listArticlesRoute = routeHandler(
  // ...snip
  (req) => {
    let query = db.article.select(
      // ...snip
      {
        // ...snip
        author: (q) => userRepo(q.author).selectDTO(currentUserId),
      },
    );

    // ...snip
  },
);

Note that in the user.repo.ts the selectDTO has two arguments: first is a user query, and second is currentUserId.

The first argument is injected automatically, so in the controller, we are only passing the rest of the arguments. An editor can be confused by this and print a warning, but TypeScript understands it well, if you put a string instead of currentUserId TS will show an error.

Later we will load the same article fields in other endpoints, and it makes sense for both readability and re-usability to move the common selection of articles into articleRepo.selectDTO:

// src/app/article/article.repo.ts
import { createRepo } from 'orchid-orm';
import { db } from '../../db';
import { userRepo } from '../user/user.repo';

export const articleRepo = createRepo(db.article, {
  queryMethods: {
    selectDTO(q, currentUserId: number | undefined) {
      return q.select(
        'slug',
        'title',
        'body',
        'favoritesCount',
        'createdAt',
        'updatedAt',
        {
          tags: (q) => q.tags.order('name').pluck('name'),
          favorited: currentUserId
            ? (q) => q.favorites.where({ userId: currentUserId }).exists()
            : q.sql((t) => t.boolean())`false`,
          author: (q) => userRepo(q.author).selectDTO(currentUserId),
        },
      );
    },
  },
});

When using the repo in a subquery, as we did for the author field, need to wrap a subquery into a repo like userRepo(q.user).selectDTO(...).

But if the repo is not inside the subquery, you can use the repo object directly to build queries:

// src/article/article.controller.ts
import { userRepo } from '../user/user.repo';

export const listArticlesRoute = routeHandler(
  // ...snip
  (req) => {
    const currentUserId = getOptionalCurrentUserId(req);

    let query = articleRepo
      .selectDTO(currentUserId)
      .order({
        createdAt: 'DESC',
      })
      .limit(req.query.limit)
      .offset(req.query.offset);

    // ...snip
  },
);

Let's move all article filtering logic into repo methods:

// src/app/article/article.repo.ts
import { createRepo } from 'orchid-orm';
import { db } from '../../db';
import { userRepo } from '../user/user.repo';

export const articleRepo = createRepo(db.article, {
  queryMethods: {
    selectDTO(q, currentUserId: number | undefined) {
      return q.select(
        'slug',
        'title',
        'body',
        'favoritesCount',
        'createdAt',
        'updatedAt',
        {
          tags: (q) => q.tags.order('name').pluck('name'),
          favorited: currentUserId
            ? (q) => q.favorites.where({ userId: currentUserId }).exists()
            : q.sql((t) => t.boolean())`false`,
          author: (q) => userRepo(q.author).selectDTO(currentUserId),
        },
      );
    },
    filterByAuthorUsername(q, username: string) {
      return q.whereExists('author', (q) => q.where({ username }));
    },
    filterByTag(q, name: string) {
      return q.whereExists('tags', (q) => q.where({ name }));
    },
    filterForUserFeed(q, currentUserId: number) {
      return q.whereExists('author', (q) =>
        q.whereExists('follows', (q) => q.where({ followerId: currentUserId })),
      );
    },
    filterFavorite(q, currentUserId: number) {
      return q.whereExists('favorites', (q) =>
        q.where({ userId: currentUserId }),
      );
    },
  },
});

And now the article controller can look so fabulous:

// src/article/article.controller.ts
// ...imports

export const listArticlesRoute = routeHandler(
  // ...snip
  (req) => {
    const currentUserId = getOptionalCurrentUserId(req);

    let query = articleRepo
      .selectDTO(currentUserId)
      .order({
        createdAt: 'DESC',
      })
      .limit(req.query.limit)
      .offset(req.query.offset);

    if (req.query.author) {
      query = query.filterByAuthorUsername(req.query.author);
    }

    if (req.query.tag) {
      query = query.filterByTag(req.query.tag);
    }

    if (req.query.feed || req.query.favorite) {
      if (!currentUserId) throw new UnauthorizedError();

      if (req.query.feed) {
        query = query.filterForUserFeed(currentUserId);
      }

      if (req.query.favorite) {
        query = query.filterFavorite(currentUserId);
      }
    }

    return query;
  },
);

With the help of repositories, the controller code became more than twice shorter, each repo method can be reused individually in other controllers or other repositories, the code became easy to read and grasp.

create an article

Here is the test for creating an article:

// src/app/article/article.controller.test.ts

describe('article controller', () => {
  // ...snip

  describe('POST /articles', () => {
    const params = factory.article
      .pick({
        slug: true,
        title: true,
        body: true,
      })
      .build();

    itShouldRequireAuth(() =>
      testRequest.post('/articles', {
        ...params,
        tags: [],
      }),
    );

    it('should create article without tags, return articleDTO', async () => {
      const currentUser = await factory.user.create();

      const res = await testRequest.as(currentUser).post('/articles', {
        ...params,
        tags: [],
      });

      const data = res.json();
      expect(data.tags).toEqual([]);
      expect(data.author.username).toBe(currentUser.username);
    });

    it('should create article and tags, should connect existing tags, return articleDTO', async () => {
      const currentUser = await factory.user.create();
      const tagId = await db.tag.get('id').create({ name: 'one' });

      const res = await testRequest.as(currentUser).post('/articles', {
        ...params,
        tags: ['one', 'two'],
      });

      const data = res.json();
      expect(data.tags).toEqual(['one', 'two']);
      expect(data.favorited).toBe(false);
      expect(data.author.username).toBe(currentUser.username);
      expect(data.author.following).toBe(false);

      const savedArticle = await db.article
        .findBy({ slug: data.slug })
        .select('slug', 'title', 'body', {
          tags: (q) => q.tags.order('name'),
        });

      expect(savedArticle).toMatchObject(params);
      expect(savedArticle.tags).toMatchObject([
        {
          id: tagId,
          name: 'one',
        },
        {
          name: 'two',
        },
      ]);
    });
  });
});

Implementation of the controller:

// src/app/article/article.dto.ts

export const articleCreateDTO = ArticleTable.schema()
  .pick({
    slug: true,
    title: true,
    body: true,
  })
  .extend({
    tags: tagListDTO,
  });

// src/app/article/article.controller.ts

export const createArticleRoute = routeHandler(
  {
    body: articleCreateDTO,
    result: articleDTO,
  },
  (req) => {
    const currentUserId = getCurrentUserId(req);

    // wrap creating an article and retrieving it to the transaction
    return db.$transaction(async () => {
      const { tags, ...params } = req.body;

      const articleId = await db.article.get('id').create({
        ...params,
        favoritesCount: 0,
        userId: currentUserId,
        articleTags: {
          create: tags.map((name) => ({
            tag: {
              connectOrCreate: {
                where: { name },
                create: { name },
              },
            },
          })),
        },
      });

      return articleRepo.selectDTO(currentUserId).find(articleId);
    });
  },
);

This example demonstrates the use of nested create with nested connectOrCreate: it will try to find a tag by name and will create a tag only if not found.

// src/routes.ts
import * as article from './app/article/article.controller';

export const routes = async (app: FastifyInstance) => {
  // ...snip
  app.post('/articles', article.listArticlesRoute);
};

update article endpoint

One specific thing which is needed to be tested properly is tags: when the user is updating article tags, the app should create new tag records in case they didn't exist before, it should delete tags that aren't used by any article, and connect the article to all tags properly.

So if in the future the app will have a tags endpoint that lists all tags, there won't be duplicates.

Tests for the endpoint:

describe('article controller', () => {
  // ...snip

  describe('PATCH /articles/:slug', () => {
    const params = factory.article
      .pick({
        slug: true,
        title: true,
        body: true,
      })
      .build();

    // this test helper was defined earlier
    itShouldRequireAuth(() =>
      testRequest.patch('/articles/article-slug', params),
    );

    it('should return unauthorized error when trying to update article of other user', async () => {
      const currentUser = await factory.user.create();
      const author = await factory.user.create();
      const article = await factory.article.create({
        userId: author.id,
      });

      const res = await testRequest
        .as(currentUser)
        .patch(`/articles/${article.slug}`, params);

      // this test helper was defined earlier
      expectUnauthorized(res);
    });

    it('should update article fields', async () => {
      const currentUser = await factory.user.create();
      const article = await factory.article.create({
        userId: currentUser.id,
      });

      const res = await testRequest
        .as(currentUser)
        .patch(`/articles/${article.slug}`, params);

      const data = res.json();
      expect(data).toMatchObject(params);
    });

    it('should set new tags to article, create new tags, delete not used tags', async () => {
      const [currentUser, otherAuthor] = await factory.user.createList(2);

      const article = await factory.article.create({
        userId: currentUser.id,
        articleTags: {
          create: ['one', 'two'].map((name) => ({
            tag: {
              create: {
                name,
              },
            },
          })),
        },
      });

      await factory.article.create({
        userId: otherAuthor.id,
        articleTags: {
          create: ['two', 'three'].map((name) => ({
            tag: {
              create: {
                name,
              },
            },
          })),
        },
      });

      const res = await testRequest
        .as(currentUser)
        .patch(`/articles/${article.slug}`, {
          tags: ['two', 'new tag'],
        });

      const data = res.json();
      expect(data.tags).toEqual(['new tag', 'two']);

      const allTagNames = await db.tag.pluck('name');
      expect(allTagNames).not.toContain('one');
    });
  });
});

Implementation:

// src/app/article/article.dto.ts

export const articleUpdateDTO = articleCreateDTO
  .extend({
    favorite: z.boolean(),
  })
  .partial();

export const articleSlugDTO = ArticleTable.schema().pick({ slug: true });

// src/app/article/article.controller.ts

export const updateArticleRoute = routeHandler(
  {
    body: articleUpdateDTO,
    params: articleSlugDTO,
    result: articleDTO,
  },
  (req) => {
    const currentUserId = getCurrentUserId(req);

    return db.$transaction(async (db) => {
      const { slug } = req.params;

      // retrieve required fields and the current tags of article
      const article = await articleRepo
        .findBy({ slug })
        .select('id', 'userId', {
          tags: (q) => q.tags.select('id', 'name'),
        });

      if (article.userId !== currentUserId) {
        throw new UnauthorizedError();
      }

      const { tags, favorite, ...params } = req.body;

      await articleRepo
        .find(article.id)
        .update(params)
        // updateTags is a repo method, see below
        .updateTags(article.tags, tags);

      return await articleRepo.selectDTO(currentUserId).find(article.id);
    });
  },
);

The logic for updating tags is complex enough, so it is encapsulated into the article repo.

// src/app/article/article.repo.ts
import { createRepo } from 'orchid-orm';
import { db } from '../../db';

export const articleRepo = createRepo(db.article, {
  queryMethods: {
    // ...snip
  },
  queryOneWithWhereMethods: {
    async updateTags(q) {
      // TODO
    },
  },
});

All previous repo methods were placed under queryMethods, but here we place it under the queryOneWithWhereMethods. The difference is in the type of the q parameter.

It is forbidden to create related records from the query which returns multiple records, for example:

// will result in a TS error
db.article.where({ id: { in: [1, 2, 3] } }).update({
  articleTags: {
    create: { ...someData },
  },
});

This code not only creates new articleTags but also connects them to the article. If we select 3 articles and create articleTags for the query it wouldn't make much sense because a single articleTag can be connected to a single article only, but cannot connect to many.

That's why the type of q have to indicate that it is returning a single record.

Also, the update query must be applied only after we pass search conditions, to make sure we won't update all records in the database by mistake.

// will result in TS error
db.article.update({ ...data });

That's why the type of q have to indicate it has some search statements. So we placed a new query method into queryOneWithWhereMethods where q is promised to have search conditions and to search for a single record.

Here is the updateTags implementation:

// src/app/article/article.repo.ts
import { createRepo } from 'orchid-orm';
import { db } from '../../db';
import { tagRepo } from './tag.repo';

export const articleRepo = createRepo(db.article, {
  queryMethods: {
    // ...snip
  },
  queryOneWithWhereMethods: {
    async updateTags(
      q,
      // tags which article is connected to at the moment
      currentTags: { id: number; name: string }[],
      // tag names from user parameters to use for the article
      tags?: string[],
    ) {
      const currentTagNames = currentTags.map(({ name }) => name);
      const addTagNames = tags?.filter(
        (name) => !currentTagNames.includes(name),
      );
      const removeTagIds = tags
        ? currentTags
            .filter(({ name }) => !tags.includes(name))
            .map((tag) => tag.id)
        : [];

      await q.update({
        articleTags: {
          // note the `?` mark: nothing will happen if `addTagNames` is not defined
          create: addTagNames?.map((name) => ({
            tag: {
              connectOrCreate: {
                where: { name },
                create: { name },
              },
            },
          })),
          // won't delete anything if we pass an empty array
          delete: removeTagIds.length ? { tagId: { in: removeTagIds } } : [],
        },
      });

      if (removeTagIds.length) {
        // `deleteUnused` will be defined in a tag repo
        await tagRepo.whereIn('id', removeTagIds).deleteUnused();
      }
    },
  },
});

This method doesn't return a query object, so it cannot be chained. This is a limitation for the case when you want to await a query inside the method of repo.

deleteUnused is not complex and could be inlined, but it feels good to move the code to places where it feels like home. It is not a concern of the article to know what an unused tag is, it is a concern of a tag, so it belongs to the tag repo:

// src/app/tag/tag.repo.ts
import { createRepo } from 'orchid-orm';
import { db } from '../../db';

export const tagRepo = createRepo(db.tag, {
  queryMethods: {
    deleteUnused(q) {
      return q.whereNotExists('articleTags').delete();
    },
  },
});

Add a controller to the router:

// src/routes.ts
import * as article from './app/article/article.controller';

export const routes = async (app: FastifyInstance) => {
  // ...snip
  app.patch('/articles/:slug', article.updateArticleRoute);
};

mark/unmark the article as a favorite

Tests:

// src/app/article/article.controller.test.ts

describe('article controller', () => {
  // ...snip

  describe('POST /articles/:slug/favorite', () => {
    it('should mark article as favorited when passing true', async () => {
      const [currentUser, author] = await factory.user.createList(2);
      const article = await factory.article.create({
        userId: author.id,
      });

      await testRequest
        .as(currentUser)
        .post(`/articles/${article.slug}/favorite`, {
          favorite: true,
        });

      const { favorited } = await articleRepo
        .find(article.id)
        // .selectFavorited will be defined in articleRepo later
        .selectFavorited(currentUser.id);
      expect(favorited).toBe(true);
    });

    it('should not fail when passing true and article is already favorited', async () => {
      const [currentUser, author] = await factory.user.createList(2);
      const article = await factory.article.create({
        userId: author.id,
        favorites: {
          create: [
            {
              userId: currentUser.id,
            },
          ],
        },
      });

      const res = await testRequest
        .as(currentUser)
        .post(`/articles/${article.slug}/favorite`, {
          favorite: true,
        });

      expect(res.statusCode).toBe(200);
    });

    it('should unmark article as favorited when passing false', async () => {
      const [currentUser, author] = await factory.user.createList(2);
      const article = await factory.article.create({
        userId: author.id,
        favorites: {
          create: [
            {
              userId: currentUser.id,
            },
          ],
        },
      });

      await testRequest
        .as(currentUser)
        .post(`/articles/${article.slug}/favorite`, {
          favorite: false,
        });

      const { favorited } = await articleRepo
        .find(article.id)
        .selectFavorited(currentUser.id);
      expect(favorited).toBe(false);
    });

    it('should not fail when article is not favorited and passing false', async () => {
      const [currentUser, author] = await factory.user.createList(2);
      const article = await factory.article.create({
        userId: author.id,
      });

      const res = await testRequest
        .as(currentUser)
        .post(`/articles/${article.slug}/favorite`, {
          favorite: false,
        });

      expect(res.statusCode).toBe(200);
    });
  });
});

Define .selectFavorite to use in this test and the controller later:

It is not possible to use one method from another due to some TS limitations, so the way to do it is to define a standalone function.

// src/app/article/article.repo.ts

import { SelectQueryBuilder } from 'pqb';

// define selectFavorite as a standalone function to use in multiple methods:
const selectFavorited = (currentUserId: number | undefined) => {
  return (q: SelectQueryBuilder<typeof db.article>) =>
    currentUserId
      ? q.favorites.where({ userId: currentUserId }).exists()
      : q.sql((t) => t.boolean())`false`;
};

export const articleRepo = createRepo(db.article, {
  queryMethods: {
    selectDTO(q, currentUserId: number | undefined) {
      return q.select(
        'slug',
        'title',
        'body',
        'favoritesCount',
        'createdAt',
        'updatedAt',
        {
          tags: (q) => q.tags.order('name').pluck('name'),
          // use selectFavorited from above
          favorited: selectFavorited(currentUserId),
          author: (q) => userRepo(q.author).selectDTO(currentUserId),
        },
      );
    },
    selectFavorited(q, currentUserId: number | undefined) {
      return q.select({ favorited: selectFavorited(currentUserId) });
    },
    // ...snip
  },
  // ...snip
});

Controller code:

export const toggleArticleFavoriteRoute = routeHandler(
  {
    body: z.object({
      favorite: z.boolean(),
    }),
    params: articleSlugDTO,
  },
  async (req) => {
    const currentUserId = getCurrentUserId(req);
    const { slug } = req.params;
    const { favorite } = req.body;

    // assign favorites query to a variable to use it for different queries later:
    const favoritesQuery = db.article.findBy({ slug }).favorites;

    if (favorite) {
      try {
        await favoritesQuery.create({
          userId: currentUserId,
        });
      } catch (err) {
        // ignore case when an article is already favorited
        if (err instanceof db.articleFavorite.error && err.isUnique) {
          return;
        }
        throw err;
      }
    } else {
      await favoritesQuery
        .where({
          userId: currentUserId,
        })
        .delete();
    }
  },
);

Add a controller to the router:

// src/routes.ts
import * as article from './app/article/article.controller';

export const routes = async (app: FastifyInstance) => {
  // ...snip
  app.patch('/articles/:slug', article.updateArticleRoute);
};

delete an article

Tests for the future endpoint:

// src/app/article/article.controller.test.ts

describe('article controller', () => {
  // ...snip

  describe('DELETE /articles/:slug', () => {
    itShouldRequireAuth(() => testRequest.delete('/articles/article-slug'));

    it('should return unauthorized error when trying to delete article of other user', async () => {
      const [currentUser, author] = await factory.user.createList(2);
      const article = await factory.article.create({
        userId: author.id,
      });

      const res = await testRequest
        .as(currentUser)
        .delete(`/articles/${article.slug}`);

      expectUnauthorized(res);
    });

    it('should delete article', async () => {
      const currentUser = await factory.user.create();
      const article = await factory.article.create({
        userId: currentUser.id,
      });

      await testRequest.as(currentUser).delete(`/articles/${article.slug}`);

      const exists = await db.article.find(article.id).exists();
      expect(exists).toBe(false);
    });

    it('should delete unused tags, and leave used tags', async () => {
      const currentUser = await factory.user.create();
      const article = await factory.article.create({
        userId: currentUser.id,
        articleTags: {
          create: ['one', 'two'].map((name) => ({
            tag: {
              create: {
                name,
              },
            },
          })),
        },
      });

      await factory.article.create({
        userId: currentUser.id,
        articleTags: {
          create: ['two', 'three'].map((name) => ({
            tag: {
              connectOrCreate: {
                where: { name },
                create: { name },
              },
            },
          })),
        },
      });

      await testRequest.as(currentUser).delete(`/articles/${article.slug}`);

      const allTagNames = await db.tag.pluck('name');
      expect(allTagNames).toEqual(['two', 'three']);
    });
  });
});

Controller code:

// src/app/article/article.controller.ts

export const deleteArticleRoute = routeHandler(
  {
    params: articleSlugDTO,
  },
  async (req) => {
    const currentUserId = getCurrentUserId(req);
    const { slug } = req.params;

    // wrapping in the transaction to search for an article and delete it in a single transaction
    await db.$transaction(async () => {
      const article = await db.article
        .select('id', 'userId', {
          tagIds: (q) => q.tags.pluck('id'),
        })
        .findBy({ slug });

      if (article.userId !== currentUserId) {
        throw new UnauthorizedError();
      }

      // assign a query to a variable to reuse it
      const articleQuery = db.article.find(article.id);

      if (article.tagIds.length) {
        // before deleting a record need to delete all its related records
        // otherwise there would be an error complaining about a foreign key violation
        await articleQuery.articleTags.all().delete();
      }

      await articleQuery.delete();

      if (article.tagIds.length) {
        // tag repo with `deleteUnused` was defined before, at the step of updating the article
        await tagRepo.whereIn('id', article.tagIds).deleteUnused();
      }
    });
  },
);

Building a sample app #

API routes #

initialize the project #

setup Orchid ORM #

user endpoints #

add migration #

table class #

writing tests for registering user endpoint #

register user endpoint #

login endpoint #

follow and unfollow #

article related tables #

list articles endpoint #

refactoring code by using repo #

create an article #

update article endpoint #

mark/unmark the article as a favorite #

delete an article #

Building a sample app

API routes

initialize the project

setup Orchid ORM

user endpoints

add migration

table class

writing tests for registering user endpoint

register user endpoint

login endpoint

follow and unfollow

article related tables

list articles endpoint

refactoring code by using repo

create an article

update article endpoint

mark/unmark the article as a favorite

delete an article