import React from 'react';
const knex = require('knex');

// we require this pakages to use this - knex , oracledb , pg , pg-query-stream
// we tried with prisma but got stucked in fetching data


function page() {
  const db = async () => {
    const database = knex({
      client: 'pg',
      connection: {
        host: '1.2.3.4',
        port: 5432,
        user: 'username',
        password: 'password',
        database: 'databasename'
      }
    });

    const a = await database.from('analytics.u_kpi_essentialdata').select();
    console.log('test---', a);
  };
  db();

  return (
    <div>
      <div>page</div>
      <div> naks</div>
    </div>
  );
}

export default page;

import { Knex } from "knex";

export async function seed(knex: Knex): Promise<void> {
    await knex('students').del();
    await knex('teachers').del();

    const [teacher_id] = await knex.insert({
        name: "Bob",
        date_of_birth: "1970-01-01"
    }).into('teachers').returning('id');

    return await knex.insert([{
        name: "Peter",
        level: 25,
        date_of_birth: "1995-05-15",
        teacher_id: teacher_id
    },{
        name:"John",
        level: 25,
        date_of_birth: "1985-06-16",
        teacher_id: teacher_id
    },{
        name:"Simon",
        level: 25,
        date_of_birth: "1987-07-17",
        teacher_id: null
    }
    ]).into('students');
};

yarn knex  seed:make -x ts create-teachers-and-students

export async function up(knex: Knex) {
    if(await knex.schema.hasTable('teachers')){
        await knex.schema.alterTable('teachers',(table)=>{
            table.renameColumn("name","teacher_name");
            table.decimal("level",3).alter();
        });  
    }
};

export async function down(knex: Knex) {
    if(await knex.schema.hasTable("teachers")){
        await knex.schema.alterTable('teachers',(table)=>{
            table.renameColumn("teacher_name","name");
            table.string("level").alter();
        });
    }
};

yarn knex migrate:make create-memos

import { Knex } from "knex";

export async function up(knex: Knex)  {
    if(!await knex.schema.hasTable("students")){
        await knex.schema.createTable("students",(table)=>{
            table.increments();
            table.string("name");
            table.string("level");
            table.date("date_of_birth");
            table.integer("teacher_id").unsigned();
            table.foreign('teacher_id').references('teachers.id');
            table.timestamps(false,true);
        });
    }
};

export async function down(knex: Knex){
    await knex.schema.dropTableIfExists("students");
};

import dotenv from 'dotenv';
dotenv.config();

module.exports = {

  development: {
    debug: true,
    client: "pg",
    connection: {
      database: process.env.DB_NAME,
      user: process.env.DB_USERNAME,
      password: process.env.DB_PASSWORD
    },
    pool: {
      min: 2,
      max: 10
    },
    migrations: {
      tableName: "knex_migrations"
    }
  },

  staging: {
    client: "pg",
    connection: {
      database: process.env.DB_NAME,
      user: process.env.DB_USERNAME,
      password: process.env.DB_PASSWORD
    },
    pool: {
      min: 2,
      max: 10
    },
    migrations: {
      tableName: "knex_migrations"
    }
  },

  production: {
    client: "pg",
    connection: {
      database: "my_db",
      user: "username",
      password: "password"
    },
    pool: {
      min: 2,
      max: 10
    },
    migrations: {
      tableName: "knex_migrations"
    }
  }

};

yarn add knex @types/knex pg @types/pg
yarn knex init -x ts
yarn add dotenv @types/dotenv