Rohit Dhand

In case we have created a table and forget to include a foreign key, we can do it by creating a new migration and then running it. The following code shows how to do it: knex.schema.table('employees', function (table) { table.bigInteger('deptno').references('id').inTable('Depts') }); The above code adds a column deptno as a foreign key in the table named "Employees", that refers a column "ID" from the Dept table. Happy Coding!!!.

Migrations are the best way of creating and maintaining database stuff. Through Knex we can easily create migrations and then finally run them against the database. After installing the "knex" package, we can use the following command: knex migrate:make setup The above command helps to create directory (migrations) and place a migration file in that folder. Now edit the contents of the file to include the following stuff: exports.up = function(knex, Promise) {     return Promise.all([     knex.schema.createTable('employees', function(table){       table.string('empname');       table.string('job');       table.timestamps();     })   ]) }; exports.down = function(knex, Promise) {     return Promise.all([     knex.schema.dropTable('employees');   ]) }; The above code has got two important concepts: a. UP b. DOWN UP is a function used when the migration is run against the Database and DOWN is the function when we wou

In case we want to update a record within a table through KNEX, we can use the following code: updateRecord(); function updateRecord(){   knex('depts')   .where("deptno","40")   .update({     dname: 'TEST DEPARTMENT'   })   .then(count=>{     console.log("record updated"+count);   }); } The above code calls a function updateRecord that updates the deptno = 40's department name to 'TEST DEPARTMENT'. Also in the Promise '.then', it prints out number of records that were updated. Happy Coding !!!

Select is the only command available in the whole SQL to retrieve data. KNEX provides an implementation of the "Select" Query. The following code illustrates: knex.select("*").from("depts") .then(function (dept) {     dept.forEach(function(value){       console.log(value.deptno);     });   }).catch(function(err) {     // All the error can be checked in this piece of code     console.log(err);   }).finally(function() {     // To close the connection pool     knex.destroy();   }); } The above code has following important observations: a. Its Selecting data from a table named "Depts". We are selecting all the columns and rows. b. Once select query has fetched rows, it will run a promise of .then c. "dept" in the .then function is a collection of all records fetched from the table. d. We can access individual columns like "value.deptno", "value.dname" within forEach loop. Happy Coding !!!

This post is all about of deleting records through KNEX ORM in Nodejs. The following code shows how to achieve this: knex("depts").where("deptno","50").del() .then(function (count) {     console.log(count); }) .finally(function () {     knex.destroy(); }); The above code deletes a record from a table "Depts" where Deptno = 50. It has a "then" Promise attached that will show how many records were deleted with this command. Also "finally" is the method that will always execute and will close the KNEX connection. Happy Coding !!!

Knex can be used to insert data into tables. The following code shows how we can do that. var knex = require ( 'knex' )({ client: 'mysql' , connection: { host : '127.0.0.1' , user : 'root' , password : '' , database : 'lovely' } }); knex ( 'depts' ). insert ({ name: 'Technology' }) . returning ( 'id' ) . then (( id ) => { console.log(id); }); In the above code, we used a node package knex to connect Nodejs to mysql. And then we are finally inserting a new record in the table named "Depts". The value of the new dname inserted is  'Technology'. Once its inserted, it will be returning an automatic incremented ID for that dept. This Id

KNEX is a wonderful ORM library that can be used with Javascript . Recently I had a chance to use it in one of my projects. Thought of sharing with all others. In order to use Knex we need to have node package installed 'knex'. Once the package is installed we can use the following code to connect to mysql through Node with Knex. var knex = require ( 'knex' )({ client: 'mysql' , connection: { host : '127.0.0.1' , user : 'root' , password : '' , database : 'test' } }); This is a very simple way to connect our node program to mysql through Knex.

In case we have a date value and we want to generate the full name of the month for that date, we can use the following code: DateTime vDt = DateTime.Now; String vMonthName = vDt.ToString("MMMM"); Happy Coding!!!.

If in a C# Program we want to generate FirstDate and LastDate of a month, and we already have month and year, the following code can be used: int vMonth = 12; DateTime vFirstDateOfMonth = new DateTime(2017, vMonth, 1); DateTime vLastDateOfMonth = vFirstDateOfMonth.AddMonths(1).AddDays(-1); Happy Coding!!!