CRUD API with Fastify, Postgres, Docker
Tags: javascript, postgres, docker, programming
Published: 2022-01-10 18:25:41

Hi I am Francesco. You can find me on Twitter here https://twitter.com/FrancescoCiull4

Creating Content in Public
All this Content has been created during 2 livestreams from scratch

Here is the link if you wanna take a look on how I created this content (Even this article as it's part of the content itself!)

## Part 1

[![IMAGE ALT TEXT HERE](https://cdn.hashnode.com/res/hashnode/image/upload/v1619119216152/dQSU9c7Yv.png)](https://www.youtube.com/watch?v=RU9SkhLGDP0)

## Part 2

[![IMAGE ALT TEXT HERE](https://cdn.hashnode.com/res/hashnode/image/upload/v1619119500665/1DVo3aNBQ.png)](https://www.youtube.com/watch?v=8SGchw0OQE4)


In this article, we will set some CRUD API using:

- Node.js (JavaScript Runtime Engine)
- Fastify (Fast and low overhead web framework, for Node.js )
- Postgres (PostgreSQL) is a free open-source relational database, very popular and stable)
- Docker (Platform to deploy applications using containers)

GitHub Repository: https://github.com/FrancescoXX/study-with-me-fastify-docker

### NODE

![image.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1610518922566/jNlJ0QU14.png)

Node is a back-end JavaScript runtime environment, which means briefly that can execute JavaScript code on a computer, for example, yours or the one where Node is installed. The good thing is that, by having Docker, you DON't actually need to install it, because we will use the Node image, and so we can also avoid versioning between my version of Node installed on my machine and yours 

### FASTIFY


![image.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1619114165119/7mF7PgTq9.png)

Fastify is a web framework focused on performance. It is inspired by Hapi and Express and it's for sure one of the fastest web frameworks in town.

### POSTGRES

![image.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1610518964662/u3zeruAUg.png)



Postgres (PostgreSQL) is a free open-source relational database, very popular and stable


### DOCKER

![image.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1610521677853/SGKWuYsvU.png)

Docker is a platform to build run and share application using the idea of containers. If you want a brief introduction, here is a short video

[![IMAGE ALT TEXT HERE](https://cdn.hashnode.com/res/hashnode/image/upload/v1610522007247/lu2KkUl9j.png)](https://www.youtube.com/watch?v=eN_O4zd4D9o)


## Step by Step
0. Create a folder named fastify-postgres-docker and enter into it

```bash
mkdir fastify-postgres-docker && cd fastify-postgres-docker
```

1. Initialize node application using npm

```bash
npm init -y
```

2. Install the dependencies

```bash
npm install fastify fastify-postgres pg
```

3. Create the app folder and enter into it

```bash
mkdir app && cd app
```

From inside the src folder, create a server.js file and a route.js file

The folder structure should look like this

![image.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1619115034772/u4_ENUj0x.png)

Let's write the server.js file

```javascript
const fastify = require('fastify')({ logger: true });
fastify.register(require('fastify-postgres'), {
  connectionString: `postgres://${process.env.POSTGRES_USER}:${process.env.POSTGRES_PASSWORD}@${process.env.POSTGRES_SERVICE}:${process.env.POSTGRES_PORT}/${process.env.POSTGRES_DB}`,
});
fastify.register(require('./routes'));

// Run the server
const start = () => {
  fastify.listen(3000, '0.0.0.0', (err, address) => {
    if (err) {
      fastify.log.error(err);
      process.exit(1);
    }
  });
};
start();
```

Fastify uses the idea of plugins, you can check more about this here

https://www.fastify.io/docs/master/Plugins/

Let's write the first part of the routes.js file

```javascript
async function routes(fastify, options) {
  // Testing route
  fastify.get('/', async (request, reply) => {
    return { hello: 'world' };
  });
}

module.exports = routes;
```

___
## DOCKER

![image.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1610519742765/3YdQTSrfX.png)

Now the Docker Part!

In the main folder, create 3 files:

- Dockerfile
- docker-compose.yml
- .dockerignore (it starts with a dot)

the .dockerignore file:

```dockerignore
node_modules
.gitignore
.env
```

the Dockerfile:

```bash
FROM node:14

EXPOSE 3000

# Use latest version of npm
RUN npm install npm@latest -g

COPY package.json package-lock.json* ./

RUN npm install --no-optional && npm cache clean --force

# copy in our source code last, as it changes the most
WORKDIR /usr

COPY . .

CMD [ "node", "app/server.js"]
```

The docker-compose.yml file:

```yml
version: '3.8'
services:
  fastify_backend:
    container_name: fastify_backend
    image: francescoxx/fastify_backend:0.0.1
    build:
      context: .
    ports:
      - '3000:3000'
    env_file: .env
    depends_on: 
      - postgres

  postgres:
    container_name: postgres
    hostname: postgres
    image: 'postgres:13'
    ports:
      - '5432:5432'
    restart: always
    env_file: .env
    volumes:
      - fastify_volume:/var/lib/postgres/data

volumes:
  fastify_volume: {}
```

replace the image "francescoxx/fastify_backend:0.0.1" with an image name of your choice!

Before running our services, we need to create a .env file, to store our environment variables, and populate it with all the environment variables we need.

```
POSTGRES_USER=francesco
POSTGRES_PASSWORD=dbpassword
POSTGRES_DB=fastifydb
POSTGRES_SERVICE=postgres
POSTGRES_PORT=5432
```
The End file should look something like this:

![image.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1619116469996/3yE2BTOTF.png)

you can change them accordingly on your needings!

Let's start the postgres service:

```bash
docker-compose up -d postgres
```
we should have a Postgres DB up and running!


let's check what is inside the DB:
From another Terminal, type

```bash
docker exec -it postgres psql -U francesco fastifydb
```

and once we are inside the container 
(you can verify this by checking the postgres=# terminal)

connect to the fastifydb database

```bash
\c fastifydb
```

this means that a database named "fastifydb" has been created by postgres using the environment variable we have passed at the beginning

and then:

```bash
\dt
```

and you should get the message:

"Did not find any relations."


![image.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1619115911960/MyaJyDwIu.png)

This is because we have created the database, using the environment variable, but we haven't created any table or relationship yet

Type 'exit' to exit from this terminal

```bash
exit
```
And you are again at your terminal

Time to build our image!

from the folder where the docker-compose.yml file is located, run

```bash
docker-compose build
```

![image.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1619116090183/XNXOtpDhY.png)

Now it's time to run our node application

```bash
docker-compose up -d fastify_backend
```

WE can verify if both the containers are running, by using the 'docker ps -a' command


![image.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1619116178560/B151Ti1Gg.png)

Let's add an endpoint to init the DB. (This could be done in other better ways!)

In the route.js file, let's add a simple endpoint that will create the users table:

```javascript
// INIT TABLE. Launch just once to create the table
  fastify.get('/initDB', (req, reply) => {
    fastify.pg.connect(onConnect);
    function onConnect(err, client, release) {
      if (err) return reply.send(err);
      client.query(
        'CREATE TABLE IF NOT EXISTS "users" ("id" SERIAL PRIMARY KEY,"name" varchar(30),"description" varchar(30),"tweets" integer);',
        function onResult(err, result) {
          release();
          reply.send(err || result);
        }
      );
    }
  });
```

___
## ADDING API ENDPOINTS

Let's add other 5 endpoints:

## Endpoint to GET all the Users:

```javascript
  //GET AL USERS
  fastify.route({
    method: 'GET',
    url: '/users',
    handler: async function (request, reply) {
      fastify.pg.connect(onConnect);
      function onConnect(err, client, release) {
        if (err) return reply.send(err);
        client.query('SELECT * from users', function onResult(err, result) {
          release();
          reply.send(err || result.rows);
        });
      }
    },
  });

```

## Endpoint to get one User

```javascript
  //GET ONE USER if exists
  fastify.route({
    method: 'GET',
    url: '/users/:id',
    handler: async function (request, reply) {
      fastify.pg.connect(onConnect);
      function onConnect(err, client, release) {
        if (err) return reply.send(err);
        client.query(`SELECT * from users where id=${request.params.id}`, function onResult(err, result) {
          release();
          reply.send(err || result.rows[0]);
        });
      }
    },
  });
```

## Endpoint to create one user

```javascript
  //UPDATE ONE USER fields
  fastify.route({
    method: 'PUT',
    url: '/users/:id',
    handler: async function (request, reply) {
      fastify.pg.connect(onConnect);
      async function onConnect(err, client, release) {
        if (err) return reply.send(err);
        const oldUserReq = await client.query(`SELECT * from users where id=${request.params.id}`);
        const oldUser = oldUserReq.rows[0];
        client.query(
          `UPDATE users SET(name,description,tweets) = ('${request.body.name}', '${request.body.description || oldUser.description}', ${
            request.body.tweets || oldUser.tweets
          })
      WHERE id=${request.params.id}`,
          function onResult(err, result) {
            release();
            reply.send(err || `Updated: ${request.params.id}`);
          }
        );
      }
    },
  });
```

## Endpoint to Delete one user:

```javascript
  //DELETE ONE USER if exists
  fastify.route({
    method: 'DELETE',
    url: '/users/:id',
    handler: async function (request, reply) {
      fastify.pg.connect(onConnect);
      function onConnect(err, client, release) {
        if (err) return reply.send(err);
        client.query(`DELETE FROM users WHERE id=${request.params.id}`, function onResult(err, result) {
          release();
          reply.send(err || `Deleted: ${request.params.id}`);
        });
      }
    },
  });
```

The final routes.js file should look like this:

```javascript
async function routes(fastify, options) {
  // Testing route
  fastify.get('/', async (request, reply) => {
    return { hello: 'world' };
  });

  // INIT TABLE. Launch just once to create the table
  fastify.get('/initDB', (req, reply) => {
    fastify.pg.connect(onConnect);
    function onConnect(err, client, release) {
      if (err) return reply.send(err);
      client.query(
        'CREATE TABLE IF NOT EXISTS "users" ("id" SERIAL PRIMARY KEY,"name" varchar(30),"description" varchar(30),"tweets" integer);',
        function onResult(err, result) {
          release();
          reply.send(err || result);
        }
      );
    }
  });

  //GET AL USERS
  fastify.route({
    method: 'GET',
    url: '/users',
    handler: async function (request, reply) {
      fastify.pg.connect(onConnect);
      function onConnect(err, client, release) {
        if (err) return reply.send(err);
        client.query('SELECT * from users', function onResult(err, result) {
          release();
          reply.send(err || result.rows);
        });
      }
    },
  });

  //GET ONE USER if exists
  fastify.route({
    method: 'GET',
    url: '/users/:id',
    handler: async function (request, reply) {
      fastify.pg.connect(onConnect);
      function onConnect(err, client, release) {
        if (err) return reply.send(err);
        client.query(`SELECT * from users where id=${request.params.id}`, function onResult(err, result) {
          release();
          reply.send(err || result.rows[0]);
        });
      }
    },
  });

  //Create users
  fastify.route({
    method: 'POST',
    url: '/users',
    handler: function (request, reply) {
      fastify.pg.connect(onConnect);
      function onConnect(err, client, release) {
        if (err) return reply.send(err);
        const newUser = request.body;
        client.query(
          `INSERT into users (name,description,tweets) VALUES('${newUser.name}','${newUser.description}',${newUser.tweets})`,
          function onResult(err, result) {
            release();
            reply.send(err || result);
          }
        );
      }
    },
  });

  //UPDATE ONE USER fields
  fastify.route({
    method: 'PUT',
    url: '/users/:id',
    handler: async function (request, reply) {
      fastify.pg.connect(onConnect);
      async function onConnect(err, client, release) {
        if (err) return reply.send(err);
        const oldUserReq = await client.query(`SELECT * from users where id=${request.params.id}`);
        const oldUser = oldUserReq.rows[0];
        client.query(
          `UPDATE users SET(name,description,tweets) = ('${request.body.name}', '${request.body.description || oldUser.description}', ${
            request.body.tweets || oldUser.tweets
          })
      WHERE id=${request.params.id}`,
          function onResult(err, result) {
            release();
            reply.send(err || `Updated: ${request.params.id}`);
          }
        );
      }
    },
  });

  //DELETE ONE USER if exists
  fastify.route({
    method: 'DELETE',
    url: '/users/:id',
    handler: async function (request, reply) {
      fastify.pg.connect(onConnect);
      function onConnect(err, client, release) {
        if (err) return reply.send(err);
        client.query(`DELETE FROM users WHERE id=${request.params.id}`, function onResult(err, result) {
          release();
          reply.send(err || `Deleted: ${request.params.id}`);
        });
      }
    },
  });
}

module.exports = routes;
```

Now let's test these APIs!

___
## POSTMAN

Important! you need to specify localhost and not 127.0.0.1 in the first part of the url, otherwise it doesn't work!

![image.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1610520716792/GJbRXRnQT.png)

We will use Postman, but you can use a whenever tool you want

First of all, we need to create the user table. We will trigger it by hitting with a GET this url:

```url
GET http://localhost:3000/initDB
```

![image.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1619117564220/11SC_c4RR.png)

If we get this answer, it means that our 'users' table has been created!

Now let's check all the users with another GET :

```
GET http://localhost:3000/users
```


![image.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1619117697966/bCWIo0Nyb.png)


if we get the empty array answer, [], it means that we actually have the users table, in our DB, but the are no users. This is perfectly fine!

Let's create some users. We will do this by making a POST request at the same endpoint, adding the values in a json 

Example:
```json
{
    "name":"Adrian",
    "description":"Kangaroo Fighter",
    "tweets":12000
}
```

![image.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1619117854527/cxQKPVumP.png)

Please notice that we don't need to add an 'id' , as it is automatically incremented at each new user

LEt's add another one


![image.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1619117958298/YEA1tf6Lm.png)

and another one 


![image.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1619118004049/-10dEx4rH.png)

Now let's check again all the users:


![image.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1619118048586/PrCJTG2mU.png)

And we see that this time we have 3 users!

We can get one single users by adding the id of the user at the end of the previous url path. For example

```
GET http://localhost:3000/users/2
```

To get the user with the id = 2

![image.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1619118145497/3C4P8QpQ6.png)

To delete an user, you can make a DELETE request at the same endpoint you use to get one user:

```
DELETE http://localhost:3000/users/2
```

![image.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1619118240845/-X63IzZqb.png)

Finally, to Update the user, you make  a PUT request, passing the new values inside a json, like this

```json
{
    "name":"Adrian2",
    "description":"SuperKANGAROO"
}
```

and you also need to pass the id of the user you wanna update in the url request, like this

```
PUT http://localhost:3000/users/3
```


![image.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1619118521762/29t2ZIE89.png)

To check if the user has been really updated, you can make another GET Request:


![image.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1619118558192/kTvzlVLJ-.png)

As you can see, the name and the description of the user has changed, but not the tweets.


___
## Conclusion

If you have tried to follow this article, I would like to know if you have encountered any problem. Thanks!

GitHub Repository:
https://github.com/FrancescoXX/study-with-me-fastify-docker