Orchid ORM

https://orchid-orm.netlify.app/guide
Admin
Orchid ORM is a TypeScript ORM that combines a powerful query builder with relational data mapping...
Tokens:323,608
Snippets:2,103
Trust Score:7.6
Update:1 week ago
Show doc for...
Tokens:
Raw
### Example database directory structure

Source: https://orchid-orm.netlify.app/guide/migration-setup-and-overview

Example directory structure created automatically when following the quickstart. Shows the layout for migrations, table factory, config, ORM db instance, db script, and seed file.

```plaintext
src/
└── db/
    ├── migrations/ - contains migrations files that can be migrated or rolled back.
    │   ├── recurrent/ - optional: sql files for triggers and functions
    │   │   └── my-function.sql - sql file containing CREATE OR REPLACE
    │   ├── 0001_create-post.ts
    │   └── 0002_create-comment.ts
    ├── table-factory.ts - for defining column type overrides.
    ├── config.ts - database credentials are exported from here.
    ├── db.ts - main file for the ORM, connects all tables into one `db` object.
    ├── db-script.ts - script run by `npm run db *command*`.
    └── seed.ts - for filling tables with data.
```

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

### Old BaseTable factory setup

Source: https://orchid-orm.netlify.app/guide/migrate-to-new-table-design

The old setup using createBaseTable. Replace this with createTableFactory.

```ts
import { createBaseTable } from 'orchid-orm';
import { zodSchemaConfig } from 'orchid-orm-schema-to-zod';

export const BaseTable = createBaseTable({
  snakeCase: true,
  schemaConfig: zodSchemaConfig,
  columnTypes: (t) => ({
    timestamp: () => t.timestamp().asDate(),
    myEnum: () => t.enum('myEnum', ['one', 'two', 'three']),
  }),
  nowSQL: `now() AT TIME ZONE 'UTC'`,
  language: 'english',
  autoForeignKeys: true,
});

export const { sql } = BaseTable;
```

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

### New table factory setup with createTableFactory

Source: https://orchid-orm.netlify.app/guide/migrate-to-new-table-design

The new setup using createTableFactory. Returns defineTable, defineView, sql, and exportAs. columnTypes must be a callback. filePath and defineTableExportAs are optional.

```ts
import { createTableFactory } from 'orchid-orm';
import { zodSchemaConfig } from 'orchid-orm-schema-to-zod';

export const { defineTable, defineView, sql } = createTableFactory({
  snakeCase: true,
  schemaConfig: zodSchemaConfig,
  columnTypes: (t) => ({
    timestamp: () => t.timestamp().asDate(),
    myEnum: () => t.enum('myEnum', ['one', 'two', 'three']),
  }),
  nowSQL: `now() AT TIME ZONE 'UTC'`,
  language: 'english',
  autoForeignKeys: true,
  // the following are only needed in rare cases for `db pull` command,
  // normally they're not needed.
  defineTableExportAs: 'defineTable',
  filePath: '/optional/path.ts',
});
```

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

### ORM setup with new table definitions

Source: https://orchid-orm.netlify.app/guide/migrate-to-new-table-design

ORM setup accepts new table definitions in the same places as table classes. Register tables in the second argument.

```ts
import { orchidORM } from 'orchid-orm';
import { UserTable } from './tables/user.table';

export const db = orchidORM(config.database, {
  user: UserTable,
});
```

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

### get and getOptional with column name and raw SQL

Source: https://orchid-orm.netlify.app/guide/query-methods

Shows basic usage of get and getOptional. get adds LIMIT 1 and throws NotFoundError when not found; getOptional returns undefined. The raw SQL example uses sql template from table-factory.

```typescript
import { NumberColumn } from 'orchid-orm';
import { sql } from './table-factory';

const firstName: string = await db.table.get('name');

const rawResult: number = await db.table.get(sql((t) => t.integer())`1 + 1`);

const firstNameOptional: string | undefined =
  await db.table.getOptional('name');
```

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

### Install orchid-orm-test-factory and faker.js

Source: https://orchid-orm.netlify.app/guide/test-factories

Install the library and faker.js as dev dependencies using npm.

```Shell
npm i -D orchid-orm-test-factory @faker-js/faker
```

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

### Install @anatine/zod-mock

Source: https://orchid-orm.netlify.app/guide/test-factories

Install the @anatine/zod-mock package to automatically generate JSON data based on zod schemas.

```Shell
npm install @anatine/zod-mock
```

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

### Install valimock

Source: https://orchid-orm.netlify.app/guide/test-factories

Install the valimock package to generate JSON data based on valibot schemas.

```Shell
npm i -D valimock
```

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

