Introduction to Databases

There are 2 main types of databases. The first one is called non-relational databases and the second one, you guessed it, relational databases. There was a lot of buzz around non-relational databases that will solve all the bottlenecks that relational databases have but that is not the case. In the industry 95% of the applications do not need a non relational databases. So, my advice to you is to use a non-relational database only when you don't know the structure of the data. Everything else can be solved with a relational database.


Table of Contents:


PostgreSQL

The database that we are going to use is called PostgreSQL and is a relational database. The query language used by PostgreSQL is of course SQL. PostgreSQL is date back to 1986 and it was a build as a project from University of California at Berkeley. The development of PostgreSQL is still active today and it is used by many companies in the industry.It is ACID compliance, reliable, predictable and it runs on most operating systems.

Install PostgreSQL

My favourite way to handle external dependencies when developing is through Docker. Docker is a virtual environment that can simulate different kinds of operating systems. Docker can run applications that are containerized. This means that we can run a containerized version of PostgreSQL without installing it on our machines. It will be installed through Docker. Docker is another topic on its own and it will be covered in a future post. The takeaway here is that it is really easy to learn, makes development a better experience and we can deploy applications in a predictable way.

  1. Download Docker.
  2. Run Docker image that contains PostgreSQL (in terminal)
 docker run --name picoblog-db -e POSTGRES_PASSWORD=mypassword -p 5432:5432  -d postgres

Explanation of the command above:

docker run runs a specified docker image.

--name it gives a name to the image that we run so we can reference it later.

-e it passes environment variables to the underlying OS that the docker image is running on.

-d runs the image in a detached mode, which means after executing the command in the terminal, it will return and the image will continue to run in the background.

-p 5432:5432 binds the docker port 5432 to external port 5432 so we can access the database outside of the docker container (e.g from localhost). So, if you think docker as a different computer, it has a postgres instance running on port 5432. However, from a different computer we cannot have access to that port because it is an internal port of that machine. By using the -p parameter we can specify what port docker should expose to the world.

Therefore, this will run the docker image postgres in a detached mode. It will give it a name called picoblog-db that can be used later to kill it or perform different actions on that image. Last, it will pass the environment variable POSTGRES_PASSWORD=mypassword.

3. Verify that the database is running.

docker ps

You should be able to see something similar to this.

CONTAINER ID        IMAGE               COMMAND                  CREATED              STATUS              PORTS               NAMES
738782899beb        postgres            "docker-entrypoint.s…"   About a minute ago   Up About a minute   5432/tcp            picoblog-db

Which means that the database is running at the port 5432.

Using databases in Node.js

There are  3 main ways to use a database in Node and pretty much in any language ot technology you choose to build your applications.

  1. Raw SQL queries
  2. ORM which stands for Object-Relational Mapping
  3. Query builders

The first option, it is not really ideal because you will have to implement a lot of logic to do simple things and also reimplement features around the security of your database. This will take time and money and it should be avoided.

The second option is really popular. It comes however, with additional complexity of creating models in code that will represent your domain objects. The reason I don't usually prefer ORMs is that it may work well for simple queries but when you want to have complex logic and not just inserting or reading stuff from the database you still have to write some sql code to do it.

The third option, in my opinion gives a happy medium between the other 2 aforementioned methods. It gives you a nice way to create and execute queries and is simple enough that you don't have to spend a lot of time learning how to set it up or use it. It also provides tooling around security and sql injections that is a real thing when you are running raw sql queries.

The query builder of my choice is Knex. You can find more about Knex here.

How to install Knex

  1. Navigate to the home directory of the app.
  2. Import the Knex library to our app, as before we can do
npm install knex

By doing so our package.json file will now include that dependency. Next, because we want to use Knex with PostgresSQL we have to install the appropriate database dependency as well.

npm install pg

Once more, this will add the dependency in the package.json file.

Connect to PostgreSQL database via terminal

First, before trying to connect to postgres programmatically, let's try to connect using terminal first. To do so, you should have psql installed on your machine. Because, I am using a mac computer my package manager of choice is Homebrew. You can install it by following this tutorial. After, homebrew is installed on your machine you can install postgres by typing in your terminal

brew install postgres

This will install postgres in your machine and gives as a cli tool to connect to postgres instances. To connect, type

psql -h localhost -U postgres

it will ask you for a password, which is the password we defined when we run the docker image which was mypassword. After, you enter your password you can list databases, tables and pretty much execute sql queries.

Connect to PostgreSQL via Postico

Postico is basically a GUI client for postgres. Instead of using the terminal we can use a nice user interface with many features that will help us develop our application faster and easier. You can set it up as shown below.

Postico setup

After you connect you will be able to see something similar to this.

Connected to postgres instance

List Postgres databases

To list all the available postgres database you can do so by typing \list or \l for shorthand.

List all databases in PostgreSQL

As you can see in the image above, I connected to the running database and listed all available databases. These are the default postgres databases and they should stay in unmodified condition because when we are creating a new database, postgres is actually copying one of these template databases as a starting point. You can read more about this in the official postgres documentation. The database named postgres it is safe to delete but I will leave it there for now.

Database Schema

Now, it is a good time to start defining our schema. So far, we have users and posts.

What information do we want to keep track for a user?

Maybe, a good starting point would be to keep track of the user's name, surname, email and password. Each user can create multiple posts.

What information do we want to keep track for a post?

We should keep track of post's content, the date that was created, who created each post etc. This is the first iteration of our app. At a later point we will look how to migrate our database to extend it with maybe additional entities or extend our existing entities with more fields. For example, we might want to add an additional field to posts called tags so we can search and find relevant posts.

