Knex.js

https://knexjs.org
Admin
Knex.js is a flexible and portable SQL query builder for Node.js, supporting multiple databases like...
Tokens:150,260
Snippets:1,443
Trust Score:9.8
Update:5 months ago
Show doc for...
Tokens:
Raw
### Install Knex.js Core

Source: https://knexjs.org/guide

Install the main Knex.js library using npm. This is the first step for both Node.js and browser environments.

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

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

### Seed File Configuration Example

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

Example configuration in knexfile.js showing how to specify the directory for seed files. If not defined, seeds are created in './seeds'.

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

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

### Install Knex.js Migration CLI Globally

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

Install the Knex.js migration command-line interface globally to manage migrations from any project directory.

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

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

### SQL WITH clause examples (standard)

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

Examples of SQL WITH clauses generated by Knex.js.

```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 Primary Key Constraint Examples

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

Examples of SQL syntax for creating primary 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)

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)

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)

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 Unique Constraint Examples

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

Examples of SQL syntax for creating unique constraints with 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] 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)
```

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

### Create Table (SQL - Generic)

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

Basic example of creating a table using generic SQL syntax.

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

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

### SQL for Specifying Schema

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

These examples show the SQL generated when using `.withSchema()` for different database dialects.

```sql
select * from "public"."users"
```

```sql
select * from [public].[users]
```

```sql
select * from `public`.`users`
```

```sql
select * from "public"."users"
```

```sql
select * from "public"."users"
```

```sql
select * from "public"."users"
```

```sql
select * from `public`.`users`
```

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

### Install Database Drivers for Node.js

Source: https://knexjs.org/guide

Install specific database drivers based on your chosen database. These are required for Knex to connect to your database. For CockroachDB or Redshift, use 'pg'. For MariaDB, use 'mysql'.

```bash
# 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
```

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

### Create Table and Index (SQL - Oracle)

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

Example of creating a table and an index using SQL syntax common in Oracle.

```sql
create table "index_example" ("email" varchar2(255))

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

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

### Create Table and Index (SQL - MySQL)

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

Example of creating a table and an index using SQL syntax common in MySQL.

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

alter table `index_example` add index `idx_email`(`email`)
```

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

### Create Table and Index (SQL - MySQL)

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

Example of creating a table and an index using SQL syntax common in MySQL.

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

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

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

### SQL Example: Basic With Materialized

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

This SQL demonstrates the basic syntax for a 'WITH' materialized clause using raw SQL.

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

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

### Create Table and Index (SQL - PostgreSQL/MySQL)

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

Example of creating a table and an index using SQL syntax common in PostgreSQL and MySQL.

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

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

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

### Define Index - SQL (PostgreSQL)

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

Example of creating a table with a foreign key constraint in PostgreSQL.

```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 Example: Returning Multiple Columns (Oracle)

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

This SQL shows returning multiple columns using `returning into` for Oracle.

```sql
insert into "books" ("title") values (?) returning "id","title" into ?,?
```

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

### Rank SQL Syntax with Arrays (Brackets)

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

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

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

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

### Define Index - SQL (MySQL)

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

Example of creating a table with a foreign key constraint in MySQL.

```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`)
```

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

### Rank SQL Syntax (Brackets)

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

Example of the generated SQL for .rank() using brackets for identifiers.

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

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

### SQL for onIn clause

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

These examples show the SQL generated for the onIn clause across different SQL dialects.

```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 (?, ?, ?, ?)
```

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

### Add Columns to Table (SQL Examples)

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

