Knex.js

https://knexjs.org/guide/
Admin
Knex.js is a lightweight SQL query builder for Node.js and browsers that supports multiple databases...
Tokens:156,916
Snippets:1,107
Trust Score:9.8
Update:6 months ago
Show doc for...
Tokens:
Raw
### Seed File Configuration Example

Source: https://knexjs.org/guide/migrations.html

Example configuration within knexfile.js specifying the directory for seed files.

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

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

### Install Knex.js Core

Source: https://knexjs.org/guide

Install the main Knex.js library using npm. This is the first step for using Knex in a Node.js project.

```bash
$ npm install knex --save
```

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

### SQL Examples for joinRaw

Source: https://knexjs.org/guide/query-builder.html

These SQL examples demonstrate the output of joinRaw with different table quoting conventions.

```sql
select * from "accounts" natural full join table1 where "id" = ?

-- ----

select * from "accounts" inner join natural full join table1 where "id" = ?
```

```sql
select * from [accounts] natural full join table1 where [id] = ?

-- ----

select * from [accounts] inner join natural full join table1 where [id] = ?
```

```sql
select * from `accounts` natural full join table1 where `id` = ?

-- ----

select * from `accounts` inner join natural full join table1 where `id` = ?
```

```sql
select * from "accounts" natural full join table1 where "id" = ?

-- ----

select * from "accounts" inner join natural full join table1 where "id" = ?
```

```sql
select * from "accounts" natural full join table1 where "id" = ?

-- ----

select * from "accounts" inner join natural full join table1 where "id" = ?
```

```sql
select * from "accounts" natural full join table1 where "id" = ?

-- ----

select * from "accounts" inner join natural full join table1 where "id" = ?
```

```sql
select * from `accounts` natural full join table1 where `id` = ?

-- ----

select * from `accounts` inner join natural full join table1 where `id` = ?
```

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

### Install Database Drivers for Knex.js

Source: https://knexjs.org/guide

After installing Knex, install the appropriate npm package for your specific database. For example, 'pg' for PostgreSQL or 'mysql' for MySQL.

```bash
$ npm install pg
```

```bash
$ npm install pg-native
```

```bash
$ npm install sqlite3
```

```bash
$ npm install better-sqlite3
```

```bash
$ npm install mysql
```

```bash
$ npm install mysql2
```

```bash
$ npm install oracledb
```

```bash
$ npm install tedious
```

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

### Install Knex.js Globally

Source: https://knexjs.org/guide/migrations.html

Installs the Knex.js CLI globally for use in your terminal.

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

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

### SQL Dialect Examples for Query Context

Source: https://knexjs.org/guide/query-builder.html

These examples show different SQL dialects that might be generated, illustrating how the query context could be applied.

```sql
select "a1"."email", "a2"."email" from "accounts" as "a1"
```

```sql
select [a1].[email], [a2].[email] from [accounts] as [a1]
```

```sql
select `a1`.`email`, `a2`.`email` from `accounts` as `a1`
```

```sql
select "a1"."email", "a2"."email" from "accounts" "a1"
```

```sql
select "a1"."email", "a2"."email" from "accounts" as "a1"
```

```sql
select "a1"."email", "a2"."email" from "accounts" as "a1"
```

```sql
select `a1`.`email`, `a2`.`email` from `accounts` as `a1`
```

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

### Create Table and Index (MySQL Alternative)

Source: https://knexjs.org/guide/schema-builder.html

Another MySQL syntax example for creating a table and its index.

```sql
create table `index_example` (`email` varchar(255))

create index `idx_email` on `index_example` (`email`)
```

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

### SQL example for FOR KEY SHARE

Source: https://knexjs.org/guide/query-builder.html

This is the SQL equivalent of using `.forKeyShare()` in Knex.js for PostgreSQL.

```sql
select * from "tableName" for key share
```

```sql
select * from "tableName" for key share
```

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

### Start Event Listener

Source: https://knexjs.org/guide/interfaces.html

The 'start' event is fired just before a query builder is compiled. Useful for modifying the builder before compilation.

```APIDOC
## Start Event

### Description
A `start` event is fired right before a query-builder is compiled. While this event can be used to alter a builders state prior to compilation it is not to be recommended. Future goals include ways of doing this in a different manner such as hooks.

### Method
`knex.on('start', function(builder) { ... })

### Parameters
- **builder** (object) - The query builder instance.

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

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

### SQL example for FOR SHARE

Source: https://knexjs.org/guide/query-builder.html

This is the SQL equivalent of using `.forShare()` in Knex.js for PostgreSQL and MySQL.

