A CRUD API for posts

CRUD stands for the four basic data actions: Create, Read, Update, Delete. Here they all come together in one post routes file that works with the database from the previous lesson and checks data with schemas.

API routes

Method and address	Action	Success code
`GET /posts`	List of posts	200
`GET /posts/:id`	One post	200
`POST /posts`	Create a post	201
`PUT /posts/:id`	Update a post	200
`DELETE /posts/:id`	Delete a post	204

Schemas

The schemas sit at the top of src/routes/posts.js so the routes stay short:

// src/routes/posts.js
const params = {
  type: 'object',
  properties: { id: { type: 'integer' } },
};
 
const postInput = {
  type: 'object',
  required: ['title', 'body'],
  properties: {
    title: { type: 'string', minLength: 3, maxLength: 120 },
    body: { type: 'string', minLength: 1 },
  },
};

params converts id to a number, and postInput checks the body on create and update.

The routes

Further down in the same file:

// src/routes/posts.js
export default async function postRoutes(app) {
  const findPost = (id) => {
    const post = app.db.prepare('SELECT * FROM posts WHERE id = ?').get(id);
    if (!post) {
      throw app.httpErrors.notFound('Post not found');
    }
    return post;
  };
 
  app.get('/posts', async () => {
    return app.db.prepare('SELECT id, title, created_at FROM posts ORDER BY id DESC').all();
  });
 
  app.get('/posts/:id', { schema: { params } }, async (request) => {
    return findPost(request.params.id);
  });
 
  app.post('/posts', { schema: { body: postInput } }, async (request, reply) => {
    const { title, body } = request.body;
    const result = app.db
      .prepare('INSERT INTO posts (title, body) VALUES (?, ?)')
      .run(title, body);
    return reply.code(201).send(findPost(result.lastInsertRowid));
  });
 
  app.put('/posts/:id', { schema: { params, body: postInput } }, async (request) => {
    const { id } = request.params;
    findPost(id);
    const { title, body } = request.body;
    app.db.prepare('UPDATE posts SET title = ?, body = ? WHERE id = ?').run(title, body, id);
    return findPost(id);
  });
 
  app.delete('/posts/:id', { schema: { params } }, async (request, reply) => {
    const result = app.db.prepare('DELETE FROM posts WHERE id = ?').run(request.params.id);
    if (result.changes === 0) {
      throw app.httpErrors.notFound('Post not found');
    }
    return reply.code(204).send();
  });
}

The findPost function is used in four routes: it either returns the post or throws a 404 error.

Trying it

curl -X POST http://localhost:3000/posts \
  -H "Content-Type: application/json" \
  -d '{"title":"Hello, Fastify","body":"The first post in the database"}'
 
curl http://localhost:3000/posts
curl -X DELETE -i http://localhost:3000/posts/1

The first command returns the post with id and created_at, the second the list, the third a 204 No Content reply. After a server restart the posts are still there.

Comments

The comment routes in src/routes/comments.js are built the same way. For example, adding a comment to a post:

// src/routes/comments.js
app.post('/posts/:id/comments', async (request, reply) => {
  const { author, text } = request.body;
  const result = app.db
    .prepare('INSERT INTO comments (post_id, author, text) VALUES (?, ?, ?)')
    .run(request.params.id, author, text);
  return reply.code(201).send({ id: result.lastInsertRowid, author, text });
});

Add params and body schemas to it following the posts example.

Common mistake

A PUT route that does not check whether the post exists "successfully" updates zero rows and returns an empty reply with code 200. Look the record up first and throw 404 if it is missing — as findPost does.

Thanks for reading!