Previously, we set up our Graphql server. Today, we will work on getting our database started.

I suggest watching the installation video in my previous article to see how to start the Postgres server. Then I'll be creating the database through a GUI app called Postico instead of through the command line. However, that's just a preference of mine. You can continue watching the video to learn the command-line way.

Start Server & Create a Database

FYI be sure to have the Postgres server running while interacting with the database. By default, these are the databases they give you.

Interacting with the database using Postico
When you first get started, it'll ask you to connect to the database/server you're trying to work with.

Just follow those instructions and connect to the same server you have running on Postgres. Once that's finished, create a database named Chat. I clicked the "+ database" button below to create a new one.

when it says connected at the top, that's the database your connected to.

Configure our database in the project.
Now that we have set our database, now it is time to config all this within the project. In the terminal of your root project folder, run the command

npx sequelize init

this command will create a config folder with a config.json file that has some default database connection info, which you will need to change so it matches your database created earlier. it should look similar to this

/** config/config.js */
{
  "development": {
    "username": "ajeas",
    "password": "",
    "database": "chat",
    "host": "127.0.0.1",
    "dialect": "postgres"
  },
  "test": {
    "username": "root",
    "password": null,
    "database": "database_test",
    "host": "127.0.0.1",
    "dialect": "postgres"
  },
  "production": {
    "username": "root",
    "password": null,
    "database": "database_production",
    "host": "127.0.0.1",
    "dialect": "postgres"
  }
}

the "username" will be your name, by default password is an empty string, the "database" will be the chat, and be sure to make the "dialect" Postgres (tells us which database to use, you could put a different one if you wanted, like MySQL. But, were using Postgres) ignore test and production for now.

Create models (Tables) using Sequelize
After configuring the database info, its time to create our first model through the command line. Run this code to generate a User model (which represents a registered user).

npx sequelize model:generate --name User --attributes username:string,email:string,password:string

This will create our User model (table) with the username, email, and password fields (columns) we need to specify the data types, that's why we added :string at the end. Your model should look like this

const { Model } = require("sequelize");
module.exports = (sequelize, DataTypes) => {
  class User extends Model {
    /**
     * Helper method for defining associations.
     * This method is not a part of Sequelize lifecycle.
     * The `models/index` file will call this method automatically.
     */
    static associate() {
      // define association here

    }
  }
  User.init(
    {
      username: DataTypes.STRING,
      email: DataTypes.STRING,
      password: DataTypes.STRING
    },
    {
      sequelize,
      modelName: "User"
    }
  );
  return User;
};

It will also create a migrations folder and an xxxxxxxx-create-user.js file which represents our table structure in the database. Each time you create a model it will have a migration file attached, located in the migrations folder. (also creates a seeder folder that we won't use throughout this series, so you can ignore for now.)

Before we get into the others, let's connect to the database when we start the server. Add this is the server.js file

/** add this */
const { sequelize } = require("./models");

server.listen(4000, () => {
  console.log(`🚀 Server ready at port 4000`);
  // Connecting to Database

/** add this */
  sequelize
    .authenticate()
    .then(() => {
      console.log("database connected");
    })
    .catch(err => console.log(err));
});

It uses sequelize and our config.js file to connect to our database. Restart the server(npm run dev), now you should see this in terminal

Now, let's create the rest of the models we'll need. Running the same command as before, create a Team, Channel, Message models

FYI: these commands will create the files for you in the model folder

npx sequelize model:generate --name Team --attributes name:string
npx sequelize model:generate --name Channel --attributes name:string,public:boolean
npx sequelize model:generate --name Message --attributes text:string

All your models should look similar to the User model image. Before migrating your models to the database. We need to create relationships among them.

Model relationships (associations)
We'll be using the belongsToMany and belongsTo relations and we have to do this manually in each model file.

"A user can belong to many teams in slack", this will make them linked together by a different table called member (we'll create later on) which adds a foreign key to the member table. (FYI, I'm still new to relational databases and relationships. So I'm explaining it in the best way I can understand :) ).

/** User model */
static associate({ Team }) {
  // define association here
  this.belongsToMany(Team, {
     through: "member",
     foreignKey: "userId"
  });
}

"A team can belong to many users and it belongs to a specific user", linked to the same member table with a different foreign key. belongsTo doesn't create another table, it just adds a foreign key to the team. The foreign key owner will be the "ids" of the user (model) that owns a team.

/** Team model */
static associate({ User }) {
   // define association here
   this.belongsToMany(User, { through: "member", foreignKey: "teamId"});
   this.belongsTo(User, { foreignKey: "owner" });
}

"A message belongs to a channel and it belongs to a user", adds two foreign keys to the message. The foreign key channelId will be the "ids" of the channels that message belongs to, and the userId foreign key will be "ids" of the user (model) that created that message.

/** Message model */
static associate({ Channel, User }) {
   // define association here
   this.belongsTo(Channel, { foreignKey: "channelId" });
   this.belongsTo(User, { foreignKey: "userId" });
}

Lastly, our channel model. "A channel belongs to a team", since we cant create channels without a team the teamId foreign key is the "ids" of the team (model) that this channel belongs to.

/** Channel model */
static associate({ Team }) {
   // define association here
   this.belongsTo(Team, { foreignKey: "teamId" });
}

Now, let's migrate this into the database so it'll have the same structure. run this command to migrate.

npx sequelize db:migrate

if everything went successfully, you should see a message in your terminal that says something like xxxxxxxxxxx-create-user.js migrated along with the other migration files.

Be sure to be connected to your chat database, check-in Postico, you should now see all your tables with all the foreign keys added.

Thus far, your folder structure should now look like this

That is all for this one folks, please feel free to let me know if you had any problems during this process, it was a lot to do so I would be glad to help if you're stuck. Cheers!