sqlc-dev / sqlc

Generate type-safe code from SQL
https://sqlc.dev
MIT License
13.37k stars 803 forks source link

warn when using quotes or parse the SQL #3162

Open mpldr opened 9 months ago

mpldr commented 9 months ago

What do you want to change?

I have the following setup:

queries.sql

-- name: CheckDatabase :exec
SELECT
    COUNT(ID),
    COUNT(Destination),
    COUNT(Hits),
    COUNT(Owner)
FROM redirections;

schema.sql

CREATE TABLE "redirections" (
    "ID"    TEXT NOT NULL,
    "Destination"   TEXT NOT NULL,
    "Hits"  INTEGER NOT NULL DEFAULT 0,
    "Owner" TEXT NOT NULL,
    PRIMARY KEY("ID")
);

sqlc.json

{
    "version": "2",
    "sql": [{
        "schema": "./internal/datastore/db/schema.sql",
        "queries": "./internal/datastore/db/queries.sql",
        "engine": "sqlite",
        "gen": {
            "go": {
                "out": "db"
            }
        }
    }]
}

I think this should work, and in fact play.sqlc.dev agrees with me there. BUT

$ go run github.com/sqlc-dev/sqlc/cmd/sqlc@latest generate
# package 
internal/datastore/db/queries.sql:1:1: relation "redirections" does not exist
exit status 1

Thanks to @qbit, I learned about the use of quotations in the CREATE statement leading to all uses of the identifier having to be quoted. Leaving aside how … unideal … this limitation is, I would consider this unexpected behaviour, so I propose one of the following solutions:

Since I don't think this affects any specific database engine, or language backend, I have not selected any of them. If that's incorrect, I apologise.

What database engines need to be changed?

No response

What programming language backends need to be changed?

No response

apastuhov commented 6 months ago

Current behaviour actually affects usage of SQLC with SQLite. If initial table was created without quotes, but then developer decided to rename table and generate dump with sqlite3 db.sqlite .schema - new schema will have quotes, it will break SQLC codegen, and reason is not obvious.

Steps to reproduce:

sqlite3 db.sqlite3 'CREATE TABLE test (id INT);'
sqlite3 db.sqlite3 .schema
# output: CREATE TABLE test (id INT);

sqlite3 db.sqlite3 'ALTER TABLE test RENAME TO test_upd;'
sqlite3 db.sqlite3 .schema
# output: CREATE TABLE IF NOT EXISTS "test_upd" (id INT);

See more explanation here - https://sqlite.org/forum/info/6c48ec4e4cb99987e65afb50f1b40e8b5b70ddb34f07ec13ee2032bed487892a

I understand that it is specifics of sqlite, but actual name of db has no quotes, here is result of sqlite3 db.sqlite3 'select * from sqlite_master;' -header

type  | name     | tbl_name | rootpage | sql
table | test_upd | test_upd | 2        | CREATE TABLE "test_upd" (id INT)