I created users, roles and users_roles(mapper) table. They are M:N relation.

users table has id, name

roles has id, name

users_roles has user_id, role_id

I am new on knex.js so I created tables separately without ORM, with migration files.

I have to set user's role and insert user row and users_roles row.

How to insert rows act like knex.js??

I thought insert user, insert role and then insert users_roles with user_id, role_id...

Is it right way? or let me know better way. Even the way of creating table with knex.js.

import { Knex } from 'knex';

export async function up(knex: Knex): Promise<void> {
  const result = await knex.schema.createTable('users', (table) => {
    table.bigIncrements('id');
    table.string('discord_user_id', 20).notNullable().unique();
    table.string('email', 50).nullable();
    table.string('public_key', 50).notNullable().unique();
    table.timestamp('created_at').defaultTo(knex.fn.now());
    table.timestamp('updated_at').defaultTo(knex.fn.now());
  });
  console.log(result);
}

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

import { Knex } from 'knex';

export async function up(knex: Knex): Promise<void> {
  await knex.schema.createTable('roles', (table) => {
    table.increments('id');
    table.string('name', 30).notNullable().unique();
    table.string('permission', 50).notNullable();
    table.timestamp('created_at').defaultTo(knex.fn.now());
    table.timestamp('updated_at').defaultTo(knex.fn.now());
  });
}

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

import { Knex } from 'knex';

export async function up(knex: Knex): Promise<void> {
  await knex.schema.createTable('users_roles', (table) => {
    table.bigInteger('user_id');
    table.integer('role_id');
    table.foreign('user_id').references('Users.id');
    table.foreign('role_id').references('Roles.id');
    table.timestamp('created_at').defaultTo(knex.fn.now());
    table.timestamp('updated_at').defaultTo(knex.fn.now());
  });
}

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