Build your own REST API with Node, Express, Knex and PostgreSQL — part 2

23 Sep 2019 - John


In the last post, we set up our user model. Let's add two more models and explore its relationship to the user model.

Create the posts model

Fire up a terminal and type knex migrate:make create-posts and once again, this time for a comments column: knex migrate:make create-comments. It's important that you create this files in the same order, you'll see why later. This will create two files called timestamp_create-posts.js and timestamp_create-comments.js. For the sake of brevity, I will omit any mention of the timestamps from now on.
Great, we now have two new migration that we can start tweaking around. Let's start with our create-posts.js migration and add an id, a title and a body:

exports.up = function (knex) {
    return knex.schema.createTable('posts', (table) => {
        table.increments(); // This is our id field
        table.string('title');
        table.text('body', 'longtext');
    });
};
exports.down = function (knex) {
    return knex.schema.dropTable('posts')
};

Add the relationship

We have now our posts migration set up, but we're missing something. If you think about it, we still have no way of telling who each post BELONGS TO. I used bolds and caps for my Rails friends. When you set up a belongs_to relationship, what you are setting up is a column that references the "owner" of said model. Let's take a look:
We're going to add a new line in our posts migration and add:

table.integer('user_id').notNullable().references('id').inTable('users').onDelete('cascade');

Now we're talking! We now have a column called user_id that we can use to keep track of our authors. Whatever number we put in this column will be treated as the owner of the post.
Also, please note that while rails makes a lot more methods available when we set our relationships, we're just setting a simple one, but for the time being, it's just enough.
So our completed posts model should look like this now:

exports.up = function (knex) {
    return knex.schema.createTable('posts', (table) => {
        table.increments(); // This is our id field
        table.integer('user_id').unsigned().references('users.id');
        table.string('title');
        table.text('body', 'longtext');
    });
};
exports.down = function (knex) {
    return knex.schema.dropTable('posts')
};

I chose to add my relationship right after my id, but you can choose anywhere you want, as long as you're still inside the table definition.

On to the comments table

Nothing new, except this time we'll be referencing our two other tables, so a comment both belongs to a post and a user:

exports.up = function (knex) {
    return knex.schema.createTable('comments', (table) => {
        table.increments(); // Again, this will be our id
        table.integer('user_id').notNullable().references('id').inTable('users').onDelete('cascade');
        table.integer('post_id').notNullable().references('id').inTable('posts').onDelete('cascade');
        table.text('body', 'longtext');
    })
};
exports.down = function (knex) {
    return knex.schema.dropTable('comments');
};

And with this we have our database fully set up. All we need to do now is run knex migrate:latest to get our new tables into the database. Let's check it out by running psql tutorial:

~/: psql tutorial
psql (12.2)
Type "help" for help.
tutorial=# \d
                     List of relations
 Schema |              Name              |   Type   | Owner 
--------+--------------------------------+----------+-------
 public | comments                       | table    | john
 public | comments_id_seq                | sequence | john
 public | knex_migrations                | table    | john
 public | knex_migrations_id_seq         | sequence | john
 public | knex_migrations_lock           | table    | john
 public | knex_migrations_lock_index_seq | sequence | john
 public | posts                          | table    | john
 public | posts_id_seq                   | sequence | john
 public | users                          | table    | john
 public | users_id_seq                   | sequence | john
(10 rows)

Sweet! Everything is there plus a few more tables created by knex to keep track of our id's.
Adding more seed data
Let's add some more data to make sure we have something to work with. Run knex seed:make create_dummy_posts and go to your newly created /seeds/create_dummy_posts.js file. Edit it so it looks like this:

exports.seed = function (knex) {
  return knex('table_name').del()
    .then(function () {
      return knex('table_name').insert([
        {
          user_id: 1,
          title: 'JavaScript',
          body: 'JavaScript, often abbreviated as JS, is a programming language that conforms to the ECMAScript specification. JavaScript is high-level, often just-in-time compiled, and multi-paradigm. It has curly-bracket syntax, dynamic typing, prototype-based object-orientation, and first-class functions.'
        },
        {
          user_id: 1,
          title: 'Node.js',
          body: 'Node.js is an open-source, cross-platform, JavaScript runtime environment that executes JavaScript code outside of a web browser. Node.js lets developers use JavaScript to write command line tools and for server-side scripting-running scripts server-side to produce dynamic web page content before the page is sent to the user\'s web browser. Consequently, Node.js represents a \"JavaScript everywhere\" paradigm, unifying web-application development around a single programming language, rather than different languages for server- and client-side scripts.'
        },
        {
          user_id: 1,
          title: 'Express.js',
          body: 'Express.js, or simply Express, is a web application framework for Node.js, released as free and open-source software under the MIT License. It is designed for building web applications and APIs. It has been called the de facto standard server framework for Node.js.'
        }
      ]);
    });
};