### Install big.js

Source: https://orchid-orm.netlify.app/guide/columns-types

Install the big.js library and its TypeScript types for precise number handling.

```sh
npm i big.js
npm i -D @types/big.js

```

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

### take

Source: https://orchid-orm.netlify.app/guide/query-methods

Adds LIMIT 1 and throws a NotFoundError when no record is found. Makes no effect if the query previously has get, pluck, exec. Changes getOptional to get.

```APIDOC
## take

### Description
Use `take` to "take" a single record. It adds `LIMIT 1`, throws a `NotFoundError` when not found.

### Method
Not applicable (ORM query method)

### Endpoint
Not applicable

### Parameters
None

### Request Example
```ts
const taken: TableType = await db.table.where({ key: 'value' }).take();
```

### Response
#### Success Response
- **taken** (TableType) - The single record found.

#### Error Response
- **NotFoundError** - Thrown when no record is found.

### Notes
- Makes no effect if the query previously has get, pluck, exec.
- Changes getOptional to get.
```

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

### bundleOrchidORM

Source: https://orchid-orm.netlify.app/guide/views.html

For split ORM setup, pass both tables and views to `bundleOrchidORM` to bundle them together.

```APIDOC
## bundleOrchidORM

### Description
For split ORM setup, pass both tables and views to `bundleOrchidORM` to bundle them together.

### Method
N/A (TypeScript function)

### Endpoint
N/A

### Parameters
#### Arguments
- **tables** (object) - Required - An object mapping table aliases to table definitions.
- **views** (object) - Required - An object mapping view aliases to view definitions.

### Request Example
```ts
import { bundleOrchidORM } from 'orchid-orm';

export const orm = bundleOrchidORM({
  tables: {
    user: UserTable,
  },
  views: {
    monthlySales: MonthlySalesView,
  },
});
```

### Response
#### Success Response
Returns a bundled ORM object that includes both tables and views.

#### Response Example
```ts
// No direct response; the ORM object is used for queries.
```
```

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

### Generated SQL for chain order

Source: https://orchid-orm.netlify.app/guide/relation-queries

The SQL generated by the previous chain example, showing the order of ORDER BY clauses.

```sql
ORDER BY "one"."a", "one"."b", "two"."c"
```

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

### Decrement with find or get throws NotFoundError

Source: https://orchid-orm.netlify.app/guide/update

When using find or get, decrement throws NotFoundError if no records are found. Both examples show the error behavior.

```TypeScript
// throws when not found
const updatedCount = await db.table.find(1).decrement('numericColumn');

// also throws when not found
const updatedCount2 = await db.table
  .where(...conditions)
  .get('columnName')
  .decrement('numericColumn');
```

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

### Initialize createDb with pqb

Source: https://orchid-orm.netlify.app/guide/query-builder-standalone.html

Shows how to import createDb from pqb for different drivers (postgres-js, node-postgres, bun) and configure it with ORM options, snakeCase, nowSQL, schemaConfig, and columnTypes.

```ts
// for porsager/postgres driver:
import { createDb } from 'pqb/postgres-js';
// for node-postgres driver:
import { createDb } from 'pqb/node-postgres';
// for Bun SQL driver:
import { createDb } from 'pqb/bun';

import { zodSchemaConfig } from 'orchid-orm-schema-to-zod';
// or
import { SchemaConfig } from 'orchid-orm-valibot';

const db = createDb({
  // ORM configuration options
  databaseURL: process.env.DATABASE_URL,
  log: true,

  // columns in db are in snake case:
  snakeCase: true,

  // override default SQL for timestamp, see `nowSQL` above
  nowSQL: `now() AT TIME ZONE 'UTC'`,

  // optional, but recommended: makes zod schemas for your tables
  schemaConfig: zodSchemaConfig,
  // or
  schemaConfig: valibotSchemaConfig,

  // override column types:
  columnTypes: (t) => ({
    // by default timestamp is returned as a string, override to a number
    timestamp: () => t.timestamp().asNumber(),
  }),
});
```

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

### Increment with find or get throws NotFoundError

Source: https://orchid-orm.netlify.app/guide/update

When using find or get, increment throws NotFoundError if no records are found. Both examples show the error behavior.

```TypeScript
// throws when not found
const updatedCount = await db.table.find(1).increment('numericColumn');

// also throws when not found
const updatedCount2 = await db.table
  .where(...conditions)
  .get('columnName')
  .increment('numericColumn');
```

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

### Create database with clone

Source: https://orchid-orm.netlify.app/guide/migration-programmatic-use.html

Shows how to create a database by cloning the adapter with a power user and an existing database, then calling `createDatabase`. The `owner` option is optional.