```sql
select * from "tableName" for share
```

```sql
select * from `tableName` lock in share mode
```

```sql
select * from "tableName" for share
```

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

### Initialize PostgreSQL Client

Source: https://knexjs.org/guide

Configuration examples for PostgreSQL, including search path settings and complex connection objects with SSL.

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

```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,
  },
});
```

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

### SQL Examples for onIn Clause

Source: https://knexjs.org/guide/query-builder.html

These SQL examples show the generated query when using the onIn clause with different table quoting.

```sql
select * from "users" inner join "contacts" on "users"."id" = "contacts"."id" and "contacts"."id" in (?, ?, ?, ?)
```

```sql
select * from [users] inner join [contacts] on [users].[id] = [contacts].[id] and [contacts].[id] in (?, ?, ?, ?)
```

```sql
select * from `users` inner join `contacts` on `users`.`id` = `contacts`.`id` and `contacts`.`id` in (?, ?, ?, ?)
```

```sql
select * from "users" inner join "contacts" on "users"."id" = "contacts"."id" and "contacts"."id" in (?, ?, ?, ?)
```

```sql
select * from "users" inner join "contacts" on "users"."id" = "contacts"."id" and "contacts"."id" in (?, ?, ?, ?)
```

```sql
select * from "users" inner join "contacts" on "users"."id" = "contacts"."id" and "contacts"."id" in (?, ?, ?, ?)
```

```sql
select * from `users` inner join `contacts` on `users`.`id` = `contacts`.`id` and `contacts`.`id` in (?, ?, ?, ?)
```

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

### SQL CountDistinct Syntax Examples

Source: https://knexjs.org/guide/query-builder.html

Provides SQL syntax examples for the `count(distinct ...)` aggregate function across various database dialects.

```sql
select count(distinct "active") from "users"
```

```sql
select count(distinct [active]) from [users]
```

```sql
select count(distinct `active`) from `users`
```

```sql
select count(distinct "active") from "users"
```

```sql
select count(distinct "active") from "users"
```

```sql
select count(distinct `active`) from `users`
```

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

### SQL examples for primary key operations

Source: https://knexjs.org/guide/schema-builder.html

Various SQL dialect implementations for adding columns to a table.

```sql
alter table "users" add column "first_name" varchar(255), add column "last_name" varchar(255)
```

```sql
ALTER TABLE [users] ADD [first_name] nvarchar(255), [last_name] nvarchar(255)
```

```sql
alter table `users` add `first_name` varchar(255), add `last_name` varchar(255)
```

```sql
alter table "users" add ("first_name" varchar2(255), "last_name" varchar2(255))
```

```sql
alter table "users" add column "first_name" varchar(255), add column "last_name" varchar(255)
```

```sql
alter table "users" add column "first_name" varchar(255)

alter table "users" add column "last_name" varchar(255)
```

```sql
alter table `users` add column `first_name` varchar(255)

alter table `users` add column `last_name` varchar(255)
```

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

### SQL output for references example

Source: https://knexjs.org/guide/schema-builder.html

Various SQL dialect outputs for reference constraints.

```sql
create table "references_example" ("company_id" integer)

alter table "references_example" add constraint "references_example_company_id_foreign" foreign key ("company_id") references "company" ("companyId")
```

```sql
CREATE TABLE [references_example] ([company_id] int, CONSTRAINT [references_example_company_id_foreign] FOREIGN KEY ([company_id]) REFERENCES [company] ([companyId]))
```

```sql
create table `references_example` (`company_id` int)

alter table `references_example` add constraint `references_example_company_id_foreign` foreign key (`company_id`) references `company` (`companyId`)
```

```sql
create table `references_example` (`company_id` integer, foreign key(`company_id`) references `company`(`companyId`))
```

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

### Running All Migrations and Seeds

Source: https://knexjs.org/guide/migrations.html

Example of how to run all pending migrations followed by all seed files. This is useful for setting up a database to a known state.

```javascript
knex.migrate
  .latest()
  .then(function () {
    return knex.seed.run();
  })
  .then(function () {
    // migrations are finished
  });
```

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

### SQL Output Examples

Source: https://knexjs.org/guide/query-builder.html

Various SQL dialect representations of the whereIn query patterns.

```sql
select "name" from "users" where "id" in (?, ?, ?) or "id" in (?, ?, ?)

-- ----

select "name" from "users" where "account_id" in (select "id" from "accounts")

-- ----

select "name" from "users" where "account_id" in (select "id" from "accounts")

-- ----

select "name" from "users" where ("account_id", "email") in ((?, ?), (?, ?))

-- ----

select "name" from "users" where ("account_id", "email") in (select "id", "email" from "accounts")
```

