Coding Bootcamp - Backend #3 - Databases, SQL, and knex

What is a Relational Database?

Relational databases are easy to think of as tables, like you would find in excel, they have descriptive column headers and each new row represents a different case. The relational aspect comes in when we start matching tables up by rows they have in common.

This is easier to think of with an example, if we had one table of movies with movie_id, name, director, and rating.
It’s likely that we would have multiple films in that table with the same director, in this example ‘x’.

film_id	director_name	rating
1	x	10
2	y	5
3	x	2
4	z	2

This is when we could use another table to store all our director data. These tables can then be ‘joined’ together to avoid repeating data, and to store more extra information about directors separately.

• Change the director name from the table above to a director_id instead.

• A second table will have a director_id column and contain information about each director on a new row (and therefore a new director_id).

• The id in both tables must match correctly in order to join them by that column.

• After joining, because the two tables have a column in common, they will each have access to data from the other.

Shown below is what you could expect to have access to after joining two tables of film data and director data. This style of database, using multiple tables to store separate data and giving them unique id’s to perform joins, is called a relational database.

film_id	director_id	rating	director_name	age
1	1	10	x	52
2	2	5	y	33
3	1	2	x	52
4	3	2	z	45

Migrations and Seeding

Migrations are files which are run to create the initial tables in a database. They declare the column headers, data type for each column and certain rules the columns must follow. An example of this is that some columns MUST contain data, and can therefore be given a NOT NULL property. Seeding is the action of populating the empty tables that have been created with data.

Migrations and seed files can be automated with mocha testing hooks to be run before each test, this makes developing a larger testing suite for your server easier as you don't need to keep track of what you've already edited in the database every time you run a test.

Querying Databases

Server ✅
Populated Database ✅
Test file ✅

It’s time to make a GET request!

Servers need to communicate with databases to fetch information for the end user (or the front end developer). This communication is called querying. Querying a database would take place in the models files, and after receiving a response from the database the data is returned into the controller files to be sent.

GET request -> API router -> correct controller -> invokes model -> fetches data from database -> returns data to controller -> sends data in the response body.

SQL and knex.js

The good news is you can create a database, populate it with data and make requests to that database in your server models all using SQL or knex (which actually still uses SQL). A good online resource for learning some SQL is https://sqlbolt.com/, and once you’ve got a solid understanding of SQL you can move on to knex http://knexjs.org/.

The docs for knex are very good once you know SQL, the knex syntax is similar so by searching the docs page you can quite easily follow the examples to implement knex instead. Benefits of using knex are that it makes code more readable then using pure SQL, and can work with both callbacks and promise based async flows.

knex.js migrations

This is what a basic migrations file looks like for the films table described above. The two main sections are exports.up, which will initialise the table, and exports.down, which will remove it.

exports.up = function(knex) {
  return knex.schema.createTable("films", table => {
    table.increments("film_id").primary();
    table.text("title").notNullable();
    table
      .integer("director_id")
      .references("director_id")
      .inTable("directors")
      .notNullable()
  });
};

exports.down = function(knex) {
  return knex.schema.dropTable("films");
};

As you can see it contains readable methods like:

• .createTable () ... self explanatory right??
• .text('title') which will create a column with header of title and data type of text
• .increments(film_id) which will create a colum with header of film_id and will auto increase the id by 1 when each row is added
• .notNullable() which dictates that the column must contain data or an error will be thrown.

This is a brief example of some of the methods avaliable, and far more complex tables will require larger exports functions for adding more columns, and more restrictive rules on data types for example. The good news is methods are chain-able (as in the case of director_id below, i've chained a few methods together to create that column). Have a look at the docs and see what tables you can manage to create!

knex.js seeding

This is what a basic seeding file looks like for the films table.
It's important to .rollback() (deleting the tables) and .migrate.latest() (re-initialising them empty) before inserting your data. This is shown in the example below using promises to ensure the order is followed correctly. The actual insertion of data is simple and uses the .insert() method, with .returning('*') chained to show us the data we've inserted.

exports.seed = function(knex) {
  return knex.migrate
    .rollback()
    .then(() => knex.migrate.latest())
    .then(() => {
        return knex("films")
        .insert(filmData)
        .returning("*");
    }); 
};

Once your first table is populated you can chain more .then() blocks to add data to other tables, this can get trick though as you may have to edit your data to correctly format the director_id columns so they match and the joining will go smoothly.

knex.js querying

This is what querying the database above would look like in a server model called fetchFilmById, this would be invoked by a controller and pass the received data back up to the controller to be sent.

exports.fetchFilmById = film_id => {
  return connection("films")
    .select("*")
    .where("film_id", film_id)
};

This model takes one argument, the film_id the user wants to get data about (this would usually be passed in as a parameter in the request.If the film_id matched correctly, a films data (that row of the table) would be returned as an object and sent to the user.

Full examples

An example of a full backend project with migrations, seeding, lots of endpoints and more complex querying can be seen on my github repo https://github.com/alexandertrout/alex-be-nc-news - please get in touch if you have any questions regarding what it's like to attend a coding bootcamp, or any of the things I've tried to cover in this article.