warn when using quotes or parse the SQL

@qbit

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:

actually parse the name of the identifier (preferred)
warn the user about this pitfall (if not in the CLI output, then in the docs)

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

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)

fix (engine/sqlite): normalize table names (sqlc-dev#3162)

fix (engine/sqlite): normalize table names (#3162) (#3657)

fix (engine/sqlite): normalize table names (sqlc-dev#3162) (sqlc-dev#…

Navigation Menu

Repository navigation

warn when using quotes or parse the SQL #3162

What do you want to change?

What database engines need to be changed?

What programming language backends need to be changed?

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

warn when using quotes or parse the SQL #3162

Description

What do you want to change?

What database engines need to be changed?

What programming language backends need to be changed?

Activity

apastuhov commented on May 6, 2024

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions