# Database

Novel makes use of PostgreSQL exclusively because it has very wide adoption across cloud and a vibrant plugin community.

Knex.js with Objection.js is the ORM of choice for the project.

{% embed url="<https://www.postgresql.org/>" %}

To install PostgreSQL, follow the [Start](/start.md#manual-installation) tutorial.

{% embed url="<https://knexjs.org/>" %}

{% embed url="<https://vincit.github.io/objection.js/>" %}

## Setup

If you have not followed the Getting started tutorial, it would be best to start with that

{% embed url="<https://docs.novel.dev/start>" %}

Your Novel instance will use the environment variable below to create a connection pool against your database.

{% code title=".env" lineNumbers="true" %}

```sh
DB_HOST=postgres://username:password@postgres-server:5432/novel
```

{% endcode %}

## Why not Drizzle/Prisma/TypeORM/Kysely?

* **Prisma** uses a different DSL that you most likely would not touch frequently. It also has abstraction overhead and troubleshooting issues due to having a different runtime engine.
* **Drizzle** has a smaller community but with comparable pedigree as knex
* **TypeORM** is more of an ORM but also suffers from abstraction overhead. It has better support for typescript than knex.
* **Kysely** is a newer player and have a smaller community similar to Drizzle.

## Managing the Data

There are a lot of tools out there you can use to connec to your database. What we recommend is [Datagrip by Jetbrains](https://www.jetbrains.com/datagrip/), or [pgAdmin for OSX](https://www.pgadmin.org/download/pgadmin-4-macos/).

Novel ships with default database tables that function as a foundation for all the relevant features you have access to.

These are located in `/packages/novel/migrations`.

These migrations are executed during development runtime as well as when you start the server.

{% embed url="<https://docs.novel.dev/guides/knowledge-base/database/creating-a-table>" %}

## Usage

You can use the internal database connection like below

{% code title="feature.ts" lineNumbers="true" %}

```typescript
import db from 'novel/db';

await db('accounts').select();
```

{% endcode %}

You can use knex methods here.

## Request Bound Transactions

You can create a request bound transaction

{% code title="feature.ts" lineNumbers="true" %}

```typescript
import db from 'novel/db';

function handler(request, reply) {
    await db().session();
    // this will run in a transaction
    await db('accounts').where('id', 1).update({ test: 1 });
    // and any db calls from required functions
    await updateAccount();
}
```

{% endcode %}

All database calls within the request lifecycle uses the transaction.

## Base Models

These models follows the multi-tenancy principle. These are also covered under warranty and not advised to be overwritten.

These models are located in `/packages/novel/models/`.

<div data-full-width="false"><figure><img src="/files/gWTSuwVepXJNtMKvHpSb" alt=""><figcaption><p>Overview of the Base Models included with Novel</p></figcaption></figure></div>

A deeper discussion and explanation of the way models work are explained below

{% embed url="<https://docs.novel.dev/novel-server/models>" %}

## Changelog

* 2024-12-20 - Initial Documentation


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.novel.dev/novel-server/database.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.