Hi Gigs,

Are you writing the raw queries for the CRUD operations. Creating tables from mysql Client. Suppose unfortunately you lost all your tables what will you do u must say i have already backuped the database and i will import it. How about installing the same application into multiple machines with same database, you have to import the database again and again . Suppose if you want to fill that database automatically how easy this will be. Get ready for this easiness we will be creating the migrations and seeding the database in very easy steps.

In previous tutorial Install Feathers for creating the Simple Rest APIs Application we have created the simple rest api application using feathers for inserting and authenticating the users. Now we will create some migrations for creating the database tables into same (tutorial) database. You have to create the knexfile.js in the same directory.

// Update with your config settings.

module.exports = {
  development: {
    client: 'mysql2',
    connection: {
      database: 'tutorial',
      user: 'tutorial',
      password: 'tutorial',
    },
    pool: {
      min: 2,
      max: 10,
    },
    migrations: {
      tableName: 'knex_migrations',
    },
  },

}; 

One command will create the migration for the service what we will be using after some time.

yarn knex migrate:make products 

One file has been created in the migration directory named as 20210925095644_products.ts. Now we will write the script into it to create the product tables.

import { Knex } from 'knex';

export async function up(knex: Knex): Promise<void> {
  await knex.schema.createTable('product', (table) => {
    table.increments('id');
    table.string('product_name', 50).unique().notNullable();
    table.integer('product_price', 50).notNullable();
    table.string('product_description').notNullable();
    table.integer('product_quantity').notNullable();
    table.timestamp('created_at');
    table.timestamp('updated_at');
  });
}

export async function down(knex: Knex): Promise<void> {
  await knex.schema.dropTableIfExists('product');
} 

Now we have to run the command to create table. Command is as follows

yarn knex migrate:latest 

Congratulations you have created the product tables with the column names what you have given. In the next chapter we will be creating the knex service for inserting and updating the data.

Related Links:

Knex offical documents