---
title: PostgreSQL
description: Integrate Better Auth with PostgreSQL.
---

PostgreSQL is a powerful, open-source relational database management system known for its advanced features, extensibility, and support for complex queries and large datasets.
Read more [here](https://www.postgresql.org/).

## Example Usage

Make sure you have PostgreSQL installed and configured.
Then, you can connect it straight into Better Auth.

```ts title="auth.ts"
import { betterAuth } from "better-auth";
import { Pool } from "pg";

export const auth = betterAuth({
  database: new Pool({
    connectionString: "postgres://user:password@localhost:5432/database",
  }),
});
```

<Callout>
  For more information, read Kysely's documentation to the
  [PostgresDialect](https://kysely-org.github.io/kysely-apidoc/classes/PostgresDialect.html).
</Callout>

## Schema generation & migration

The [Better Auth CLI](/docs/concepts/cli) allows you to generate or migrate
your database schema based on your Better Auth configuration and plugins.

<table>
  <thead>
    <tr className="border-b">
      <th>
        <p className="font-bold text-[16px] mb-1">PostgreSQL Schema Generation</p>
      </th>
      <th>
        <p className="font-bold text-[16px] mb-1">PostgreSQL Schema Migration</p>
      </th>
    </tr>
  </thead>
  <tbody>
    <tr className="h-10">
      <td>✅ Supported</td>
      <td>✅ Supported</td>
    </tr>
  </tbody>
</table>

```package-install title="Schema Generation"
npx @better-auth/cli@latest generate
```

```package-install title="Schema Migration"
npx @better-auth/cli@latest migrate
```

## Joins (Experimental)

Database joins is useful when Better-Auth needs to fetch related data from multiple tables in a single query.
Endpoints like `/get-session`, `/get-full-organization` and many others benefit greatly from this feature,
seeing upwards of 2x to 3x performance improvements depending on database latency.

The Kysely PostgreSQL dialect supports joins out of the box since version `1.4.0`.
To enable this feature, you need to set the `experimental.joins` option to `true` in your auth configuration.

```ts title="auth.ts"
export const auth = betterAuth({
  experimental: { joins: true }
});
```
<Callout type="warn">
  It's possible that you may need to run migrations after enabling this feature.
</Callout>

## Use a non-default schema

In most cases, the default schema is `public`. To have Better Auth use a
non-default schema (e.g., `auth`) for its tables, you have several options:

### Option 1: Set search_path in connection string (Recommended)

Append the `options` parameter to your connection URI:

```ts title="auth.ts"
import { betterAuth } from "better-auth";
import { Pool } from "pg";

export const auth = betterAuth({
  database: new Pool({
    connectionString: "postgres://user:password@localhost:5432/database?options=-c search_path=auth",
  }),
});
```

URL-encode if needed: `?options=-c%20search_path%3Dauth`.

### Option 2: Set search_path using Pool options

```ts title="auth.ts"
import { betterAuth } from "better-auth";
import { Pool } from "pg";

export const auth = betterAuth({
  database: new Pool({
    host: "localhost",
    port: 5432,
    user: "postgres",
    password: "password",
    database: "my-db",
    options: "-c search_path=auth",
  }),
});
```

### Option 3: Set default schema for database user

Set the PostgreSQL user's default schema:

```sql
ALTER USER your_user SET search_path TO auth;
```

After setting this, reconnect to apply the changes.

### Prerequisites

Before using a non-default schema, ensure:

1. **The schema exists:**
   ```sql
   CREATE SCHEMA IF NOT EXISTS auth;
   ```

2. **The user has appropriate permissions:**
   ```sql
   GRANT ALL PRIVILEGES ON SCHEMA auth TO your_user;
   GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA auth TO your_user;
   ALTER DEFAULT PRIVILEGES IN SCHEMA auth GRANT ALL ON TABLES TO your_user;
   ```

### How it works

The Better Auth CLI migration system automatically detects your configured `search_path`:

- When running `npx @better-auth/cli migrate`, it inspects only the tables in your configured schema
- Tables in other schemas (e.g., `public`) are ignored, preventing conflicts
- All new tables are created in your specified schema

### Troubleshooting

<Callout type="warning">
**Issue:** "relation does not exist" error during migration

**Solution:** This usually means the schema doesn't exist or the user lacks permissions. Create the schema and grant permissions as shown above.
</Callout>

<Callout type="info">
**Verifying your schema configuration:**

You can verify which schema Better Auth will use by checking the `search_path`:

```sql
SHOW search_path;
```

This should return your custom schema (e.g., `auth`) as the first value.
</Callout>

## Additional Information

PostgreSQL is supported under the hood via the [Kysely](https://kysely.dev/) adapter, any database supported by Kysely would also be supported. (<Link href="/docs/adapters/other-relational-databases">Read more here</Link>)

If you're looking for performance improvements or tips, take a look at our guide to <Link href="/docs/guides/optimizing-for-performance">performance optimizations</Link>.