```typescript
import { createDatabase } from 'orchid-orm/migrations';

const adapter = db.$getAdapter().clone({
  user: 'postgres',
  database: 'postgres',
});

await createDatabase(adapter, {
  database: 'database-to-create',
  owner: 'username', // optional
});
```

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

### max function usage

Source: https://orchid-orm.netlify.app/guide/aggregate

Gets the maximum value for a numeric column. Returns number or null if there are no records. Includes a grouped query example.

```TypeScript
const result: number | null = await db.table.max(
  'numericColumn',
  aggregateOptions,
);

// select max product price grouped by product category
db.product
  .select('category', {
    maxPrice: (q) => q.max('price'),
  })
  .group('category')
  .take();
```

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

### Create database with npm run db create

Source: https://orchid-orm.netlify.app/guide/quickstart

Creates a new database. Run this command from the project root after setting up the project.

```sh
npm run db create
```

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

### min function usage

Source: https://orchid-orm.netlify.app/guide/aggregate

Gets the minimum value for a numeric column. Returns number or null if there are no records. Includes a grouped query example.

```TypeScript
const result: number | null = await db.table.min(
  'numericColumn',
  aggregateOptions,
);

// select min product price grouped by product category
db.product
  .select('category', {
    minPrice: (q) => q.min('price'),
  })
  .group('category')
  .take();
```

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

### Define table with init callback for beforeCreate hook

Source: https://orchid-orm.netlify.app/guide/define-tables

Defines a table with an init callback that is executed when a DB-aware ORM instance is created Accordig to the source, it is called by 'orchidORM' in the regular one-step setup or by 'makeOrchidOrmDb' in the split setup. This example shows configuring a 'beforeCreate' hook.

```ts
export const UserTable = defineTable('user', (t) => ({
  id: t.identity().primaryKey(),
  name: t.text(),
})).init((orm: typeof db, hooks) => {
  hooks.beforeCreate(({ set }) => {
    set({ name: 'new user' });
  });
});
```

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

### Example usage of db queries in src/hello.ts

Source: https://orchid-orm.netlify.app/guide/quickstart

Shows common query patterns with the db object: loading all records, taking the first, selecting with where/order/limit/offset, finding by id, and finding by conditions. Place this in src/hello.ts and run it to see the results.

```ts
// src/hello.ts
import { db } from './db';

const main = async () => {
  // load all records
  const records = await db.sample;

  // load first record
  const first = await db.sample.take();

  // select, where, order, limit, offset, etc
  const result = await db.sample
    .select('id', 'name')
    .where({ name: 'name' })
    .order({ name: 'DESC' })
    .limit(10)
    .offset(10);

  // find by id
  const recordById = await db.sample.find(123);

  // find one by conditions
  const record = await db.sample.findBy({ name: 'name' });
};

main();
```

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

### testTransaction hook setup

Source: https://orchid-orm.netlify.app/guide/transactions

Defines a useTestDatabase hook that starts, rolls back, and closes transactions around tests, keeping the database state unchanged and speeding up tests.

```typescript
// src/lib/test-utils.ts
import { testTransaction } from 'orchid-orm';
import { db } from './path-to-your-db';

export const useTestDatabase = () => {
  beforeAll(async () => {
    await testTransaction.start(db);
  });

  beforeEach(async () => {
    await testTransaction.start(db);
  });

  afterEach(async () => {
    await testTransaction.rollback(db);
  });

  afterAll(async () => {
    await testTransaction.close(db);
  });
};
```

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

### Initialize orchidORM with configuration and tables

Source: https://orchid-orm.netlify.app/guide/orm-setup

Creates the ORM instance by passing configuration options and table definitions. The configuration includes databaseURL, ssl, schema, connectRetry, log, autoForeignKeys, and autoPreparedStatements. The second argument maps table names to table classes.

```ts
// for porsager/postgres driver:
import { orchidORM } from 'orchid-orm/postgres-js';
// for node-postgres driver:
import { orchidORM } from 'orchid-orm/node-postgres';
// for Bun SQL driver:
import { orchidORM } from 'orchid-orm/bun';

// import all tables
import { UserTable } from './tables/user';
import { MessageTable } from './tables/message';

export const db = orchidORM(
  {
    // details for databaseURL are below
    databaseURL: process.env.DATABASE_URL,

    // ssl and schema can be set here or as databaseURL parameters:
    ssl: true,
    schema: 'my_schema',

    // retry connecting when db is starting up, no retry by default,
    // see `connectRetry` section below
    connectRetry: true,

    // option for logging, false by default
    log: true,

    // automatically create foreign keys for relations
    // see `autoForeignKeys` section below
    autoForeignKeys: true,

    // option to create named prepared statements implicitly, false by default
    autoPreparedStatements: true,
  },
  {
    user: UserTable,
    message: MessageTable,
  },
);
```

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

