DOCSEARCHbyAlgolia
Submit Docs

Knex.js SQL Query Builder

website·Indexed 38 days ago

https://knexjs.org/

A SQL query builder for Node.js and browser environments. It provides a flexible interface for building queries, a schema builder for managing database structures, and a migration system to define and manage schema changes. The library supports transaction handling, raw expressions, and connection pool management, with releases tracking from version 1.0.6 through 3.3.0.

Tokens
18K
Snippets
111
Records
220
Agent score
74%

What's inside Knex

  1. Overview of Knex.js SQL Query Builder

    Knex.js is a flexible and portable SQL query builder for JavaScript. It provides a standardized interface for interacting with multiple database dialects, including PostgreSQL, CockroachDB, MSSQL, MySQL, MariaDB, SQLite3, Better-SQLite3, Oracle, and Amazon Redshift.

    Key features include:

    • Full-featured query and schema builders.
    • Transaction support with savepoints.
    • Connection pooling.
    • Multiple async interfaces: Promises, traditional Node-style callbacks, and a stream interface.
    • Standardized responses across different query clients and dialects.

  2. Use Knex Query Builder to construct SQL queries

    The Knex query builder is the primary interface for building and executing standard SQL queries (select, insert, update, delete). It uses a fluent, chainable API. You can start a query by passing a table name to the knex object or by calling a builder method directly on it.
    // Start with table name
knex('users').where('id', 1).first();

// Start with a method
knex.select('*').from('users');

  3. Use the Knex Schema Builder

    The knex.schema property is a getter function that returns a stateful object used to build schema queries. Because it is stateful, you must obtain a new instance of knex.schema for every individual query. All schema builder methods return promises.

  4. MariaDB driver and RETURNING support

    As of version 3.3.0, Knex officially supports the MariaDB driver and the returning clause for MariaDB queries.

  5. Implement type safety with TypeScript in Knex

    To get autocompletion and type inference for database rows, pass the row type as a generic parameter to the knex function.

    Caveat on Type Inference: Because Knex uses a mutable fluent API, type inference works best when the result is handled at the end of the chain. If you store a query builder in a variable and conditionally add .select() calls, the variable's type will not update. In such cases, explicitly define the result type using the second type parameter: knex<RowType, ResultType>(tableName).

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

// Basic type inference
knex<User>('users')
  .where('id', 1)
  .first(); // Resolves to User | undefined

// Explicit result type for mutable chains
const queryBuilder = knex<User, Pick<User, 'id' | 'age'>>('users');

  6. Implement a custom seed source

    Custom seed sources allow you to control where seeds are loaded from, which is useful for bundled applications (Webpack/Browserify) or non-standard folder structures. A custom source must implement getSeeds() (returning a Promise of seed identifiers) and getSeed(seed) (returning the seed function).
    class MySeedSource {
  getSeeds() {
    // Return a list of seed identifiers
    return Promise.resolve(['seed1']);
  }

  getSeed(seed) {
    switch (seed) {
      case 'seed1':
        return (knex) => {
          /* seed logic */
        };
    }
  }
}

// Use the custom source in the run method
knex.seed.run({ seedSource: new MySeedSource() });

  7. Populate database using Seed files

    Seed files allow you to populate the database with test or seed data independently of migrations. Seeds are executed in alphabetical order, and every seed file is executed every time seed:run is called. It is recommended to design seed files to reset tables before inserting data.
    # Create a seed file
knex seed:make seed_name

# Run all seed files
knex seed:run

# Run specific seed files
knex seed:run --specific=seed-filename.js --specific=another-seed-filename.js

  8. Use raw SQL expressions with knex.raw

    When you need to use a raw SQL expression in a query, use knex.raw(sql, bindings). This can be injected anywhere in the query chain. To prevent SQL injection, always use bindings instead of string interpolation.
    knex('users')
  .select(knex.raw('count(*) as user_count, status'))
  .where(knex.raw(1))
  .orWhere(knex.raw('status <> ?', [1]))
  .groupBy('status');

  9. Maintain a changelog for seeds using multiple migration directories

    To use Knex's migration changelog functionality for seeds without mixing seed files into the main migration directory, pass an array of directories to knex.migrate.latest and set sortDirsSeparately: true.
    await knex.migrate.latest({
  directory: [
    'src/services/orders/database/migrations',
    'src/services/orders/database/seeds',
  ],
  sortDirsSeparately: true,
  tableName: 'orders_migrations',
  schemaName: 'orders',
});

  10. Import Knex in ESM projects

    Due to a revert of breaking changes in version 3.2.8, the correct way to import Knex in an ESM (ECMAScript Module) environment is via the .mjs entry point.
    import { knex } from 'knex/knex.mjs';

  11. Create timestamp columns with microsecond precision in MySQL

    Knex supports creating timestamp columns with microsecond precision for MySQL version 5.6 and newer.

  12. Handle transactions with deferred execution

    Knex supports returning a started transaction without immediately executing it, allowing the transaction object to be passed around and only started when needed.