Skip to content

Problem with using multiple schemas with queries #3883

Open
@abtris

Description

@abtris

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

Metadata

Metadata

Assignees

No one assigned

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions