### Basic Knex Configuration

Source: https://github.com/knex/documentation/blob/main/src/guide/index.md

Demonstrates the fundamental way to initialize Knex with a configuration object, specifying the database client and connection details.

```javascript
const knex = require('knex')({
  client: 'mysql',
  connection: {
    host : '127.0.0.1',
    port : 3306,
    user : 'your_database_user',
    password : 'your_database_password',
    database : 'myapp_test'
  }
});
```

--------------------------------

### Install Knex and Database Drivers (Node.js)

Source: https://github.com/knex/documentation/blob/main/src/guide/index.md

Installs the core Knex library and specific database drivers for Node.js environments. Dependencies include `knex` and one of the listed database drivers like `pg`, `sqlite3`, `mysql`, etc.

```bash
$ npm install knex --save

# Then add one of the following (adding a --save flag):
$ npm install pg
$ npm install pg-native
$ npm install sqlite3
$ npm install better-sqlite3
$ npm install mysql
$ npm install mysql2
$ npm install oracledb
$ npm install tedious
```

--------------------------------

### PostgreSQL Configuration with Search Path

Source: https://github.com/knex/documentation/blob/main/src/guide/index.md

Shows how to configure the PostgreSQL client in Knex, including setting an initial search path for connections.

```javascript
const pg = require('knex')({
  client: 'pg',
  connection: process.env.PG_CONNECTION_STRING,
  searchPath: ['knex', 'public'],
});
```

--------------------------------

### SQLite3 Configuration with File Path

Source: https://github.com/knex/documentation/blob/main/src/guide/index.md

Demonstrates configuring Knex with the SQLite3 adapter, specifying the database file path.

```javascript
const knex = require('knex')({
  client: 'sqlite3', // or 'better-sqlite3'
  connection: {
    filename: "./mydb.sqlite"
  }
});
```

--------------------------------

### Connection with User Parameters

Source: https://github.com/knex/documentation/blob/main/src/guide/index.md

Initializes Knex with custom user-defined parameters that can be accessed via the `knex.userParams` property.

```js
const knex = require('knex')({
  client: 'mysql',
  connection: {
    host : '127.0.0.1',
    port : 3306,
    user : 'your_database_user',
    password : 'your_database_password',
    database : 'myapp_test'
  },
  userParams: {
    userParam1: '451'
  }
});
```

--------------------------------

### MSSQL Configuration with Custom Binding Mapping

Source: https://github.com/knex/documentation/blob/main/src/guide/index.md

Illustrates configuring the MSSQL client to use a custom `mapBinding` function for mapping Knex query parameters to tedious types, with an example of binding strings to VarChar.

```javascript
import { TYPES } from 'tedious';

const knex = require('knex')({
  client: 'mssql',
  connection: {
    options: {
      mapBinding: value => {
        // bind all strings to varchar instead of nvarchar
        if (typeof value === 'string') {
          return {
            type: TYPES.VarChar,
            value
          };
        }

        // allow devs to pass tedious type at query time
        if (value != null && value.type) {
          return {
            type: value.type,
            value: value.value
          };
        }

        // undefined is returned; falling back to default mapping function
      }
    }
  }
});
```

--------------------------------

### Better-SQLite3 Configuration with Native Binding Path

Source: https://github.com/knex/documentation/blob/main/src/guide/index.md

Demonstrates configuring the Better-SQLite3 adapter to specify the location of its compiled C++ addon using `options.nativeBinding`.

```javascript
const knex = require('knex')({
  client: 'better-sqlite3',
  connection: {
    filename: ":memory:",
    options: {
      nativeBinding: "/path/to/better_sqlite3.node",
    },
  },
});
```

--------------------------------

### PostgreSQL Configuration with Database Version

Source: https://github.com/knex/documentation/blob/main/src/guide/index.md

Shows how to specify the database version in the Knex configuration when using the PostgreSQL adapter to connect to a non-standard database.

```javascript
const knex = require('knex')({
  client: 'pg',
  version: '7.2',

```

--------------------------------

### Querying with Different Clients and Returning Columns

Source: https://github.com/knex/documentation/blob/main/src/guide/index.md

Demonstrates initializing Knex with different SQL clients (PostgreSQL and generic) and executing an insert query that returns all columns.

```js
const pg = require('knex')({client: 'pg'});

knex('table')
  .insert({a: 'b'})
  .returning('*')
  .toString();
// "insert into "table" ("a") values ('b')"

pg('table')
  .insert({a: 'b'})
  .returning('*')
  .toString();
// "insert into "table" ("a") values ('b') returning *"
```

--------------------------------

### afterCreate Callback for Connection Initialization

Source: https://github.com/knex/documentation/blob/main/src/guide/index.md

Defines a callback function executed after a new connection is acquired from the database. This allows for initial connection setup, such as setting timezones or executing other initial queries. The 'done' callback must be invoked to validate the connection.

```js
const knex = require('knex')({
  client: 'pg',
  connection: {/*...*/},
  pool: {
    afterCreate: function (conn, done) {
      // in this example we use pg driver's connection API
      conn.query('SET timezone="UTC";', function (err) {
        if (err) {
          // first query failed, 
          // return error and don't try to make next query
          done(err, conn);
        } else {
          // do the second query...
          conn.query(
            'SELECT set_limit(0.01);',
            function (err) {
              // if err is not falsy, 
              //  connection is discarded from pool
              // if connection aquire was triggered by a 
              // query the error is passed to query promise
              done(err, conn);
            });
        }
      });
    }
  }
});
```