### QueryHelperResult: extract helper result type

Source: https://orchid-orm.netlify.app/guide/query-methods

Use the QueryHelperResult type to get the query type of a helper. The example shows how to derive the query type and the awaited result type.

```TypeScript
import { QueryHelperResult } from 'orchid-orm';

const selectHelper = db.table.makeHelper((q) => q.select('id', 'name'));

// This type is identical to `db.table.select('id', 'name')`
type SelectQuery = QueryHelperResult<typeof selectHelper>;

// Await to get result, the type is `{ id: number, name: string }[]`
type Result = Awaited<QueryHelperResult<typeof selectHelper>>;
```

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

### Using toSQL to get SQL text and values

Source: https://orchid-orm.netlify.app/guide/query-methods

Call toSQL on a query to obtain an object with a text SQL string and a values array of binding values. The example shows the expected SQL text and values for a select query with a where clause.

```typescript
const sql = db.table.select('id', 'name').where({ name: 'name' }).toSQL();

expect(sql.text).toBe(
  'SELECT "table"."id", "table"."name" FROM "table" WHERE "table"."name" = $1',
);
expect(sql.values).toEqual(['name']);
```

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

### Generate and apply migration immediately

Source: https://orchid-orm.netlify.app/guide/generate-migrations

Pass the up argument to apply the migration right after it generates.

```shell
pnpm db g create-some-tables up
```

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

### Configure roles with simple and advanced options

Source: https://orchid-orm.netlify.app/guide/generate-migrations

Activates role management by providing a `roles` array. The example shows a simple role and an admin role with various options like `super`, `inherit`, `createRole`, `createDb`, `canLogin`, `replication`, `connLimit`, `validUntil`, `bypassRls`, and `config`.

```typescript
export const db = orchidORM(
  {
    databaseURL: process.env.DATABASE_URL,
    roles: [
      {
        // a simple role with no options
        name: 'guest',
      },
      {
        name: 'admin',
        super: true,
        inherit: true,
        createRole: true,
        createDb: true,
        canLogin: true,
        replication: true,
        connLimit: 123,
        validUntil: new Date('2030-01-01'),
        bypassRls: true,
        // config is of type Record<string, string>:
        // consult with Postgres docs for supported variables.
        config: {
          statement_timeout: '30s',
          work_mem: '128MB',
        },
      },
    ],
  },
  { ...tables },
);
```

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

### $ensureTransaction: Ensure queries run in a transaction

Source: https://orchid-orm.netlify.app/guide/transactions

Use $ensureTransaction to ensure the sequence of queries runs in a transaction without Postgres savepoints. The example shows nested calls where the inner transaction won't be started if already inside a transaction.

```typescript
async function updateUserBalance(userId: string, amount: number) {
  await db.$ensureTransaction(async () => {
    await db.transfer.create({ userId, amount })
    await db.user.find(userId).increment({ balance: amount })
  })
}

async function saveDeposit(userId: string, deposit: { ... }) {
  await db.$ensureTransaction(async () => {
    await db.deposit.create(deposit)
    // transaction in updateUserBalance won't be started
    await updateUserBalance(userId, deposit.amount)
  })
}
```

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

### createTable with and without options

Source: https://orchid-orm.netlify.app/guide/migration-writing

Example showing `createTable` with options (comment, dropMode, noPrimaryKey) and without options. Returns `{ table }` for inserting records.

```typescript
import { change } from '../db-script';

change(async (db, up) => {
  // call `createTable` with options
  await db.createTable(
    'table',
    {
      comment: 'Table comment',
      dropMode: 'CASCADE',
      noPrimaryKey: true,
    },
    (t) => ({
      // ...
    }),
  );

  // call without options
  const { table } = await db.createTable('user', (t) => ({
    id: t.identity().primaryKey(),
    email: t.text().unique(),
    name: t.text(),
    active: t.boolean().nullable(),
    ...t.timestamps(),
  }));
});
```

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

### parse method with zod and valibot schemas

Source: https://orchid-orm.netlify.app/guide/common-column-methods

Defines columns with parse functions. When a validation library is installed, the first argument is a validation schema (zod or valibot). Without it, only the transform function is provided. The example shows parsing a string to an integer after loading.

