tessl/npm-knex

A batteries-included SQL query & schema builder for PostgresSQL, MySQL, CockroachDB, MSSQL and SQLite3

—

Pending

Quality

Pending

Does it follow best practices?

Impact

Pending

No eval scenarios have been run

Securityby

Pending

The risk profile of this skill

Overview

Eval results

Files

Knex.js

Name: tessl/npm-knex
Author: tessl

Knex.js is a comprehensive SQL query builder library for Node.js that provides a flexible, portable, and intuitive API for building SQL queries across multiple database systems including PostgreSQL, MySQL, CockroachDB, MSSQL, SQLite3, and Oracle. It features a chainable interface for constructing complex queries, schema building capabilities, transaction support, connection pooling, streaming queries, and supports both promise-based and callback-based APIs.

Package Information

Package Name: knex
Package Type: npm
Language: JavaScript/TypeScript
Installation: npm install knex

Core Imports

const knex = require('knex');

For TypeScript:

import knex from 'knex';
import { Knex } from 'knex';

For ESM:

import knex from 'knex';

Basic Usage

const knex = require('knex')({
  client: 'sqlite3',
  connection: {
    filename: './mydb.sqlite'
  }
});

// Basic query
const users = await knex('users')
  .select('*')
  .where('age', '>', 18);

// Insert data
await knex('users').insert({
  name: 'John Doe',
  email: 'john@example.com',
  age: 25
});

// Update data
await knex('users')
  .where('id', 1)
  .update({ age: 26 });

// Create table
await knex.schema.createTable('posts', table => {
  table.increments('id');
  table.string('title').notNullable();
  table.text('content');
  table.integer('user_id').references('id').inTable('users');
  table.timestamps(true, true);
});

Architecture

Knex.js is built around several key components:

Main Knex Instance: Central factory that creates database connections and query builders
Query Builder: Fluent interface for constructing SQL queries with method chaining
Schema Builder: Database schema management for creating, altering, and dropping tables/views
Migration System: Version control for database schema changes
Transaction Manager: ACID transaction support with savepoints and rollbacks
Connection Pooling: Efficient database connection management
Multi-Dialect Support: Unified API across different database systems

Capabilities

Core Knex Instance

Main knex factory function and instance management for database connections and configuration.

interface KnexConfig {
  client: string;
  connection: string | ConnectionConfig;
  pool?: PoolConfig;
  migrations?: MigratorConfig;
  seeds?: SeederConfig;
  debug?: boolean;
  useNullAsDefault?: boolean;
}

interface ConnectionConfig {
  host?: string;
  port?: number;
  user?: string;
  password?: string;
  database?: string;
  filename?: string;
  ssl?: boolean | object;
}

function knex(config: KnexConfig): Knex;
function knex<TRecord = any, TResult = any>(tableName: string): Knex.QueryBuilder<TRecord, TResult>;

interface Knex {
  <TRecord = any, TResult = any>(tableName: string): Knex.QueryBuilder<TRecord, TResult>;
  raw(sql: string, bindings?: readonly RawBinding[] | ValueDict): Knex.Raw;
  ref(columnName: string): Knex.Ref<string, { [K in string]: string }>;
  transaction<T>(callback: (trx: Knex.Transaction) => Promise<T>): Promise<T>;
  schema: Knex.SchemaBuilder;
  migrate: Knex.Migrator;
  seed: Knex.Seeder;
  fn: Knex.FunctionHelper;
  client: Knex.Client;
  destroy(): Promise<void>;
}

Query Building

Comprehensive SQL query construction with a fluent chainable interface for SELECT, INSERT, UPDATE, DELETE operations and advanced query features.

interface QueryBuilder<TRecord = any, TResult = any> {
  select<ColNameUT extends keyof TRecord>(...columns: readonly ColNameUT[]): QueryBuilder<TRecord, Pick<TRecord, ColNameUT>[]>;
  from<TTable extends TableNames>(tableName: TTable): QueryBuilder<ResolveTableType<TTable>, TResult>;
  where<K extends keyof TRecord>(columnName: K, value: DbColumn<TRecord[K]>): QueryBuilder<TRecord, TResult>;
  insert(data: TRecord | readonly TRecord[]): QueryBuilder<TRecord, number[]>;
  update(data: DbRecordArr<TRecord>): QueryBuilder<TRecord, number>;
  del(): QueryBuilder<TRecord, number>;
}

Query Building

Schema Management

Database schema operations for creating, altering, and managing tables, columns, indexes, and constraints across different database systems.

interface SchemaBuilder {
  createTable(tableName: string, callback: (tableBuilder: CreateTableBuilder) => any): SchemaBuilder;
  alterTable(tableName: string, callback: (tableBuilder: AlterTableBuilder) => any): SchemaBuilder;
  dropTable(tableName: string): SchemaBuilder;
  hasTable(tableName: string): Promise<boolean>;
  createSchema(schemaName: string): SchemaBuilder;
  dropSchema(schemaName: string): SchemaBuilder;
}

interface CreateTableBuilder {
  increments(columnName?: string): ColumnBuilder;
  integer(columnName: string): ColumnBuilder;
  string(columnName: string, length?: number): ColumnBuilder;
  text(columnName: string): ColumnBuilder;
  boolean(columnName: string): ColumnBuilder;
  date(columnName: string): ColumnBuilder;
  timestamp(columnName: string, options?: Readonly<{ useTz?: boolean; precision?: number }>): ColumnBuilder;
  primary(columnNames: readonly string[]): TableBuilder;
  unique(columnNames: readonly string[]): TableBuilder;
  foreign(column: string): ReferencingColumnBuilder;
}

Schema Management

Migrations & Seeds

Database version control through migrations and data seeding functionality for managing schema changes and populating databases.

interface Migrator {
  make(name: string, config?: MigratorConfig): Promise<string>;
  latest(config?: MigratorConfig): Promise<[number, string[]]>;
  rollback(config?: MigratorConfig, all?: boolean): Promise<[number, string[]]>;
  status(config?: MigratorConfig): Promise<number>;
  currentVersion(config?: MigratorConfig): Promise<string>;
  list(config?: MigratorConfig): Promise<[string[], string[]]>;
}

interface Seeder {
  make(name: string, config?: SeederConfig): Promise<string>;
  run(config?: SeederConfig): Promise<[string[]]>;
}

Migrations & Seeds

Transactions & Raw Queries

Transaction management with ACID compliance and raw SQL query execution for complex operations and database-specific functionality.

interface Transaction extends QueryBuilder {
  commit(value?: any): Promise<any>;
  rollback(error?: any): Promise<any>;
  savepoint<T>(callback: (trx: Transaction) => Promise<T>): Promise<T>;
  isCompleted(): boolean;
}

interface Raw<TResult = any> extends Promise<TResult> {
  wrap<TResult2 = TResult>(before: string, after: string): Raw<TResult2>;
  toSQL(): Sql;
  timeout(ms: number, options?: { cancel?: boolean }): Raw<TResult>;
}

function raw(sql: string, bindings?: readonly RawBinding[] | ValueDict): Raw;
function transaction<T>(callback: (trx: Transaction) => Promise<T>): Promise<T>;

Transactions & Raw Queries

CLI Tools

Command-line interface for managing migrations, seeds, and project initialization with comprehensive tooling support.

# Initialize project
knex init

# Migration commands
knex migrate:make <name>
knex migrate:latest
knex migrate:rollback
knex migrate:up
knex migrate:down
knex migrate:currentVersion
knex migrate:list

# Seed commands  
knex seed:make <name>
knex seed:run

CLI Tools

Types

type RawBinding = Value | QueryBuilder | Raw;
type Value = string | number | boolean | Date | Array<string | number | boolean | Date> | null | undefined;
type ColumnName<TRecord> = keyof TRecord | string;
type TableName = string;

interface Sql {
  method: string;
  sql: string;
  bindings: readonly RawBinding[];
  options: any;
  toNative(): SqlNative;
}

interface SqlNative {
  sql: string;
  bindings: readonly RawBinding[];
}

interface ColumnBuilder {
  index(indexName?: string): ColumnBuilder;
  primary(): ColumnBuilder;
  unique(): ColumnBuilder;
  references(columnName: string): ReferencingColumnBuilder;
  onDelete(command: string): ColumnBuilder;
  onUpdate(command: string): ColumnBuilder;
  defaultTo(value: Value): ColumnBuilder;
  unsigned(): ColumnBuilder;
  notNullable(): ColumnBuilder;
  nullable(): ColumnBuilder;
  comment(val: string): ColumnBuilder;
}

interface ReferencingColumnBuilder extends ColumnBuilder {
  inTable(tableName: string): ColumnBuilder;
  deferrable(type: deferrableType): ColumnBuilder;
}

type deferrableType = 'not deferrable' | 'immediate' | 'deferred';

interface PoolConfig {
  min?: number;
  max?: number;
  createTimeoutMillis?: number;
  acquireTimeoutMillis?: number;
  idleTimeoutMillis?: number;
  reapIntervalMillis?: number;
  createRetryIntervalMillis?: number;
  propagateCreateError?: boolean;
}

interface MigratorConfig {
  database?: string;
  directory?: string | readonly string[];
  extension?: string;
  tableName?: string;
  schemaName?: string;
  disableTransactions?: boolean;
  sortDirsSeparately?: boolean;
  loadExtensions?: readonly string[];
  migrationSource?: MigrationSource<any>;
}

interface SeederConfig {
  database?: string;
  directory?: string | readonly string[];
  loadExtensions?: readonly string[];
  timestampFilenamePrefix?: boolean;
  sortDirsSeparately?: boolean;
}

interface MigrationSource<TMigrationSpec> {
  getMigrations(loadExtensions: readonly string[]): Promise<string[]>;
  getMigrationName(migration: string): string;
  getMigration(migration: string): Promise<TMigrationSpec>;
}

interface FunctionHelper {
  now(precision?: number): Raw;
  uuid(): Raw;
}

Workspace: tessl
Visibility: Public
Created: 7 months ago
Last updated: 2 months ago
Describes: pkg:npm/knex@3.1.x
Publish Source: CLI
Badge