fastify-auto-sqlapi

Automatic CRUD API generation from PostgreSQL, MySQL, and MariaDB tables for Fastify. No ORM, no magic — just raw SQL, with TypeBox validation and Swagger docs out of the box.

Point it at your database, and get a full REST API with search, advanced conditions, pagination, joins, aggregations, bulk operations, multi-tenant isolation, validation, and hooks.

Features

• Zero boilerplate — define your tables, get 7 endpoints each
• No ORM — raw SQL via pg + parameterized queries
• TypeBox validation — request/response schemas auto-generated from your DB
• Virtual joins — fetch related records without complex SQL
• Bulk operations — batch insert/upsert/delete in single queries
• Multi-tenant — automatic row-level isolation, zero code in handlers
• Validation — structured field-level validation with cross-entity support
• Hooks — beforeInsert, beforeUpdate, afterInsert for custom logic
• Swagger UI — optional, auto-configured from your schemas
• Composable — register all routes or pick only what you need

Quick Start

1. Install

npm install fastify-auto-sqlapi fastify @fastify/postgres

2. Create the config file

Create sqlapi.config.ts in your project root. This is used only by the CLI generators, not at runtime.

export default {
  outputDir: './src/schemas',  // where to generate Schema files
  schema: 'public',            // PostgreSQL schema to introspect
};

The DB connection is read from environment variables:

# Either a full connection string:
DATABASE_URL=postgres://user:pass@localhost:5432/mydb

# Or individual vars:
POSTGRES_HOST=127.0.0.1
POSTGRES_PORT=5432
POSTGRES_USER=myuser
POSTGRES_PASSWORD=mypassword
POSTGRES_DB=mydb

3. Generate schemas from your database

npx sqlapi-generate-schema

This introspects your tables and generates one TypeBox schema file per table (e.g. SchemaCustomer.ts, SchemaOrder.ts). These files are auto-generated — don't edit them.

4. Generate the tables template

npx sqlapi-generate-tables

This creates a tables.ts file with a defineTable() call for each table. It auto-detects primary keys, foreign key relations, and lays out all available options as commented code. This file is yours to customize.

5. Register the plugin

import Fastify from 'fastify';
import fastifyPostgres from '@fastify/postgres';
import { fastifyAutoSqlApi } from 'fastify-auto-sqlapi';
import { dbTables } from './src/schemas/tables.js';

const app = Fastify();

await app.register(fastifyPostgres, {
  connectionString: 'postgres://user:pass@localhost:5432/mydb',
});

await app.register(fastifyAutoSqlApi, {
  DbTables: dbTables,
  swagger: true,
  prefix: '/api',
});

await app.listen({ port: 3000 });

That's it. For a table called customer, you now have:

| Method | URL | Description | |--------|-----|-------------| | POST | /api/search/customer | Search with filters, pagination, joins | | GET | /api/rest/customer/:id | Get a single record by primary key | | POST | /api/rest/customer | Insert a new record | | PUT | /api/rest/customer | Update a record | | DELETE | /api/rest/customer/:id | Delete a record | | PUT | /api/bulk/customer | Bulk upsert (array of records) | | POST | /api/bulk/customer/delete | Bulk delete (array of PKs) |

Note: Search uses POST because filters are passed as JSON in the request body.

Table Configuration

Tables are configured with defineTable(). The only required fields are primary and the output of exportTableInfo():

import { defineTable, exportTableInfo } from 'fastify-auto-sqlapi';
import { SchemaCustomer } from './SchemaCustomer.js';

const TableCustomer = defineTable({
  primary: 'id',
  ...exportTableInfo(SchemaCustomer),
});

exportTableInfo() provides the schema, a filter builder (auto-generates WHERE clauses from request fields), and extra filter definitions.

Export all your tables as a single record:

export const dbTables = {
  customer: TableCustomer,
  order: TableOrder,
};

The keys in this record (customer, order) become the table names in the URL paths.

All available options

