Build a CRUD API with Fastify, Prisma, and SQLite

Fastify can validate an API request against a route's JSON Schema before the handler runs. In this tutorial, the handler uses Prisma to read or write a SQLite database, and a shared error handler distinguishes missing records from unexpected failures. The result is a small blog API with all five CRUD routes.

Create the project

This example uses Fastify 5 and Prisma 6 with CommonJS on Node.js 24. Pinning Prisma's major version matters: Prisma 7 changes client generation, configuration, and database adapter setup. Follow its upgrade guide when moving this example to that version.

mkdir blog-api
cd blog-api
npm init -y
npm install fastify@5 @prisma/client@6
npm install --save-dev prisma@6

Create a prisma directory and save this complete schema as prisma/schema.prisma:

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "sqlite"
  url      = "file:./dev.db"
}

model Post {
  id        Int      @id @default(autoincrement())
  title     String
  content   String
  published Boolean  @default(false)
  createdAt DateTime @default(now())
}

Create the local database and generate the client:

touch prisma/dev.db
npx prisma migrate dev --name init
npx prisma generate

migrate dev is a development workflow. Review and deploy migrations through a controlled release process for production; do not run development migration commands against a production database.

Define request and response schemas

Save the following blocks in order as app.cjs. This example explicitly rejects unknown request properties by disabling Ajv's removeAdditional behavior. With Fastify's default settings, additionalProperties: false typically causes those fields to be removed instead.

const Fastify = require('fastify');
const { PrismaClient, Prisma } = require('@prisma/client');

const fastify = Fastify({
  logger: true,
  ajv: { customOptions: { removeAdditional: false } },
});
const prisma = new PrismaClient();

const postBodySchema = {
  type: 'object',
  required: ['title', 'content'],
  properties: {
    title: { type: 'string', minLength: 1, maxLength: 200 },
    content: { type: 'string', minLength: 1 },
    published: { type: 'boolean', default: false },
  },
  additionalProperties: false,
};

const idSchema = {
  type: 'object',
  required: ['id'],
  properties: { id: { type: 'integer', minimum: 1, maximum: 2147483647 } },
  additionalProperties: false,
};

const postSchema = {
  type: 'object',
  required: ['id', 'title', 'content', 'published', 'createdAt'],
  properties: {
    id: { type: 'integer' },
    title: { type: 'string' },
    content: { type: 'string' },
    published: { type: 'boolean' },
    createdAt: { type: 'string', format: 'date-time' },
  },
  additionalProperties: false,
};

function postData(body) {
  return {
    title: body.title,
    content: body.content,
    published: body.published,
  };
}

Fastify's default validator coerces compatible values, including numeric path parameters, to match their schema. That is why a valid :id reaches these handlers as a number. Add stricter type/coercion rules if your contract requires them. The explicit postData() mapping also makes the fields written to the database easy to audit.

Response schemas are separate from input schemas. They allow Fastify to compile a serializer and constrain the response shape. A body schema alone does not activate schema-based response serialization. Neither schema replaces authorization.

Implement the routes

fastify.post('/posts', {
  schema: { body: postBodySchema, response: { 201: postSchema } },
}, async (request, reply) => {
  const post = await prisma.post.create({ data: postData(request.body) });
  return reply.code(201).send(post);
});

fastify.get('/posts', {
  schema: { response: { 200: { type: 'array', items: postSchema } } },
}, async () => {
  return prisma.post.findMany({
    orderBy: [{ createdAt: 'desc' }, { id: 'desc' }],
    take: 100,
  });
});

fastify.get('/posts/:id', {
  schema: { params: idSchema, response: { 200: postSchema } },
}, async (request, reply) => {
  const post = await prisma.post.findUnique({ where: { id: request.params.id } });
  if (!post) return reply.code(404).send({ message: 'Post not found' });
  return post;
});

fastify.put('/posts/:id', {
  schema: { params: idSchema, body: postBodySchema, response: { 200: postSchema } },
}, async (request) => {
  return prisma.post.update({
    where: { id: request.params.id },
    data: postData(request.body),
  });
});

