Create the models and move data to the db

The first thing we’re going to do is to move the houses from the simple JSON file we now have in pages/houses.json to the new Postgres database we defined, by first creating the Sequelize models for the data, and then letting Sequelize create the tables for us.

The file pages/houses.json currently hosts this content, which forms all our application data so far:

pages/houses.json

[
  {
    "id": "2329",
    "hostName": "Anna",
    "picture": "/img/houses/1.jpg",
    "type": "Entire house",
    "town": "Ostuni",
    "title": "Beautiful flat in Ostuni!",
    "price": "50.00",
    "rating": 4.93,
    "reviewsCount": 198,
    "superhost": true,
    "description": "Spend a unforgettable holiday in the enchanting surroundings of the town of Cisternino (reachable from the near airports of Bari and Brindisi).<br>Trullo Edera offers a heaven of peace and tranquillity, set in an elevated position with a stunning view.<br> It's the perfect place if you like nature. You can stay under an olive tree reading a good book, you can have a walk in the small country streets or go to the nearest beaches.<br> You can even easily visit any of the sights in Apulia such as the caves of Castellana, the trulli of Alberobello, the baroque cities of Lecce and Martina Franca, the excavations of Egnazia, the zoosafari of Fasano, Castel del Monte with Frederick's castle, Grottaglie famous for its ceramics, Taranto, Brindisi and Lecce museums.<br>    Prices vary on period and are to be considered included: in-outcoming cleanings, towels, sheets, water, gas, electricity.",
    "guests": 4,
    "bedrooms": 1,
    "beds": 2,
    "baths": 1,
    "amenities": {
      "wifi": true,
      "kitchen": true,
      "heating": true,
      "freeParking": true
    },
    "entirePlace": true,
    "reviews": [
      {
        "user": "Radhika",
        "date": "August 2019",
        "avatar": "https://a0.muscache.com/im/users/34403074/profile_pic/1432859567/original.jpg?aki_policy=profile_x_medium",
        "comment": "We had an excellent stay at the trullo - everything was perfect, starting with Anna’s generosity to meet us in town so we wouldn’t lose our way, to the beautiful setting of the trullo, to the fresh eggs and tomatoes for our use, to Anna’s tips and suggestions for local"
      }
    ]
  },
  {
    "id": "1523",
    "picture": "/img/houses/2.jpg",
    "type": "Entire house",
    "town": "Isla Mujeres",
    "title": "The World Famous Seashell House ~ Casa Caracol",
    "price": "70.00",
    "rating": 4.77,
    "reviewsCount": 246,
    "superhost": false
  }
]

Let’s move this to our Sequelize models.

I’m first going to remove from src/model.js the Sequelize initialization, and I’m going to move it to the src/database.js file.

Basically instead of exporting the database data, we’ll export the Sequelize connection already initialized.

The reason I’m doing this is that I want to get rid of src/model.js and create a separate file for each model, as we’re soon going to have 3 models (User, House and Review) and I need a centralized place where to initialize Sequelize.

src/database.js now has this content:

src/database.js

const Database = {
  user: 'flaviocopes',
  password: '',
  host: 'localhost',
  database: 'nextbnb'
}
module.exports = Database

src/model.js now has this content:

const bcrypt = require('bcrypt')
const Sequelize = require('sequelize')

const Model = Sequelize.Model
const DataTypes = Sequelize.DataTypes

const Database = require('./database.js')
const { user, password, host, database } = Database

const sequelize = new Sequelize(database, user, password, {
  host,
  dialect: 'postgres',
  logging: false
})

class User extends Model {}

User.init(
  {
    email: {
      type: DataTypes.STRING,
      allowNull: false
    },
    password: {
      type: DataTypes.STRING,
      allowNull: false
    }
  },
  {
    sequelize,
    modelName: 'user',
    timestamps: false,
    hooks: {
      beforeCreate: async user => {
        const saltRounds = 10
        const salt = await bcrypt.genSalt(saltRounds)
        user.password = await bcrypt.hash(user.password, salt)
      }
    }
  }
)

User.prototype.isPasswordValid = async function(password) {
  return await bcrypt.compare(password, this.password)
}

exports.sequelize = sequelize
exports.User = User

After we move the Sequelize initialization, we end up with:

database.js

const user = 'flaviocopes'
const password = ''
const host = 'localhost'
const database = 'nextbnb'

const Sequelize = require('sequelize')

const sequelize = new Sequelize(database, user, password, {
  host,
  dialect: 'postgres',
  logging: false
})

module.exports = sequelize

And we need to update server.js because it referenced sequelize from model.js, from:

const sequelize = require('./model.js').sequelize

to

const sequelize = require('./database.js')

Note how I changed sequelize to be the default export, so we don’t need the reference to .sequelize anymore.

Next, create the models folder.

In there, create 3 files:

• user.js
• house.js
• review.js

Let’s move the User model from model.js into models/user.js:

models/user.js

const bcrypt = require('bcrypt')
const Sequelize = require('sequelize')

const sequelize = require('../database.js')

class User extends Sequelize.Model {}

User.init(
  {
    email: {
      type: Sequelize.DataTypes.STRING,
      allowNull: false
    },
    password: {
      type: Sequelize.DataTypes.STRING,
      allowNull: false
    }
  },
  {
    sequelize,
    modelName: 'user',
    timestamps: false,
    hooks: {
      beforeCreate: async user => {
        const saltRounds = 10
        const salt = await bcrypt.genSalt(saltRounds)
        user.password = await bcrypt.hash(user.password, salt)
      }
    }
  }
)

