> ## Documentation Index
> Fetch the complete documentation index at: https://thenile.dev/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# Prisma

Prisma is a popular ORM. It is a flexible tool for managing database migrations.
In this guide, we'll walk through the process of setting up Prisma in a project and
running several migration scenarios.

## Setting Up an New Prisma project

**1. Create a new project (or clone our example project)**

```bash theme={null}
mkdir hello-prisma
cd hello-prisma
# Initialize a new npm project
npm init -y
# Install and initialize typescript
npm install typescript tsx @types/node --save-dev
npx tsc --init
# Install and initialize Prisma
npm install prisma --save-dev
npx prisma init --datasource-provider postgresql
```

If you'd like to clone our example project, you can do so with the following command:

```bash theme={null}
git clone https://github.com/niledatabase/niledatabase
cd niledatabase/examples/migrations/prisma
```

**2. Configure your environment**

```bash theme={null}
# Create a .env file
touch .env
# Add your database connection string to the .env file
echo "DATABASE_URL='postgresql://user:password@host:5432/dbname'" >> .env
```

You can get your connection string from the Nile console under the "Connections" page.

## Using Prisma with Nile in development

Nile has built-in tables that can be useful in your project.
Especially the `tenants` table, which is used to store tenant information.

Therefore, we recommend first pulling in the built-in tables to your project, and
then starting to build your own tables.

**1. Pull in the built-in tables**

```bash theme={null}
npx prisma db pull
```

This will generate the `schema.prisma` file with the built-in `tenants` table.

**2. Generate Prisma client**

```bash theme={null}
npx prisma generate
```

**3. Add your own tables**

To add your own tables, you can edit the `schema.prisma` file and add your own models. For example:

```prisma theme={null}
  model posts {
  id        String  @default(dbgenerated("public.uuid_generate_v7()")) @db.Uuid
  tenant_id String  @db.Uuid
  title     String
  content   String?
  authorId  String
  @@id([id, tenant_id])
  }
```

**4. Push changes to the database**

```bash theme={null}
npx prisma db push
```

## Using Prisma with Nile in production

`db pull` and `db push` work well in development, where you can evolve the schema without tracking every change.

However, in production, we recommend using tracked migrations to ensure that the schema is always
in sync with the code.

Because Nile has a built-in `tenants` table, we first create a baseline migration that will include the built-in tables,
and then create additional migrations for our own tables.

**1. Create a baseline migration**

If you don't yet have a schema.prisma file with the built-in tables, start by creating one:

```bash theme={null}
npx prisma db pull
```

Then, create a baseline migration:

```bash theme={null}
mkdir -p prisma/migrations/0_init

npx prisma migrate diff \
--from-empty \
--to-schema-datamodel prisma/schema.prisma \
--script > prisma/migrations/0_init/migration.sql
```

**2. Apply the baseline migration**

```bash theme={null}
npx prisma migrate resolve --applied 0_init
```

**3. Create a migration for your own tables**

Now, lets modify the schema to include our own tables.
For example, we'll add a `posts` table to `schema.prisma`.
If you already have this table in your schema, you can add a column instead.

```prisma theme={null}
model posts {
  id        String  @default(dbgenerated("public.uuid_generate_v7()")) @db.Uuid
  tenant_id String  @db.Uuid
  title     String
  content   String?
  authorId  String
  @@id([id, tenant_id])
}
```

Now, we'll create a migration for this change by comparing the existing schema in the
DB (the `datasource`) with the new schema in `schema.prisma` (the `datamodel`).

While both the `datasource` and `datamodel` are in the same file, the first will refer to the
`datasource` definition in the `schema.prisma` file while the second will refer to the model itself.

```bash theme={null}
mkdir -p prisma/migrations/1_add_posts
npx prisma migrate diff \
--from-schema-datasource prisma/schema.prisma \
--to-schema-datamodel prisma/schema.prisma \
--script > prisma/migrations/1_add_posts/migration.sql
```

> [!NOTE]
>
> Prisma docs recommend generating migrations with `npx prisma migrate dev`. However, this will not work
> for Nile because the `npx prisma migrate dev` command starts by attempting to "reset" a shadow database by dropping all tables.
> This is not possible in Nile because the built-in tables are required for core functionality.

**4. Apply the migration**

Last but not least, we can apply the migration to the database:

```bash theme={null}
npx prisma migrate deploy
```


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.