const TableCustomer = defineTable({
  // Required
  primary: 'id',
  ...exportTableInfo(SchemaCustomer),

  // Ordering & filtering
  defaultOrder: 'name',                     // default ORDER BY
  excludeFromCreation: ['id'],              // omit from INSERT (e.g. auto-increment)
  distinctResults: true,                    // SELECT DISTINCT

  // Relations
  allowedReadJoins: [
    buildRelation(SchemaCustomer, 'id', SchemaOrder, 'customerId'),
  ],
  allowedWriteJoins: [
    buildRelation(SchemaCustomer, 'id', SchemaOrder, 'customerId'),
  ],

  // Upsert (ON CONFLICT)
  upsertMap: buildUpsertRules(
    buildUpsertRule(SchemaCustomer, ['id']),
  ),

  // Schema overrides (tighten generated schema without editing Schema files)
  schemaOverrides: {
    email: Type.String({ format: 'email' }),
  },

  // Multi-tenant isolation
  tenantScope: { column: 'organization_id' },

  // Validation (runs after schema validation, before hooks)
  validate: async (db, req, main, secondaries) => {
    // Return ValidationError[] — tuple: [field, code] or [field, code, message]
    // message defaults to code if omitted
    if (!main.name) return [['name', 'required']];
    return [];
  },
  validateBulk: async (db, req, items) => {
    // Bulk-upsert only. Called once with all items for cross-item validation.
    return [];
  },

  // Hooks (runs after validation) — all receive camelCase records (schema field names)
  beforeInsert: async (db, req, record) => { /* camelCase; mutations propagate to INSERT */ },
  beforeUpdate: async (db, req, fields) => { /* camelCase; PK included for reference, excluded from UPDATE SET */ },
  afterInsert: async (db, req, record, secondaryRecords) => { /* camelCase; input merged with generated PK */ },

  // Auth (per-table)
  onRequests: [
    async (request, reply) => {
      if (!request.user) return reply.status(401).send({ error: 'Unauthorized' });
    },
  ],
});

Custom filters (extraFilters)

For filters that don't map to real columns (e.g. a search q field):

import { Type, ConditionBuilder } from 'fastify-auto-sqlapi';

const TableCustomer = defineTable({
  primary: 'id',
  ...exportTableInfo(
    SchemaCustomer,
    { q: Type.Optional(Type.String()) },       // extra filter definition
    (condition, filters) => {                   // custom condition builder
      if (filters.q) {
        const or = new ConditionBuilder('OR');
        or.isILike('name', `%${filters.q}%`);
        or.isILike('email', `%${filters.q}%`);
        condition.append(or);
      }
    }
  ),
});

Plugin Options

await app.register(fastifyAutoSqlApi, {
  DbTables: dbTables,           // Required — your table definitions
  dialect: 'postgres',          // Optional — 'postgres' | 'mysql' | 'mariadb' (default: 'postgres')
  prefix: '/api',               // Optional — URL prefix for all routes
  swagger: true,                // Optional — enable Swagger UI (or pass SwaggerOptions)
  onRequests: [authMiddleware],  // Optional — global hooks applied to every route
  getTenantId: (req) => id,     // Optional — multi-tenant function
  debug: true,                  // Optional — log all SQL queries and params
});

| Option | Type | Required | Description | |--------|------|----------|-------------| | DbTables | Record<string, ITable> | Yes | Table configurations | | dialect | 'postgres' \| 'mysql' \| 'mariadb' | No | DB dialect (default: 'postgres') | | prefix | string | No | URL prefix (e.g. /api) | | swagger | boolean \| SwaggerOptions | No | Enable Swagger UI | | onRequests | Function[] | No | Global auth/middleware hooks | | getTenantId | (req) => id \| null | No | Tenant resolver for multi-tenant | | debug | boolean | No | Log all SQL queries to console |

For MySQL/MariaDB, register mysql2/promise pool instead of @fastify/postgres and pass dialect: 'mysql' or 'mariadb'.

API Reference

POST /search/{table}

Search with filters, advanced conditions, pagination, ordering, joins, and aggregations.

Request body (all fields optional):

{
  "filters": { "name": "Mario" },
  "conditions": [
    { "field": "total", "method": "isGreater", "params": [100] },
    { "field": "createdAt", "method": "isBetween", "params": ["2024-01-01", "2024-12-31"] }
  ],
  "joinFilters": {
    "order": {
      "filters": { "status": "completed" },
      "conditions": [{ "field": "total", "method": "isGreater", "params": [50] }]
    }
  },
  "joins": {
    "order": {
      "filters": { "status": "completed" },
      "conditions": [{ "field": "total", "method": "isGreater", "params": [50] }]
    }
  },
  "joinGroups": {
    "order": {
      "aggregations": {
        "by": "status",
        "sum": ["total"],
        "min": ["total"],
        "max": ["total"],
        "avg": ["total"],
        "count": ["id"],
        "distinctCount": ["status"]
      },
      "filters": { "status": "completed" },
      "conditions": [{ "field": "total", "method": "isGreater", "params": [0] }]
    }
  }
}