--------------------------------

### Better-SQLite3 Configuration for Read-Only Mode

Source: https://github.com/knex/documentation/blob/main/src/guide/index.md

Shows how to configure the Better-SQLite3 adapter to open a database in read-only mode using `options.readonly`.

```javascript
const knex = require('knex')({
  client: 'better-sqlite3',
  connection: {
    filename: "/path/to/db.sqlite3",
    options: {
      readonly: true,
    },
  },
});
```

--------------------------------

### Creating a Knex Instance with Custom User Parameters

Source: https://github.com/knex/documentation/blob/main/src/guide/index.md

Shows how to create a new Knex instance with specific user parameters, which can be useful for executing similar operations with different contexts, such as running migrations for different tables.

```js
const knex = require('knex')({
  // Params
});

const knexWithParams = knex.withUserParams({
  customUserParam: 'table1'
});
const customUserParam = knexWithParams
  .userParams
  .customUserParam;
```

--------------------------------

### PostgreSQL Configuration with Connection String and SSL

Source: https://github.com/knex/documentation/blob/main/src/guide/index.md

Illustrates a PostgreSQL configuration pattern using a connection string and conditional SSL settings, prioritizing connectionString over individual fields.

```javascript
const pg = require('knex')({
  client: 'pg',
  connection: {
    connectionString: config.DATABASE_URL,
    host: config["DB_HOST"],
    port: config["DB_PORT"],
    user: config["DB_USER"],
    database: config["DB_NAME"],
    password: config["DB_PASSWORD"],
    ssl: config["DB_SSL"] ? { rejectUnauthorized: false } : false,
  }
});
```

--------------------------------

### SQLite3 Configuration with In-Memory Database

Source: https://github.com/knex/documentation/blob/main/src/guide/index.md

Shows how to configure SQLite3 or Better-SQLite3 to use an in-memory database by setting the filename to ':memory:'.

```javascript
const knex = require('knex')({
  client: 'sqlite3', // or 'better-sqlite3'
  connection: {
    filename: ":memory:"
  }
});
```

--------------------------------

### PostgreSQL Connection

Source: https://github.com/knex/documentation/blob/main/src/guide/index.md

Establishes a connection to a PostgreSQL database using Knex.js. Requires specifying client, host, port, user, password, and database name.

```js
const knex = require('knex')({
  client: 'pg',
  connection: {
    host : '127.0.0.1',
    port : 5432,
    user : 'your_database_user',
    password : 'your_database_password',
    database : 'myapp_test'
  }
});
```

--------------------------------

### MySQL Connection

Source: https://github.com/knex/documentation/blob/main/src/guide/index.md

Establishes a connection to a MySQL database using Knex.js. Requires specifying client, host, port, user, password, and database name.

```js
const knex = require('knex')({
  client: 'mysql',
  version: '5.7',
  connection: {
    host : '127.0.0.1',
    port : 3306,
    user : 'your_database_user',
    password : 'your_database_password',
    database : 'myapp_test'
  }
});
```

--------------------------------

### Enabling Debug Mode

Source: https://github.com/knex/documentation/blob/main/src/guide/index.md

Activates debug logging for all Knex queries by setting the `debug: true` flag during initialization.

```js
const knex = require('knex')({
  client: 'pg',
  connection: {
    host : '127.0.0.1',
    port : 5432,
    user : 'your_database_user',
    password : 'your_database_password',
    database : 'myapp_test'
  },
  debug: true
});
```

--------------------------------

### Migrations Configuration

Source: https://github.com/knex/documentation/blob/main/src/guide/index.md

Specifies configuration options for database migrations, including the name of the migrations table. Refer to the Knex.js Migrations guide for more details.

```js
const knex = require('knex')({
  client: 'mysql',
  connection: {
    host : '127.0.0.1',
    port : 3306,
    user : 'your_database_user',
    password : 'your_database_password',
    database : 'myapp_test'
  },
  migrations: {
    tableName: 'migrations'
  }
});
```

--------------------------------

### Development Commands

Source: https://github.com/knex/documentation/blob/main/README.md

Commands to run the development server and install dependencies for knex.js.

```bash
yarn dev # or npm run dev

npm run dev

yarn install # or npm i
yarn dev # or npm run dev
```

--------------------------------

### SQLite3 Configuration with Flags

Source: https://github.com/knex/documentation/blob/main/src/guide/index.md

Illustrates configuring the SQLite3 adapter with specific flags to control how the connection is opened, such as enabling URI and shared cache modes.

```javascript
const knex = require('knex')({
  client: 'sqlite3',
  connection: {
    filename: "file:memDb1?mode=memory&cache=shared",
    flags: ['OPEN_URI', 'OPEN_SHAREDCACHE']
  }
});
```

--------------------------------

### Install Knex Globally

Source: https://github.com/knex/documentation/blob/main/src/guide/migrations.md

Installs the Knex.js command-line interface globally on your system, allowing you to use Knex commands from any directory.

```bash
npm install knex -g
```

--------------------------------

### Custom PostgreSQL Client with JSONB Support

Source: https://github.com/knex/documentation/blob/main/src/guide/index.md

Connects to PostgreSQL using a custom client like `knex-aurora-data-api-client`, explicitly enabling JSONB column type support.