Knex database creation

Let's see how we can create our tables and model the previous 2 entities in our database by using Knex and some javascript code.

How to connect to postgres from Knex

  1. Install Knex globally as an npm package. We need this to be able to use the cli tool that knex offers.
npm i -g knex

2. Run knex init to create the knexfile.js that will have the database configurations for different environments.

knex init

In order for knex to work in our configuration as it is at this point the knexfile.js should be as below

module.exports = {
  development: {
    client: 'pg',
    connection: {
      database: 'postgres',
      user: 'postgres',
      password: 'mypassword'
    },
    pool: {
      min: 2,
      max: 10
    },
    migrations: {
      directory: __dirname + '/knex/migrations'
    }
  },

  production: {
    client: 'pg',
    connection: {
      database: 'postgres',
      user: 'postgres',
      password: 'mypassword'
    },
    pool: {
      min: 2,
      max: 10
    },
    migrations: {
      directory: __dirname + '/knex/migrations'
    }
  }

};

3. Then in the root directory of our project create a folder called knex. Inside, create a file called knex.js that will have the following

const environment = process.env.ENVIRONMENT || 'development'
const config = require('../knexfile.js')[environment];
module.exports = require('knex')(config);

This exports a knex configuration, so it can be reused throughout the application.

4. Create a knex migration to create the users table

knex migrate:make create_users_table --knexpath knex/migrations

This will create a file under /knex/migrations. The file looks similar to this. I converted the functions to arrow functions.

exports.up = (knex, Promise) => {
    return knex.schema.createTable('users', (t) => {
        t.increments('id').unsigned().primary();
        t.dateTime('createdAt').notNull();
        t.dateTime('updatedAt').nullable();
        t.dateTime('deletedAt').nullable();
        t.string('name').notNull();
        t.string('surname').notNull();
        t.string('email').unique().notNull();
        t.string('password').notNull();
    });
};

exports.down = (knex, Promise) => {
    return knex.schema.dropTable('users');
};

This tells knex to create the users table with the 8 specified fills when we run a migration. On the other hand, we can also roll back and this will drop the table (e.g delete the table with all of its data).

5. Last, to create the table you can do

knex migrate:latest

This will run every migration we have. It can be more than just 1 file. At this point checking the database from Postico we can see that we have 3 new tables. The users table we created and 2 other tables for knex migrations.

Users table shown in Postico

6. To rollback, we can do

knex migrate:rollback

This will rollback the latest migration we applied. You don't have to actually run this step.

Hopefully, at this point you should have a working database, that you can run migrations and create/drop the users table.

The structure of the project should look like this

microblog/
	knex/
		migrations/
			20190325211227_create_users_table.js
		knex.js
	node_modules/
	routes/
		index.js
		login.js
	views/
		partials/
			navigation.hbs
		index.hbs
	app.js
	knexfile.js
	package.json
	package-lock.json

Posts table

To create the posts table we need another migration file that will have all the needed information how to create that table and also how to drop that table. It needs to know how to drop that table, so we can rollback in a previous working state. With every command that creates something in our database we need the inverse of that command to be able to go back in the previous state. This allows us to run migrations one by one if needed and to rollback if a migration broke the state of our database.

knex migrate:make create_posts_table --knexpath knex/migrations

This will create a file under /knex/migrations. The file looks similar to this. I converted the functions to arrow functions as before.

exports.up = (knex, Promise) => {
    return knex.schema.createTable('posts', (t) => {
        t.increments('id').unsigned().primary();
        t.dateTime('createdAt').notNull();
        t.dateTime('updatedAt').nullable();
        t.dateTime('deletedAt').nullable();
        t.string('title').notNull();
        t.string('body').notNull();
        t.integer('user_id').unsigned();
        t.foreign('user_id').references('id').inTable('users');
    });
};

exports.down = (knex, Promise) => {
    return knex.schema.dropTable('posts');
};

As we have seen earlier by doing

knex migrate:latest

we can run all migrations that have not been applied so far. So, the posts table will be created and looking at Postico we can see the new table. Looking at Postico, we can observe something like this

Posts Table

If we want to rollback we can do

knex migrate:rollback

Again, you don't have to actually run this step, as it will drop the users and posts table.

Foreign key constrains (one-to-many relationship)

Last, but not least we have to add a foreign key to the posts table. This will allow us to create a relationship of type one-to-many. In other words this means one user can have multiple (or many) posts. Later, we will be able to query our database using this information. We will be able to answer questions like "Give me all the posts for the user called John". With a SQL database, you try to capture all the relationships that exist in the domain of the application in the database level.

Database UML diagram

This is how our database looks like in a UML diagram. As you can see, a one-to-many realtionship exists between the table users and posts.

Database UML diagram

The structure in the end of this project looks like this

microblog/
	knex/
		migrations/
			20190325211227_create_users_table.js
			20190401214024_create_posts_table.js
		knex.js
	node_modules/
	routes/
		index.js
		login.js
	views/
		partials/
			navigation.hbs
		index.hbs
	app.js
	knexfile.js
	package.json
	package-lock.json

Congratulations, you have completed chapter 4. On this chapter we learn about databases and how to use knex to create migrations. Migrating database changes is something inevitable and will definitely happen if you work on a real world application. The goal of this tutorial is to learn the basics of using Postgres and Knex but also how to work on a real world application and something that can live on a production environment.

You can find the final result of this chapter in Github.

If you liked the tutorial, please consider subscribing to my blog. That way I get to know that my work is valuable to you and also notify you for future tutorials. Stay tuned!