We have three posts mapped to our first user with a title and a body. Now let's do the same with our comments. Run knex seed:make create_dummy_comments and edit the resulting /seeds/create_dummy_comments.js file:

exports.seed = function (knex) {
  return knex('table_name').del()
    .then(function () {
      // Inserts seed entries
      return knex('table_name').insert([
        { user_id: 1, post_id: 1, body: 'Far far away, behind the word mountains, far from the countries Vokalia and Consonantia, there live the blind texts.' },
        { user_id: 1, post_id: 1, body: 'The European languages are members of the same family. Their separate existence is a myth. For science, music, sport, etc.' },
        { user_id: 1, post_id: 1, body: 'But I must explain to you how all this mistaken idea of denouncing pleasure and praising pain was born and I will give you a complete account of the system.' },
        { user_id: 1, post_id: 2, body: 'Lights creepeth may. Fowl itself you\'re. Dry given moved man gathered moved replenish living. Likeness you\'ll to his can\'t every air fruit for, morning under they\'re.' },
        { user_id: 1, post_id: 2, body: 'Perhaps a re-engineering of your current world view will re-energize your online nomenclature to enable a new holistic interactive enterprise internet communication solution.' },
        { user_id: 1, post_id: 2, body: 'Fundamentally transforming well designed actionable information whose semantic content is virtually null.' },
        { user_id: 1, post_id: 3, body: 'Empowerment in information design literacy demands the immediate and complete disregard of the entire contents of this cyberspace communication.' },
        { user_id: 1, post_id: 3, body: 'Doing business like this takes much more effort than doing your own business at home' },
        { user_id: 1, post_id: 4, body: ' The Big Oxmox advised her not to do so, because there were thousands of bad Commas' },
      ]);
    });
};

And finally, we have 9 comments, each for one post, all mapped to the same user. If you want to, you can modify your user seed file to add a few more users and use their id's in this seed so that not all comments appear to be written by the post author himself in an apparent fit of psychosis.
Finally, let's check the database to see if our data was imported correctly:

tutorial=# SELECT * FROM users;
 id | username |        email        
----+----------+---------------------
  1 | John Doe | johndoe@example.com
  2 | Jane Doe | janedoe@example.com
(2 rows)
tutorial=# SELECT * FROM posts;
 id | user_id |   title    |                                                                                                                                                                                       
                                                                                             body                                                                                                                  

----+---------+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------
  1 |       1 | JavaScript | JavaScript, often abbreviated as JS, is a programming language that conforms to the ECMAScript specification. JavaScript is high-level, often just-in-time compiled, and multi-paradig
m. It has curly-bracket syntax, dynamic typing, prototype-based object-orientation, and first-class functions.
  2 |       1 | Node.js    | Node.js is an open-source, cross-platform, JavaScript runtime environment that executes JavaScript code outside of a web browser. Node.js lets developers use JavaScript to write comm
and line tools and for server-side scripting-running scripts server-side to produce dynamic web page content before the page is sent to the user's web browser. Consequently, Node.js represents a "JavaScript ever
ywhere" paradigm, unifying web-application development around a single programming language, rather than different languages for server- and client-side scripts.
  3 |       1 | Express.js | Express.js, or simply Express, is a web application framework for Node.js, released as free and open-source software under the MIT License. It is designed for building web applicatio
ns and APIs. It has been called the de facto standard server framework for Node.js.
(3 rows)
tutorial=# SELECT * FROM comments;
 id | user_id | post_id |                                                                                      body                                                                                      
----+---------+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  1 |       1 |       1 | Far far away, behind the word mountains, far from the countries Vokalia and Consonantia, there live the blind texts.
  2 |       1 |       1 | The European languages are members of the same family. Their separate existence is a myth. For science, music, sport, etc.
  3 |       1 |       1 | But I must explain to you how all this mistaken idea of denouncing pleasure and praising pain was born and I will give you a complete account of the system.
  4 |       1 |       2 | Lights creepeth may. Fowl itself you're. Dry given moved man gathered moved replenish living. Likeness you'll to his can't every air fruit for, morning under they're.
  5 |       1 |       2 | Perhaps a re-engineering of your current world view will re-energize your online nomenclature to enable a new holistic interactive enterprise internet communication solution.
  6 |       1 |       2 | Fundamentally transforming well designed actionable information whose semantic content is virtually null.
  7 |       1 |       3 | Empowerment in information design literacy demands the immediate and complete disregard of the entire contents of this cyberspace communication.
  8 |       1 |       3 | Doing business like this takes much more effort than doing your own business at home
  9 |       1 |       3 |  The Big Oxmox advised her not to do so, because there were thousands of bad Commas
(9 rows)

While the layout makes it difficult to see, we can rest assured that our data is there. Now we have all we need to star adding our routes! Come back next week for part 3.