SQL variations 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), 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)
```

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

### Rank SQL Syntax with Raw Query (Brackets)

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

Example of the generated SQL for .rank() with a raw query using brackets.

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

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

### SQL equivalent for checkNotIn

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

These examples show the SQL syntax for creating a table with a `checkNotIn` constraint for various database systems.

```sql
create table "product" ("phone" varchar(255) check ("phone" ~ '[0-9]{8}'), "phone" varchar(255) check ("phone" ~ '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'))
```

```sql
CREATE TABLE [product] ([phone] nvarchar(255) check ([phone] LIKE '%[0-9]{8}%'), [phone] nvarchar(255) check ([phone] LIKE '%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%'))
```

```sql
create table `product` (`phone` varchar(255) check (`phone` REGEXP '[0-9]{8}'), `phone` varchar(255) check (`phone` REGEXP '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'))
```

```sql
create table "product" ("phone" varchar2(255) check (REGEXP_LIKE("phone",'[0-9]{8}')), "phone" varchar2(255) check (REGEXP_LIKE("phone",'[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')))
```

```sql
create table "product" ("phone" varchar(255) check ("phone" ~ '[0-9]{8}'), "phone" varchar(255) check ("phone" ~ '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'))
```

```sql
create table "product" ("phone" varchar(255) check ("phone" ~ '[0-9]{8}'), "phone" varchar(255) check ("phone" ~ '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'))
```

```sql
create table `product` (`phone` varchar(255) check (`phone` REGEXP '[0-9]{8}'), `phone` varchar(255) check (`phone` REGEXP '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'))
```

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

### SQL Example: With Materialized with Columns

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

This SQL shows how to define a 'WITH' materialized clause with a specified list of columns.

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

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

### Check if Table Exists - SQL Examples

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

These SQL queries demonstrate how different database systems check for table existence.

```sql
select * from information_schema.tables where table_name = 'users' and table_schema = current_schema()
```

```sql
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'users'
```

```sql
select * from information_schema.tables where table_name = 'users' and table_schema = database()
```

```sql
select TABLE_NAME from USER_TABLES where TABLE_NAME = 'users'
```

```sql
select * from sqlite_master where type = 'table' and name = 'users'
```

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

### Rank SQL Syntax with Arrays (Backticks)

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

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

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

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

### SQL Variations for Query Context

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

These examples show different SQL dialects generated based on query context or client configuration.

```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`
```

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

### SQL Foreign Key Reference (PostgreSQL/MySQL)

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

Example of creating a table with a foreign key constraint using SQL syntax.

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

alter table "on_update_example" add constraint "on_update_example_company_id_foreign" foreign key ("company_id") references "company" ("companyId") on update CASCADE
```

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

### Define Index - SQL (SQL Server)

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

Example of creating a table with a foreign key constraint in SQL Server.

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

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

### Initialize Knex with Different Clients

Source: https://knexjs.org/guide

Demonstrates initializing Knex with different SQL clients (e.g., 'pg') and shows how to generate SQL strings for specific dialects.

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

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

### SQL Example: Returning Multiple Columns (MySQL)

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

This SQL shows returning multiple columns using the `returning` clause for MySQL.

```sql
insert into `books` (`title`) values (?) returning `id`, `title`
```

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

### SQL Intersect Query Example (Standard)

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

Example of an SQL INTERSECT query generated by Knex.js.

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

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

```sql
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
```

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

### Initialize Knexfile

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

Create a knexfile.js or knexfile.ts to configure database connections and migration settings for your project.

```bash
knex init

# or for .ts

knex init -x ts
```

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

### Rank SQL Syntax with Raw Query (Backticks)

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

Example of the generated SQL for .rank() with a raw query using backticks.

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

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

### SQL Intersect Query Example (SQLite)

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

Example of an SQL INTERSECT query generated by Knex.js for SQLite.

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

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

```sql
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
```

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

### Piping a Stream Directly

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

This is a concise example of piping a query stream directly to a writable stream using the .pipe() method.

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

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

### SQL WITH clause examples (backtick identifiers)

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

Examples of SQL WITH clauses using backtick identifiers, common in MySQL.

```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 WITH clause examples (bracketed identifiers)

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

Examples of SQL WITH clauses using bracketed identifiers, common in some SQL dialects.

```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]
```

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

### Date Column SQL Equivalents

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

Examples of SQL syntax for creating a date column across different database systems.

```sql
create table "date_example" ("birthdate" date)
```

```sql
CREATE TABLE [date_example] ([birthdate] date)
```

```sql
create table `date_example` (`birthdate` date)
```

```sql
create table "date_example" ("birthdate" date)
```

```sql
create table "date_example" ("birthdate" date)
```

```sql
create table "date_example" ("birthdate" date)
```

```sql
create table `date_example` (`birthdate` date)
```

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

