The cover image took from the official Knex.js site
First of all, I'm so sorry for my bad English
What is Knex?
In short, Knex can be defined as a query builder for PostgreSQL, MSSQL, MySQL, MariaDB, SQLite3, Oracle and Amazon Redshift. Developer who uses ORM already knows what this query builder is.
The Knex' query builder inspired by the Laravel query builder.
Before we start, you should know Knex' GitHub repository.
Repository: https://github.com/knex/knex
Knex Works on Server and Browser
Knex works most efficiently with Node.JS on the server-side. However, if you want, it can work on the browser within the limits of WebSQL.
You shouldn't run browser-based SQL queries on the server. If you want to try to use it for your browser applications, a todo list application could be good.
What About TypeScript?
Knex has strong TypeScript support. TypeScript bindings will come when you install Knex' npm packages.
If you're using VSCode or similar editors, it works well.
Installation
There are two steps to install Knex. The first step you need to install Knex' itself.
npm install knex --save
In the next step, you have to choose a database engine. In this post, we will use SQLite3.
npm install pg --save
npm install sqlite3 --save
npm install mysql --save
npm install mysql2 --save
npm install oracledb --save
npm install mssql --save
In this step, you will not face any problem. Let's start coding by create a JavaScript file named index.js :)
First Configurations
There are different configs.
MySQL Example
var knex = require('knex')({
client: 'mysql',
connection: {
host : '127.0.0.1',
user : 'DATABASE_USERNAME',
password : 'DATABASE_PASSWORD',
database : 'DATABASE_NAME'
}
});
PostgreSQL Example
var knex = require('knex')({
client: 'pg',
version: '7.2',
connection: {
host : '127.0.0.1',
user : 'DATABASE_USERNAME',
password : 'DATABASE_PASSWORD',
database : 'DATABASE_NAME'
}
});
SQLite3 Example
const knex = require('knex')
const db = knex({
client: 'sqlite3',
connection: {
filename: "./DATABASE_NAME.sqlite"
}
});
We import knex to our project. However, we didn't create any migration or generate any schema. There are two types of methods to generate migrations. The first one, you create migrations from a JavaScript file. The second one is the Knex' CLI tool.
Edit package.json file
The scripts section will be like that;
"scripts": {
"dev": "node index.js",
"knex": "knex",
},
We will use dev to run the index.js file. We will use knex to run the Knex' CLI tool. Let's start with the CLI tool.
npm run knex
Using this command, we can see all the CLI commands. These are the CLI commands;
Commands:
init [options] Create a fresh knexfile.
migrate:make [options] <name> Create a named migration file.
migrate:latest [options] Run all migrations that have not yet been run.
migrate:up [<name>] Run the next or the specified migration that has not yet been run.
migrate:rollback [options] Rollback the last batch of migrations performed.
migrate:down [<name>] Undo the last or the specified migration that was already run.
migrate:currentVersion View the current version for the migration.
migrate:list|migrate:status List all migrations files with status.
seed:make [options] <name> Create a named seed file.
seed:run [options] Run seed files.
First of all, we will create a knex file named knexfile.js using the following command;
npm run knex init
The knex file will be like that;
// Update with your config settings.
module.exports = {
development: {
client: 'sqlite3',
useNullAsDefault: true,
connection: {
filename: './dev.sqlite3'
}
},
staging: {
client: 'postgresql',
connection: {
database: 'my_db',
user: 'username',
password: 'password'
},
pool: {
min: 2,
max: 10
},
migrations: {
tableName: 'knex_migrations'
}
},
}
Now, we have to create a migration. I mean, we will create a skeleton. We will use the below command;
npm run knex migrate:make todos
So, there is a task waiting to run. All the migrations will be in the migrations folder.
There will be methods named up and down in the migration file we've generated. For example;
exports.up = function(knex) {
};
exports.down = function(knex) {
};
Let's fill the blanks :P (We will not use the down)
exports.up = function(knex) {
return knex.schema.createTableIfNotExists('todos', function(table) {
table.increments();
table.string('title');
table.text('description');
table.dateTime('start_date');
table.dateTime('due_date');
table.timestamps();
});
};
Now, we will run the migrations using the below command.
npm run knex migrate:latest
Yay! We've generated a database file using the development environment. You can check the dev.sqlite3 file.
Creating CRUD Methods
We'll edit the index.js file using the knex file.
const knex = require('knex');
const knexFile = require('./knexfile').development;
const db = knex(knexFile);
Our configurations are ready for development.
Insert Method
We'll create a basic insert method;
const insertData = (tableName, data) => {
return db(tableName)
.insert(data)
.then(resp => resp)
.finally(() => db.destroy());
}
We will insert data into the database using the database instance.
The insertData method is waiting table name and data as a parameter. We can pass bulk data as an array or single data as an object.
insertData('todos', [
{
title: 'Write an article about Knex :)',
description: 'This will be description',
start_date: '2020-01-01 12:00',
due_date: '2020-02-15 16:56',
}
])
.then(insertedId => {
console.log(insertedId);
})
If you pass bulk data, insertId will be an array. If you pass a single object, it will be integer value.
Select Method
We'll create a basic select method;
const selectData = (tableName, options = { fields: [], filteringConditions: [] }) => {
const { fields, filteringConditions } = options
return db(tableName)
.select(fields)
.where(builder => {
filteringConditions.forEach(condition => {
builder.where(...condition)
});
})
.then(data => data)
.finally(() => db.destroy());
}
We can create different queries using the following examples;
Select all the todos
selectData('todos')
.then(todos => {
console.log(todos)
})
Select the todos using where
selectData('todos', {
filteringConditions: [
['id', '!=', 37],
['description', 'LIKE', '%123%']
]
})
.then(todos => {
console.log(todos)
})
Update Method
We'll create a basic update method. For example, we have an ID like 38. We want to update this record's details. First of all, we'll create an update method.
const updateData = (tableName, options = { fields: {}, filteringConditions: [] }) => {
const { fields, filteringConditions } = options
return db(tableName)
.where(builder => {
filteringConditions.forEach(condition => {
builder.where(...condition)
});
})
.update(fields)
.then(data => data)
.finally(() => db.destroy());
}
So, we have different cases.
Scenario 1:
In this example, we'll use where.
updateData('todos', {
fields: {
title: 'Updated',
},
filteringConditions: [
['id', '=', 38]
]
})
.then(updateData => {
console.log(updateData)
})
Scenario 2:
In this example, we'll not use where. So all the todo titles will be "Updated".
updateData('todos', {
fields: {
title: 'Updated',
}
})
.then(updateData => {
console.log(updateData)
})
Delete Method
We'll create a basic delete method.
const deleteData = (tableName, options = { filteringConditions: [] }) => {
const { filteringConditions } = options
return db(tableName)
.where(builder => {
filteringConditions.forEach(condition => {
builder.where(...condition)
});
})
.del()
.then(data => data)
.finally(() => db.destroy());
}
We can use this method like that;
deleteData('todos', {
filteringConditions: [
['id', '=', 38]
]
})
.then(deleteData => {
console.log(deleteData)
})
If you want to delete all the todos, you shouldn't use the filteringConditions
We created a simple CRUD architecture :P
Creating File-Based Migrations
I think you don't need to use file-based migrations. But we can create schemas like that;
db.schema.createTable('todos', (table) => {
table.increments();
table.string('title');
table.text('description');
table.dateTime('start_date');
table.dateTime('due_date');
table.timestamps();
});
We'll create our migrations like that;
db.migrate.make('todos');
And we need to run our migrations using the following code piece.
db.migrate.latest();
That's all :)
Thank you for reading :)
Resources
I used these resources while I preparing this article;
Top comments (0)