We will be using SQLite database in our code but you don't have to worry about the database because as mentioned in our previous post Knex standardize's the code you write. Standardization of code means that you will write your code once and Knex will handle it for different databases. You just need to change the configuration object for different databases.
Writing Knex Queries uisng Promise
We have used Knex callback interface in the example shown in the previous post. But instead of using callbacks interface we will use Promise. Promise provide a compelling alternative to raw callbacks when dealing with asynchronous code. In simple terms Promise is an object that represents an operation that hasn't completed yet. You can understand more about Promise from the below diagram :
Read More : What is Promise in context of Node.js
So Now we are going to change our example from Callbacks to Promise as shown below :
Callbacks
var knex = require("knex")(knexConfig);
knex.select("*").from("test").asCallback(function(err, values) {
if(err) {
console.log(err);
} else {
console.log(values);
}
knex.destroy();
});
Promise
var knex = require("knex")(knexConfig);
knex.select("*").from("test").then(function (values) {
// No need to check err object as this function will
// only be executed only when it is a success.
console.log(values);
}).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();
});
As you can see Promise is much more simpler, organized and modern way to handle asynchronous code.
Selecting Data using Knex
Knex has select method which internally creates a select query. It takes the column name as parameter and if none is specified, it selects all columns. Simple select queries are shown below :
Select method is for specifying columns and from method for table
knex.select("columnname1","columnname2").from("tablename")
Ordering of select and from is not important
knex.from("tablename").select("columnname1","columnname2")
Select can also be name as column and from can also be name as table
knex.column("columnname1","columnname2").table("tablename")
Short hand notation for selecting table
knex("tablename").column("columnname1","columnname2")
Knex has a first method which will give the first record of the query. Query to get the first record is shown below
knex.table("tablename").column("columnname1","columnname2").first();
Knex also has a limit method which allows you to specify how many records you want to get back. It will return that many records from the top.
knex.table("tablename").column("columnname1","columnname2").limit(1); //to get first record
knex.table("tablename").column("columnname1","columnname2").limit(10); //to get first 10 record
Writing Raw SQL
Lets say you want to write raw SQL query using Knex, this can be done using the raw method as shown below. The Raw SQl stateent will be injected as it is written.
knex.table("tablename").select(knex.raw("Count(*) as RowsCount"));
knex.raw("SELECT Count(*) from tablename");
But I will suggest not to use raw sql query as it is against the feature provided by Knex to standardized your data access.
More Methods
orderBy() : For ordering or sorting the results. Ex : orderby("columnname1", "desc")
count() : For counting number of records. Ex : count("coumnname1")
min() : For getting minimum value of a column Ex : min("columnname1")
max() : For getting maximum value of a column. Ex : max("coumnname1")
groupBy() : For grouping the results Ex. knex.table("tablename").column("columnname1").groupBy("columnname1")
where() : filtering the data based on certain conditions Ex : knex("tablename").where({"columnname1" : "somevalue"});
Please Like and Share CodingDefined.com Blog, if you find it interesting and helpful.
No comments:
Post a Comment