### Create Table and Index (SQL - SQL Server)

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

Example of creating a table and an index using SQL syntax specific to SQL Server.

```sql
CREATE TABLE [index_example] ([email] nvarchar(255))

CREATE INDEX [idx_email] ON [index_example] ([email])
```

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

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

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

Example of the generated SQL for .rank() with array arguments using double quotes.

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

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

### Get Minimum Value of a Column

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

Use the .min() function to get the minimum value of a single column. You can specify an alias using 'as' or by providing an object.

```javascript
knex('users').min('age');
```

```javascript
knex('users').min('age', { as: 'a' });
```

```javascript
knex('users').min('age as a');
```

```javascript
knex('users').min({ a: 'age' });
```

```javascript
knex('users').min({ a: 'age', b: 'experience' });
```

```javascript
knex('users').min('age', 'logins');
```

```javascript
knex('users').min({ min: ['age', 'logins'] });
```

```javascript
knex('users').min(knex.raw('??', ['age']));
```

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

### Get Current Timestamp with Precision

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

Use knex.fn.now() to get the current timestamp. An optional precision argument can be provided to specify the number of decimal places for seconds.

```javascript
table.datetime('some_time', { precision: 6 }).defaultTo(knex.fn.now(6));
```

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

### Running All Migrations and Seeds

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

Shows how to execute all pending database 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 equivalent for checkBetween

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

These examples demonstrate how to drop existing constraints related to price checks before applying new ones, shown for various SQL dialects.

```sql
alter table "product" drop constraint price_check, drop constraint price_proportion_check
```

```sql
alter table [product] drop constraint price_check, drop constraint price_proportion_check
```

```sql
alter table `product` drop constraint price_check, drop constraint price_proportion_check
```

```sql
alter table "product" drop constraint price_check, drop constraint price_proportion_check
```

```sql
alter table "product" drop constraint price_check, drop constraint price_proportion_check
```

```sql
alter table "product" drop constraint price_check, drop constraint price_proportion_check
```

```sql
alter table `product` drop constraint price_check, drop constraint price_proportion_check
```

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

### Streams

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

Streams are a powerful way of piping data through as it comes in, rather than all at once. You can read more about streams here at substack's stream handbook. See the following for example uses of stream & pipe. If you wish to use streams with PostgreSQL, you must also install the pg-query-stream module. If you wish to use streams with the `pgnative` dialect, please be aware that the results will not be streamed as they are received, but rather streamed after the entire result set has returned. On an HTTP server, make sure to manually close your streams if a request is aborted.

```APIDOC
## Streams

Streams are a powerful way of piping data through as it comes in, rather than all at once. You can read more about streams here at substack's stream handbook. See the following for example uses of stream & pipe. If you wish to use streams with PostgreSQL, you must also install the pg-query-stream module. If you wish to use streams with the `pgnative` dialect, please be aware that the results will not be streamed as they are received, but rather streamed after the entire result set has returned. On an HTTP server, make sure to manually close your streams if a request is aborted.
```

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

### Using Streams with Options

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

This example shows how to configure the stream behavior by passing options to the .stream() method, specifically setting the 'highWaterMark'.

```javascript
// With options:
const stream = knex.select('*').from('users').stream({ highWaterMark: 5 });
stream.pipe(writableStream);
```

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

### SQL ALTER TABLE examples for various databases

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

These examples demonstrate ALTER TABLE statements for different SQL dialects, including PostgreSQL, SQL Server, MySQL, and Oracle.

```sql
alter table "user" add column "id" serial primary key

SET enable_experimental_alter_column_type_general = true

alter table "user" alter column "username" drop default

alter table "user" alter column "username" drop not null

alter table "user" alter column "username" type varchar(35) using ("username"::varchar(35))

alter table "user" alter column "username" set not null

SET enable_experimental_alter_column_type_general = true

alter table "user" alter column "age" drop default

alter table "user" alter column "age" drop not null

alter table "user" alter column "age" type integer using ("age"::integer)

SET enable_experimental_alter_column_type_general = true

alter table "user" alter column "age" drop default

alter table "user" alter column "age" type integer using ("age"::integer)

SET enable_experimental_alter_column_type_general = true

alter table "user" alter column "age" drop default

alter table "user" alter column "age" drop not null
```

