github-starred/better-auth
2
0
Fork 0
mirror of https://github.com/better-auth/better-auth.git synced 2026-05-23 07:18:56 -05:00
Code Issues 2.5k Packages Projects Releases 112 Wiki Activity

[GH-ISSUE #8049] CLI generate and migrate commands fail to search "public" schema in Supabase PostgreSQL. Caused by search_path feature from Fall 2025 #28303

New Issue
Closed
opened 2026-04-17 19:44:05 -05:00 by GiteaMirror · 1 comment
GiteaMirror commented 2026-04-17 19:44:05 -05:00
Owner
Copy Link

Originally created by @jackshunn on GitHub (Feb 18, 2026).
Original GitHub issue: https://github.com/better-auth/better-auth/issues/8049

Is this suited for github?

  • Yes, this is suited for github

To Reproduce

  1. Get a connection string to a postgres database hosted by Supabase at https://supabase.com. Use the postgres user and do not modify the search_path using url query parameters and described optionally in the docs.
  2. Export a basic better auth instance from a auth.ts file. Turn on debug logging for a little more clarity.
  3. Run the better-auth cli generate command e.g. npx @better-auth/cli generate
  4. Observe the following unexpected logging in the console:
DEBUG [Better Auth]: PostgreSQL migration: Using schema '\$user' (from search_path)
WARN [Better Auth]: Schema '\$user' does not exist. Tables will be inspected from available schemas. Consider creating the schema first or checking your database configuration.
DEBUG [Better Auth]: Found 0 table(s) in schema '\$user': (none)
  1. If ran against a database with an existing "public" schema, notice how the generated sql migration file creates all new tables instead of modifying the existing schema.

Current vs. Expected behavior

I'd expect the logging to output something like this:

DEBUG [Better Auth]: PostgreSQL migration: Using schema 'public' (from search_path)
DEBUG [Better Auth]: Found 4 table(s) in schema 'public': user, session, account, verification

I'd expect the generated sql file to generate ALTER TABLE queries instead of CREATE TABLE queries if user, session, account, or verification tables already exist in the "public" schema.

What version of Better Auth are you using?

1.4.18

System info

{
  "node": {
    "version": "v25.4.0",
    "env": "development"
  },
  "packageManager": {
    "name": "npm",
    "version": "11.7.0"
  },
  "frameworks": [
    {
      "name": "react",
      "version": "^19.2.4"
    },
    {
      "name": "astro",
      "version": "^5.17.1"
    }
  ],
  "databases": [
    {
      "name": "pg",
      "version": "^8.18.0"
    }
  ],
  "betterAuth": {
    "version": "^1.4.18",
    "config": {
      "logger": {
        "level": "debug"
      },
      "database": {
        "_events": {},
        "_eventsCount": 0,
        "options": {
          "connectionString": "[REDACTED]",
          "max": 10,
          "min": 0,
          "maxUses": null,
          "allowExitOnIdle": false,
          "maxLifetimeSeconds": 0,
          "idleTimeoutMillis": 10000
        },
        "_clients": [],
        "_idle": [],
        "_expired": {},
        "_pendingQueue": [],
        "ending": false,
        "ended": false
      },
      "emailAndPassword": {
        "enabled": true
      },
      "plugins": []
    }
  }
}

Which area(s) are affected? (Select all that apply)

Other, Documentation

Auth config (if applicable)

import { betterAuth } from "better-auth";
import { Pool } from "pg";

export const auth = betterAuth(
    {
        logger: {
            level: "debug"
        },
        database: new Pool({
            connectionString: process.env.DATABASE_URL,
        }),
        emailAndPassword: {
            enabled: true
        },
    }
);

Additional context

This is a regression caused by a feature added in PR: #5449 Issue: #5430
See this block of code run by the cli to query the current connection's search_path and assumes that the first "non-variable" schema is the one the user intended to direct to better-auth.
2468a2d918/packages/better-auth/src/db/get-migration.ts (L99-L119)
This code assumes it can filter out variables by removing any list items that start with a quote character (either ' or ") followed by a $ character.
This works for some postgres instances. (I tested on a throwaway local instance)
However, for reasons I don't understand, the postgres connection coming from Supabase will respond to the query SHOW search_path; with the response "\$user", public, extensions
The better_auth code isn't written to expect the \ character before the variable name thus the cli no longer defaults to the "public" schema.

I think it's worth a little discussion on how this should be addressed. I'm not an expert on postgres schemas and connections strings, but my instinct is that this method of modifying the search_path to direct better-auth is brittle, hacky, and isn't defensively implemented currently. I'd wouldn't mind seeing it deprecated and not championed so strongly in the docs. It's not apples to apples but I would take some inspiration from prisma on this front. They've figured out how to configure and manage targeting explicit postgres schemas in their queries and migrations. See this page for more details there: https://www.prisma.io/docs/orm/prisma-schema/data-model/multi-schema

Originally created by @jackshunn on GitHub (Feb 18, 2026). Original GitHub issue: https://github.com/better-auth/better-auth/issues/8049 ### Is this suited for github? - [x] Yes, this is suited for github ### To Reproduce 1. Get a connection string to a postgres database hosted by Supabase at https://supabase.com. Use the `postgres` user and do not modify the `search_path` using url query parameters and described optionally in the docs. 2. Export a basic better auth instance from a auth.ts file. Turn on debug logging for a little more clarity. 3. Run the better-auth cli generate command e.g. `npx @better-auth/cli generate` 4. Observe the following unexpected logging in the console: ``` DEBUG [Better Auth]: PostgreSQL migration: Using schema '\$user' (from search_path) WARN [Better Auth]: Schema '\$user' does not exist. Tables will be inspected from available schemas. Consider creating the schema first or checking your database configuration. DEBUG [Better Auth]: Found 0 table(s) in schema '\$user': (none) ``` 5. If ran against a database with an existing "public" schema, notice how the generated sql migration file creates all new tables instead of modifying the existing schema. ### Current vs. Expected behavior I'd expect the logging to output something like this: ``` DEBUG [Better Auth]: PostgreSQL migration: Using schema 'public' (from search_path) DEBUG [Better Auth]: Found 4 table(s) in schema 'public': user, session, account, verification ``` I'd expect the generated sql file to generate `ALTER TABLE` queries instead of `CREATE TABLE` queries if user, session, account, or verification tables already exist in the "public" schema. ### What version of Better Auth are you using? 1.4.18 ### System info ```bash { "node": { "version": "v25.4.0", "env": "development" }, "packageManager": { "name": "npm", "version": "11.7.0" }, "frameworks": [ { "name": "react", "version": "^19.2.4" }, { "name": "astro", "version": "^5.17.1" } ], "databases": [ { "name": "pg", "version": "^8.18.0" } ], "betterAuth": { "version": "^1.4.18", "config": { "logger": { "level": "debug" }, "database": { "_events": {}, "_eventsCount": 0, "options": { "connectionString": "[REDACTED]", "max": 10, "min": 0, "maxUses": null, "allowExitOnIdle": false, "maxLifetimeSeconds": 0, "idleTimeoutMillis": 10000 }, "_clients": [], "_idle": [], "_expired": {}, "_pendingQueue": [], "ending": false, "ended": false }, "emailAndPassword": { "enabled": true }, "plugins": [] } } } ``` ### Which area(s) are affected? (Select all that apply) Other, Documentation ### Auth config (if applicable) ```typescript import { betterAuth } from "better-auth"; import { Pool } from "pg"; export const auth = betterAuth( { logger: { level: "debug" }, database: new Pool({ connectionString: process.env.DATABASE_URL, }), emailAndPassword: { enabled: true }, } ); ``` ### Additional context This is a regression caused by a feature added in PR: #5449 Issue: #5430 See this block of code run by the cli to query the current connection's `search_path` and assumes that the first "non-variable" schema is the one the user intended to direct to better-auth. https://github.com/better-auth/better-auth/blob/2468a2d9186f9a48621bd68e32ab2737b8b302f3/packages/better-auth/src/db/get-migration.ts#L99-L119 This code assumes it can filter out variables by removing any list items that start with a quote character (either `'` or `"`) followed by a `$` character. This works for some postgres instances. (I tested on a throwaway local instance) However, for reasons I don't understand, the postgres connection coming from Supabase will respond to the query `SHOW search_path;` with the response `"\$user", public, extensions` The better_auth code isn't written to expect the `\` character before the variable name thus the cli no longer defaults to the "public" schema. I think it's worth a little discussion on how this should be addressed. I'm not an expert on postgres schemas and connections strings, but my instinct is that this method of modifying the `search_path` to direct better-auth is brittle, hacky, and isn't defensively implemented currently. I'd wouldn't mind seeing it deprecated and not championed so strongly in the [docs](https://www.better-auth.com/docs/adapters/postgresql#use-a-non-default-schema). It's not apples to apples but I would take some inspiration from prisma on this front. They've figured out how to configure and manage targeting explicit postgres schemas in their queries and migrations. See this page for more details there: https://www.prisma.io/docs/orm/prisma-schema/data-model/multi-schema
GiteaMirror added the lockedenhancement labels 2026-04-17 19:44:05 -05:00
GiteaMirror commented 2026-04-17 19:44:06 -05:00
Author
Owner
Copy Link

@jackshunn commented on GitHub (Feb 18, 2026):

Sorry dosubot :/ This should be labeled as a bug.

<!-- gh-comment-id:3922374964 --> @jackshunn commented on GitHub (Feb 18, 2026): Sorry dosubot :/ This should be labeled as a bug.
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
adapter
astro
awaiting external contributor
blocked
breaking
breaking change
bug
c-devops
core
credentials
database
dependencies
devops
devtools
docs
documentation
duplicate
elysia
enhancement
enterprise
expo
express
fastify
good first issue
help wanted
hono
identity
infra
integration
invalid
javascript
locked
maintenance
need-more-information
needs: info
needs: repro
nextjs
nuxt
oauth
organization
P0
payments
perf
platform
plugin
pull-request
Mirrored from GitHub Pull Request
 question
ready
regression
remix
security
social-provider
solid
stale
svelte
tanstack-start
tracking
version-bump
vue
wontfix
No Label enhancement locked
Milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
GiteaMirror ninjasurge
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: github-starred/better-auth#28303
Delete Branch "%!s()"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?