```TypeScript
import { z } from 'zod';
import { number, integer } from 'valibot';

export const Table = defineTable('table', (t) => ({
  columnZod: t
    .string()
    // when having validation library, the first argument is a schema
    .parse(z.number().int(), (input) => parseInt(input))
    // no schema argument otherwise
    .parse((input) => parseInt(input)),

  columnValibot: t
    .string()
    .parse(number([integer()]), (input) => parseInt(input))
    .parse((input) => parseInt(input)),
}));

// column will be parsed to a number
const value: number = await db.table.get('column');
```

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

### encode method with and without validation schema

Source: https://orchid-orm.netlify.app/guide/common-column-methods

Defines a column with encode functions. When a validation library is installed, the first argument is a validation schema (zod). Without it, only the transform function is provided. The example shows converting boolean, number, or string to string before saving.

```TypeScript
import { z } from 'zod';

export const Table = defineTable('table', (t) => ({
  // encode boolean, number, or string to text before saving
  column: t
    .string()
    // when having validation library, the first argument is a validation schema
    .encode(
      z.boolean().or(z.number()).or(z.string()),
      (input: boolean | number | string) => String(input),
    )
    // no schema argument otherwise
    .encode((input: boolean | number | string) => String(input)),
}));

// numbers and booleans will be converted to a string:
await db.table.create({ column: 123 });
await db.table.create({ column: true });
await db.table.where({ column: 'true' }).update({ column: false });
```

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

### Instantiate orchidORM with table definition

Source: https://orchid-orm.netlify.app/guide/orm-setup

Place the table definition in the main `db` file to create the ORM instance. This is the default and recommended way to set up the ORM.

```typescript
import { UserTable } from './tables/user';

export const db = orchidORM(
  {
    databaseURL: process.env.DATABASE_URL,
  },
  {
    user: UserTable,
  },
);
```

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

### Define and use a named window with .window()

Source: https://orchid-orm.netlify.app/guide/window.html

Shows how to define a window named `windowName` with partitioning and ordering, then use it in an aggregate function like `avg` via the `over` option.

```typescript
db.table
  // define window `windowName`
  .window({
    windowName: {
      partitionBy: 'someColumn',
      order: {
        id: 'DESC',
      },
    },
  })
  .select({
    avg: (q) =>
      // calculate average price over the window
      q.avg('price', {
        // use window by its name
        over: 'windowName',
      }),
  });
```

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

### Create table and populate with values

Source: https://orchid-orm.netlify.app/guide/migration-writing

Example of creating a table and inserting records. Uses the `up` check to avoid running queries on rollback. `db` is an extended query builder with methods like `createTable` and `query`.

```typescript
import { change } from '../src';

change(async (db, up) => {
  const { table } = await db.createTable('languages', (t) => ({
    id: t.identity().primaryKey(),
    // `string` is a varchar with a limit 255 by default.
    name: t.string().unique(),
    code: t.string().unique(),
  }));

  // it's important to use this `up` check to not run the queries on rollback
  if (up) {
    // TS knows the column types, so this will be type-checked:
    await table.createMany([
      { name: 'Ukrainian', code: 'ua' },
      { name: 'English', code: 'en' },
      { name: 'Polish', code: 'pl' },
      { name: 'Belarusian', code: 'be' },
      { name: 'French', code: 'fr' },
    ]);

    // use db.query to perform raw SQL queries
    const language = 'Ukrainian';
    const { rows } = await db.query`
      SELECT * FROM languages WHERE name = ${language}
    `;
    console.log(rows);
  }
});
```

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

### Create migrations schema and table

Source: https://orchid-orm.netlify.app/guide/migration-programmatic-use.html

Shows how to create the migrations table and optionally the schema. It can be called in a transaction and won't fail if they already exist. Also shows using `makeRakeDbConfig` to provide `log: true`.

```typescript
import {
  createMigrationsSchemaAndTable,
  makeRakeDbConfig,
} from 'orchid-orm/migrations';

await createMigrationsSchemaAndTable(db, {
  migrationsTable: 'migrations',
  // can contain schema
  migrationsTable: 'custom-schema.migrations',

  logger: console, // will log if logger is provided
});

// to provide `log: true` instead of a logger, prepare the config with `makeRakeDbConfig`
const config = makeRakeDbConfig({
  log: true,
  migrationsTable: 'migrations',
  import: (path) => import(path),
});

await createMigrationsSchemaAndTable(db, config);
```

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

### Run migrations with npm run db up

Source: https://orchid-orm.netlify.app/guide/quickstart

Runs database migrations to create tables. Use this after creating demo tables or when you have migration files in src/db/migrations.

```sh
# command to run migrations (create tables):
npm run db up
```

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

### Equivalent SQL for unionAll with order/limit/offset

Source: https://orchid-orm.netlify.app/guide/advanced-queries