```sql
ALTER TABLE [user] ADD [id] int identity(1,1) not null primary key

ALTER TABLE [user] ALTER COLUMN [username] nvarchar(35) not null

ALTER TABLE [user] ALTER COLUMN [age] int

ALTER TABLE [user] ALTER COLUMN [age] int

ALTER TABLE [user] ALTER COLUMN [age] int
```

```sql
alter table `user` add `id` int unsigned not null auto_increment primary key

alter table `user` modify `username` varchar(35) not null, modify `age` int, modify `age` int, modify `age` int
```

```sql
alter table "user" add "id" integer not null primary key

alter table "user" modify ("username" varchar2(35) not null, "age" integer, "age" integer, "age" integer)

DECLARE PK_NAME VARCHAR(200);
BEGIN  
  EXECUTE IMMEDIATE ('CREATE SEQUENCE "user_seq"');  
  SELECT cols.column_name INTO PK_NAME  
  FROM all_constraints cons, all_cons_columns cols  
  WHERE cons.constraint_type = 'P'  
  AND cons.constraint_name = cols.constraint_name  
  AND cons.owner = cols.owner  
  AND cols.table_name = 'user';  
  execute immediate ('create or replace trigger "user_autoinc_trg"  
  BEFORE INSERT on "user"  
  for each row  
  declare  
    checking number := 1;
  begin    
    if (:new." || PK_NAME || " is null) then      
      while checking >= 1 loop        
        select "user_seq".nextval into :new." || PK_NAME || " from dual;
        select count("" || PK_NAME || "") into checking from "user"
        where "" || PK_NAME || "" = :new." || PK_NAME || ";
      end loop;
    end if;
  end;'); 
END;
```

```sql
alter table "user" add column "id" serial primary key

alter table "user" alter column "username" drop default

alter table "user" alter column "username" drop not null

alter table "user" alter column "username" type varchar(35) using ("username"::varchar(35))

alter table "user" alter column "username" set not null

alter table "user" alter column "age" drop default

alter table "user" alter column "age" drop not null

alter table "user" alter column "age" type integer using ("age"::integer)

alter table "user" alter column "age" drop default

alter table "user" alter column "age" type integer using ("age"::integer)

alter table "user" alter column "age" drop default

alter table "user" alter column "age" drop not null
```

```sql
alter table "user" add column "id" integer identity(1,1) primary key not null

alter table "user" alter column "username" drop default

alter table "user" alter column "username" drop not null

alter table "user" alter column "username" type varchar(35) using ("username"::varchar(35))

alter table "user" alter column "username" set not null

alter table "user" alter column "age" drop default

alter table "user" alter column "age" drop not null

alter table "user" alter column "age" type integer using ("age"::integer)

alter table "user" alter column "age" drop default

alter table "user" alter column "age" type integer using ("age"::integer)

alter table "user" alter column "age" drop default

alter table "user" alter column "age" drop not null
```

```sql
alter table `user` add column `id` integer not null primary key autoincrement

CREATE TABLE `_knex_temp_alter966` (`id` integer PRIMARY KEY AUTOINCREMENT NOT NULL, `username` varchar(35) NOT NULL, `age` integer)

INSERT INTO "_knex_temp_alter966" SELECT * FROM "user";

DROP TABLE "user"

ALTER TABLE "_knex_temp_alter966" RENAME TO "user"
```

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

### SQL Unsigned Integer (SQL Server)

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

Example of creating a table with an unsigned integer column using SQL Server syntax.

```sql
CREATE TABLE [unsigned_example] ([age] int)
```

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

### SQL Syntax for Table Check Constraint

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

Examples of `CREATE TABLE` statements with check constraints across different SQL dialects.

```sql
create table "product" ("type" varchar(255) check ("type" in ('table','chair','sofa')))
```

```sql
CREATE TABLE [product] ([type] nvarchar(255) check ([type] in ('table','chair','sofa')))
```

```sql
create table `product` (`type` varchar(255) check (`type` in ('table','chair','sofa')))
```

```sql
create table "product" ("type" varchar2(255) check ("type" in ('table', 'chair', 'sofa')))
```