```sql
select [name] from [users] where [id] in (?, ?, ?) or [id] in (?, ?, ?)

-- ----

select [name] from [users] where [account_id] in (select [id] from [accounts])

-- ----

select [name] from [users] where [account_id] in (select [id] from [accounts])

-- ----

select [name] from [users] where ([account_id], [email]) in ((?, ?), (?, ?))

-- ----

select [name] from [users] where ([account_id], [email]) in (select [id], [email] from [accounts])
```

```sql
select `name` from `users` where `id` in (?, ?, ?) or `id` in (?, ?, ?)

-- ----

select `name` from `users` where `account_id` in (select `id` from `accounts`)

-- ----

select `name` from `users` where `account_id` in (select `id` from `accounts`)

-- ----

select `name` from `users` where (`account_id`, `email`) in ((?, ?), (?, ?))

-- ----

select `name` from `users` where (`account_id`, `email`) in (select `id`, `email` from `accounts`)
```

```sql
select `name` from `users` where `id` in (?, ?, ?) or `id` in (?, ?, ?)

-- ----

select `name` from `users` where `account_id` in (select `id` from `accounts`)

-- ----

select `name` from `users` where `account_id` in (select `id` from `accounts`)

-- ----

select `name` from `users` where (`account_id`, `email`) in ( values (?, ?), (?, ?))

-- ----

select `name` from `users` where (`account_id`, `email`) in (select `id`, `email` from `accounts`)
```

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

### SQL example for FOR NO KEY UPDATE

Source: https://knexjs.org/guide/query-builder.html

This is the SQL equivalent of using `.forNoKeyUpdate()` in Knex.js for PostgreSQL.

```sql
select * from "tableName" for no key update
```

```sql
select * from "tableName" for no key update
```

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

### SQL output for table creation

Source: https://knexjs.org/guide/schema-builder.html

Examples of SQL generated for table creation with default values.

```sql
create table "users" ("column" varchar(255) default 'value')
```

```sql
CREATE TABLE [users] ([column] nvarchar(255) CONSTRAINT [df_table_value] DEFAULT 'value')
```

```sql
create table `users` (`column` varchar(255) default 'value')
```

```sql
create table "users" ("column" varchar2(255) default 'value')
```

```sql
create table "users" ("column" varchar(255) default 'value')
```

```sql
create table "users" ("column" varchar(255) default 'value')
```

```sql
create table `users` (`column` varchar(255) default 'value')
```

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

### SQL example for NOWAIT

Source: https://knexjs.org/guide/query-builder.html

This is the SQL equivalent of using `.noWait()` in Knex.js with a lock mode.

```sql
select * from "tableName" for update nowait
```

```sql
select * from `tableName` for update nowait
```

```sql
select * from "tableName" for update nowait
```

```sql
select * from "tableName" nowait
```

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

### Create Table and Add Primary Key (PostgreSQL/MySQL)

Source: https://knexjs.org/guide/schema-builder.html

Example of creating a table and then adding a deferrable primary key constraint.

```sql
create table "users_primary" ("user_id" integer)

alter table "users_primary" add constraint "users_primary_key" primary key ("user_id") deferrable initially deferred
```

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

### SQL UnionAll Query Examples (Various Dialects)

Source: https://knexjs.org/guide/query-builder.html

Demonstrates UNION ALL syntax across different SQL dialects (standard SQL, SQL Server, MySQL).

```sql
select * from "users" where "last_name" is null union all select * from "users" where "first_name" is null
```

```sql
select * from "users" where "last_name" is null union all select * from "users" where "first_name" is null
```

```sql
select * from "users" where "last_name" is null union all select * from users where first_name is null union all select * from users where email is null
```

```sql
select * from [users] where [last_name] is null union all select * from [users] where [first_name] is null
```

```sql
select * from [users] where [last_name] is null union all select * from [users] where [first_name] is null
```

```sql
select * from [users] where [last_name] is null union all select * from users where first_name is null union all select * from users where email is null
```

```sql
select * from `users` where `last_name` is null union all select * from `users` where `first_name` is null
```

```sql
select * from `users` where `last_name` is null union all select * from `users` where `first_name` is null
```

```sql
select * from `users` where `last_name` is null union all select * from users where first_name is null union all select * from users where email is null
```

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

### SQL example for FOR UPDATE

Source: https://knexjs.org/guide/query-builder.html

