the-api-routings

Auto-generate REST CRUD endpoints over PostgreSQL tables with one line of code. Built on Hono + Knex for the the-api ecosystem.

router.crud({ table: 'posts' });
// GET    /posts
// POST   /posts
// GET    /posts/:id
// PATCH  /posts/:id
// DELETE /posts/:id

Install

npm i -S the-api-routings

Environment

_unlimited query parameter is disabled by default.
Set CAN_GET_UNLIMITED=true to allow ?_unlimited=true.

_limit behavior can also be controlled with:

• LIMIT_DEFAULT — default _limit value when request has no _limit
• LIMIT_MAX — upper bound for _limit (caps both request _limit and LIMIT_DEFAULT)

CAN_GET_UNLIMITED=true
LIMIT_DEFAULT=
LIMIT_MAX=

Quick Start

import { Routings } from 'the-api-routings';

const router = new Routings();

router.crud({ table: 'users' });
router.crud({ table: 'posts', prefix: 'api/posts' });
router
  .prefix('/ships')
  .get('/:id/similar', getSimilarShips)
  .get('/:id/requests', getRequests)
  .post('/import', importShip);

// pass router to the-api
const app = new TheAPI({ routings: [router] });
export default app.up();

curl http://localhost:7788/users?_limit=10&_sort=-timeCreated
curl http://localhost:7788/api/posts?_fields=id,title&_lang=de

Query Parameters

Every GET endpoint supports a rich set of filters out of the box.

Pagination

| Param | Example | Description | |---|---|---| | _limit | ?_limit=20 | Records per page | | _page | ?_page=3 | Page number (1-based) | | _skip | ?_skip=100 | Skip N records | | _unlimited | ?_unlimited=true | Return all records (requires CAN_GET_UNLIMITED) | | _after | ?_after=2024-01-15&_sort=-timeCreated&_limit=20 | Cursor pagination |

Sorting

| Param | Example | Description | |---|---|---| | _sort | ?_sort=name | Ascending | | | ?_sort=-timeCreated | Descending (prefix -) | | | ?_sort=-timeCreated,name | Multiple fields | | | ?_sort=random() | Random order |

All sorting applies NULLS LAST automatically.

Field Selection

| Param | Example | Description | |---|---|---| | _fields | ?_fields=id,name,email | Return only listed columns | | _join | ?_join=comments,tags | Include on-demand joins |

Filtering

GET /users?status=active                  — exact match
GET /users?status=active&status=verified  — IN (multiple values)
GET /users?name~=%john%                   — iLIKE (case-insensitive)
GET /users?status!=deleted                — NOT equal
GET /users?status!=deleted&status!=banned — NOT IN
GET /users?_null_deletedAt=1              — IS NULL
GET /users?_not_null_email=1              — IS NOT NULL
GET /users?_in_id=[1,2,3]                — IN (JSON array)
GET /users?_not_in_id=[4,5]              — NOT IN (JSON array)
GET /users?_from_age=18&_to_age=65       — range (>= and <=)

Search & Localization

| Param | Example | Description | |---|---|---| | _search | ?_search=john | Trigram search (requires pg_trgm) | | _lang | ?_lang=de | Translate fields via dict table |

Routings API

HTTP Methods

const router = new Routings();

router.get('/health', async (c) => { c.set('result', { ok: true }); });
router.post('/upload', authMiddleware, async (c) => { /* ... */ });
router.patch('/items/:id', async (c) => { /* ... */ });
router.delete('/items/:id', async (c) => { /* ... */ });

// Middleware for all routes
router.use('/api/*', corsMiddleware);
router.all(loggerMiddleware);

prefix(path)

Use prefix() to set a base path for the next route registrations and keep chaining:

router
  .prefix('/ships')
  .get('/:id/similar', getSimilarShips)
  .get('/:id/requests', getRequests)
  .post('/import', importShip)
  .post('/0', parseShip)
  .post('/0/countries', guessCountryByName);

It is equivalent to:

router.get('/ships/:id/similar', getSimilarShips);
router.get('/ships/:id/requests', getRequests);
router.post('/ships/import', importShip);
router.post('/ships/0', parseShip);
router.post('/ships/0/countries', guessCountryByName);

Calling prefix() again switches the current base path:

router
  .prefix('/v1')
  .get('/users', getUsersV1)
  .prefix('/v2')
  .get('/users', getUsersV2);