• filters — equality-based, flat key/value. Supports schema fields + extraFilters.
• conditions — array of { field, method, params }. Methods: isEqual, isNotEqual, isGreater, isGreaterOrEqual, isLess, isLessOrEqual, isLike, isILike, isIn, isNotIn, isBetween, isNotBetween, isNull, isNotNull.
• joinFilters — EXISTS-based filtering: "main rows where at least one related row matches". Accepts { filters, conditions } (both optional). Only tables in allowedReadJoins.
• joins — fetch related records (virtual join via separate query). Accepts { filters, conditions }.
• joinGroups — aggregations on related tables. Supports sum, min, max, avg, count, distinctCount, and optional by for GROUP BY. Accepts { filters, conditions } inside the subquery.

Dot-notation in conditions and orderBy — use <joinTable>.<fn>.<field> to filter/order the main rows by an aggregation value (HAVING-style). Example: orderBy=order.sum.total DESC or conditions: [{ field: 'order.count.id', method: 'isGreaterOrEqual', params: [4] }]. The joinGroup must be declared in the body.

Querystring (optional): orderBy, page, itemsPerPage, computeMin, computeMax, computeSum, computeAvg

Response:

{
  "table": "customer",
  "main": [{ "id": 1, "name": "Mario", "email": "[email protected]" }],
  "joins": { "order": [{ "id": 10, "customerId": 1, "total": 50 }] },
  "joinGroups": {
    "order": {
      "sum": { "total": 300 },
      "count": { "id": 2 },
      "rows": [{ "by": "completed", "sum_total": 300, "count_id": 2 }]
    }
  },
  "pagination": {
    "total": 25,
    "pages": 3,
    "computed": { "min": { "id": 1 }, "max": { "id": 100 } },
    "paginator": { "page": 1, "itemsPerPage": 20 }
  }
}

joins, joinGroups, and pagination only appear when requested. A simple {} body returns { table, main }. pagination.computed appears only if computeMin/computeMax/computeSum/computeAvg are used.

GET /rest/{table}/:id

Returns { main: { ... } } or 404.

POST /rest/{table}

Body:

{
  "main": { "name": "Mario", "email": "[email protected]" },
  "secondaries": {
    "order": [{ "total": 50, "status": "pending" }]
  }
}

secondaries is optional. FK fields are auto-filled from the inserted main record.

Response (201): { main: { ... }, secondaries: { ... } }

PUT /rest/{table}

Body:

{
  "main": { "id": 1, "name": "Updated Name" },
  "secondaries": { "order": [{ "total": 75 }] },
  "deletions": { "order": [{ "id": 10 }] }
}

main must include the primary key. secondaries and deletions are optional.

DELETE /rest/{table}/:id

Returns the deleted record or 404.

PUT /bulk/{table}

Body: Array of { main, secondaries?, deletions? }. All main records are inserted/upserted in a single SQL query.

POST /bulk/{table}/delete

Body: Array of objects with the PK field, e.g. [{ "id": 1 }, { "id": 2 }]. Executes as a single DELETE WHERE pk IN (...).

Validation errors (400)

Both schema-level (TypeBox/Ajv) and custom (validate / validateBulk) errors use the same response shape:

{
  "statusCode": 400,
  "error": "Bad Request",
  "message": "Validation failed",
  "fields": [
    { "path": "main.email", "code": "format", "message": "must match format \"email\"" },
    { "path": "name", "code": "required", "message": "is required" }
  ]
}

Custom validators return tuples [field, code] or [field, code, message] — message defaults to code if omitted. validateBulk replaces per-item validate in bulk-upsert requests.

Multi-Tenant

Automatic row-level isolation on all CRUD operations. Configure once, no code in handlers.

await app.register(fastifyAutoSqlApi, {
  DbTables: dbTables,
  getTenantId: (request) => request.user?.organizationId ?? null,
});

