DEV Community: alexandertrout

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

alexandertrout — Fri, 07 Feb 2020 14:29:22 +0000

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.

Coding Bootcamp - Backend #2 - Servers, Express & Promises

alexandertrout — Sat, 25 Jan 2020 14:46:39 +0000

What is a server?

Servers will deal with requests made by clients to your API over the internet, requests being made by using standard web http protocols. Different 'endpoints' on the API are made avaliable to users, and methods like GET, POST, and DELETE are used on these different endpoints. RESTful servers will follow this pattern, with multiple methods being allowed on each endpoint instead of having a different endpoint for each method. For example, a GET request on the /api/users endpoint should simply respond to the client with information about all the users. However, this endpoint may also allow a POST request, to insert a new user to the database. This ability to have multiple methods allowed on one endpoint is important, and can be implemented by using express to build your server...

How do I build a server using express?

Express is a web framework for Node.js which allows users to create apps which will listen for requests from clients and will send responses. More information on express and getting started can be found here https://expressjs.com/. Using express to build a simple server can be done quickly, and then scaled up to add routers and multiple endpoints, each allowing different methods.

MVC or Model - Controller - View is the software design pattern I've been following. Essentially, once routing has been used to reach an endpoint a 'controller' will handle the request and response, and a 'model' will handle contacting another API or the database, perform any necessary logic, and will return a promise to the controller to be handled. The V in MVC stand for 'view' - this is the point where pages can be served to the user containing the data they requested, and is achieved through using EJS, PUG or similar to inject the data into a HTML file. This approach is still used by many companies, but it has become more popular to remove this presentation aspect to a front-end framework such as react instead.

Servers can look complicated at first...

So I'd recommend following a tutorial for a simple server to begin with and build upon that, adding more complexity slowly. Heres one I've made reference to a few times that will explain the basic concepts well.

https://www.youtube.com/watch?v=L72fhGm1tfE&t=1527s

How do promises make async programming easier?

As mentioned in the last post, async programming must be used to avoid blocking when writing code. This was managed previously by using callback functions for any of our asynchronous code. Promises simplify this process by returning what is essentially a 'black box' from a function. This returned 'promise' can be in one of three states:

Fulfilled: resolve() was called (Some data is returned inside the promise).
Rejected: reject() was called (Something went wrong and an error was returned inside the promise).
Pending: not yet fulfilled or rejected.

Promises can wrap functionality in the same way that asynchronous callbacks do, and gaining access to the values within the promises is possible by using the .then() and .catch() methods, once the promise has resolved or rejected.

Example:

getData = (req, res, next) => {
    return (/* data request from an external source */);
};

.then()
Using a .then() after a promise allows us to gain access to the promises return value within the parentheses, shown below is an example where the promise has fulfilled and our data would be output to the console. In place of this console log is where we could place functionality regarding that data including returning that data from our API to a client.

getData().then((data) => {
    console.log(data);
});

.catch()
Similarly to using an error first callback, a .catch(next) after a promise will invoke the next error handling middleware function in the chain and respond with an error message explaining what went wrong. So using the example below, if there was an error getting the data the promise would be rejected with an error, and next would invoke the next error handler with that error.

getData().then((data) => {
    console.log(data);
}).catch(next);

So using promises can streamline building a server using express, and will hopefully make your code easier to follow. Once you've got a grip on using promises regularly they become easy to implement and will ultimately make your backend code better.

Examples of some of my apis can be found on my gitHub:

https://github.com/alexandertrout/songgame - A song guessing game that uses express and EJS to present views to the user. This game is also hosted on heroku and can be played at https://pickthefakesong.herokuapp.com/.

https://github.com/alexandertrout/alex-be-nc-news - A more complex, reddit style backend that will allow users to post articles, comments and votes.

Coding Bootcamp - Backend #1 - Asynchronous code

alexandertrout — Tue, 21 Jan 2020 07:20:34 +0000

A quick recap, After the first 3 week block I'm comfortable using the command line, Git, GitHub, HTML, CSS, and JavaScript. I've been taught concepts such as closure, recursion, functional programming, and using classes in OOP. I've also done more katas than I can remember and made some really basic websites in my spare time outside of classes.

...

Starting a Coding Bootcamp - Fundamentals

alexandertrout — Mon, 30 Dec 2019 17:40:18 +0000

The first few weeks of coding.

To start with I was learning HTML and CSS basics, as well as some JavaScript fundamentals on websites like Codecademy and FreeCodeCamp. I’d been doing these online tutorials and katas (online coding challenges) alongside working full-time, so it was quite time consuming but I needed to be sure that coding was for me. Nailing these fundamentals and fully understanding them was also key to getting along easier when more complex ideas would be introduced later. Once I was confident enough, and after a lot of researching bootcamp graduate outcomes (including a fair amount of LinkedIn stalking), I took the leap and signed up for a bootcamp.

So, what's it actually like getting started?

One of the realisations I’ve had so far is I now know how it feels to be coding full-time and it’s easier to get a feel for how that might translate to the workplace. I’ve always preferred to learn quickly, especially when you get straight to putting the concepts into practice. Let's just say doing a bootcamp is hard work, but at least you'll never get bored!

It’s important to understand you won’t be building apps from scratch on day one, even if you could follow a YouTube tutorial and do this at home. That won't cut it in a job. You're changing careers to do work in what is basically a foreign language in just a few months. There's a lot of times you'll feel like this...

It’s about understanding everything from the bottom up; not just being able to code, but about being able to explain why you're doing what you’re doing. One of the trickiest things to get used to is that the answers aren’t always going to be there, and that tutors will only help you so far. In this case, learn to Google. What you'll be told a lot is that devs are basically professional Googlers.

Getting stuck and getting yourself unstuck is a huge part of what makes code enjoyable, if you like to solve problems then you need problems to solve!

What Have I Learnt?

So far in the first 3 week block I've been introduced to the command line, Git, GitHub, HTML, CSS, and JavaScript. I feel comfortable with coding concepts such as closure, recursion, functional programming, and using classes in OOP. Everything has been done using test driven development (TDD) - which is something that you should definitely, always, 100%, be using. It's what's used in actual development of production code to make sure everything works as expected, so early exposure to TDD is a great thing for anyone looking for a career in development.

Over the next few weeks I'll be covering all things back end. I’m excited to start using my coding skills to work with API’s and real-world data. You can check in on what I'm doing by having a look at my GitHub account.

Key Lessons:

Coding style is IMPORTANT
Use the command line now so you’re ready for later
~~recursion is really easy to understand~~
Closure, recursion and async are difficult to wrap your head around. You need to know them well, so just do the research until you understand!
You can never do enough katas
Google.
TDD.
Enjoy it, it's exciting to learn how tech works!