User.prototype.isPasswordValid = async function(password) {
  return await bcrypt.compare(password, this.password)
}

module.exports = User

See, here I changed User to be the default export.

We have 2 similar variables, one named Sequelize and the other sequelize. Sequelize is the reference to the Sequelize class, which we use to access Sequelize.DataTypes and Sequelize.Model. sequelize is our instance of the database connection.

Now, we were using this model into the server.js file. We therefore need to update the reference, in that file, from:

server.js

const User = require('./model.js').User

to

server.js

const User = require('./models/user.js')

Great! Things should be working like before now.

You can see that by looking at the terminal, since the application automatically restarts. If we don’t see any error, good! If lots of error lines appear.. bad!

Let’s now take inspiration from the User model and create the House model into models/house.js:

models/house.js

const Sequelize = require('sequelize')
const sequelize = require('../database.js')

class House extends Sequelize.Model {}

House.init(
  {
    id: {
      type: Sequelize.DataTypes.INTEGER,
      autoIncrement: true,
      primaryKey: true
    },
    host: { type: Sequelize.DataTypes.INTEGER, allowNull: false },
    picture: { type: Sequelize.DataTypes.STRING, allowNull: false },
    type: { type: Sequelize.DataTypes.STRING, allowNull: false },
    town: { type: Sequelize.DataTypes.STRING, allowNull: false },
    title: { type: Sequelize.DataTypes.STRING, allowNull: false },
    price: { type: Sequelize.DataTypes.INTEGER, allowNull: false },
    superhost: { type: Sequelize.DataTypes.BOOLEAN, allowNull: false },
    description: { type: Sequelize.DataTypes.TEXT },
    guests: { type: Sequelize.DataTypes.INTEGER, allowNull: false },
    bedrooms: { type: Sequelize.DataTypes.INTEGER, allowNull: false },
    beds: { type: Sequelize.DataTypes.INTEGER, allowNull: false },
    baths: { type: Sequelize.DataTypes.INTEGER, allowNull: false },
    wifi: { type: Sequelize.DataTypes.BOOLEAN, allowNull: false },
    kitchen: { type: Sequelize.DataTypes.BOOLEAN, allowNull: false },
    heating: { type: Sequelize.DataTypes.BOOLEAN, allowNull: false },
    freeParking: { type: Sequelize.DataTypes.BOOLEAN, allowNull: false },
    entirePlace: { type: Sequelize.DataTypes.BOOLEAN, allowNull: false }
  },
  {
    sequelize,
    modelName: 'house',
    timestamps: false
  }
)

module.exports = House

It’s all the same, except the fields are different. I used different data types for different kinds of data, most notably DataTypes.BOOLEAN and DataTypes.INTEGER, provided by the Sequelize object.

The only change I implemented, from the JSON file, is the amenities field. It was this object:

amenities: {
  wifi: true,
  kitchen: true,
  heating: true,
  freeParking: true
}

and I changed it to plain columns in the database, because I might use those parameters as filters for searching the house, one day, and having them stored in some other format (like stringified JSON) would prevent me that.

A simple change in the data structure we previously built without thinking too much about it.

I left out reviews and reviewsCount and rating, which we’ll count separately in the Review model.

hostName is now host, a reference to a user of the system.

This is because house owners are registered users of our app, just like people that want to book.

And the review date will be managed by Sequelize automatically using one particular feature it provides, setting timestamps: true, which will add the createdAt and updatedAt columns and manage them for us.

Here’s the Review model:

models/review.js

const Sequelize = require('sequelize')
const sequelize = require('../database.js')

class Review extends Sequelize.Model {}

Review.init(
  {
    id: {
      type: Sequelize.DataTypes.INTEGER,
      autoIncrement: true,
      primaryKey: true
    },
    houseId: { type: Sequelize.DataTypes.INTEGER, allowNull: false },
    userId: { type: Sequelize.DataTypes.INTEGER, allowNull: false },
    comment: { type: Sequelize.DataTypes.TEXT, allowNull: false }
  },
  {
    sequelize,
    modelName: 'review',
    timestamps: true
  }
)

module.exports = Review

Great!

Now go in the server.js file and import the new models:

server.js

import User from './models/user.js'
import House from './models/house.js'
import Review from './models/review.js'

and call the sync() method on House and Review to make Sequelize create the new tables automatically:

server.js

House.sync()
Review.sync()

Before this sync() call, the database is this:

and after the call, 2 new tables are added:

with all the fields we defined:

Once this is done the first time, you could comment out the sync() method calls, although you can leave them in if you plan to modify the models, because Sequelize is going to keep the tables in sync with the changes.

We’ll keep them, just in case, and I also added User.sync({ alter: true }) to the mix:

server.js

User.sync({ alter: true })
House.sync({ alter: true })
Review.sync({ alter: true })

The alter: true option makes sure tables are updated when we change the model, something very useful as we build the app.

The code for this lesson is available at https://github.com/flaviocopes/airbnb-clone-react-nextjs/commit/baaa50dceb79d46fd7c91c475f28d811a1a85e5a

---

Go to the next lesson