node.jsormdatabase-connectionknex.jsnode-mysql2

Knex vs. mysql2 based on performance, stability, and ES8+ support

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:

Solution

  • 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 of an effort
    3. Lets you compose queries as you compose javascript functions (this is a really 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.

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

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.