Getting Started with KnexJs for SQLs database, firstly install all the important dependencies like express e.t.c for your project.
For the installation of knex.js:
INSTALLATION
$ npm install knex --save
Then add one of the following types of SQL you chose to use:
$ npm install pg #For PostgreSQL
$ npm install pg-native #PostgreSQL with native C++ libpq
$ npm install @vscode/sqlite3 # required for sqlite
$ npm install better-sqlite3
$ npm install mysql #For MySQL
$ npm install mysql2 #For MySQL2
$ npm install oracledb #For oracledb
$ npm install tedious
Then the next thing you want to do is generate a knexfile in your config folder for configuring your SQL database by using:
$ npx knex init
This will generate a file called knexfile.js and it contains:
knexfile.js
// Update with your config settings.
/**
* @type { Object.<string, import("knex").Knex.Config> }
*/
module.exports = {
development: {
client: 'sqlite3',
connection: {
filename: './dev.sqlite3'
}
},
staging: {
client: 'postgresql',
connection: {
database: 'my_db',
user: 'username',
password: 'password'
},
pool: {
min: 2,
max: 10
},
migrations: {
tableName: 'knex_migrations'
}
},
production: {
client: 'postgresql',
connection: {
database: 'my_db',
user: 'username',
password: 'password'
},
pool: {
min: 2,
max: 10
},
migrations: {
tableName: 'knex_migrations'
}
}
};
Here you can edit this to suit your configuration, this is my own settings below:
// Update with your config settings.
/**
* @type { Object.<string, import("knex").Knex.Config> }
*/
module.exports = {
development: {
client: 'mysql',
connection: {
user: 'Abdulraqeeb',
password: null,
database: 'test'
},
pool: {
min: 2,
max: 10
},
migrations: {
tableName: 'knex_migrations'
}
}
};
After setting your configuration, you need to link your configuration with knex by creating another file in your config folder called db.js or config.js, inside here you import knex and connect it with your configuration as shown below:
config.js or db.js
const knex = require('knex');
const knexfile = require('./knexfile');
const db = knex(knexfile.development);
module.exports = db;
Then you need to create a folder called migrations inside your config folder and then generate/write migrations for API or web applications. To generate the migration file(s), use the command below:
$ npx knex migrate:make init --migrations-directory config/migrations
#you can change "init" to "user" or whatever table name you want to create in your database
This will generate a file like this:
20220319104333_users.js
Inside it you will find:
/**
* @param { import("knex").Knex } knex
* @returns { Promise<void> }
*/
exports.up = function(knex) {
};
/**
* @param { import("knex").Knex } knex
* @returns { Promise<void> }
*/
exports.down = function(knex) {
};
"exports.up" literally means add to the database this "table's schema"
"export.down" means drop or delete this "table's schema"
Then you can write your table schema like this:
/**
* @param { import("knex").Knex } knex
* @returns { Promise<void> }
*/
exports.up = function(knex) {
return knex.schema
.createTable('users', table => {
table.string('id', 10).notNullable().unique().primary();
table.string('email').notNullable().unique();
table.string('fullname').notNullable();
table.string('username').notNullable().unique();
table.string('password').notNullable();
table.bigInteger('money').notNullable().defaultTo(0);
table.timestamp('createdAt').defaultTo(knex.raw('CURRENT_TIMESTAMP'));
table.timestamp('updatedAt').defaultTo(knex.raw('CURRENT_TIMESTAMP'))
});
};
/**
* @param { import("knex").Knex } knex
* @returns { Promise<void> }
*/
exports.down = function(knex) {
return knex.schema.dropTable('users');
};
You can find how to use the datatypes on knexjs here.
After this, it's time to migrate it to the SQL you are using, in my case I am using MySQL,
so to make it easier to migrate, you can include the "migrate" script in your
package.json file
"scripts": {
"test": "jest",
"test:watch": "jest --watch",
"dev": "nodemon app.js",
"start": "node app,js",
"migrate": "npx knex migrate:latest --knexfile config/knexfile.js"
}
And using the terminal to run
$ npm run migrate
#or
$ npx knex migrate:latest --knexfile config/knexfile.js
This will create the tables and also knex_migration and knex_migration_lock
The knex_migration shows a table that contains each schema you migrated and the knex_migration_lock shows the schema's access locked or not locked.
You can find more about knex migrations here.
Now it's time to use our knex in our controllers, to fetch and insert data into our database. Here compare to other ORMs like sequelize or TypeORM etc, we won't use the same syntax like findByPk or any of such, But will be using the select, insert, where, del, update and etc. to perform different functions. Find more here.
I will explain some of this with examples;
exports.getUsers = async(req, res) => {
try {
await db('users')
.select({
id: 'id',
fullname: "fullname",
username: "username",
email: "email",
money: "money"
})
.then((user) => {
if(user) {
res.status(200).json(user)
} else{
res.status(404).json("No user found")
}
})
} catch (error) {
console.error(error)
return res.status(500).json({
status: false,
msg: "an error occured"
});
}
}
In the example above the** SELECT** method is used to define the parameter we need to send to the client from the database, in this case, we want to send the information of all users in the database, note that this is an asynchronous function meaning it has a callback function (".then()") to give the results/response after performing the function, it is very important to add a callback function with it, without that the function won't work.
This function will list the users in an array of objects:
[
{
"id": "_bnKpvCKaS",
"fullname": "admin test",
"username": "admin",
"email": "admin@gmail.com",
"money": 295000
},
{
"id": "pO5bMfU1yV",
"fullname": "admin2 test",
"username": "admin2",
"email": "admin2@gmail.com",
"money": 20000
}
]
For a case where one of the user's information is needed, ".where" should be added to the function as in:
exports.getUser = async(req, res) => {
const userinfo = req.user // for cases where you use authorization for logging in
try {
await db('users')
.select({
id: 'id',
fullname: "fullname",
username: "username",
email: "email",
money: "money"
})
.where({
username: userinfo.username
})
.then((user) => {
if(user[0]) {
res.status(200).json(user[0])
} else{
res.status(404).json("No user found")
}
})
} catch (error) {
console.error(error)
return res.status(500).json({
status: false,
msg: "an error occured"
});
}
}
Here you notice the addition of ".where" and also using "user[0]", this is because the response comes in arrays. This way you will get:
{
"id": "_bnKpvCKaS",
"fullname": "admin test",
"username": "admin",
"email": "admin@gmail.com",
"money": 295000
},
For INSERT, insert is used to get data into the database
await db('users')
.insert({
id: nanoid(10),
fullname: fullname,
username: username,
email: email,
money: 0,
password: hashedPass,
pin: hashedPin,
})
.then((user)=>{
return res.status(201).json({
status: true,
mesaage: "user Account created successfully"
})
});
For UPDATE
await db('users')
.where({
username: userinfo[0].username
})
.update(
req.body
)
.catch((err) => console.log(err));
For DELETE
await db('users')
.where({
username: username
})
.del()
.catch((err) => console.log(err));
Note: Without callbacks, these functions won't work.
Thank you for reading my article, I hope I was able to help someone with it. If there is anything you want me to add, feel free to reach out.
Top comments (0)