4

Does anybody have a hands-on experience with both DB-libraries — knex vs. mysql2?

After some googling (e.g. at NPMCompare), I'm still curious to know, based on real experience, what are the pros & contra of both options?

So far, the only real advantage of using knex over mysql2, that I clearly see, is its universal support of MSSQL, MySQL, PostgreSQL, SQLite3, and Oracle, while the latter supports MySQL only, but since currently I'm focusing on MySQL only, this knex's feature seems to be less relevant.

The parameters I would consider:

  • Performance & load resistance;
  • Stability (production ready);
  • Native ES8+ support (callback-hell-free, no extra Util.promisify wrappers, ESM/MJS support);
  • Short and clear, the less verbose the better.
Mike
  • 14,010
  • 29
  • 101
  • 161
  • Good question, I currently don't have an answer. However, what I've noticed is knex.js is in very active development and has a big community. – Eugene M. May 02 '20 at 05:03
  • 1
    `mysql2` is direct DB driver, which is really low level tool for app development. `Knex` is a query builder, which provides some additional JS helpers for building SQL queries instead of using RAW SQL strings. IMO that is also too low level tool, when you want to handle complicated data with relations etc. (I'm knex maintainer btw.). To get really productive with DB code you could consider using some higher level library like objection https://www.jakso.me/blog/objection-to-orm-hatred – Mikael Lepistö May 04 '20 at 06:43
  • @MikaelLepistö, thanks for the link and detalization on Knex. – Mike May 04 '20 at 07:28

1 Answers1

7

I'm using knex on my primary project, I think that you are trying to compare apples with oranges, because Knex is a query builder that underline uses (mysql2) as the transport lib (in a case of MySql usage).

Benefits that I see in Knex are:

  1. Prevents SQL injection by default.
  2. Lets you build queries really easily without much on an effort
  3. Lets you compose queries as you would compose javascript functions (this is a big big advantage in my opinion).

Since # 3 is a such big advantage in my opinion it is better to demonstrate it:

Think you have 2 endpoints

  1. /users/list - which suppose to return a list of users ({id, name})
  2. /users/:id - which suppose to return a single user with the same structure.

You can implement it like this.

async function getAllUsers() {
  return db('users').columns('id', 'name'); //think that this can consist of many joins
}

async function getUserById(userId) {
  return getAllUsers().where('id', userId);
}

Look how getUserById is re-uses the same query (may be really complex), and just "adding" the limitation that it requires.

Performance wise, I don't think that this abstraction has a big cost, (I didn't noticed any performance issues yet)

I'm not sure what do you refer as stability, but Knex has a really cool TS support which can make your queries strongly typed.

interface User {
  id: number;
  name: string;
}

const users = await db<User>('users').columns('id', 'name'); // it will autocomplete the columns names & users will be of type User[] automatically.

With a combination of auto generating these db type from the DB using @typed-code/schemats it makes the work & refactoring sooo much better.

As of ES6, Knex supports by default Promises & callbacks, so you can choose whatever suits you.

Other cool features that I'm using is auto converting between cases, my db has a snake case style as for tables & columns names but in my node I work with camel case, using knex-stringcase plugin.

Migrations, allow you to define how to build / upgrade your schema with code, which can help you to auto update your production schema from CI.

Mysql2 is a low level driver above the DB.

felixmosh
  • 32,615
  • 9
  • 69
  • 88