---
url: /guide.md
description: >-
  Orchid ORM overview, features, philosophy, and comparison with other database
  tools for Node.js and TypeScript.
---

# Overview

`Orchid ORM` is a library for node.js to help to work with a Postgres database,
with super-flexible and type-safe query builder,
allowing to easily define and compose queries with table relations.

The main focus is to keep it as powerful as possible, concise and intuitive, performant, and completely type-safe.

To get maximum control over the db, it has a query builder `pqb` which is inspired by [knex](http://knexjs.org/) and has all the same functionalities,
but `pqb` is written from scratch with TypeScript in mind.

Type safety is achieved by defining a schema of columns and using inferred types in all query methods.

Unlike other ORMs in OOP style that rely on Active Record pattern and may look similar to:

```ts
// In some other ORMs, post is an instance of class Post
const post = await Post.findBy({ id: 123 });
await post.update({ title: 'new title' });
```

`Orchid ORM` is designed with different goals, so the records are returned as a plain objects:

```ts
// In Orchid ORM, post is a plain object
const post = await db.post.findBy({ id: 123 });
await db.post.update(post, { title: 'new title' });
```

This approach allows to select relations in a nested way, perform customized sub-queries, and keep everything type-safe:

```ts
// post type is completely inferred
const post = await db.post
  .find(123)
  .select('title', 'body', {
    likesCount: (q) => q.likes.count(),
    comments: (q) =>
      q.comments
        .order({ createdAt: 'DESC' })
        .limit(50)
        .select('body', {
          author: (q) => q.author.select('avatar', 'username'),
        }),
  })
  // can filter and order by the selected count of likes
  .where({ likesCount: { gt: 100 } })
  .order({ likesCount: 'DESC' });
```

The query builder functionality is aimed to be as flexible as possible, allowing to chain queries with relations and conditions.

For example, selecting posts that have 2 specific tags:

```ts
const posts = await db.post.where((q) =>
  q.tags.whereIn('tagName', ['typescript', 'node.js']).count().gte(2),
);
```

Relations can be chained in a sub-query.
Collecting array of all commenters' names for every post:

```ts
const posts = await db.post.select({
  // `pluck` collects a plain array
  commentedBy: (q) => q.comments.author.pluck('username'),
});
```

Custom SQL can be injected into any place of the query.
Inserted values are properly handled to not allow SQL injections.

```ts
import { sql } from './table-factory';

const posts = await db.customer
  .select({
    upper: sql<string>`upper(title)`,
  })
  .whereSql`reverse(title) = ${reversedTitle}`
  .orderSql`reverse(title)`
  .havingSql`count("someColumn") > 300`;
```

`Orchid ORM` allows you to define custom chainable methods (via [repository](/guide/repo)) to write clean abstract queries like:

```ts
const posts = await postRepo
  .selectForList()
  .search('word')
  .filterByTags(['tag 1', 'tag 2'])
  .orderByPopularity()
  .limit(20);
```