```js
const knex = require('knex')({
    client: require('knex-aurora-data-api-client').postgres,
    connection: { resourceArn, secretArn, database: `mydb` },
    version: 'data-api',
    jsonbSupport: true
})
```

--------------------------------

### Knexfile Seed Configuration Example

Source: https://github.com/knex/documentation/blob/main/src/guide/migrations.md

An example of how to configure the directory for seed files within the knexfile.js. Seed files are executed in alphabetical order.

```javascript
module.exports = {
  // ...
  development: {
    client: {/* ... */},
    connection: {/* ... */},
    seeds: {
        directory: './seeds/dev'
    }
  }
  // ...
  }
```

--------------------------------

### Webpack Migration Source Example

Source: https://github.com/knex/documentation/blob/main/src/guide/migrations.md

Provides an example of a custom migration source for integrating migrations within a Webpack bundle.

```javascript
const path = require('path')

class WebpackMigrationSource {
  constructor(migrationContext) {
    this.migrationContext = migrationContext
  }

  getMigrations() {
    return Promise.resolve(
      this.migrationContext.keys().sort()
    )
  }

  getMigrationName(migration) {
    return path.parse(migration).base
  }

  getMigration(migration) {
    return this.migrationContext(migration)
  }
}

// Pass an instance of your migration source as knex config
knex.migrate.latest({
  migrationSource: new WebpackMigrationSource(
    require.context('./migrations', false, /.js$/)
  )
})

// With webpack >=5, filter out absolute paths to avoid duplicates
knex.migrate.latest({
  migrationSource: new WebpackMigrationSource(
    require.context('./migrations', false, /^\.\/.*\.js$/)
  )
})
```

--------------------------------

### Dynamic Connection Configuration

Source: https://github.com/knex/documentation/blob/main/src/guide/index.md

Configures Knex.js connection using a function that returns connection details dynamically. This function can return a configuration object or a promise for one.

```js
const knex = require('knex')({
  client: 'sqlite3',
  connection: () => ({
    filename: process.env.SQLITE_FILENAME
  })
});
```

--------------------------------

### Dynamic Connection with Token Expiration

Source: https://github.com/knex/documentation/blob/main/src/guide/index.md

Demonstrates dynamic connection configuration where the connection details, including an authentication token with a limited lifespan, are fetched asynchronously. An `expirationChecker` function is provided to refresh the token when it expires.

```js
const knex = require('knex')({
  client: 'postgres',
  connection: async () => {
    const { 
      token, 
      tokenExpiration 
    } = await someCallToGetTheToken();

    return {
      host : 'your_host',
      port : 5432,
      user : 'your_database_user',
      password : token,
      database : 'myapp_test',
      expirationChecker: () => {
        return tokenExpiration <= Date.now();
      }
    };
  }
});
```

--------------------------------

### SQL Output for Ref Usage

Source: https://github.com/knex/documentation/blob/main/src/guide/ref.md

The SQL generated by the primary `knex.ref` usage example, showing how references and schema are translated into SQL.

```sql
knex(knex.ref('Users').withSchema('TenantId'))
  .where(knex.ref('Id'), 1)
  .orWhere(knex.ref('Name'), 'Admin')
  .select(['Id', knex.ref('Name').as('Username')])
```

--------------------------------

### MySQL Connection via Unix Domain Socket

Source: https://github.com/knex/documentation/blob/main/src/guide/index.md

Connects to a MySQL database using a Unix domain socket, bypassing the need for host and port configurations.

```js
const knex = require('knex')({
  client: 'mysql',
  connection: {
    socketPath : '/path/to/socket.sock',
    user : 'your_database_user',
    password : 'your_database_password',
    database : 'myapp_test'
  }
});
```

--------------------------------

### Connection Pool Configuration

Source: https://github.com/knex/documentation/blob/main/src/guide/index.md

Configures the connection pool using tarn.js, allowing adjustments for minimum and maximum connections. Recommended to set 'min' to 0 to terminate idle connections.

```js
const knex = require('knex')({
  client: 'mysql',
  connection: {
    host : '127.0.0.1',
    port : 3306,
    user : 'your_database_user',
    password : 'your_database_password',
    database : 'myapp_test'
  },
  pool: { min: 0, max: 7 }
});
```

--------------------------------

### Enabling Async Stack Traces

Source: https://github.com/knex/documentation/blob/main/src/guide/index.md

Enables the capture and throwing of asynchronous stack traces for all query builders and raw queries. This feature aids in debugging by preserving stack traces across asynchronous operations, though it incurs a small performance overhead and is recommended for development use.

```js
const knex = require('knex')({
  client: 'pg',
  connection: {
    host : '127.0.0.1',
    port : 5432,
    user : 'your_database_user',
    password : 'your_database_password',
    database : 'myapp_test'
  },
  asyncStackTraces: true
});
```

--------------------------------

### Customizing Knex Logging

Source: https://github.com/knex/documentation/blob/main/src/guide/index.md

Knex provides internal logging functions for warnings, errors, deprecations, and debug information. These can be overridden by providing custom functions within the `log` option for each Knex instance.

