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
  hiddenFields: ['password'],    // stripped from responses
  readOnlyFields: ['id', 'timeCreated', 'timeUpdated', 'isDeleted'],
  requiredFields: { title: 'TITLE_REQUIRED' },
  aliases: { userName: 'author' },

  // filtering
  defaultWhere: { tenantId: '1' },
  defaultWhereRaw: '"publishedAt" IS NOT NULL',
  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`,
}]

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