Basic usage - relation "x" does not exist

[eyal-solomon1]

Version

1.26.0

What happened?

Getting relation "x" does not exist for every query I mention in my queries .sql files

Relevant log output

query.sql:1:1: relation "models" does not exist
query.sql:15:1: relation "models" does not exist
query.sql:20:1: relation "models" does not exist
query.sql:28:1: relation "models" does not exist

Database schema

CREATE TABLE `transcription_jobs` (
  `id` integer PRIMARY KEY,
  `org_id` integer,
  `user_id` integer,
  `started_at` timestamp,
  `ended_at` timestamp,
  `tokens` integer,
  FOREIGN KEY (`org_id`) REFERENCES `organization` (`org_id`)
);

CREATE TABLE `organization` (
  `org_id` integer PRIMARY KEY
);

CREATE TABLE `models` (
  `id` integer PRIMARY KEY,
  `model_name` VARCHAR(255),
  `balance` integer,
  `org_id` integer,
  FOREIGN KEY (`org_id`) REFERENCES `organization` (`org_id`)
);

SQL queries

-- name: CreateModel :one
INSERT INTO models (
    id,
    model_name,
    org_id,
    balance
) VALUES (
    :id,
    :model_name,
    :org_id,
    :balance
) RETURNING *;

-- name: GetModel :one
SELECT * FROM models
WHERE id = :id
LIMIT 1;

-- name: UpdateModel :one
UPDATE models
SET model_name = :model_name,
        org_id = :org_id,
        balance = :balance
WHERE id = :id
RETURNING *;

-- name: DeleteModel :one
DELETE FROM models
WHERE id = :id
RETURNING *;

Configuration

version: "2"
plugins:
  - name: "py"
    wasm:
      url: "https://downloads.sqlc.dev/plugin/sqlc-gen-python_1.0.0.wasm"
      sha256: "aca83e1f59f8ffdc604774c2f6f9eb321a2b23e07dc83fc12289d25305fa065b"
sql:
  - schema: "path_to_folder"
    queries: "path_to_folder"
    engine: "sqlite"
    codegen:
      - plugin: "py"
        out: "src"
        options:
          package: "foo"
          emit_sync_querier: true
          emit_async_querier: true
          query_parameter_limit: 5

Playground URL

https://play.sqlc.dev/p/7150dcc1ccab07d5dcca915b08525b015d4f94968345157779d016a74eb04279

What operating system are you using?

macOS

What database engines are you using?

SQLite

What type of code are you generating?

Python

[dbhoot]

@eyal-solomon1 I'm guessing you're using the wrong syntax. Also, be careful on macs. It's the only platform I see which uses ` when you meant '.

My sqlite example generates go just fine using your schema

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

[apastuhov]

@dbhoot maybe syntax is not ideal from SQLC point of view, but from SQLite point of view it is ok. Seems that issue is related to quotes, because in your example there are no quotes in schema. Also, pls check my comment under another issue - https://github.com/sqlc-dev/sqlc/issues/3162#issuecomment-2097077192

[vn7n24fzkq]

I encountered the same issue when using SQLite and performing migrations with Atlas.

The migrations generated by Atlas include quotes, which cause sqlc to fail during parsing. When I run sqlc generate, it throws an error: relation "x" does not exist.

If I manually remove the quotes, the generation works fine. However, this requires me to rehash the migration, which is quite frustrating. Shouldn't sqlc handle single quotes properly in this case?

[deckchairlabs]

I'm also facing this issue when using Atlas for managing migrations. I would expect the parser to be able to handle quotes, as this is often needed to "escape" particular table or column names that may conflict with a built in statement CASE for example

[lumosminima]

I'm new to sqlc and was testing out to see if it's a good fit using an existing sqlite db's schema via sqlite3 data.db .schema > schema.sql which included backticks by default on table names (data.db is generated by some lib which uses backticks) and caused this same error. If I remove all the quotes/backticks, it works but I will have to do that manually everytime or use some preprocessor. So +1 for handling this by default.

[unbiased-dev]

I would like to bump this as it's also the same atlas + sqlc combination that I'm trying to use and running into the same issue.