This is the SQL equivalent of using `.forUpdate()` in Knex.js for PostgreSQL and MySQL.

```sql
select * from "tableName" for update
```

```sql
select * from `tableName` for update
```

```sql
select * from "tableName" for update
```

```sql
select * from "tableName" for update
```

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

### SQL Intersect Output Examples

Source: https://knexjs.org/guide/query-builder.html

Example SQL outputs generated by the intersect method across different database dialects.

```sql
select * from "users" where "last_name" is null intersect select * from "users" where "first_name" is null

-- ----

select * from "users" where "last_name" is null intersect select * from "users" where "first_name" is null

-- ----

select * from "users" where "last_name" is null intersect select * from users where first_name is null intersect select * from users where email is null
```

```sql
select * from [users] where [last_name] is null intersect select * from [users] where [first_name] is null

-- ----

select * from [users] where [last_name] is null intersect select * from [users] where [first_name] is null

-- ----

select * from [users] where [last_name] is null intersect select * from users where first_name is null intersect select * from users where email is null
```

```sql
select * from `users` where `last_name` is null intersect select * from `users` where `first_name` is null

-- ----

select * from `users` where `last_name` is null intersect select * from `users` where `first_name` is null

-- ----

select * from `users` where `last_name` is null intersect select * from users where first_name is null intersect select * from users where email is null
```

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

### SQL Count Syntax Examples

Source: https://knexjs.org/guide/query-builder.html

Illustrates the SQL syntax for the `count` aggregate function across different database dialects.

```sql
select count("age") from "users"

-- ----

select count(*) as "count" from "users"
```

```sql
select count([age]) from [users]

-- ----

select count(*) as [count] from [users]
```

```sql
select count(`age`) from `users`

-- ----

select count(*) as `count` from `users`
```

```sql
select count("age") from "users"

-- ----

select count(*) "count" from "users"
```

```sql
select count("age") from "users"

-- ----

select count(*) as "count" from "users"
```

```sql
select count(`age`) from `users`

-- ----

select count(*) as `count` from `users`
```

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

### SQL Examples for onNotIn Clause

Source: https://knexjs.org/guide/query-builder.html

These SQL examples illustrate the generated query when using the onNotIn clause with various table quoting styles.

```sql
select * from "users" inner join "contacts" on "users"."id" = "contacts"."id" and "contacts"."id" not in (?, ?, ?, ?)
```

```sql
select * from [users] inner join [contacts] on [users].[id] = [contacts].[id] and [contacts].[id] not in (?, ?, ?, ?)
```

```sql
select * from `users` inner join `contacts` on `users`.`id` = `contacts`.`id` and `contacts`.`id` not in (?, ?, ?, ?)
```

```sql
select * from "users" inner join "contacts" on "users"."id" = "contacts"."id" and "contacts"."id" not in (?, ?, ?, ?)
```

```sql
select * from "users" inner join "contacts" on "users"."id" = "contacts"."id" and "contacts"."id" not in (?, ?, ?, ?)
```

```sql
select * from "users" inner join "contacts" on "users"."id" = "contacts"."id" and "contacts"."id" not in (?, ?, ?, ?)
```

```sql
select * from `users` inner join `contacts` on `users`.`id` = `contacts`.`id` and `contacts`.`id` not in (?, ?, ?, ?)
```

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

### Wrapped Query Example (PostgreSQL/MySQL)

Source: https://knexjs.org/guide/raw.html

Example of a raw query wrapped for use as a subquery alias, typically seen in PostgreSQL or MySQL.

```sql
(select avg(salary) from employee where dept_no = e.dept_no) avg_sal_dept
```

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

### SQL for Adding Columns

Source: https://knexjs.org/guide/schema-builder.html

Examples of SQL syntax for adding multiple columns to a table across different database systems.

```sql
alter table "users" add column "first_name" varchar(255), add column "last_name" varchar(255)
```

```sql
ALTER TABLE [users] ADD [first_name] nvarchar(255), [last_name] nvarchar(255)
```

```sql
alter table `users` add `first_name` varchar(255), add `last_name` varchar(255)
```

```sql
alter table "users" add ("first_name" varchar2(255), "last_name" varchar2(255))
```

```sql
alter table "users" add column "first_name" varchar(255)

alter table "users" add column "last_name" varchar(255)
```

```sql
alter table `users` add column `first_name` varchar(255)

alter table `users` add column `last_name` varchar(255)
```

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

### SQL output for in-table example

Source: https://knexjs.org/guide/schema-builder.html

Various SQL dialect outputs for in-table primary/foreign key constraints.

