ON TO THE KNEX ONE

TIME FOR A KNEXPLANATION

Knex.js is a SQL query builder for POSTGRES, MySQL, and SQLite databases that can be used in the browser and Node.js. Features include traditional node style callbacks, promise interface, query and schema builders, and connection pooling. Knex.js can be installed in Node.js with the following command: npm i -S knex . Once installed, you will also need to execute installation commands based on which database library is being used. npm i -S mysql, npm i -S sqlite3 for sqlite, and npm i -S pg for postgres. In the browser, knex can be built with the webpack and browserify Javascript tools.

INITIALIZING

The knex module is a function that takes a configuration object which stores parameters. Some parameters, such as the client are required. The client parameter determines which database library will be used.

The connection options are passed into database client to create a connection. It can be an object or a string.

var knex = require('knex')({
client: 'mysql',

// CONNECTION OPTION AS AN OBJECT
connection: {
host : '127.0.0.1',
user : 'your_database_user',
password : 'your_database_password',
database : 'myapp_test'
}
});
var pg = require('knex')({
client: 'pg',
// CONNECTION OBJECT AS A STRING
connection: process.env.PG_CONNECTION_STRING,
searchPath: 'knex,public'
});

In sqlite3, the connection option requires a filename instead of a connection.

var knex = require('knex')({
client: 'sqlite3',
connection: {
filename: "./mydb.sqlite`
}
});

SCHEMA BUILDING

Knex.schema is a function that returns an object containing a query. A new instance of knex.schema must be used in every query. A promise is returned.

withSchema Specifies the schema to be used when using the schema-building commands.

createTable Creates a new table on the database, with a callback function to modify the table’s schema.

hasTable Checks for a table’s existence by tableName, resolving with a boolean to signal if the table exists.

More schema building functions can be found here

PROMISES

Promises are the most efficient way of handling queries in knex.js. They allow you to return values from a fulfillment handler, which in turn become the value of the promise. You are also able to catch thrown errors without crashing your node app.

— .then()places current query builder chain into a promise state that tales resolve and reject handlers.

db.knex.schema.hasTable(‘urls’).then(function(exists) {
if (!exists) {
db.knex.schema.createTable(‘urls’, function (link) {
link.increments(‘id’).primary();
link.string(‘url’, 255);
link.string(‘baseUrl’, 255);
link.string(‘code’, 100);
link.string(‘title’, 255);
link.integer(‘visits’);
link.timestamps();
}).then(function (table) {
console.log(‘Created Table’, table);
});
}
});

— .catch() places query builder into a promise state. Catches any error thrown by a query.

More examples of promises can be seen here

Show your support

Clapping shows how much you appreciated Uraeus Millet’s story.