```javascript
const knex = require('knex')({
  log: {
    warn(message) {
      // Custom warning logging logic
      console.warn('Knex Warning:', message);
    },
    error(message) {
      // Custom error logging logic
      console.error('Knex Error:', message);
    },
    deprecate(message) {
      // Custom deprecation logging logic
      console.log('Knex Deprecation:', message);
    },
    debug(message) {
      // Custom debug logging logic
      console.log('Knex Debug:', message);
    },
  }
});
```

--------------------------------

### Identifier Syntax Examples

Source: https://github.com/knex/documentation/blob/main/src/guide/query-builder.md

Demonstrates how to use aliases for table and column names in Knex queries, including object-based aliasing and the use of `as`.

```js
knex({ a: 'table', b: 'table' })
  .select({
    aTitle: 'a.title',
    bTitle: 'b.title'
  })
  .whereRaw('?? = ??', ['a.column_1', 'b.column_2'])
```

--------------------------------

### Simple Raw Query with Single Binding

Source: https://github.com/knex/documentation/blob/main/src/guide/raw.md

Provides an example of using `knex.raw` for simpler queries with a single binding, where the binding can be passed directly as the second argument.

```javascript
knex('users')
  .where(
    knex.raw('LOWER("login") = ?', 'knex')
  )
  .orWhere(
    knex.raw('accesslevel = ?', 1)
  )
  .orWhere(
    knex.raw('updtime = ?', '01-01-2016')
  )
```

--------------------------------

### Knexfile Configuration with Default Export

Source: https://github.com/knex/documentation/blob/main/src/guide/migrations.md

An example of a knexfile.js using a default export for configuration. This default export takes precedence over any named exports, providing a clean way to define Knex settings.

```ts
/**
 * filename: knexfile.js
 * For the knexfile you can use a default export
 **/
export default {
  client: 'sqlite3',
  connection: {
    filename: '../test.sqlite3',
  },
  migrations: {
    directory: './migrations',
  },
  seeds: {
    directory: './seeds',
  },
}
```

--------------------------------

### fetchAsString for Oracledb Data Types

Source: https://github.com/knex/documentation/blob/main/src/guide/index.md

Configures Oracledb client to return specific data types (DATE, NUMBER, CLOB) as strings. This is useful for handling large or specific data formats consistently.

```js
const knex = require('knex')({
  client: 'oracledb',
  connection: {/*...*/},
  fetchAsString: [ 'number', 'clob' ]
});
```

--------------------------------

### TypeScript Module Resolution for Knex Types

Source: https://github.com/knex/documentation/blob/main/src/guide/index.md

Provides guidance on adjusting the module path for augmenting Knex.js types when using modern TypeScript module resolution settings like `node16` or `nodenext`. This ensures the TypeScript compiler correctly resolves the type declarations.

```TypeScript
// The trailing `.js` is required by the TypeScript compiler in certain configs:
declare module 'knex/types/tables.js' { 
  interface Tables {
    // ...
  }
}
```

--------------------------------

### Augmenting Knex Tables Interface with TypeScript

Source: https://github.com/knex/documentation/blob/main/src/guide/index.md

Demonstrates how to augment the `Tables` interface in Knex.js for TypeScript to provide inferred types for database tables and their columns. This includes defining base types, insert types, and update types for enhanced auto-completion and type safety.

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

declare module 'knex/types/tables' {
  interface User {
    id: number;
    name: string;
    created_at: string;
    updated_at: string;
  }
  
  interface Tables {
    users: User;
    users_composite: Knex.CompositeTableType<
      User,
      Pick<User, 'name'> & Partial<Pick<User, 'created_at' | 'updated_at'>>,
      Partial<Omit<User, 'id'>>
    >;
  }
}
```

--------------------------------

### Knex.js Ref Usage Example

Source: https://github.com/knex/documentation/blob/main/src/guide/ref.md

Demonstrates the basic usage of `knex.ref` for referencing columns and tables within a query builder chain. It shows how to use `ref` in `knex()`, `.where()`, and `.select()` clauses.

```javascript
knex(knex.ref('Users').withSchema('TenantId'))
  .where(knex.ref('Id'), 1)
  .orWhere(knex.ref('Name'), 'Admin')
  .select(['Id', knex.ref('Name').as('Username')])
```

--------------------------------

### Basic Transaction Usage

Source: https://github.com/knex/documentation/blob/main/src/guide/transactions.md

Demonstrates how to start a transaction using `transactionProvider`, perform database operations within the transaction, and reuse the same transaction for multiple operations.

```javascript
const trxProvider = knex.transactionProvider();

const books = [
  {title: 'Canterbury Tales'},
  {title: 'Moby Dick'},
  {title: 'Hamlet'}
];

// Starts a transaction
const trx = await trxProvider();
const ids = await trx('catalogues')
  .insert({name: 'Old Books'}, 'id')
books.forEach((book) => book.catalogue_id = ids[0]);
await trx('books').insert(books);

// Reuses same transaction
const sameTrx = await trxProvider();
const ids2 = await sameTrx('catalogues')
  .insert({name: 'New Books'}, 'id')
books.forEach((book) => book.catalogue_id = ids2[0]);
await sameTrx('books').insert(books);
```

--------------------------------

### SQL Output for Ref with Alias

Source: https://github.com/knex/documentation/blob/main/src/guide/ref.md

The SQL output for the example demonstrating aliasing a referenced column using `knex.ref('Id').as('UserId')`.

```sql
knex('users')
  .select(knex.ref('Id').as('UserId'))