```sql
create table "in_table_example" ("company_id" integer)

alter table "in_table_example" add constraint "in_table_example_company_id_foreign" foreign key ("company_id") references "company" ("companyId")
```

```sql
CREATE TABLE [in_table_example] ([company_id] int, CONSTRAINT [in_table_example_company_id_foreign] FOREIGN KEY ([company_id]) REFERENCES [company] ([companyId]))
```

```sql
create table `in_table_example` (`company_id` int)

alter table `in_table_example` add constraint `in_table_example_company_id_foreign` foreign key ("company_id") references "company" ("companyId")
```

```sql
create table `in_table_example` (`company_id` integer, foreign key(`company_id`) references `company`(`companyId`))
```

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

### SQL Equivalent of Basic Join

Source: https://knexjs.org/guide/query-builder.html

Illustrates the standard SQL INNER JOIN syntax generated by the Knex.js basic join examples.

```sql
select "users"."id", "contacts"."phone" from "users" inner join "contacts" on "users"."id" = "contacts"."user_id"
```

```sql
select [users].[id], [contacts].[phone] from [users] inner join [contacts] on [users].[id] = [contacts].[user_id]
```

```sql
select `users`.`id`, `contacts`.`phone` from `users` inner join `contacts` on `users`.`id` = `contacts`.`user_id`
```

```sql
select "users"."id", "contacts"."phone" from "users" inner join "contacts" on "users"."id" = "contacts"."user_id"
```

```sql
select "users"."id", "contacts"."phone" from "users" inner join "contacts" on "users"."id" = "contacts"."user_id"
```

```sql
select "users"."id", "contacts"."phone" from "users" inner join "contacts" on "users"."id" = "contacts"."user_id"
```

```sql
select `users`.`id`, `contacts`.`phone` from `users` inner join `contacts` on `users`.`id` = `contacts`.`user_id`
```

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

### SQL count output examples

Source: https://knexjs.org/guide/query-builder.html

SQL representations of the count operations across different database dialects.

```sql
select count("active") from "users"

-- ----

select count("active") as "a" from "users"

-- ----

select count("active") as "a" from "users"

-- ----

select count("active") as "a" from "users"

-- ----

select count("active") as "a", count("valid") as "v" from "users"

-- ----

select count("id") from "users"

-- ----

select count("id", "active") as "count" from "users"

-- ----

select count("active") from "users"
```

```sql
select count([active]) from [users]

-- ----

select count([active]) as [a] from [users]

-- ----

select count([active]) as [a] from [users]

-- ----

select count([active]) as [a] from [users]

-- ----

select count([active]) as [a], count([valid]) as [v] from [users]

-- ----

select count([id]) from [users]

-- ----

select count([id], [active]) as [count] from [users]

-- ----

select count([active]) from [users]
```

```sql
select count(`active`) from `users`

-- ----

select count(`active`) as `a` from `users`

-- ----

select count(`active`) as `a` from `users`

-- ----

select count(`active`) as `a` from `users`

-- ----

select count(`active`) as `a`, count(`valid`) as `v` from `users`

-- ----

select count(`id`) from `users`

-- ----

select count(`id`, `active`) as `count` from `users`

-- ----

select count(`active`) from `users`
```

```sql
select count("active") from "users"

-- ----

select count("active") "a" from "users"

-- ----

select count("active") "a" from "users"

-- ----

select count("active") "a" from "users"

-- ----

select count("active") "a", count("valid") "v" from "users"

-- ----

select count("id") from "users"

-- ----

select count("id", "active") "count" from "users"

-- ----

select count("active") from "users"
```

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

### SQL Example: WITH NOT MATERIALIZED Clause

Source: https://knexjs.org/guide/query-builder.html

Illustrates the SQL syntax for 'WITH NOT MATERIALIZED' clauses across different dialects.

```sql
with "with_alias" as not materialized (select * from "books" where "author" = ?) select * from "with_alias"
```

```sql
with "with_alias"("title") as not materialized (select "title" from "books" where "author" = ?) select * from "with_alias"
```

```sql
with "with_alias" as not materialized (select * from "books" where "author" = ?) select * from "with_alias"
```

```sql
with `with_alias` as not materialized (select * from "books" where "author" = ?) select * from `with_alias`
```

```sql
with `with_alias`(`title`) as not materialized (select "title" from "books" where "author" = ?) select * from `with_alias`
```

```sql
with `with_alias` as not materialized (select * from `books` where `author` = ?) select * from `with_alias`
```

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

### Create except queries with Knex.js

Source: https://knexjs.org/guide/query-builder.html

