---
tags: sequelize,practical
---

# Sequelize Introduction Practical: Have I Fed the Cat?

For this practical session, you will be building a simple web app to track when the cat was last fed.

## User Stories

As a cat owner
So I can organise my cat's feeding
I want to be able to create a cat with description in the database

As a cat owner
So I can keep track of feedings
I want to be able to record the last time I fed the cat 

As a cat onwer
So I can check when the cat was last fed
I want to be able to read the last time I fed the cat

## Requirements

From the user stories, we can infer some requirements on what sort of information our app should store. The main thing we want to keep track of is when a cat was last fed, but to prevent confusion, we should also have a few ways to differentiate between cats. This is one way our `Cat` table could look:

{
    name: STRING,
    breed: STRING,
    markings: STRING,
    lastFed: DATE
}

We can also see from the user stories that we are going to want to be able to `CREATE`, `READ` and `UPDATE` cats. `DELETE` might also be useful, but isn't nessesary for this practical.

From this, we can expect to make the following `endpoints` in our web app:

### POST /cats

This endpoint creates a new cat record in the database

Request Body:

{
    "name": STRING,
    "breed": STRING,
    "markings": STRING
}


Response Status: 201
Response Body:

{
    "id": INTEGER
    "name": STRING,
    "breed": STRING,
    "markings": STRING
    "lastFed": NULL
}

Note that we don't need to supply the `lastFed` property when creating the cat record, this is because can be handled automatically, and at the point of creating the record should be `null` (never fed).

### GET /cats

This endpoint will return a list of all cats in the database.
Example response:

Response Status: 200
Response Body:

[
    {
        "id": INTEGER
        "name": STRING,
        "breed": STRING,
        "markings": STRING
        "lastFed": DATE
    },  
    {
        "id": INTEGER
        "name": STRING,
        "breed": STRING,
        "markings": STRING
        "lastFed": DATE
    },
]

### GET /cats/:catId

This endpoint returns a single cat record, where `:catId` is the `id` of the cat in the database

Response Status: 200

Response Body:

{
    "id": INTEGER
    "name": STRING,
    "breed": STRING,
    "markings": STRING
    "lastFed": DATE
}

### PATCH /cats/:catId

Using this endpoint, users can update a single cat record in the database, where `:catId` is the `id` of the cat in the database.

Request Body:
{
    lastFed: DATE
}

Response Status: 200

### Optional: PATCH /feed/cat/:catId

We could make our interface simpler for requests that we expect to be perfoming regularly. In this case we could have our client send a simple `PATCH` request without a body, and have the app automatically update the time the cat was fed.

Response Status: 200

## Getting Set Up
In order to start using Sequelize, we will need to set up a simple express app, and a Postgres database (If you already have a docker container running for another project then you can just use that).

1. Set up a new git repository:

    mkdir have-i-fed-the-cat-app
    cd have-i-fed-the-cat-app
    git init
    npm init -y

1. Use PGAdmin to create a database for the app to store data in:

    CREATE DATABASE have_i_fed_the_cat_app;

1. Install `express`, `sequelize` and `pg` as dependancies:

    npm i -S express sequelize pg

1. Install `nodemon` as a dev-dependancy:

    npm i -D nodemon

1. Create a `.gitignore` and add your `node_modules` to it:

    echo /node_modules >> .gitignore

Or use the `gitignore` node module to autogenerate one for you:

    npx gitignore node

1. In your `package.json`, add a `start` script with the following command:

    ...
    "scripts": {
        "start": "nodemon index.js"
    },
    ...

We won't worry about `dotenv` for the scope of this exercise.
1. Create an `index.js` and a `src` directory containing an `app.js`. Your project structure should look like this (excluding `node_modules`):

    have-i-fed-the-cat-app
    ├── index.js
    ├── package-lock.json
    ├── package.json
    └── src
        └── app.js

    1 directory, 4 files

1. Set up a simple express app in your `src/app.js`:

    // In src/app.js add this code:
    const express = require('express');

    const app = express();

    // we expect to have to parse json from request bodies, 
    // so we need the JSON middleware
    app.use(express.json());

    // we will put our routes and controller functions here

    module.exports = app;

1. Require your app in your `index.js` and call `app.listen()`:

    // In index.js add this code:
    const app = require('./src/app');

    const APP_PORT = 3000;
    
    app.listen(APP_PORT, () => console.log(`Cats app is listening on localhost:${APP_PORT}`))

At this point, you should be able to run `npm start` in your terminal, and see that your app is listening to port 3000. Commit your work to git and change driver/navigator.

## Adding Sequelize

For this next section, we will start adding routes and controller functions to our express app. First we want to focus on being able to send a `POST` request to `localhost:3000/cats` to create a new cat in our database.

To get started, declare a `POST` route in `app.js` for `/cats`. Just have the controller return a `201` status and the request body for now. Hit the endpoint with a request from Postman to confirm that it is working.

Now, lets bring `sequelize` into our app, so we can start saving cats in our database.

1. Create a new `models` directory inside your `src` directory.
2. Inside your `models` directory, create two new files, `cats.js` and `index.js`. Your project should now look like this:

    have-i-fed-the-cat-app
    ├── index.js
    ├── package-lock.json
    ├── package.json
    └── src
        ├── app.js
        └── models
            ├── cats.js
            └── index.js

    2 directories, 6 files

3. Inside `models/index.js`, require `Sequelize` at the top of the file:

    // In models/index.js add this code:
    const Sequelize = require('sequelize');

