"Relation does not exist" error when using SQLC with goose migrations

[rob2244]

Version

1.26.0

What happened?

I'm integrating sqlc into an existing golang project using Goose. When I run sqlc generate I'm getting the following error:

migrations/0036_source_table_embeddings.sql:1:1: relation "global_metric" does not exist

The table does exist and is in an earlier migration file. Strangely if remove the lines referencing that table in the migration file referenced in the error, sqlc generate runs successfully.

The table in question is created in migration 5 and if I use it in any migration before migration 9 sqlc generate runs successfully, if I include it in any migration after migration 9 I get the above error of the reference not being found. The table is not deleted or modified in any way in migration 9.

Relevant log output

migrations/0036_source_table_embeddings.sql:1:1: relation "global_metric" does not exist

Database schema

-- Migration 5 (table is introduced)
-- +goose Up
CREATE TABLE IF NOT EXISTS client_portal.public.global_metric (
    id SERIAL primary key,
    metric varchar NOT NULL,
    user_id uuid NOT NULL,
    org_id uuid NOT NULL,
    formula varchar NOT NULL,
    required_metrics varchar,
    "description" varchar,
    tags varchar,
    tags_query tsquery,
    created_date timestamp DEFAULT (now() at time zone 'utc'),
    updated_date timestamp DEFAULT (now() at time zone 'utc'),
    UNIQUE(org_id, user_id, metric)
);

-- +goose Down
DROP TABLE IF EXISTS client_portal.public.global_metric;

-- Migration 9 (Last migration where I can include the table and it runs successfully)
-- +goose Up
CREATE TABLE IF NOT EXISTS client_portal.public.global_metric (
    id SERIAL primary key,
    metric varchar NOT NULL,
    user_id uuid NOT NULL,
    org_id uuid NOT NULL,
    formula varchar NOT NULL,
    required_metrics varchar,
    "description" varchar,
    tags varchar,
    tags_query tsquery,
    created_date timestamp DEFAULT (now() at time zone 'utc'),
    updated_date timestamp DEFAULT (now() at time zone 'utc'),
    UNIQUE(org_id, user_id, metric)
);

-- +goose Down
DROP TABLE IF EXISTS client_portal.public.global_metric;

-- Migration 10 (starts failing here if I include the table)
-- +goose Up
CREATE TABLE IF NOT EXISTS client_portal.public.global_concept (
    id SERIAL primary key,
    user_id uuid NOT NULL,
    org_id uuid NOT NULL,
    concept varchar NOT NULL,
    tags varchar,
    tags_query tsquery,
    context varchar,
    description varchar,
    created_date timestamp DEFAULT (now() at time zone 'utc'),
    updated_date timestamp DEFAULT (now() at time zone 'utc')
);

CREATE INDEX IF NOT EXISTS global_concept_org_id_user_id_tags_idx ON global_concept ("org_id", "user_id", "tags");

-- +goose Down
DROP TABLE IF EXISTS client_portal.public.global_concept;
DROP INDEX IF EXISTS global_concept_org_id_user_id_tags_idx;

-- Migration 36 (first migration after migration 10 that references the table, this is where it's failing)
-- +goose Up
ALTER TABLE client_portal.public.glossary ADD COLUMN IF NOT EXISTS term_embedding vector(1024);
CREATE INDEX IF NOT EXISTS glossary_term_embedding_idx ON client_portal.public.glossary USING hnsw ("term_embedding" vector_cosine_ops);

ALTER TABLE client_portal.public.global_metric ADD COLUMN IF NOT EXISTS metric_embedding vector(1024);
CREATE INDEX IF NOT EXISTS global_metric_embedding_idx ON client_portal.public.global_metric USING hnsw ("metric_embedding" vector_cosine_ops);

ALTER TABLE client_portal.public.global_concept ADD COLUMN IF NOT EXISTS concept_embedding vector(1024);
CREATE INDEX IF NOT EXISTS global_concept_embedding_idx ON client_portal.public.global_concept USING hnsw ("concept_embedding" vector_cosine_ops);

-- +goose Down
DROP INDEX IF EXISTS glossary_term_embedding_idx;
ALTER TABLE client_portal.public.glossary DROP COLUMN IF EXISTS term_embedding;

DROP INDEX IF EXISTS global_metric_embedding_idx;
ALTER TABLE client_portal.public.global_metric DROP COLUMN IF EXISTS metric_embedding;

DROP INDEX IF EXISTS global_concept_embedding_idx;
ALTER TABLE client_portal.public.global_concept DROP COLUMN IF EXISTS concept_embedding;

SQL queries

N/A as the issue is the schema

Configuration

version: "2"
sql:
  - engine: "postgresql"
    queries: "/pkg/dal/queries"
    schema: "migrations/"
    gen:
      go:
        package: "dal"
        out: "/pkg/dal/sqlc"
        sql_package: "pgx/v5"

Playground URL

No response

What operating system are you using?

Linux

What database engines are you using?

PostgreSQL

What type of code are you generating?

Go

[GuiFernandess7]

This is happening to me too.

[rob2244]

I think I've found the issue: when the migrations are loaded if you're using numbers for the migrations, e.g. 001, 002, ..., etc. The files don't get sorted correctly passed 009 and so 0010 is run before 001.

You can see this in the parseCatalog() function in compile.go (see image below)
if you put a break point were the files are being loaded you can see the sort order is incorrect. Not sure if this also happens when using dates for migrations.

Not sure who the best person is to mention here, but I'm happy to create a pr for this. Should I use a regex to detect the file format name? Or is there a way you would rather handle this?

[rob2244]

Digging into this a little more the culprit seems to be the os.ReadDir call in the sqlpath.Glob() function. It's sorting the files by name and doesn't handle the numbering of the migrations correctly

[rob2244]

Looks like this is an issue of documentation, there is a warning for golang-migrate for number based migrations but none for goose:

[noahjamison]

Quick Fix

Leaving this here for anyone who runs into this and needs a quick fix, one that avoids having to rename all your existing migrations:

1. In sqlc.yaml, set the schema to a single file (instead of the goose migrations folder)
   For example:

    schema:
      - "schema.sql"

2. SImply dump your SQL schema into that file. For postgres users, that looks like:

PGPASSWORD=<PASSWORD> pg_dump -h localhost -p 5432 -s -U <USER> <DATABASE> > schema.sql

[VinewZ]

Noah's quick fix didn't work for me, but changing the goose comment from:
-- +goose down to -- +goose Down worked.
Notice the capital D

Using SQLITE3
Schema:

-- +goose up
CREATE TABLE books (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    author TEXT NOT NULL,
    pages INTEGER NOT NULL
);

CREATE TABLE book_text (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    book_id INTEGER NOT NULL,              
    chapter INTEGER NOT NULL,
    sentence TEXT NOT NULL,
    status TEXT DEFAULT 'pending',     
    FOREIGN KEY (book_id) REFERENCES books (id) ON DELETE CASCADE
);

-- +goose Down
DROP TABLE book_text;
DROP TABLE books;

Query:

-- name: GetBookIDByTitle :one
SELECT id FROM books WHERE title = ?;

Config:

version: "2"
sql:
  - engine: "sqlite"
    queries: "./internal/sql/queries"
    schema: "./internal/sql/schema"
    gen:
      go:
        package: "db"
        out: "db"

sqlc.mp4