Examples of using the except method with callbacks, arrays of builders, and raw statements.

```javascript
knex
  .select('*')
  .from('users')
  .whereNull('last_name')
  .except(function () {
    this.select('*').from('users').whereNull('first_name');
  });
knex
  .select('*')
  .from('users')
  .whereNull('last_name')
  .except([knex.select('*').from('users').whereNull('first_name')]);
knex
  .select('*')
  .from('users')
  .whereNull('last_name')
  .except(
    knex.raw('select * from users where first_name is null'),
    knex.raw('select * from users where email is null')
  );
```

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

### SQL Output Examples

Source: https://knexjs.org/guide/schema-builder.html

Various SQL outputs generated by the schema builder for different database dialects.

```sql
alter table "users" add constraint "fk_fkey_company" foreign key ("companyId") references "company" ("companyId")
```

```sql
ALTER TABLE [users] ADD CONSTRAINT [fk_fkey_company] FOREIGN KEY ([companyId]) REFERENCES [company] ([companyId])
```

```sql
alter table `users` add constraint `fk_fkey_company` foreign key (`companyId`) references `company` (`companyId`)
```

```sql
alter table "users" add constraint "fk_fkey_company" foreign key ("companyId") references "company" ("companyId")
```

```sql
alter table "users" add constraint "fk_fkey_company" foreign key ("companyId") references "company" ("companyId")
```

```sql
alter table "users" add constraint "fk_fkey_company" foreign key ("companyId") references "company" ("companyId")
```

```sql
CREATE TABLE `_knex_temp_alter569` (`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL, `first_name` varchar(255), `last_name` varchar(255), `email` varchar(255), `age` integer, `companyId` integer, `user_id` integer, FOREIGN KEY (`companyId`) REFERENCES `company` (`companyId`), CONSTRAINT `fk_fkey_company` FOREIGN KEY (`companyId`) REFERENCES `company` (`companyId`))

INSERT INTO "_knex_temp_alter569" SELECT * FROM "users";

DROP TABLE "users"

ALTER TABLE "_knex_temp_alter569" RENAME TO "users"
```

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

### Retrieve minimum values using Knex.js

Source: https://knexjs.org/guide/query-builder.html

Examples of using the min() method with various column specifications and aliasing options.

```javascript
knex('users').min('age');
knex('users').min('age', { as: 'a' });
knex('users').min('age as a');
knex('users').min({ a: 'age' });
knex('users').min({ a: 'age', b: 'experience' });
knex('users').min('age', 'logins');
knex('users').min({ min: ['age', 'logins'] });
knex('users').min(knex.raw('??', ['age']));
```

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

### SQL Rank with Raw Syntax (Brackets)

Source: https://knexjs.org/guide/query-builder.html

Example of the generated SQL for the rank() function using raw syntax with brackets.

```sql
select *, rank() over (order by [email]) as alias_name from [users]
```

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

### SQL Right Join Examples

Source: https://knexjs.org/guide/query-builder.html

These are the SQL equivalents for performing a right join with both simple and complex conditions, demonstrating standard SQL syntax.

```sql
select * from "users" right join "accounts" on "users"."id" = "accounts"."user_id"

-- ----

select * from "users" right join "accounts" on "accounts"."id" = "users"."account_id" or "accounts"."owner_id" = "users"."id"
```

```sql
select * from [users] right join [accounts] on [users].[id] = [accounts].[user_id]

-- ----

select * from [users] right join [accounts] on [accounts].[id] = [users].[account_id] or [accounts].[owner_id] = [users].[id]
```

```sql
select * from `users` right join `accounts` on `users`.`id` = `accounts`.`user_id`

-- ----

select * from `users` right join `accounts` on `accounts`.`id` = `users`.`account_id` or `accounts`.`owner_id` = `users`.`id`
```

```sql
select * from "users" right join "accounts" on "users"."id" = "accounts"."user_id"

-- ----

select * from "users" right join "accounts" on "accounts"."id" = "users"."account_id" or "accounts"."owner_id" = "users"."id"
```

```sql
select * from "users" right join "accounts" on "users"."id" = "accounts"."user_id"

-- ----

select * from "users" right join "accounts" on "accounts"."id" = "users"."account_id" or "accounts"."owner_id" = "users"."id"
```

```sql
select * from "users" right join "accounts" on "users"."id" = "accounts"."user_id"

-- ----

select * from "users" right join "accounts" on "accounts"."id" = "users"."account_id" or "accounts"."owner_id" = "users"."id"
```

```sql
select * from `users` right join `accounts` on `users`.`id` = `accounts`.`user_id`