```sql
create table "product" ("type" varchar(255) check ("type" in ('table','chair','sofa')))
```

```sql
create table "product" ("type" varchar(255) check ("type" in ('table','chair','sofa')))
```

```sql
create table `product` (`type` varchar(255) check (`type` in ('table','chair','sofa')))
```

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

### Get column information

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

Use columnInfo to retrieve details about table columns, such as type, default value, and nullability. An optional columnName can be provided to get info for a specific column.

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

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

### SQL Output for knex.ref Usage

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

These examples show the generated SQL for the `knex.ref` usage with different SQL dialects (PostgreSQL, SQL Server, MySQL).

```sql
select "Id", "Name" as "Username" from "TenantId"."Users" where "Id" = ? or "Name" = ?
```

```sql
select [Id], [Name] as [Username] from [TenantId].[Users] where [Id] = ? or [Name] = ?
```

```sql
select `Id`, `Name` as `Username` from `TenantId`.`Users` where `Id` = ? or `Name` = ?
```

```sql
select "Id", "Name" as "Username" from "TenantId"."Users" where "Id" = ? or "Name" = ?
```

```sql
select "Id", "Name" as "Username" from "TenantId"."Users" where "Id" = ? or "Name" = ?
```

```sql
select "Id", "Name" as "Username" from "TenantId"."Users" where "Id" = ? or "Name" = ?
```

```sql
select `Id`, `Name` as `Username` from `TenantId`.`Users` where `Id` = ? or `Name` = ?
```

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

### Alter Query Builder State Before Compilation with 'start' Event

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

The 'start' event is fired just before a query builder is compiled. 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) {});
```

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

### Create Table with Primary Key (SQL - PostgreSQL/MySQL)

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

Example of creating a table and then adding a primary key constraint using SQL syntax common in PostgreSQL and MySQL.

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

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

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

### whereExists - SQL variations

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

These examples illustrate the SQL generated by `whereExists` for different SQL dialects.

```sql
select * from "users" where exists (select * from "accounts" where users.account_id = accounts.id)

-- ----

select * from "users" where exists (select * from "accounts" where users.account_id = accounts.id)
```

```sql
select * from [users] where exists (select * from [accounts] where users.account_id = accounts.id)

-- ----

select * from [users] where exists (select * from [accounts] where users.account_id = accounts.id)
```

```sql
select * from `users` where exists (select * from `accounts` where users.account_id = accounts.id)

-- ----

select * from `users` where exists (select * from `accounts` where users.account_id = accounts.id)
```

```sql
select * from "users" where exists (select * from "accounts" where users.account_id = accounts.id)

-- ----

select * from "users" where exists (select * from "accounts" where users.account_id = accounts.id)
```

```sql
select * from "users" where exists (select * from "accounts" where users.account_id = accounts.id)

-- ----

select * from "users" where exists (select * from "accounts" where users.account_id = accounts.id)
```

```sql
select * from "users" where exists (select * from "accounts" where users.account_id = accounts.id)

-- ----

select * from "users" where exists (select * from "accounts" where users.account_id = accounts.id)
```

```sql
select * from `users` where exists (select * from `accounts` where users.account_id = accounts.id)

-- ----

