My own realtime chat with react, graphql and postgresql [part 1-Backend Setup]

I've been learning react a while ago, and of course when apollo started to appear on my stackoverflow's feed i also decided to give a chance to graphql.

Graphql is (in short words) an alternative to (in some cases better than) REST. It allows you to cache the responses, query for specific values in a large service response and also has subscriptions: an abstraction of sockets that let you listen when some value was modified, created or deleted.

I built some CRUDs with plain data on the server, but i wanted to challenge my self, so i decided to use a database to achieve the persistence of my data. I chose postgresql.

Now, stop the talk, the code!

Backend

Database

For the database i used ElephantSQL. Creating a database here is a piece of cake, when you finish you will have access to it through an endpoint like this (but don't use this one, is mine!) postgres://pvfaleqnz:T3FGof..ruby.db.elephantsql.com:5432/pvfnhqn

Copy it, or check it later on the dashboard, as you prefer c:

Server

Because of using JS Elon Musk can go to the moon an back the times he decide, i also decided to use it on my backend with Node.

So here starts the fun c:

On your console, terminal, or that thing the hackers use to spy on your chats, type:

• npm init To initialize your project. Now you will have your package.json and you'll be able to import packages from npm!

• npm i apollo-server apollo-server-express To use apollo functions

• npm i graphql graphql-tools To use graphql schemas, queries and subscriptions!

• npm i pg knex To create database models and connect it with our database using SQL operations.

• npm i express To create our http server!

• npm i bcryptjs crypto Because every chat must have authentication, we will use these two to achive a secure signup and signin.

Now our package.json is ready! (for now).

The next step is connect our server with our remote database, for that we are gonna copy the db url showed in the Elephant dashboard and asign it to an environment variable, or wherever you wanna have it. (I highly recommend you to use dotenv package for node to achive the privacy of this url).

First execute knex init on your terminal, in your project folder. This will create a knexfile.js, a file where you can configure your query builder for the database.

For this example is ok if you only configure your development database, but of course pro hackers and facebook techleads define every database for each environment eg. Stagin, QA, Production, etc. So, go on as you like.

./knexfile.js

  development: {
    client: "pg",
    connection: *your db url*,
    migrations: {
      directory: "./data/migrations",
    },
  },

The data/migrations folder is where you are gonna define the structure of your tables eg. Users or Messages.

So the next step is execute knex migrate:make TABLE_NAME to create a file where you are gonna define one table structure.
For the messages table i created this model:

./data/migrations/12345_messages.js

exports.up = function (knex) {
    let createQuery = `CREATE TABLE messages (
                id SERIAL PRIMARY KEY NOT NULL,
                text TEXT NOT NULL,
                date TIMESTAMP NOT NULL,
                usr TEXT REFERENCES users(usr) ON DELETE CASCADE
                )`;

    return knex.raw(createQuery);
  };

  exports.down = function (knex) {
    let dropQuery = `DROP TABLE users`;
    return knex.raw(dropQuery);
  };

And for the users table this one:

./data/migrations/12345_users.js

exports.up = function (knex) {
    let createQuery = `CREATE TABLE users (
                usr TEXT PRIMARY KEY NOT NULL,
                name TEXT NOT NULL,
                type TEXT NOT NULL,
                password_digested TEXT,
                token TEXT NOT NULL
                )`;

    return knex.raw(createQuery);
  };

  exports.down = function (knex) {
    let dropQuery = `DROP TABLE users`;
    return knex.raw(dropQuery);
  };

Now in the ./data folder you can create a db.js file to configure where your connection will point to.

./data/db.js

const knex = require("knex");

const knexfile = require("../knexfile");

const env = process.env.NODE_ENV || "development";
const configOptions = knexfile[env];

module.exports = knex(configOptions);

SQL Queries

Now we can create or query functions for our database. For this we are gonna create a new folder in ./data called sql or somehing like that. The idea is that it is where we gonna find our sql queries.

So create two files ./data/sql/messages.js and ./data/sql/users.js in both files we have to import the db configuration.

Messages

const db = require("../db.js"); 

const addMessage = (message) => {
  return createMessagePromise(message)
    .then((message) => (message ? message : "Error adding new message"))
    .catch((err) => "Error creating message");
};

const createMessagePromise = (message) => {
  return db
    .raw(
      "INSERT INTO messages (text, date, usr) VALUES (?, ?, ? ) RETURNING id, text, date, usr",
      [message.text, message.date, message.usr]
    )
    .then(
      (data) => data.rows[0],
      (err) => console.log(err)
    );
};

const getMessages = () => {
  return getMessagesPromise()
    .then((messages) => (messages ? messages : "Error getting messages"))
    .catch((err) => "Error getting mensajes");
};

const getMessagesPromise = () => {
  return db.raw("SELECT * FROM messages").then(
    (data) => data.rows,
    (err) => console.log(err)
  );
};

module.exports = {
  addMessage,
  getMessages,
};

I'm going to explain the users file in the next part, because of the authentication and register process, so go ahead and check it out!