The equivalent SQL for the unionAll example, showing that each union part has its own order, limit, offset, and the whole query has its own.

```sql
-- both union parts have their own order, limit, offset
( SELECT * FROM one ORDER x ASC LIMIT 1 OFFSET 1 )
UNION ALL
( SELECT * FROM two ORDER y ASC LIMIT 2 OFFSET 2 )
-- order, limit, offset of the whole query
ORDER BY z ASC LIMIT 3 OFFSET 3
```

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

### Create a migration with db new

Source: https://orchid-orm.netlify.app/guide/migration-setup-and-overview

Run the migration creation command from the command line using your package manager. The command creates a new migration file with a generated name, but you can provide a custom name as an argument; spaces are replaced with dashes.

```sh
npm run db new create-a-table
pnpm db new create-a-table
yarn db new create-a-table
pnpm db new "create a table" # spaces are replaced with dashes
```

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

### Before hooks example

Source: https://orchid-orm.netlify.app/guide/hooks

Example of defining before hooks in Orchid ORM. The snippet shows a hook that sets userId.

```typescript
set({ userId });
```

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

### Install OrchidORM with npm or pnpm

Source: https://orchid-orm.netlify.app/guide/orm-setup

Install the orchid-orm package using npm or pnpm. The pnpm command is an alternative to npm.

```sh
npm i orchid-orm
# or
pnpm i orchid-orm
```

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

### createDatabase()

Source: https://orchid-orm.netlify.app/guide/migration-programmatic-use.html

Creates a database. Requires an adapter with power user credentials and an existing database to connect to.

```APIDOC
## createDatabase()

### Description
Creates a database. Requires an adapter with power user credentials and an existing database to connect to.

### Parameters
- **adapter** (Adapter) - Required - Adapter with power user credentials and an existing database to connect to.
- **options** (object) - Required - Options for database creation.
  - **database** (string) - Required - Name of the database to create.
  - **owner** (string) - Optional - Owner of the database.

### Request Example
```ts
import { createDatabase } from 'orchid-orm/migrations';

const adapter = db.$getAdapter().clone({
  user: 'postgres',
  database: 'postgres',
});

await createDatabase(adapter, {
  database: 'database-to-create',
  owner: 'username', // optional
});
```
```

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

### Create test factory and generate objects

Source: https://orchid-orm.netlify.app/guide/test-factories

Initializes the factory with the database and demonstrates building, creating, and creating multiple users with random or specific values. Timestamps are handled automatically, increasing by 1 millisecond per generated object.

```ts
import { ormFactory } from 'orchid-orm-test-factory';
import { db } from '../path-to-db';

const factory = ormFactory(db);

const user = factory.user.build();
// user is an object with random values, like:
// {
//   id: 89613,
//   name: 'Jackie Homenick',
//   password: 'MHDDzAPYHzuklCN',
// }

// save the user with random values to the database
const createdUser = await factory.user.create();

// create many users with specific emails
const manyUsers = await factory.user.createMany(
  { email: 'one@email.com' },
  { email: 'two@email.com' },
);
```

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

### Install orchid-orm-schema-to-zod or orchid-orm-valibot

Source: https://orchid-orm.netlify.app/guide/columns-validation-methods

Install the validation packages for Zod (orchid-orm-schema-to-zod) or Valibot (orchid-orm-valibot) to convert column schemas into validation schemas.

```sh
# for zod
npm i orchid-orm-schema-to-zod
# for valibot
npm i orchid-orm-valibot
```

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

### Install rake-db package

Source: https://orchid-orm.netlify.app/guide/migration-setup-and-overview

Install the rake-db package as a dev dependency using pnpm. The package is named after a Ruby on Rails command and was initially inspired by it.

```bash
pnpm i -D rake-db
```

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

### Create table

Source: https://orchid-orm.netlify.app/guide/migration-programmatic-use.html

Shows how to create a table using `createTable`. It uses a savepoint in a transaction to avoid breaking if the table already exists.

```typescript
import { createTable } from 'orchid-orm/migrations';

const result: 'done' | 'already' = await createTable(db, '"table"');
```

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

### Reset a database

Source: https://orchid-orm.netlify.app/guide/migration-commands

Shortcut command to drop, create, and migrate the database.

```sh
npm run db reset

```

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

### updateManyByOptional example

Source: https://orchid-orm.netlify.app/guide/update

Updates multiple records by a key, skipping records with no matching key. In the example, the record with email 'unknown@test.com' is skipped because it does not match any existing record.

```typescript
await db.table.updateManyByOptional('email', [
  { email: 'alice@test.com', name: 'Alice' },
  { email: 'unknown@test.com', name: 'Ghost' },
]);
```

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

