Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Problem with using multiple schemas with queries #3883

Open
abtris opened this issue Mar 21, 2025 · 0 comments
Open

Problem with using multiple schemas with queries #3883

abtris opened this issue Mar 21, 2025 · 0 comments
Labels

Comments

@abtris
Copy link

abtris commented Mar 21, 2025

Version

1.28.0

What happened?

I tried make example on playground as minimal for problem that I have. We have 3 schemas that was creating with 20+ migrations. We started using sqlc and generated models work and everything work for schema news as it's default schema, but isn't. I tried debug AST and looks that news schema is set as default and works only without prefix in queries that cause problem that we use public default schema where is majority of tables and I need use whole path for table in queries but that throw error as in playground example. query.sql:1:1: relation "authors" does not exist.

Only working queries are

-- name: GetAuthor :one
SELECT * FROM public.authors
WHERE id = $1 LIMIT 1;


-- name: GetPublisher :one
SELECT * FROM public.publishers
WHERE id = $1 LIMIT 1;

instead correct ones

-- name: GetAuthor :one
SELECT * FROM news.authors
WHERE id = $1 LIMIT 1;


-- name: GetPublisher :one
SELECT * FROM public.publishers
WHERE id = $1 LIMIT 1;

Relevant log output

([]interface {}) (len=1 cap=1) {
 (*catalog.Catalog)(0x140006c69a0)({
  Comment: (string) "",
  DefaultSchema: (string) (len=6) "public",
  Name: (string) "",
  Schemas: ([]*catalog.Schema) (len=6 cap=8) {
   (*catalog.Schema)(0x140006c6a10)({
    Name: (string) (len=6) "public",
    Tables: ([]*catalog.Table) (len=8 cap=8) {
     (*catalog.Table)(0x14000a10900)({
      Rel: (*ast.TableName)(0x140009daae0)({
       Catalog: (string) "",
       Schema: (string) "",
       Name: (string) (len=17) "authors"
      }),

Database schema

CREATE SCHEMA news;

SET search_path TO news;

CREATE TABLE authors (
  id   BIGSERIAL PRIMARY KEY,
  name text      NOT NULL,
  bio  text
);

SET search_path TO public;

CREATE TABLE publishers (
  id   BIGSERIAL PRIMARY KEY,
  name text      NOT NULL,
  bio  text  
);

SQL queries

-- name: GetAuthor :one
SELECT * FROM news.authors
WHERE id = $1 LIMIT 1;


-- name: GetPublisher :one
SELECT * FROM public.publishers
WHERE id = $1 LIMIT 1;

Configuration

{
  "version": "2",
  "sql": [{
    "schema": "schema.sql",
    "queries": "query.sql",
    "engine": "postgresql",
    "gen": {
      "go": {
        "out": "db"
      }
    }
  }]
}

Playground URL

https://play.sqlc.dev/p/b65c5f24c22d216b30dd0825b38e5560943f40ce7fe9bc3be7ddea1972c9becb

What operating system are you using?

macOS

What database engines are you using?

PostgreSQL

What type of code are you generating?

Go

@abtris abtris added the bug Something isn't working label Mar 21, 2025
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
Projects
None yet
Development

No branches or pull requests

1 participant