```

--------------------------------

### Configuring Knex Migrations with .mjs Extension

Source: https://github.com/knex/documentation/blob/main/src/guide/migrations.md

Example of a knexfile.mjs configuration specifying '.mjs' as the extension for migration files. This ensures Knex correctly identifies and loads migration files with the .mjs extension.

```ts
/** 
 * knexfile.mjs
 */
export default {
  migrations: {
    // ... client, connection,etc ....
    directory: './migrations',
    loadExtensions: ['.mjs'] // 
  }
}
```

--------------------------------

### Calling Oracle Stored Procedures with Bindout Variables

Source: https://github.com/knex/documentation/blob/main/src/faq/recipes.md

Illustrates how to execute an Oracle stored procedure using Knex.js and retrieve output parameters. It shows the setup for bind variables, including output parameters, and how to access the results.

```ts
const oracle = require('oracledb');
const bindVars = {
  input_var1: 6,
  input_var2: 7,
  output_var: {
    dir: oracle.BIND_OUT
  },
  output_message: {
    dir: oracle.BIND_OUT
  }
};

const sp = 'BEGIN MULTIPLY_STORED_PROCEDURE(:input_var1, :input_var2, :output_var, :output_message); END;';
const results = await knex.raw(sp, bindVars);
console.log(results[0]); // 42
console.log(results[1]); // 6 * 7 is the answer to life
```

--------------------------------

### Knex.js Query with Callbacks

Source: https://github.com/knex/documentation/blob/main/src/guide/interfaces.md

Provides an example of executing a Knex.js query using the .asCallback() method, which accepts a Node.js-style callback function for handling results and errors.

```javascript
knex.select('name').from('users')
  .where('id', '>', 20)
  .andWhere('id', '<', 200)
  .limit(10)
  .offset(x)
  .asCallback(function(err, rows) {
    if (err) return console.error(err);
    knex.select('id')
      .from('nicknames')
      .whereIn('nickname', _.pluck(rows, 'name'))
      .asCallback(function(err, rows) {
        if (err) return console.error(err);
        console.log(rows);
      });
  });
```

--------------------------------

### ESM Seed File Structure

Source: https://github.com/knex/documentation/blob/main/src/guide/migrations.md

Example of an ECMAScript module (ESM) seed file. It demonstrates the required named export 'seed' function, which Knex uses to execute seed logic.

```ts
// file: seed.js
/** 
 * Same as with the CommonJS modules
 * You will need to export a "seed" named function.
 * */
export function seed(knex) {
  // ... seed logic here
}
```

--------------------------------

### ESM Migration File Structure

Source: https://github.com/knex/documentation/blob/main/src/guide/migrations.md

Example of an ECMAScript module (ESM) migration file. It shows the required named exports 'up' and 'down' functions for performing database migrations.

```ts
// file: migration.js
/** 
 * Same as the CommonJS version, the miration file should export 
 * "up" and "down" named functions
 */
export function up(knex) {
  // ... migration logic here
}
export function down(knex) {
// ... migration logic here
}
```

--------------------------------

### Initialize Knexfile

Source: https://github.com/knex/documentation/blob/main/src/guide/migrations.md

Creates a sample knexfile.js or knexfile.ts, which contains database configuration settings for different environments.

```bash
knex init
knex init -x ts
```

--------------------------------

### Knex.js Error Handling with Promises

Source: https://github.com/knex/documentation/blob/main/src/guide/interfaces.md

Shows how to use the .catch() method in Knex.js to handle errors that occur during query execution. This example demonstrates catching errors from an insert operation and subsequent select.

```javascript
return knex.insert({id: 1, name: 'Test'}, 'id')
  .into('accounts')
  .catch(function(error) {
    console.error(error);
  })
  .then(function() {
    return knex.select('*')
      .from('accounts')
      .where('id', 1);
  })
  .then(function(rows) {
    console.log(rows[0]);
  })
  .catch(function(error) {
    console.error(error);
  });
```

--------------------------------

### Production Commands

Source: https://github.com/knex/documentation/blob/main/README.md

Command to build the project for production using knex.js.

```bash
yarn build # or npm run build
```

--------------------------------

### Knex CLI General Options

Source: https://github.com/knex/documentation/blob/main/src/guide/migrations.md

Lists common command-line options available for the Knex migration CLI. These options control debugging, file paths, database client, connection details, and more.

```bash
knex migrate:latest --help
--debug
--knexfile [path]
--knexpath [path]
--cwd [path]
--client [name]
--connection [address]
--migrations-table-name
--migrations-directory
--env
--esm
--help
```

--------------------------------

### Basic Knex.js Configuration

Source: https://github.com/knex/documentation/blob/main/src/guide/migrations.md

Demonstrates the basic configuration for Knex.js, specifying the database client and connection details. The connection can be a direct object or fetched asynchronously.

```js
module.exports = {
  client: 'pg',
  connection: process.env.DATABASE_URL || {
    user: 'me',
    database: 'my_app'
  }
};
```

```js
const getPassword = async () => {
  // TODO: implement me
  return 'my_pass'
}