### composite exclude: Example

Source: https://orchid-orm.netlify.app/guide/migration-column-methods

Shows how to define a composite EXCLUDE constraint using `t.exclude` with an array of column/expression objects and an options object. The example uses `using: 'GIST'`.

```TypeScript
import { change } from '../db-script';

change(async (db) => {
  await db.createTable(
    'table',
    (t) => ({
      id: t.identity().primaryKey(),
      roomId: t.integer(),
      startAt: t.timestamp(),
      endAt: t.timestamp(),
    }),
    (t) => [
      t.exclude(
        [
          { column: 'roomId', with: '=' },
          { expression: 'tstzrange("startAt", "endAt")', with: '&&' },
        ],
        {
          using: 'GIST',
        },
      ),
    ],
  );
});
```

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

### AfterCreate hook example updating commentCount

Source: https://orchid-orm.netlify.app/guide/hooks

Example of using afterCreate hook to update a related post's commentCount. The hook selects postId and updates the post.

```typescript
export const CommentTable = defineTable('someTable', (t) => ({
  ...someColumns,
})).init((orm: typeof db, hooks) => {
  hooks.afterCreate(['postId'], async (data, q) => {
    const allPostIds = data.map((comment) => comment.postId);
    const uniquePostIds = [...new Set(allPostIds)];

```

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

### Use orchidORMWithAdapter with a manually instantiated adapter

Source: https://orchid-orm.netlify.app/guide/customize-db-adapter.html

Imports AdapterClass and orchidORMWithAdapter from 'orchid-orm', and the driver-specific adapter (e.g., PostgresJsAdapter). Creates an adapter instance with the driver adapter and database URL, then passes it to orchidORMWithAdapter along with table definitions and logging enabled.

```ts
import { AdapterClass, orchidORMWithAdapter } from 'orchid-orm';
// for porsager/postgres driver:
import { Adapter as PostgresJsAdapter } from 'orchid-orm/postgres-js';
// for node-postgres driver:
// import { Adapter as NodePostgresAdapter } from 'orchid-orm/node-postgres';
// for Bun SQL driver:
// import { Adapter as BunAdapter } from 'orchid-orm/bun';

const adapter = new AdapterClass({
  driverAdapter: PostgresJsAdapter,
  config: { databaseURL: process.env.DATABASE_URL },
});

export const db = orchidORMWithAdapter(
  {
    adapter,
    log: true,
  },
  {
    // ...tables
  },
);
```

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

### Create views with createView and options

Source: https://orchid-orm.netlify.app/guide/migration-writing

Creates a simple view, a view with t.sql variables, and a view with options like createOrReplace, dropIfExists, dropMode, temporary, recursive, columns, checkOption, securityBarrier, and securityInvoker. securityInvoker defaults to true.

```typescript
import { change } from '../db-script';

change(async (db) => {
  await db.createView(
    'simpleView',
    `
    SELECT a.one, b.two
    FROM a
    JOIN b ON b."aId" = a.id
  `,
  );

  // view can accept t.sql with variables in such way:
  const value = 'some value';
  await db.createView(
    'viewWithVariables',
    t.sql`
      SELECT * FROM a WHERE key = ${value}
    `,
  );

  // view with options
  await db.createView(
    'schemaName.recursiveView',
    {
      // createOrReplace has effect when creating the view
      createOrReplace: true,

      // dropIfExists and dropMode have effect when dropping the view
      dropIfExists: true,
      dropMode: 'CASCADE',

      // for details, check Postgres docs for CREATE VIEW,
      // these options are matching CREATE VIEW options
      temporary: true,
      recursive: true,
      columns: ['n'],
      checkOption: 'LOCAL', // or 'CASCADED'
      securityBarrier: true,
      // securityInvoker defaults to true; set it to false to opt out.
      securityInvoker: true,
    },
    `
      VALUES (1)
      UNION ALL
      SELECT n + 1 FROM "schemaName"."recursiveView" WHERE n < 100;
    `,
  );
});
```

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

### SQL schema for timestamp handling example

Source: https://orchid-orm.netlify.app/guide/migration-commands

Example database schema with two tables, one with camelCase columns and the other with snake_case, used to illustrate how db pull handles timestamps.

```sql
CREATE TABLE "camel" (
  "id" integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  "camelCaseColumn" text NOT NULL,
  "createdAt" timestamptz DEFAULT now(),
  "updatedAt" timestamptz DEFAULT now()
);

CREATE TABLE "snake" (
  "id" integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  "snake_case_column" text NOT NULL,
  "created_at" timestamptz DEFAULT now(),
  "updated_at" timestamptz DEFAULT now()
);

```

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

