Quickstart #

Orchid ORM has a script to initialize the project with a command-line prompts. Use it to start from scratch, or it can be run inside the existing project, it won't remove any existing files.

init script #

npx orchid-orm@latest
# or if you're using pnpm
pnpm dlx orchid-orm@latest

This script will ask a few questions to customize the setup:

Where would you like to install Orchid ORM?

Press enter to init in the current directory, or specify a path. It will create directories recursively unless they exist.

Preferred type of returned timestamps:

Here you can choose how timestamps will be returned from a database: as a string, as a number, or as a Date object.

This can be changed later, and this can be overridden for a specific table.

Should the script add a separate database for tests:

Hit y if you're going to write integration tests over a real database.

Are you going to use `Zod` for validation?

When chosen, table schemas can be used as Zod schemas for validation. orchid-orm does not perform validation on its own.

Do you want object factories for writing tests?

This adds a library for generating mock objects from defined tables.

Should the script add demo tables?

Adds two tables for example.

Let's add fast TS compiler swc?

It's only asked when initializing a new project (when no tsconfig.json found), this will add swc compiler to a package.json and to tsconfig.json.

After answering these questions, it will create all the necessary config files.

package.json #

After running the script, check if the package.json file looks well, and install dependencies (npm i) :

{
  "scripts": {
    // for running db scripts, like npm run db create, npm run db migrate
    "db": "ts-node src/db/dbScript.ts"
  },
  "dependencies": {
    // dotenv loads variables from .env
    "dotenv": "^16.0.3",
    // the ORM is responsible for defining tables and relations between them
    "orchid-orm": "^1.5.18",
    // query builder, used by other parts to build chainable query objects
    "pqb": "^0.9.12",
    // convert table columns to a Zod schema to use it for validations
    "orchid-orm-schema-to-zod": "^0.2.18"
  },
  "devDependencies": {
    // rake-db is a toolkit for migrations
    "rake-db": "^2.3.17",
    // for generating mock objects in tests
    "orchid-orm-test-factory": "^0.2.24",
    // for the fastest typescript compilation
    "@swc/core": "^1.3.32",
    // node.js types
    "@types/node": "^18.11.18",
    // for running typescript
    "ts-node": "^10.9.1",
    "typescript": "^4.9.5"
  }
}

ES modules #

Note the db script: it is for running migrations, and it's being launched with ts-node.

If you'd like to use ts-node in ES modules mode (it works, but outputs experimental warning), change the script to:

{
  "scripts": {
    "db": "node --loader ts-node/esm src/db/dbScript.ts"
  }
}

If you'd like to use vite-node instead, which works in ES modules mode by default, install vite-node and change the script to:

{
  "scripts": {
    "db": "vite-node src/db/dbScript.ts --"
  }
}

Note the double-dash at the end - it is needed.

If, during init script, you opted for swc compiler - it's no longer needed, remove swc from dependencies and from tsconfig.json.

To use vite you also need to add the following line to dbScript.ts:

// src/db/dbScript.ts

export const change = rakeDb(config.database, {
  // add this line:
  import: (path: string) => import(path),
});

structure #

Consider the created structure:

.
├── src/
│   └── db/
│       ├── migrations/ - contains migrations files that can be migrated or rolled back.
│       │   ├── timestamp_createPost.ts
│       │   └── timestamp_createComment.ts
│       ├── tables/ - tables are used in the app, define columns and relations here.
│       │   ├── comment.table.ts
│       │   └── post.table.ts
│       ├── baseTable.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.
│       ├── dbScript.ts - script run by `npm run db *command*`.
│       └── seed.ts - for filling tables with data.
├── .env - contains database credentials.
├── .gitignore - .env must be ignored by git.
├── package.json
└── tsconfig.json - specifying strict mode is very important.

database setup #

Change database credentials in the .env file:

DATABASE_URL=postgres://user:password@localhost:5432/dbname?ssl=false
DATABASE_TEST_URL=postgres://user:password@localhost:5432/dbname-test?ssl=false

If you're using a hosted database, change ssl to true in the above config.

In case of using hosted database, it's already created by provided, but if you develop with a local Postgres, create databases with this command:

# command to create a database:
npm run db create

By default, camelCase naming is used for columns in a database. If you prefer snake_case, set snakeCase: true option in src/db/dbScript.ts and src/db/baseTable.ts:

// src/db/baseTable.ts

export const BaseTable = createBaseTable({
  snakeCase: true,
  // ...snip
});

// src/db/dbScript.ts

export const change = rakeDb(config.database, {
  snakeCase: true,
  // ...other options
});

If you chose to create demo tables, there are migrations files in src/db/migrations. Run migrations:

# command to run migrations (create tables):
npm run db migrate

Run the seeds for demo tables:

npm run db seed

The setup is completely ready at this point. For the next steps, create your tables and write queries.

Generate a new migration by running a command:

npm run db new createSample

The file with such content will appear in the src/db/migrations directory:

// src/db/migrations/*timestamp*_createSample.ts
import { change } from '../dbScript';

change(async (db) => {
  await db.createTable('sample', (t) => ({}));
});

Add columns to the table:

// src/migrations/*timestamp*_createTable.ts
import { change } from '../dbScript';

change(async (db) => {
  await db.createTable('sample', (t) => ({
    id: t.identity().primaryKey(),
    text: t.text(),
    ...t.timestamps(),
  }));
});

Apply migration by running:

npm run db migrate

defining tables #

src/db/tables/sample.table.ts was created after running a migration.

TypeScript should highlight t.text() because it doesn't have min and max specified, this is needed to prevent unpleasant situations when empty or huge texts are submitted.

// src/sb/tables/sample.table.ts
import { BaseTable } from './baseTable';

export class SampleTable extends BaseTable {
  readonly table = 'sample';
  columns = this.setColumns((t) => ({
    id: t.identity().primaryKey(),
    // specify min and max length
    text: t.text(1, 10000),
    ...t.timestamps(),
  }));
}

src/db/db.ts is the main file for the ORM, it connects all tables into one db object.

// src/db/db.ts
import { orchidORM } from 'orchid-orm';
import { config } from './config';
import { PostTable } from './tables/post.table';
import { CommentTable } from './tables/comment.table';
import { SampleTable } from './tables/sample.table';

export const db = orchidORM(config.database, {
  post: PostTable,
  comment: CommentTable,
  sample: SampleTable,
});

example usage #

// 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();