module.exports = {
  client: 'pg',
  connection: async () => {
    const password = await getPassword()
    return { user: 'me', password }
  },
  migrations: {}
};
```

--------------------------------

### Running Knex.js Test Suite

Source: https://github.com/knex/documentation/blob/main/src/faq/index.md

This section details how to run the Knex.js test suite. It requires setting the `KNEX_TEST` environment variable to the path of your database configuration file and then executing `npm test`.

```bash
$ export KNEX_TEST='/path/to/your/knex_config.js'
$ npm test
```

--------------------------------

### postProcessResponse Hook

Source: https://github.com/knex/documentation/blob/main/src/guide/index.md

A hook to modify query results before they are returned to the user. This can be used for transformations like converting snake_case column names to camelCase. The 'queryContext' is available if configured.

```js
const knex = require('knex')({
  client: 'mysql',
  // overly simplified snake_case -> camelCase converter
  postProcessResponse: (result, queryContext) => {
    // TODO: add special case for raw results 
    // (depends on dialect)
    if (Array.isArray(result)) {
      return result.map(row => convertToCamel(row));
    } else {
      return convertToCamel(result);
    }
  }
});
```

--------------------------------

### acquireConnectionTimeout Configuration

Source: https://github.com/knex/documentation/blob/main/src/guide/index.md

Sets the timeout in milliseconds for acquiring a database connection from the pool. If a connection cannot be acquired within this time, a timeout error is thrown. This is useful for diagnosing issues where the connection pool may be exhausted.

```js
const knex = require('knex')({
  client: 'pg',
  connection: {/*...*/},
  pool: {/*...*/},
  acquireConnectionTimeout: 10000
});
```

--------------------------------

### Launching Knex with ESM on Node v10

Source: https://github.com/knex/documentation/blob/main/src/guide/migrations.md

This command demonstrates how to launch the Knex CLI on Node.js v10, enabling the use of 'mjs' or 'cjs' extensions by utilizing the '--experimental-module' flag.

```bash
node --experimental-modules ./node_modules/.bin/knex $@
```

--------------------------------

### Controlling SQL Compilation on Error

Source: https://github.com/knex/documentation/blob/main/src/guide/index.md

By default, Knex includes compiled SQL in error messages. Setting `compileSqlOnError` to `false` changes this behavior to use parameterized SQL instead, which can be useful for security or performance reasons.

```javascript
const knex = require('knex')({
  compileSqlOnError: false
});
```

--------------------------------

### Executing a Full Raw Query

Source: https://github.com/knex/documentation/blob/main/src/guide/raw.md

Demonstrates how `knex.raw` can be used to build and execute an entire SQL query, leveraging Knex's connection pooling and standard interface.

```javascript
knex.raw('select * from users where id = ?', [1])
  .then(function(resp) { /*...*/ });
```

--------------------------------

### Environment-Specific Knex.js Configuration

Source: https://github.com/knex/documentation/blob/main/src/guide/migrations.md

Shows how to configure Knex.js for different environments like development and production, allowing distinct database connection settings for each.

```js
module.exports = {
  development: {
    client: 'pg',
    connection: { user: 'me', database: 'my_app' }
  },
  production: {
    client: 'pg',
    connection: process.env.DATABASE_URL
  }
};
```

--------------------------------

### Custom Identifier Wrapping with wrapIdentifier

Source: https://github.com/knex/documentation/blob/main/src/guide/index.md

Allows overriding the default way Knex transforms identifier names, enabling custom transformations like camelCase to snake_case. The `wrapIdentifier` function receives the identifier value, the original dialect implementation, and an optional query context.

```javascript
const knex = require('knex')({
  client: 'mysql',
  // overly simplified camelCase -> snake_case converter
  wrapIdentifier: (
    value,
    origImpl,
    queryContext
  ) => origImpl(convertToSnakeCase(value))
});

// Helper function (not provided in original text, assumed for example)
function convertToSnakeCase(value) {
  return value.replace(/([A-Z])/g, '_$1').toLowerCase();
}
```

--------------------------------

### Migration API Configuration Options

Source: https://github.com/knex/documentation/blob/main/src/guide/migrations.md

Details the configuration options available for the `knex.migrate` API, including directory paths, file extensions, table names, schema names, and transaction control.

```APIDOC
knex.migrate API Configuration:

Each method takes an optional `config` object with the following properties:

- `directory`: Relative path to migration files. Can be an array of paths. (Default: `./migrations`)
- `extension`: File extension for generated migrations. (Default: `js`)
- `tableName`: Table name for migration state. (Default: `knex_migrations`)
- `schemaName`: Schema name for migration state table (DBs supporting multiple schemas). (Optional)
- `disableTransactions`: If true, migrations do not run within transactions. (Default: `false`)
- `disableMigrationsListValidation`: If true, skips validation of existing migrations. (Default: `false`)
- `sortDirsSeparately`: If true, migrations from each directory are executed sequentially. (Default: `false`)
- `loadExtensions`: Array of file extensions to treat as migrations. (Default: `['.co', '.coffee', '.eg', '.iced', '.js', '.litcoffee', '.ls', '.ts']`)
- `migrationSource`: Specify a custom migration source. (Default: filesystem)
```

--------------------------------

### Get Column Information with .columnInfo()

Source: https://github.com/knex/documentation/blob/main/src/guide/query-builder.md

The .columnInfo() method retrieves metadata about the current table's columns. It can be called without arguments to get info for all columns or with a columnName to get info for a specific column. Returned keys include defaultValue, type, maxLength, and nullable.

```js
knex('users').columnInfo().then(function(info) { /*...*/ });
```

--------------------------------

### Debugging Knex.js

Source: https://github.com/knex/documentation/blob/main/src/faq/index.md

This snippet shows how to enable debugging for Knex.js using environment variables. You can enable all debugging namespaces or specific ones like 'knex:query' and 'knex:tx'. Additionally, passing `{debug: true}` during initialization logs query calls.

```bash
export DEBUG=knex:*
export DEBUG=knex:query,knex:tx
```

```javascript
const knex = require('knex')({ debug: true, ... });
```

--------------------------------

### Knex Migration Commands

Source: https://github.com/knex/documentation/blob/main/src/guide/migrations.md

Provides an overview of the core Knex.js migration commands for managing database schema changes.

```APIDOC
knex.migrate.make(name, [config])
  Creates a new migration file with the specified name.