crud() also respects the current prefix:

router.prefix('/api/v1').crud({ table: 'posts' });
// GET    /api/v1/posts
// POST   /api/v1/posts
// GET    /api/v1/posts/:id
// PATCH  /api/v1/posts/:id
// DELETE /api/v1/posts/:id

crud(options)

Generates all 6 endpoints at once:

router.crud({
  table: 'posts',
  prefix: 'api/posts',           // URL prefix (default: table name)
  schema: 'public',              // DB schema (default: 'public')

  // fields
  fields: ['id', 'name', 'email'], // only these fields are returned by GET
  hiddenFields: ['password'],    // stripped from responses; takes precedence over fields
  readOnlyFields: ['id', 'timeCreated', 'timeUpdated', 'isDeleted'],
  requiredFields: { title: 'TITLE_REQUIRED' },
  aliases: { userName: 'author' },

  // filtering
  defaultWhere: { tenantId: '1' },
  defaultWhereRaw: '"publishedAt" IS NOT NULL OR "userId" = :currentUserId',
  defaultWhereRawBindings: { currentUserId: 'env.user.userId' },
  defaultSort: '-timeCreated',
  searchFields: ['title', 'body'],

  // joins
  join: [
    {
      table: 'categories',
      alias: 'category',
      field: `"categories"."name"`,
      where: `"categories"."id" = "posts"."categoryId"`,
    },
  ],
  joinOnDemand: [
    {
      table: 'comments',
      where: `"comments"."postId" = "posts"."id"`,
    },
  ],
  leftJoin: [['tags', 'tags.id', 'posts.tagId']],

  // soft delete
  deletedReplacements: { title: '[deleted]', body: '' },
  includeDeleted: false,

  // translations
  translate: ['title', 'description'],

  // access control
  tokenRequired: ['add', 'update', 'delete'],
  ownerRequired: ['update', 'delete'],

  // permissions
  permissions: {
    methods: ['POST', 'PATCH', 'DELETE'], // preferred
    // protectedMethods: ['POST', 'PATCH', 'DELETE'], // legacy alias
    owner: ['posts.view_private'],
    fields: {
      viewable: {
        'admin.view_emails': ['email', 'phone'],
      },
    },
  },

  // relations
  relations: {
    comments: { table: 'comments' },
  },

  // caching
  cache: { ttl: 60 },
});

errors()

router.errors({
  TITLE_REQUIRED: { code: 1001, status: 400, description: 'Post title is required' },
  POST_NOT_FOUND: { code: 1002, status: 404 },
});

// or array
router.errors([errors1, errors2]);

emailTemplates()

router.emailTemplates({
  welcome: {
    subject: 'Welcome, {{name}}!',
    html: '<h1>Hello {{name}}</h1>',
  },
});

Migrations

const router = new Routings({
  migrationDirs: [path.join(__dirname, 'migrations')],
});

Join Types

Static Join (always included)

join: [{
  table: 'users',
  alias: 'author',
  fields: ['id', 'name', 'avatar'],            // json_build_object
  where: `"users"."id" = "posts"."userId"`,
  orderBy: '"users"."name" ASC',
  limit: 1,
  byIndex: 0,                                   // extract first element
  defaultValue: null,
}]

Computed Field Join

join: [{
  table: 'likes',
  alias: 'likesCount',
  field: 'COUNT(*)::int',
  where: `"likes"."postId" = "posts"."id"`,
}]

Boolean Field Join

join: [{
  table: 'likes',
  alias: 'isLiked',
  field: `EXISTS(SELECT 1 FROM "likes" WHERE "likes"."postId" = "posts"."id" AND "likes"."userId" = :userId)::bool`,
  where: '1=1',
  whereBindings: { userId: 'env.user.userId' },
}]

On-Demand Join (via ?_join=comments)

joinOnDemand: [{
  table: 'comments',
  where: `"comments"."postId" = "posts"."id"`,
  orderBy: `"comments"."timeCreated" DESC`,
}]

Relations

relations are a normalized alternative to embedding a join in every row. The main result keeps foreign-key values, while the related records are deduplicated into top-level maps keyed by their identifier. A relation key is normally the foreign-key field from the main result.