When getTenantId returns null, no filtering is applied (admin mode).

Direct tenant (column on the table)

defineTable({
  primary: 'id',
  ...exportTableInfo(SchemaCustomer),
  tenantScope: { column: 'organization_id' },
});

Indirect tenant (via parent table)

defineTable({
  primary: 'id',
  ...exportTableInfo(SchemaOrder),
  tenantScope: {
    column: 'organization_id',
    through: { schema: SchemaCustomer, localField: 'customer_id', foreignField: 'id' },
  },
});

Tables without tenantScope are unaffected.

Swagger

Enabled by passing swagger: true (or a config object) to the plugin options. Requires @fastify/swagger and @fastify/swagger-ui as peer dependencies.

npm install @fastify/swagger @fastify/swagger-ui

await app.register(fastifyAutoSqlApi, {
  DbTables: dbTables,
  swagger: {
    title: 'My API',
    description: 'Auto-generated CRUD API',
    version: '1.0.0',
    routePrefix: '/docs',
  },
});

If the swagger packages are not installed, the plugin logs a warning and continues without Swagger.

Granular Composition

Instead of registering the all-in-one plugin, you can register individual route plugins for more control:

import {
  searchRoutes, getRoutes, insertRoutes, updateRoutes,
  deleteRoutes, bulkUpsertRoutes, bulkDeleteRoutes, setupSwagger,
} from 'fastify-auto-sqlapi';

// Read-only API
await app.register(async (instance) => {
  await setupSwagger(instance, { swagger: true });
  const opts = { DbTables: dbTables };
  await instance.register(searchRoutes, opts);
  await instance.register(getRoutes, opts);
}, { prefix: '/public' });

// Full CRUD with auth
await app.register(async (instance) => {
  const opts = { DbTables: dbTables, onRequests: [authMiddleware] };
  await instance.register(searchRoutes, opts);
  await instance.register(getRoutes, opts);
  await instance.register(insertRoutes, opts);
  await instance.register(updateRoutes, opts);
  await instance.register(deleteRoutes, opts);
  await instance.register(bulkUpsertRoutes, opts);
  await instance.register(bulkDeleteRoutes, opts);
}, { prefix: '/admin' });

Conventions

• camelCase everywhere in the API — requests, responses, validate, and all hooks (beforeInsert, beforeUpdate, afterInsert) use schema field names
• Conversion to DB column format is automatic via colMap — supports both snake_case and camelCase DB columns (e.g. betterauth-style)
• Joins are virtual — separate SELECT ... WHERE fk IN (...) queries, not SQL JOINs
• All response fields are Optional — response schemas use Type.Partial since RETURNING * may return any subset

Re-exports

The package re-exports commonly needed utilities so you don't need to install them separately:

import {
  Type,                   // from @sinclair/typebox
  type Static,            // from @sinclair/typebox
  ConditionBuilder,       // from node-condition-builder
  Expression,             // from node-condition-builder

  // Table configuration
  defineTable,
  exportTableInfo,
  buildRelation,
  buildUpsertRule,
  buildUpsertRules,

  // DB layer
  QueryClient,            // raw SQL query helper
  createQueryClient,      // factory with dialect string
  pgQueryable,            // pg pool adapter
  mysqlQueryable,         // mysql2 pool adapter

  // Programmatic high-level API
  createSqlApi,           // standalone SqlApi (for scripts/tests)
  setupSwagger,           // manual Swagger registration
} from 'fastify-auto-sqlapi';

import type {
  ValidationError,        // [field, code] | [field, code, message]
  ValidatorFn,
  BulkValidatorFn,
  JoinRefFilter,
  JoinGroupRequest,
  SearchCondition,
  ConditionMethod,
} from 'fastify-auto-sqlapi';

After registering the plugin, app.sqlApi is decorated on the Fastify instance and exposes search, get, insert, update, delete, bulkUpsert, bulkDelete for custom routes — same code path as the auto-generated endpoints.

Requirements

• Node.js >= 18
• Fastify >= 4 (peer dependency)
• One of:
  • PostgreSQL + pg + @fastify/postgres
  • MySQL / MariaDB + mysql2
• Optional: @fastify/swagger + @fastify/swagger-ui for Swagger UI

License

MIT