fastify.delete('/posts/:id', {
  schema: { params: idSchema },
}, async (request, reply) => {
  await prisma.post.delete({ where: { id: request.params.id } });
  return reply.code(204).send();
});

Invalid input returns 400 before these handlers run. POST returns 201; DELETE returns 204 with no body. PUT requires a title and content and resets an omitted published field to its declared default, false. Use a separate PATCH schema if you want omitted fields to remain unchanged.

The list route caps results at 100 and uses the ID to break creation-time ties. Add cursor or offset pagination for larger datasets.

Handle errors and close the database

A failed database operation is not necessarily a missing record. Prisma's P2025 identifies the missing-record case used by these update and delete operations. A connection failure or unexpected query error must remain a server error.

fastify.setErrorHandler((error, request, reply) => {
  if (error.validation) {
    return reply.code(400).send({ message: 'Invalid request' });
  }
  if (error instanceof Prisma.PrismaClientKnownRequestError && error.code === 'P2025') {
    return reply.code(404).send({ message: 'Post not found' });
  }
  if (error.statusCode >= 400 && error.statusCode < 500) {
    return reply.code(error.statusCode).send({ message: 'Request rejected' });
  }
  request.log.error({ err: error }, 'Request failed');
  return reply.code(500).send({ message: 'Internal server error' });
});

fastify.addHook('onClose', async () => {
  await prisma.$disconnect();
});

if (require.main === module) {
  for (const signal of ['SIGINT', 'SIGTERM']) {
    process.once(signal, () => {
      fastify.close().catch((error) => {
        fastify.log.error({ err: error }, 'Shutdown failed');
        process.exitCode = 1;
      });
    });
  }
  fastify.listen({ port: 3000, host: '127.0.0.1' }).catch(async (error) => {
    fastify.log.error({ err: error }, 'Startup failed');
    await fastify.close();
    process.exitCode = 1;
  });
}

module.exports = fastify;

Start it with node app.cjs. Keeping the app export separate from the listening condition also lets a test use fastify.inject() without opening a network port. This is a local tutorial service: authentication, authorization, production shutdown deadlines, and deployment configuration still need to be designed.

What Fastify provides here

Route schemas supply validation through Fastify's integrated Ajv validator; you do not need separate Express-style validation middleware. logger: true enables Pino logs, with a request ID available in the request logger. Async handlers may return response data directly, and thrown errors reach the configured error handler.

JSON Schema can also feed generated OpenAPI documentation through @fastify/swagger, but declaring a schema does not install or configure that plugin. Likewise, evaluate @fastify/jwt for authentication and @fastify/rate-limit for request limits. Use plugin versions compatible with your Fastify major version and add tests for each integration.

Check latency and failures after deployment

Once the API passes functional tests, instrument a request that reaches the database. Guance's Node.js tracing guide explains setup requirements; check framework compatibility and initialize tracing before loading instrumented libraries. Verify that the inbound request and database spans connect, then configure log collection and log/trace correlation.

Group measurements by route templates or stable operation names. User IDs, concrete URLs, and full GraphQL queries are poor choices for metric dimensions because they can create high cardinality and expose sensitive data.

Common questions

Does additionalProperties: false reject unknown fields?

It depends on validator configuration. Fastify defaults to removing additional properties for such schemas. This tutorial sets removeAdditional: false, so an unknown property causes validation to fail. Always distinguish rejection from mutation, and map writable fields explicitly.

How do I share Prisma across route files?

Create the client in a plugin, expose it with fastify.decorate('prisma', prisma), and close it through an onClose hook. A fastify-plugin wrapper can make the decoration available beyond its encapsulation scope. Keep one client per intended application process rather than constructing one for each request; separate processes still have separate clients and pools.

What changes when moving from Express?

Handlers use request and reply; they can return data instead of calling res.json(). Plugins and lifecycle hooks organize most cross-cutting behavior. Test hook scope and order during migration rather than treating every Express middleware as a direct substitution.

References and related reading

• Fastify validation and serialization
• Prisma 6 client reference
• File uploads with Fastify (Chinese)
• WebSockets with Fastify (Chinese)

The local example was checked for CRUD behavior, validation, and error handling. No performance comparison, authentication integration, or Guance ingestion test was performed.