PGLite search_path doesn't include public schema by default

[nissoh]

PGLite search_path Issue in Ponder Dev Mode

Summary

Raw SQL queries return 0 rows in ponder dev mode (PGLite) while Drizzle ORM queries work correctly. This affects @ponder/client SQL HTTP endpoints.

Environment

• ponder: 0.15.17
• Mode: dev (PGLite)

What works

• Drizzle ORM queries via db.select().from(schema.table)
• Production with Postgres

What fails

• Raw SQL via db.execute(sqlSELECT * FROM "TableName") returns 0 rows
• @ponder/client SQL HTTP endpoints return 0 rows

Root cause

PGLite's default search_path doesn't resolve unqualified table names to the public schema.

Drizzle ORM internally uses schema-qualified names, so it works. Raw SQL doesn't, so it fails silently.

Reproduction

// src/api/index.ts
import { db } from 'ponder:api'
import schema from 'ponder:schema'
import { count, sql } from 'ponder'

// This works - returns correct count
const drizzleResult = await db.select({ count: count() }).from(schema.myTable)

// This fails - returns 0
const rawResult = await db.execute(sql`SELECT COUNT(*) FROM "MyTable"`)

// This works - explicit schema
const fixedResult = await db.execute(sql`SELECT COUNT(*) FROM public."MyTable"`)

Workaround

Use schema-qualified table names in raw SQL:

SELECT * FROM public."TableName"

Why it's confusing

1. Drizzle queries work fine
2. Raw SQL fails silently (empty results, no error)
3. Same code works in production with Postgres
4. @ponder/client uses raw SQL internally, so all client queries returns empty

Suggested fix

Set search_path = public when initializing the PGLite connection in ponder dev mode.

[kyscott18]

Thanks for opening up such a detailed issue.

I am able to reproduce your issue, but Ponder already sets the search patch like you described

ponder/packages/core/src/database/index.ts

Line 638 in ab71016

(tx) => tx.execute(`SET search_path TO "${namespace.schema}"`),

.

There is a suspicious looking issue in the pglite GitHub electric-sql/pglite#626. I'll keep digging.