Stop using Knex.js

Using SQL query builder is an anti-pattern

3 years have passed, and this article remains as relevant and as controversial as it was on the day it was published. While the article is titled Knex.js, it applies to all trending query builders that encourage to write anything but raw SQL.

Abstract

Survey

“Knex.js allows to use language-native types and syntax for constructing queries”

SELECT id
FROM person
WHERE first_name = 'foo'
knex('person')
.where({
first_name: 'foo'
})
.select('id')

“Knex.js allows to build queries with dynamic conditions”

let query = knex('person');if (userInputFirstName) {
query = query.where({
first_name: userInputFirstName
});
}
if (userInputLimit) {
query = query.limit(userInputLimit);
}
const createWhereFragment = (firstName: string) => {
if (firstName) {
return sql.raw('WHERE first_name = $1', [firstName]);
}
return sql.raw('');
};
const createLimitFragment = (limit: number, offset?: number) => {
if (offset) {
return sql.raw('LIMIT $1 OFFSET $2', [limit, offset]);
}
return sql.raw('LIMIT $1', limit)
};
const query = sql`
SELECT id
FROM user
${createWhereFragment(userInputFirstName)}
${createLimitFragment(userInputLimit)}
`;

“Knex.js is safe by default”

Bulletproof, you could say.
knex('user')
.where({
first_name: potentiallyUnsafeUserInput
})
.select('id')
SELECT "id" FROM "user" WHERE "first_name" = $1
'SELECT id FROM user WHERE first_name = \'' + potentiallyUnsafeUserInput + '\'';
sql`SELECT id FROM user WHERE first_name = ${potentiallyUnsafeUserInput}`
{
sql: 'SELECT id FROM user WHERE first_name = $1',
values: [
potentiallyUnsafeUserInput
]
}
SQL syntax highlighting using sql tagged template literal.

“What about the identifiers?”

'SELECT "' + potentiallyUnsafeUserInput + '" FROM "user"';
knex('user')
.select(potentiallyUnsafeUserInput)
SELECT "foo" FROM "user"
'"' + identifier.replace(/"/g, '""') + '"'
sql`
SELECT ${sql.identifier([potentiallyUnsafeUserInput])}
FROM "user"
`
{
sql: 'SELECT "foo" FROM "user"',
values: []
}

“Using Knex.js reduces the SQL learning curve”

knex.schema.createTable('users', (table) => {
table.increments();
table.string('first_name');
});
knex('person')
.where({
first_name: potentiallyUnsafeUserInput
})
.select('id');

“Knex.js allows to compose together partial queries”

const query0 = sql`SELECT ${'foo'} FROM bar`;
const query1 = sql`SELECT ${'baz'} FROM (${query0})`;
{
sql: 'SELECT $1 FROM (SELECT $2 FROM bar)',
type: 'SQL',
values: [
'baz',
'foo'
]
};

“Knex.js allows to migrate between different SQL dialects”

“Knex.js abstracts transaction handling”

“Knex.js strict types warn about errors”

Is there a use case for Knex.js?

Closing notes

If (you support my open-source work through Buy me a coffee or Patreon) {you will have my eternal gratitude 🙌}

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Gajus Kuizinas

Founder, engineer interested in JavaScript, PostgreSQL and DevOps. Follow me on Twitter for outbursts about startups & engineering. https://twitter.com/kuizinas