### connect: create book with author and create author with books

Source: https://orchid-orm.netlify.app/guide/relation-queries

The `connect` option searches for records by given conditions and connects them. Throws `NotFoundError` if no record found. The first example creates a book and connects an existing author by name. The second creates an author and connects two existing books by title.

```typescript
const book = await db.book.create({
  title: 'Book title',
  author: {
    connect: {
      name: 'Author',
    },
  },
});

const author = await db.author.create({
  name: 'Author name',
  books: {
    connect: [
      {
        title: 'Book 1',
      },
      {
        title: 'Book 2',
      },
    ],
  },
});
```

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

### onJsonPathEquals example

Source: https://orchid-orm.netlify.app/guide/join

Use onJsonPathEquals to join records based on JSON field values using the @? or @@ JSON path operators. The example shows joining table with otherTable on JSON paths '$.key'.

```typescript
db.table.join(db.otherTable, (q) =>
  // '$.key' is a JSON path
  q.onJsonPathEquals('otherTable.data', '$.key', 'table.data', '$.key'),
);
```

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

### Using take to fetch a single record

Source: https://orchid-orm.netlify.app/guide/query-methods

Adds LIMIT 1 and throws a NotFoundError when no record is found. Makes no effect if the query previously has get, pluck, or exec. Changes getOptional to get.

```ts
const taken: TableType = await db.table.where({ key: 'value' }).take();
```

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

### Bundle tables and bind later with makeOrchidOrmDb

Source: https://orchid-orm.netlify.app/guide/customize-db-adapter.html

Imports bundleOrchidORMTables from 'orchid-orm' and makeOrchidOrmDb from the driver-specific path (e.g., 'orchid-orm/postgres-js'). Bundles table classes, exposes static table name and makeHelper, then creates the db instance with database URL and logging.

```ts
import { bundleOrchidORMTables } from 'orchid-orm';
import { makeOrchidOrmDb } from 'orchid-orm/postgres-js';
// for node-postgres driver:
// import { makeOrchidOrmDb } from 'orchid-orm/node-postgres';
// for Bun SQL driver:
// import { makeOrchidOrmDb } from 'orchid-orm/bun';

import { UserTable } from './tables/user';
import { MessageTable } from './tables/message';

export const orm = bundleOrchidORMTables({
  user: UserTable,
  message: MessageTable,
});

export const userTableName = orm.user.table;

export const selectUserProfile = orm.user.makeHelper((q) =>
  q.select('id', 'name'),
);

export const db = makeOrchidOrmDb(orm, {
  databaseURL: process.env.DATABASE_URL,
  log: true,
});
```

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

### orWhereNotExists example

Source: https://orchid-orm.netlify.app/guide/where

Prepends OR and negates with NOT.

```typescript
// find users who don't have an account OR who don't have a profile
// imagine that the user has both `account` and `profile` relations defined.
db.user.whereNotExists('account').orWhereNotExists('profile');
```

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

### Configure database credentials in .env

Source: https://orchid-orm.netlify.app/guide/quickstart

Set database credentials in the .env file. The DATABASE_URL is required, and DATABASE_TEST_URL is optional for tests. The ssl parameter can be true or false, and for hosted databases it should be true.

```sh
DATABASE_URL=postgres://user:password@localhost:5432/dbname?ssl=true|false

# If you'd like to have a separate database for tests
DATABASE_TEST_URL=postgres://user:password@localhost:5432/dbname-test?ssl=true|false
```

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

### createRole with options

Source: https://orchid-orm.netlify.app/guide/migration-writing

Creates a database role with optional settings. Uses IF NOT EXISTS logic by default, so re-creating the same role won't fail. Options include super, inherit, createRole, createDb, canLogin, replication, connLimit, validUntil, bypassRls, and config for Postgres variables.

```typescript
import { change } from '../db-script';

change(async (db) => {
  // options are optional
  await db.createRole('name');

  await db.createRole('name', {
    super: true,
    inherit: true,
    createRole: true,
    createDb: true,
    canLogin: true,
    replication: true,
    connLimit: 123,
    validUntil: new Date('2030-01-01'),
    bypassRls: true,
    // config is of type Record<string, string>:
    // consult with Postgres docs for supported variables.
    config: {
      statement_timeout: '30s',
      work_mem: '128MB',
    },
  });
});
```

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

### whereNotExists example

Source: https://orchid-orm.netlify.app/guide/where

Negates whereExists with NOT.

```typescript
// find users who don't have an account,
// image that the user `belongsTo` or `hasOne` account.
db.user.whereNotExist('account');
```
Tokens:
Raw