-- ----

select * from `users` right join `accounts` on `accounts`.`id` = `users`.`account_id` or `accounts`.`owner_id` = `users`.`id`
```

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

### SQL output for ON DELETE CASCADE

Source: https://knexjs.org/guide/schema-builder.html

Examples of SQL generated for foreign key constraints with ON DELETE CASCADE behavior.

```sql
create table "on_delete_example" ("company_id" integer)

alter table "on_delete_example" add constraint "on_delete_example_company_id_foreign" foreign key ("company_id") references "company" ("companyId") on delete CASCADE
```

```sql
CREATE TABLE [on_delete_example] ([company_id] int, CONSTRAINT [on_delete_example_company_id_foreign] FOREIGN KEY ([company_id]) REFERENCES [company] ([companyId]) ON DELETE CASCADE)
```

```sql
create table `on_delete_example` (`company_id` int)

alter table `on_delete_example` add constraint `on_delete_example_company_id_foreign` foreign key (`company_id`) references `company` (`companyId`) on delete CASCADE
```

```sql
create table "on_delete_example" ("company_id" integer)

alter table "on_delete_example" add constraint "on_delete_example_company_id_foreign" foreign key ("company_id") references "company" ("companyId") on delete CASCADE
```

```sql
create table "on_delete_example" ("company_id" integer)

alter table "on_delete_example" add constraint "on_delete_example_company_id_foreign" foreign key ("company_id") references "company" ("companyId") on delete CASCADE
```

```sql
create table "on_delete_example" ("company_id" integer)

alter table "on_delete_example" add constraint "on_delete_example_company_id_foreign" foreign key ("company_id") references "company" ("companyId") on delete CASCADE
```

```sql
create table `on_delete_example` (`company_id` integer, foreign key(`company_id`) references `company`(`companyId`) on delete CASCADE)
```

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

### SQL Rank with Raw Syntax (Backticks)

Source: https://knexjs.org/guide/query-builder.html

Example of the generated SQL for the rank() function using raw syntax with backticks.

```sql
select *, rank() over (order by `email`) as alias_name from `users`
```

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

### SQL Rank with Array Syntax (Brackets)

Source: https://knexjs.org/guide/query-builder.html

Example of the generated SQL for the rank() function with array arguments using brackets.

```sql
select *, rank() over (partition by [firstName], [lastName] order by [email], [address]) as alias_name from [users]
```

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

### Generated SQL for WITH Clause (Standard)

Source: https://knexjs.org/guide/query-builder.html

Example SQL generated by Knex.js for queries using the WITH clause with standard SQL syntax.

```sql
with "with_alias" as (select * from "books" where "author" = ?) select * from "with_alias"
```

```sql
-- ----

with "with_alias"("title") as (select "title" from "books" where "author" = ?) select * from "with_alias"
```

```sql
-- ----

with "with_alias" as (select * from "books" where "author" = ?) select * from "with_alias"
```

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

### SQL Rank with Array Syntax (Backticks)

Source: https://knexjs.org/guide/query-builder.html

Example of the generated SQL for the rank() function with array arguments using backticks.

```sql
select *, rank() over (partition by `firstName`, `lastName` order by `email`, `address`) as alias_name from `users`
```

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

### Knex.js .where() and .orWhere() Methods

Source: https://knexjs.org/guide/query-builder.html

Examples of using .where() and .orWhere() with various syntaxes including objects, key-value pairs, subqueries, and grouped conditions.

```APIDOC
## .where() and .orWhere()

### Description
The .where() method is used to add a WHERE clause to the query. It supports object syntax, key-value pairs, functions for nested logic, and custom operators.

### Usage Examples

#### Object Syntax
```javascript
knex('users')
  .where({
    first_name: 'Test',
    last_name: 'User',
  })
  .select('id');
```

#### Key-Value Pair
```javascript
knex('users').where('id', 1);
```

#### Operator Usage
```javascript
knex('users').where('columnName', 'like', '%rowlikeme%');
```

#### Function Grouping
```javascript
knex('users')
  .where((builder) =>
    builder.whereIn('id', [1, 11, 15]).whereNotIn('id', [17, 19])
  )
  .andWhere(function () {
    this.where('id', '>', 10);
  });
```