4. Declare a function in the same file called `setUpDatabase`. Keep the function to empty for now. Invoke the function and export it's return value at the bottom of the file:

    // models/index.js
    
    // ...
    
    const setUpDatabase = () => {}
    
    module.exports = setUpDatabase();

5. Inside your `setUpDatabase` function, declare a `const` called `connection` and assign it as a `new Sequelize()`:

    // models/index.js
    ...
    const setUpDatabase = () => {
        const connection = new Sequelize("have_i_fed_the_cat_app", "postgres", "password", {
        host: "localhost",
        port: 5433,
        dialect: "postgres"
        })
    }
    ...

1. Next, still inside your `setUpDatabase` function, call `connection.sync({alter: true})`. This will allow changes to be saved into the DB. Finally, return an empty object from your function. Your complete `models.index.js` should look something like this:

    // models/index.js
    const Sequelize = require('sequelize');

    const setUpDatabase = () => {
      const connection = new Sequelize("have_i_fed_the_cat_app", "postgres", "password", {
        host: "localhost",
        port: 5433,
        dialect: "postgres"
      })

      connection.sync({alter: true});

      return {};
    };

    module.exports = setUpDatabase();

    

This code will connect to our database and return `Models` that we can use to interact with our tables. Before we can do this though, we need to write a model for our `Cat` table.

1. In `models/cats.js` we want to export through `module.exports` an arrow function like so:

    // In models/cats.js start by adding:
    module.exports = (sequelize, DataTypes) => {}

2. Now we need to define a schema inside our arrow function:

    // In models/cats.js continue defining the schema:
    module.exports = (sequelize, DataTypes) => {
        const schema = {
            name: DataTypes.STRING
            ...
        }
    }

Check the requirements for the other fields. Most of them will be `STRING` types, but one will need to be a `DATE`.

3. Have your function `return` `sequelize.define('Cat', schema)`. Your full file should look something like this:

    // Your models/cats.js should look like this at the end:
    const { Sequelize } = require("sequelize");

    module.exports = (sequelize, DataTypes) => {
      const schema = {
        name: DataTypes.STRING,
        breed: DataTypes.STRING,
        markings: DataTypes.STRING,
        lastFed: DataTypes.DATE
      }

      return sequelize.define('Cat', schema)
    }

Now that we have a model for our `Cat` table, we need to use it. Let's require it in `models/index.js`.

1. Inside `models/index.js`, require your `catModel` at the top of your file:

    // models/index.js

    ...

    const CatModel = require('./cats');

    ...

1. Finally, inside your `setUpDatabase` function, after you have declared you `connection` but **before your have called `connection.sync()`**, declare `const Cat = CatModel(connection, Sequelize)`. Add your `Cat` to the object in your `return` statment:

    // models/index.js

    ...

    const Cat = CatModel(connection, Sequelize);

    ...
    
    connection.sync({ alter: true });
    return { Cat }
    
    ...
    

Now we are ready to wire up `Sequelize` to the rest of our app. You should use this opportunity to commit your work and swap driver/navigator.

## Saving a Cat in the database

Now we are going to alter the controller function we have written in `app.js` so that it actually saves our cat data in our database.

1. In your `app.js`, requre `{ Cat }` from your models directory:

    // In app.js require Cat model:

    const express = require('express');
    const { Cat } = require('../models');
    
    // ...

1. Inside your `POST /cats` controller, call `Cat.create(req.body).then(cat => res.status(201).json(cat))`. Test your code with Postman by trying to make a cat.

1. Now create a `GET /cats` endpoint which return all the cats in your database. This will be similar to the code above, but this time you will be calling `Cat.findAll` rather than `Cat.create`. Use Postman to confim that your controller function works.

1. Now we want to create an endpoint to get information about a specific cat. This endpoint will be `GET /cats/:catId`. It will work almost exactly the same way as your `GET /cats` endpoint, but you will need to call `Cat.findByPk()` and pass it `req.params.catId`.

This covers our `CREATE` and `READ` requirements for our app.

### Optional: Retrieve cats using query
A nice feature you may wish to add is the ability to return all cats which match a `query`.
To do this, simply pass into your `Cat.findAll({ where: req.query })`. This will let you add a `query string` to your `GET` request, and only return cats which match it. for example `GET http://localhost3000/cats?markings=tuxedo` will only return cats with tuxedo markings.

## Feeding the Cat

In order to record the last time the cat was fed, we will need to implement a `PATCH /cats/:catId` endpoint. This again will be very similar to your other endpoints, but you will need to call `Cat.update(req.body, { where: { id: req.params.catId } })`.

Test this new endpoint by trying to update the `lastFed` property for your cat. Note that `Postgres` `DATETIME` is formatted as `yyyy-mm-dd hh-mm-ss`

### Optional: Deleting a Cat

With our app requiremnts we don't *need* to delete cats from the database, but it is one of the CRUD operations. Can you implement a `DELETE /cats/:catId` endpoint? You will need to use `Cat.destroy({ where: { id: ID_TO_DELETE } })`

### Optional: Easy Feeding

The `PATCH` endpoint we just implemented can be used to update any part of our `Cat` record, including when they were last fed. This does however require the user to send a valid date string. To make things easier for the user, we could implement `PATCH /feed/cat/:catId` endpoint, where we specify that we are only updating the `lastFed` property and setting the timestamp ourselves. This will be very similar to our previous `PATCH` endpoint, but this time we want to pass it `{ lastFed: new Date() }` rather than `req.body`.

### Optional: Error handling

All of the endpoints we have written assume that the data the user is supplying is always perfect. This is probably not going to be the case. Can you add `.catch()` blocks after your `.then()` to catch any errors and return a `400` status code to the user?