knex.migrate.latest([config])
  Runs all pending migrations.
  Example:
  knex.migrate.latest().then(() => knex.seed.run()).then(() => { /* migrations finished */ });

knex.migrate.rollback([config], [all])
  Rolls back the latest migration group. If 'all' is true, all applied migrations are rolled back.

knex.migrate.up([config])
  Runs the next chronological migration or a specific migration defined by config.name.

knex.migrate.down([config])
  Undoes the last run migration or a specific migration defined by config.name.

knex.migrate.currentVersion([config])
  Retrieves the current migration version. Returns 'none' if no migrations have run.

knex.migrate.list([config])
  Returns a list of completed and pending migrations.

knex.migrate.forceFreeMigrationsLock([config])
  Forcibly unlocks the migrations lock table, ensuring only one row exists.
```

--------------------------------

### Basic Raw Query with Positional Bindings

Source: https://github.com/knex/documentation/blob/main/src/guide/raw.md

Demonstrates using `knex.raw` with positional bindings (`?`) for values and (`??`) for identifiers to construct a query safely.

```javascript
knex('users')
  .select(knex.raw('count(*) as user_count, status'))
  .where(knex.raw(1))
  .orWhere(knex.raw('status <> ?', [1]))
  .groupBy('status')
```

--------------------------------

### Knex Seed Commands

Source: https://github.com/knex/documentation/blob/main/src/guide/migrations.md

Provides an overview of the primary commands for managing Knex.js database seeds.

```APIDOC
knex.seed.make(name, [config])
  Creates a new seed file with the specified name.
  If the seed directory config is an array of paths, the seed file will be generated in the latest specified.

knex.seed.run([config])
  Runs all seed files for the current environment.
  Supports custom seed sources via the 'seedSource' option in the config.
```

--------------------------------

### Knex.js Stream Retrieval

Source: https://github.com/knex/documentation/blob/main/src/guide/interfaces.md

Demonstrates how to retrieve query results as a stream using the .stream() method in Knex.js. This example shows piping the stream to a writable stream.

```javascript
// Retrieve the stream:
const stream = knex.select('*')
  .from('users')
  .stream();
stream.pipe(writableStream);
```

--------------------------------

### List Migrations

Source: https://github.com/knex/documentation/blob/main/src/guide/migrations.md

Displays a list of all completed and pending migration files.

```bash
knex migrate:list
```

--------------------------------

### Knex.js Insert with Promises

Source: https://github.com/knex/documentation/blob/main/src/guide/interfaces.md

Illustrates how to perform an insert operation within a Promise chain in Knex.js. This example shows inserting data into the 'accounts' table based on a previous query's result.

```javascript
knex.select('*')
  .from('users')
  .where({name: 'Tim'})
  .then(function(rows) {
    return knex
      .insert({user_id: rows[0].id, name: 'Test'}, 'id')
      .into('accounts');
  })
  .then(function(id) {
    console.log('Inserted Account ' + id);
  })
  .catch(function(error) { console.error(error); });
```

--------------------------------

### Create Seed File

Source: https://github.com/knex/documentation/blob/main/src/guide/migrations.md

Generates a new seed file for populating the database with test or seed data.

```bash
knex seed:make seed_name
```

--------------------------------

### Handle Query Events

Source: https://github.com/knex/documentation/blob/main/src/guide/interfaces.md

Listens for various query-related events such as 'query', 'query-error', 'query-response', and 'start'. These events provide insights into the query lifecycle, including data, errors, responses, and builder states.

```js
knex.select('*')
  .from('users')
  .on('query', function(data) {
    app.log(data);
  })
  .then(function() {
    // ...
  });

knex.select(['NonExistentColumn'])
  .from('users')
  .on('query-error', function(error, obj) {
    app.log(error);
  })
  .then(function() { /* ... */ })
  .catch(function(error) {
    // Same error object as the query-error event provides.
  });

knex.select('*')
  .from('users')
  .on('query-response', function(response, obj, builder) {
    // ...
  })
  .then(function(response) {
    // Same response as the emitted event
  })
  .catch(function(error) { });

knex.select('*')
  .from('users')
  .on('start', function(builder) {
    builder
    .where('IsPrivate', 0)
  })
  .then(function(Rows) {
    //Only contains Rows where IsPrivate = 0
  })
  .catch(function(error) { });
```

--------------------------------

### Knexfile Configuration with Default and Named Exports

Source: https://github.com/knex/documentation/blob/main/src/guide/migrations.md

Demonstrates a knexfile.js where both a default export and named exports are provided. The default export is used by Knex, showcasing export precedence.

```ts
/**
 * filename: knexfile.js
 * Let knex find the configuration by providing named exports,
 * but if exported a default, it will take precedence, and it will be used instead
 **/
