In this article we do a quick WHY you might need access to relational databases, and then show a brief HOW you can do it.
Data storage is an important part of any computer application. Collected data needs to be stored for later analysis, or data needs to be retrieved to generate desired outputs. There are many ways of storing memory — file storage (including text and/or binary options), relational databases, NoSQL systems, and I’m sure I’ve missed a few.
SQL is a standard shared across many different relational database management systems (aka RDBMS). The SQL that gets implemented sometimes varies from the official standard. Those variations don’t matter too much if you stay within the same database platform. However the moment you start moving between different database vendors or even different versions within the same vendor, the differences can become important. Shifting between relational and no relational systems brings even more work.
You could use a DB specific library to talk to your database. For instance you could do npm install sqlite3 mysql pgsql,mssql to bring in the ability to communicate with a SQLite, MySQL/MariaDB, PostgreSQL, or a Microsoft SQL Server system — you would normally only see one of these used by an application.
Using this specific DB library does work and exposes the full capabilities of the database system. If the DB is ever changed though — or worse yet you don’t know what the DB will be until the actual system installation is done — then this quickly breaks down. The library for one type of database cannot properly talk to a different type of database.
Wouldn’t it be nice if there was a way you could write your code without worrying about which database system may be used for your application?
Enter the concept of Database Abstraction. In short, this is a library that presents a layer above the specific DB libraries. This layer separates the database specifics from your application code. You get to write your code once, and then if/when the database needs to change you just make a minor configuration change (and install the support libraries that may be needed). But your code itself — with all of its query goodness — does not change.
There is a disclaimer needed here. If you use a database abstraction system and then use a feature that is very specific to your database, then your code will most definitely need changes to address that when the database vendor changes.
For instance stored procedures are generally platform specific extensions to SQL — that functionality would need to be recreated in the new database system if it were used. That does not mean stored procedures cannot be used though. They would just need to be used consciously of the implications.
Abstraction Layers vs Object Relational Mapping systems Link to heading
There is some overlap between the idea of an abstraction layer and an Object Relational Mapping system. Both provide some automated ways for talking to your database. The ORM though goes further and attempts to understand your database structure. With an abstraction layer you can create an INSERT statement that works across whatever DB system is configured. With an ORM, you could extend that insert to create the new record AND the related records with a single command.
ORMs may be powerful and convenient in some cases. My own experience suggests they come with a cost though and using them can quickly result in more work than necessary. (I might post about this sometime if anyone is interested)
You may be thinking Sequelize should be mentioned here. Sequelize is an ORM and does more than simple database abstraction. My experience with Sequelize is limited, but it just never clicked for me.
If I wanted to use an ORM I would use Objection.JS. Objection is built on top of Knex.JS. I tried to implement Objection for a major project and eventually had to rollback to just using Knex. Not because Objection wasn’t capable, but because our use case broke the limits of what Objection should do with regards to the relations. (Which *could* have been some bad design choices on the part of the developer — er, that would be me…)
Node.JS options Link to heading
I’m sure I’m missing some great options but for me Knex.JS is the only real choice. It is lightweight and tries to be as unopinionated as possible.
Knex is simple enough and follows pretty close with the normal SQL language.
const records = knex('tasks')
.where({user_id: 5})
.orderBy('task_date');
This JS code would result in select * from tasks where user_id = 5 order by task_date. There are many options for what goes inside the brackets, and all can be dynamic. While the SQL is simple, the capabilities that Knex represents is very powerful.
Getting started with Knex.JS Link to heading
Read the docs at Knex.JS. This is the defacto “source” for any Knex information.
Getting started is as simple as doing an npm install.
mkdir dbProject
cd dbProject
npm init -y
npm install knex pg
This sets up a practice project and installs knex. We also install the “pg” library to talk with a PostgreSQL database. If you are intending on using MySQL, then replace “pg” with “mysql”. If you are going to use a different database system, then just ensure you have a library to talk to it installed. The Knex docs give more info on this.
Now that we have knex, we need to set things up. I like to do this with an NPM script. So I alter my package.json’s script section
"scripts": {
"knex": "knex",
"test": 'echo "Error: no test specified" && exit 1'
},
Now we can issue the command npm run knex init in our project directory. This will create a knexfile.js file. Examine that file and you will see a standard Node module returning an object that has environment specific configurations. This allows you to use SQLite for testing, MySQL for development, but MSSQL for production, if you desired. (that sounds like a bad mix, but it *is* feasible). You alter the connections to whatever you need for your environment. You could remove the “staging” and “production” objects and just use the “development” object.
You set the client property to match which database system you are using. And then you set the connection property with the details for connecting to your server — host name/ip, username, password, etc.
Once you have that done you can now talk to the database and execute commands against it.
Migrations Link to heading
Knex uses what is known as a ‘Migration’ to create, drop, and alter the database elements. These migration files can set up indexes, foreign keys, or even run custom SQL if needed. The intent here is that we can “apply” the migrations that have not yet been applied to our database and bring the system up to date. Or return the system to a previous state before we added that problematic index (or table).
Knex has a convenience method for creating and running migrations.
npm run knex migrate:make create_tasks_table
This generates a file in the migrations directory. The directory is created if it does not exist. And you can specify where the directory gets created and what to call it in the knexfile.js file. The generated file has a name in the form of 20200807222531_create_tasks_table. That is Year, Month, Day, Hour, Minute, Second followed by a description. Do not alter the FORMAT of this name, though changing name is allowed — unless it has already been applied to the database. (if you apply the change, then change the filename, then try to rollback, you will encounter errors because the original file name no longer exists.)
The contents of that file looks like this:
exports.up = function(knex) {
};
exports.down = function(knex) {
};
Then we can call the Knex Schema system to create and drop our table whenever this migration is applied or un-applied.
exports.up = function(knex) {
return knex.schema.createTable('tasks', table => {
table.increments('id')
table.string('name')
})
};
exports.down = function(knex) {
return knex.schema.dropTable('tasks')
};
It is important to note that the up and down methods must return a promise. See the Schema Docs for more details what you can do here.
Now we can run npm run knex migrate:latest to apply that change to the database. If your connection is all set up and there are no errors, your database should have a tasks table now. But it will also have ‘migrations’ and ‘migrations-lock’ tables as well. These help track which migrations have been applied.
Using Knex in your modules Link to heading
Finally to use Knex in our application we need to call it.
I usually create a _connection.js file that contains the details of how to connect to the database. This file calls the Knex object with the appropriate configuration settings.
const config = require('./knexfile.js')
const Knex = require('knex')
module.exports = Knex(config\[process.env.NODE\_ENV || 'development'\])
I do this because sometimes I need something more complex here. I may be setting up multiple connections, or doing other work related to setting up the database. Added bonus for the resulting knex object being treated as a singleton thanks to the module system.
Once that is done, I create what I call a “Model” that will do the data work.
// src/models/task.js
const knex = require('./connection.js')
async function getAll() {
return knex('tasks').orderBy('name')
}
module.exports = {
getAll
}
Now I can require that file and use it wherever needed in my application:
// src/services/current.js
const TaskModel = require('../models/task.js')
console.log(TaskModel.getAll())
If I have data in my tasks table, I will end up with an array of objects representing each record in that table.
Boom! We have RDBMS connectivity with a great deal of potential.
Of course the details get a little more involved, but this should get you started and pointed in the right direction.