router.crud({
  table: 'posts',
  relations: {
    categoryId: {
      table: 'categories',
      fields: ['id', 'name'],
    },
    userId: {
      table: 'users',
      fields: ['id', 'name'],
    },
  },
});

When this router is used by the-api, a list request such as GET /posts?_fields=id,title,categoryId,userId produces this shape:

{
  "result": [
    { "id": 1, "title": "Hello API", "categoryId": 1, "userId": 1 },
    { "id": 2, "title": "Routing Guide", "categoryId": 2, "userId": 1 }
  ],
  "relations": {
    "categoryId": {
      "1": { "id": 1, "name": "News" },
      "2": { "id": 2, "name": "Guides" }
    },
    "userId": {
      "1": { "id": 1, "name": "Alice" }
    }
  }
}

The client resolves a row without searching an array:

const { result: posts, relations } = await api.get('/posts?_fields=id,title,categoryId,userId');

for (const post of posts) {
  const category = relations.categoryId?.[post.categoryId];
  const author = relations.userId?.[post.userId];
  console.log(post.title, category?.name, author?.name);
}

Keep the foreign-key field in _fields; relations are loaded from ids found in result. A null, missing, or unmatched id does not create an entry in the relation map. Use fields and fieldRules in each relation definition to return only data that is safe for that relation's consumers.

The target key defaults to id. For a foreign key that stores another unique column, set relationIdName; the response map is then keyed by that column:

relations: {
  categorySlug: {
    table: 'categories',
    relationIdName: 'slug',
    fields: ['slug', 'name'],
  },
}

// relations.categorySlug['news'] => { slug: 'news', name: 'News' }

Use join when a response must contain nested data in every row or when the relationship is derived by SQL. Use relations for ordinary id references in lists: each related record is sent once, even when many rows point to it.

the-api-routings stores the declarations in c.var.relationsData. the-api provides the middleware that loads the records and serializes the top-level relations object.

Response Format

GET /posts?_limit=2&_page=1

{
  "result": [
    { "id": 1, "title": "First post" },
    { "id": 2, "title": "Second post" }
  ],
  "meta": {
    "total": 42,
    "limit": 2,
    "page": 1,
    "pages": 21,
    "skip": 0,
    "nextPage": 2,
    "isFirstPage": true,
    "isLastPage": false,
    "nextAfter": "2024-01-15T12%3A00%3A00.000999Z"
  }
}

Cursor Pagination

GET /posts?_sort=-timeCreated&_limit=20
→ meta.nextAfter = "2024-01-15T12%3A00%3A00.000000Z"

GET /posts?_sort=-timeCreated&_limit=20&_after=2024-01-15T12%3A00%3A00.000000Z
→ next page

GET /posts/1

{
  "result": { "id": 1, "title": "First post", "category": "tech" }
}

POST /posts

// request
{ "title": "New post", "body": "Content" }

// response
{ "result": { "id": 3, "title": "New post", "body": "Content", "timeCreated": "..." } }

DELETE /posts/1

{
  "result": { "ok": true },
  "meta": { "countDeleted": 1 }
}

Soft Delete

If the table has an isDeleted boolean column, DELETE sets it to true instead of removing the row. All GET queries automatically filter isDeleted = false.

Show deleted records with replaced values:

router.crud({
  table: 'posts',
  includeDeleted: true,
  deletedReplacements: {
    title: '[deleted]',
    body: '',
    author: null,
  },
});

Search

Requires PostgreSQL pg_trgm extension:

CREATE EXTENSION IF NOT EXISTS pg_trgm;

router.crud({
  table: 'posts',
  searchFields: ['title', 'body'],
});

GET /posts?_search=typescript

Results are sorted by similarity distance (closest first) unless _sort is specified.

Programmatic Usage

Use CrudBuilder directly for custom logic:

import { CrudBuilder } from 'the-api-routings';

router.get('/my-posts', async (c) => {
  const crud = new CrudBuilder({ table: 'posts' });
  const { result, meta } = await crud.getRequestResult(c, {
    _limit: ['5'],
    _sort: ['-timeCreated'],
    userId: [c.var.user.userId],
  });
  c.set('result', result);
  c.set('meta', meta);
});

Requirements

• PostgreSQL 12+ (for json_build_object, jsonb_agg)
• pg_trgm extension (for _search)
• Bun runtime (build & test)

License

MIT © Dimitry Ivanov