const config = {
  client: 'sqlite3',
  connection: {
    filename: '../test.sqlite3',
  },
  migrations: {
    directory: './migrations',
  },
  seeds: {
    directory: './seeds',
  },
};
/** this will be used, it has precedence over named export */
export default config;
/** Named exports, will be used if you didn't provide a default export */
export const { client, connection, migrations, seeds } = config;
```

--------------------------------

### Connect to PostgreSQL-compatible databases

Source: https://github.com/knex/documentation/blob/main/src/faq/recipes.md

Configures Knex.js to connect to databases like CockroachDB that are compatible with the PostgreSQL wire protocol. It requires specifying a `version` option that matches a PostgreSQL version for protocol compatibility. Dependencies include the 'pg' client. Input is a connection configuration object.

```js
const knex = require('knex')({
  client: 'pg',
  version: '7.2',
  connection: {
    host: '127.0.0.1',
    user: 'your_database_user',
    password: 'your_database_password',
    database: 'myapp_test'
  }
});
```

--------------------------------

### Knexfile in Other Languages

Source: https://github.com/knex/documentation/blob/main/src/guide/migrations.md

Provides information on using Knexfile configurations written in other compile-to-js languages like TypeScript and CoffeeScript, highlighting necessary dependencies.

```js
// Example for TypeScript (requires typescript and ts-node)
// module.exports = {
//   client: 'pg',
//   connection: {
//     host : '127.0.0.1',
//     user : '...', 
//     password : '...'
//   }
// };
```

--------------------------------

### Raw Query with Array Bindings

Source: https://github.com/knex/documentation/blob/main/src/guide/raw.md

Explains how to handle array bindings in raw queries by manually constructing the placeholder string and spreading the array values.

```javascript
const myArray = [1,2,3]
knex.raw('select * from users where id in (' + myArray.map(_ => '?').join(',') + ')', [...myArray]);
```

```sql
select * from users where id in (?, ?, ?) /* with bindings [1,2,3] */
```

--------------------------------

### Custom Seed Source Implementation

Source: https://github.com/knex/documentation/blob/main/src/guide/migrations.md

Demonstrates how to create and use a custom seed source in Knex.js for advanced seed management.

```javascript
class MySeedSource {
  // Must return a Promise containing a list of seeds.
  // Seeds can be whatever you want, they will be passed as
  // arguments to getSeed
  getSeeds() {
    // In this example we are just returning seed names
    return Promise.resolve(['seed1'])
  }

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

// pass an instance of your seed source as knex config
knex.seed.run({ seedSource: new MySeedSource() })
```

--------------------------------

### Run Seed Files

Source: https://github.com/knex/documentation/blob/main/src/guide/migrations.md

Executes all seed files in the configured directory. Seed files should be designed to reset tables as needed before inserting data.

```bash
knex seed:run
```

--------------------------------

### Custom Migration Source Implementation

Source: https://github.com/knex/documentation/blob/main/src/guide/migrations.md

Demonstrates how to create and use a custom migration source class in Knex.js for flexible migration management.

```javascript
class MyMigrationSource {
  getMigrations() {
    // Must return a Promise containing a list of migrations.
    return Promise.resolve(['migration1'])
  }

  getMigrationName(migration) {
    return migration;
  }

  getMigration(migration) {
    switch(migration) {
      case 'migration1':
        return {
          up(knex)   { /* ... */ },
          down(knex) { /* ... */ },
        }
    }
  }
}

// Pass an instance of your migration source as knex config
knex.migrate.latest({ 
  migrationSource: new MyMigrationSource() 
})
```

--------------------------------

### Run Migrations

Source: https://github.com/knex/documentation/blob/main/src/guide/migrations.md

Applies all pending migrations to the database. You can specify a different environment using the --env flag or by setting the NODE_ENV variable.

```bash
knex migrate:latest
knex migrate:latest --env production
NODE_ENV=production knex migrate:latest
```

--------------------------------

### Add Optimizer Hints

Source: https://github.com/knex/documentation/blob/main/src/guide/query-builder.md

The `hintComment` method adds optimizer hints to the SQL query using comment-like syntax `/*+ ... */`. This is useful for MySQL and Oracle for optimizer hints, and for database proxies/routers to alter behavior. Other dialects ignore these hints as simple comments.

```js
knex('accounts')
  .where('userid', '=', 1)
  .hintComment('NO_ICP(accounts)')
```

--------------------------------

### Wrapped Raw Query for Subqueries

Source: https://github.com/knex/documentation/blob/main/src/guide/raw.md

Shows how to use the `wrap` method on a `knex.raw` object to create a subquery expression, which can then be included in a larger query.

```javascript
const subcolumn = knex.raw(
    'select avg(salary) from employee where dept_no = e.dept_no'
  )
  .wrap('(', ') avg_sal_dept');

knex.select('e.lastname', 'e.salary', subcolumn)
  .from('employee as e')
  .whereRaw('dept_no = e.dept_no')
```

--------------------------------

### Run Specific Seed Files

Source: https://github.com/knex/documentation/blob/main/src/guide/migrations.md

Executes only the specified seed files, allowing for targeted data population.

```bash
knex seed:run --specific=seed-filename.js --specific=another-seed-filename.js
```