#### Subquery Usage
```javascript
const subquery = knex('users')
  .where('votes', '>', 100)
  .andWhere('status', 'active')
  .orWhere('name', 'John')
  .select('id');
knex('accounts').where('id', 'in', subquery);
```
```

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

### Union Query Example (SQL Server)

Source: https://knexjs.org/guide/query-builder.html

This SQL snippet shows a union query for SQL Server, using square brackets for table and column names.

```sql
select * from [users] where [last_name] is null union select * from [users] where [first_name] is null
```

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

### SQL Rank with Raw Syntax (Double Quotes)

Source: https://knexjs.org/guide/query-builder.html

Example of the generated SQL for the rank() function using raw syntax with double quotes.

```sql
select *, rank() over (order by "email") as alias_name from "users"
```

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

### Initialize Knexfile

Source: https://knexjs.org/guide/migrations.html

Creates a new knexfile.js for database configuration. Use -x ts for TypeScript.

```bash
$ knex init
```

```bash
# or for .ts

$ knex init -x ts
```

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

### SQL Query Variants for Inference Examples

Source: https://knexjs.org/guide/query-builder.html

Various SQL dialect outputs corresponding to the TypeScript inference examples.

```sql
select "id" from "users"

-- ----

select "users"."id" from "users"

-- ----

select "users"."id" from "users"

-- ----

select "id" as "identifier" from "users"

-- ----

select "id" as "identifier" from "users"
```

```sql
select [id] from [users]

-- ----

select [users].[id] from [users]

-- ----

select [users].[id] from [users]

-- ----

select [id] as [identifier] from [users]

-- ----

select [id] as [identifier] from [users]
```

```sql
select `id` from `users`

-- ----

select `users`.`id` from `users`

-- ----

select `users`.`id` from `users`

-- ----

select `id` as `identifier` from `users`

-- ----

select `id` as `identifier` from `users`
```

```sql
select "id" from "users"

-- ----

select "users"."id" from "users"

-- ----

select "users"."id" from "users"

-- ----

select "id" "identifier" from "users"

-- ----

select "id" as "identifier" from "users"
```

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

### Union Query Example (PostgreSQL/MySQL)

Source: https://knexjs.org/guide/query-builder.html

This SQL snippet demonstrates a basic union query combining results from the 'users' table where 'last_name' is null and 'first_name' is null.

```sql
select * from "users" where "last_name" is null union select * from "users" where "first_name" is null
```

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

### Basic Join in Knex.js

Source: https://knexjs.org/guide/query-builder.html

Use the `.join()` method to join tables based on matching column IDs. This example demonstrates a basic join operation.

```javascript
knex
  .from('users')
  .join('accounts', { 'accounts.id': 'users.account_id' });
```

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

### Instantiate Knex with Different Clients

Source: https://knexjs.org/guide

Demonstrates how to instantiate Knex.js with different SQL clients (e.g., PostgreSQL) and shows the difference in query string generation for the `returning` clause.

```javascript
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 *"
```

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

### Initialize MySQL Client

Source: https://knexjs.org/guide

Basic configuration for a MySQL database connection.

```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',
  },
});
```

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

### SQL Examples for onNull Clause

Source: https://knexjs.org/guide/query-builder.html

These SQL examples demonstrate the generated query when using the onNull clause with different table quoting.

```sql
select * from "users" inner join "contacts" on "users"."id" = "contacts"."id" and "contacts"."email" is null
```

```sql
select * from [users] inner join [contacts] on [users].[id] = [contacts].[id] and [contacts].[email] is null
```

```sql
select * from `users` inner join `contacts` on `users`.`id` = `contacts`.`id` and `contacts`.`email` is null
```

```sql
select * from "users" inner join "contacts" on "users"."id" = "contacts"."id" and "contacts"."email" is null
```

```sql
select * from "users" inner join "contacts" on "users"."id" = "contacts"."id" and "contacts"."email" is null
```

```sql
select * from "users" inner join "contacts" on "users"."id" = "contacts"."id" and "contacts"."email" is null
```

```sql
select * from `users` inner join `contacts` on `users`.`id` = `contacts`.`id` and `contacts`.`email` is null
```

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

### Intercept Query Compilation with 'start' Event

Source: https://knexjs.org/guide/interfaces.html

The 'start' event is emitted just before a query builder is compiled into an SQL query. While it can modify the builder, using hooks is recommended for future changes.

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

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

### Initialize SQLite3 and Better-SQLite3 Clients

Source: https://knexjs.org/guide

Configuration patterns for SQLite, including file-based, in-memory, and driver-specific options.

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

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

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

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

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

```javascript
const knex = require('knex')({
  client: 'better-sqlite3',
  connection: {
    filename: './mydb.sqlite',
    options: {
      safeIntegers: true, // Returns bigint for large integers
    },
  },
});
```
Tokens:
Raw