select * from `users` where exists (select * from `accounts` where users.account_id = accounts.id)
```

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

### whereNotNull - SQL variations

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

These examples show the SQL generated by `whereNotNull` for different SQL dialects.

```sql
select * from "users" where "created_at" is not null
```

```sql
select * from [users] where [created_at] is not null
```

```sql
select * from `users` where `created_at` is not null
```

```sql
select * from "users" where "created_at" is not null
```

```sql
select * from "users" where "created_at" is not null
```

```sql
select * from "users" where "created_at" is not null
```

```sql
select * from `users` where `created_at` is not null
```

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

### whereNull - SQL variations

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

These examples demonstrate the SQL generated by `whereNull` for different SQL dialects.

```sql
select * from "users" where "updated_at" is null
```

```sql
select * from [users] where [updated_at] is null
```

```sql
select * from `users` where `updated_at` is null
```

```sql
select * from "users" where "updated_at" is null
```

```sql
select * from "users" where "updated_at" is null
```

```sql
select * from "users" where "updated_at" is null
```

```sql
select * from `users` where `updated_at` is null
```

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

### Basic Knex.js Configuration

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

Defines the database client and connection details. Supports environment variables for dynamic connection strings.

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

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

### Rank SQL Syntax (Backticks)

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

Example of the generated SQL for .rank() using backticks for identifiers.

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

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

### Create Primary Key Table (SQL - PostgreSQL/MySQL)

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

Example of creating a table with a primary key using SQL syntax common in PostgreSQL and MySQL.

```sql
create table "users_primary" ("user_id" integer, constraint "users_primary_key" primary key ("user_id") deferrable initially deferred)
```

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

### Create Primary Key Table (SQL - SQL Server)

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

Example of creating a table with a primary key using SQL syntax specific to SQL Server.

```sql
CREATE TABLE [users_primary] ([user_id] int, CONSTRAINT [users_primary_key] PRIMARY KEY ([user_id]))
```

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

### SQL for havingBetween clause

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

These examples illustrate the SQL generated for the `havingBetween` clause in various SQL dialects.

```sql
select * from "users" having "id" between ? and ?
```

```sql
select * from [users] having [id] between ? and ?
```

```sql
select * from `users` having `id` between ? and ?
```

```sql
select * from "users" having "id" between ? and ?
```

```sql
select * from "users" having "id" between ? and ?
```

```sql
select * from "users" having "id" between ? and ?
```

```sql
select * from `users` having `id` between ? and ?
```

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

### knex.migrate.make(name, [config])

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

Creates a new migration file with the specified name.

```APIDOC
## knex.migrate.make(name, [config])

### Description
Creates a new migration, with the name of the migration being added.

### Parameters
- **name** (string) - The name for the new migration file.
- **config** (object, optional) - Configuration options for migration creation.
```

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

### SQL for onNull clause

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

These examples demonstrate the SQL output for the onNull clause in different SQL dialects.

```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
```

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

### whereNotIn - SQL variations

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

These examples show the SQL generated by `whereNotIn` and `orWhereNotIn` for different SQL dialects.

```sql
select * from "users" where "id" not in (?, ?, ?)

-- ----

select * from "users" where "name" like ? or "id" not in (?, ?, ?)
```

```sql
select * from [users] where [id] not in (?, ?, ?)

-- ----

select * from [users] where [name] like ? or [id] not in (?, ?, ?)
```

```sql
select * from `users` where `id` not in (?, ?, ?)

-- ----

select * from `users` where `name` like ? or `id` not in (?, ?, ?)
```

```sql
select * from "users" where "id" not in (?, ?, ?)

-- ----

select * from "users" where "name" like ? or "id" not in (?, ?, ?)
```

```sql
select * from "users" where "id" not in (?, ?, ?)

-- ----

select * from "users" where "name" like ? or "id" not in (?, ?, ?)
```

```sql
select * from "users" where "id" not in (?, ?, ?)

-- ----

select * from "users" where "name" like ? or "id" not in (?, ?, ?)
```

```sql
select * from `users` where `id` not in (?, ?, ?)

-- ----

select * from `users` where `name` like ? or `id` not in (?, ?, ?)
```

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

### Create Primary Key Table (SQL - MySQL)

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

Example of creating a table with a primary key using SQL syntax common in MySQL.

```sql
create table `users_primary` (`user_id` int, constraint `users_primary_key` as `constraintName`, `deferred` as `deferrable` primary key (`user_id`))
```

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

### SQL for WITH NOT MATERIALIZED Clause

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

These examples show the SQL generated for the `.withNotMaterialized()` clause 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 Primary Key Table (SQL - MySQL)

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

Example of creating a table with a primary key using SQL syntax common in MySQL.

```sql
create table `users_primary` (`user_id` integer, constraint `users_primary_key` as `constraintName`, `deferred` as `deferrable` primary key (`user_id`))
```

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

### SQL Not Null Column (PostgreSQL/MySQL)

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

Example of creating a table with a not null column using SQL syntax.

```sql
create table "not_nullable_example" ("email" varchar(255) not null)
```
Tokens:
Raw