Beginner’s Tutorial: Node, Express, PostgreSQL with Knex DB Migration & Seed in MVC Project Structure

In this tutorial, we will cover the basics of creating an MVC project structure using Node.js, Express, and PostgreSQL. We will also learn how to use Knex for database migration and seeding. This tutorial is aimed at beginners who are looking to learn how to build a full-stack web application.

Prerequisites

Before we get started, make sure you have the following installed on your computer:

• Node.js
• PostgreSQL
• Visual Studio Code (or any other code editor of your choice)

Setting Up the Project

1. Create a new folder for your project and open it in your code editor.
2. Open a terminal in your code editor and run the following command to create a new Node.js project:

   npm init -y

3. Install the necessary packages by running the following command:

   npm install express knex pg

Creating the MVC Project Structure

1. Create three folders in your project directory: controllers, models, and views.
2. Inside the controllers folder, create a new file called homeController.js. This file will contain the logic for the home page.
3. Inside the models folder, create a new file called userModel.js. This file will contain the schema for the user model.
4. Inside the views folder, create a new file called home.ejs. This file will contain the HTML for the home page.

Setting Up the Database

1. Create a new PostgreSQL database and name it whatever you like.
2. Update the knexfile.js in your project directory with your database information:

   module.exports = {
   development: {
   client: 'pg',
   connection: {
     database: 'your_database_name',
     user: 'your_database_username',
     password: 'your_database_password'
   },
   migrations: {
     directory: __dirname + '/db/migrations'
   },
   seeds: {
     directory: __dirname + '/db/seeds'
   }
   }
   };

Creating Database Migration

1. Create a new folder in your project directory called db.
2. Inside the db folder, create a new folder called migrations.
3. Run the following command in your terminal to create a new migration file:

   npx knex migrate:make create_users_table

4. Open the migration file that was created and add the following code to create a users table:

   
   exports.up = function(knex) {
   return knex.schema.createTable('users', function(table) {
   table.increments();
   table.string('name');
   table.string('email');
   });
   };

exports.down = function(knex) {
return knex.schema.dropTable(‘users’);
};

### Running Database Migration
1. Run the following command in your terminal to run the migration:

npx knex migrate:latest

### Creating Database Seed
1. Inside the `db` folder, create a new folder called `seeds`.
2. Run the following command in your terminal to create a new seed file:

npx knex seed:make add_users

3. Open the seed file that was created and add the following code to seed the `users` table:
```javascript
exports.seed = function(knex) {
  // Deletes ALL existing entries
  return knex('users').del()
    .then(function () {
      // Inserts seed entries
      return knex('users').insert([
        {name: 'John Doe', email: 'john.doe@example.com'},
        {name: 'Jane Smith', email: 'jane.smith@example.com'}
      ]);
    });
};

Running Database Seed

1. Run the following command in your terminal to run the seed:

   npx knex seed:run

Creating Routes and Controllers

1. Open the homeController.js file and add the following code to create a controller for the home page:

   
   const knex = require('knex')({
   client: 'pg',
   connection: {
   database: 'your_database_name',
   user: 'your_database_username',
   password: 'your_database_password'
   }
   });

exports.homePage = async (req, res) => {
const users = await knex(‘users’).select(‘*’);
res.render(‘home’, { users });
};

2. Open the `index.js` file in your project directory and add the following code to create a route for the home page:
```javascript
const express = require('express');
const app = express();
const homeController = require('./controllers/homeController');

app.set('view engine', 'ejs');

app.get('/', homeController.homePage);

app.listen(3000, () => {
  console.log('Server running on port 3000');
});

Conclusion

In this tutorial, we have covered the basics of creating an MVC project structure using Node.js, Express, and PostgreSQL. We have also learned how to use Knex for database migration and seeding. By following these steps, you should now have a fully functional web application that can interact with a PostgreSQL database. Feel free to explore and expand upon this project to further enhance your skills as a web developer.