英文:
getting error while generating gocode with sqlc
问题
sqlc.yaml
version: "1"
packages:
- name: "db"
path: "/hcms-backend-go/db/sqlc/"
queries: "/hcms-backend-go/db/query/"
schema: "/hcms-backend-go/db/migrations/"
engine: "postgresql"
# 如果为true,则在生成的结构体中添加JSON标签。[默认为false]。
emit_json_tags: true
# 如果为true,则包括对预编译查询的支持。[默认为false]。
emit_prepared_queries: false
# 如果为true,则在生成的包中输出一个Querier接口。[默认为false]。
emit_interface: false
# 如果为true,则结构体名称将与表名相同。否则,sqlc将尝试将复数表名转换为单数。[默认为false]。
emit_exact_table_names: false
# camel表示驼峰命名法,pascal表示帕斯卡命名法,snake表示蛇形命名法,none表示使用数据库中的列名。[默认为none]。
json_tags_case_style: "camel"
user.sql
-- name: CreateUser :one
INSERT INTO "user" (
email, password, firstName, lastName
) VALUES (
$1, $2, $3, $4
)
RETURNING *;
000001_init_schema.up.sql
CREATE TABLE "user" (
"id" BIGSERIAL PRIMARY KEY,
"email" varchar(50) NOT NULL,
"password" varchar(70) NOT NULL,
"firstName" varchar(240) NOT NULL,
"lastName" varchar(240) NOT NULL,
"created_at" timestamptz NOT NULL DEFAULT 'now()',
"created_by" bigint NOT NULL,
"modified_at" timestamptz,
"modified_by" bigint,
"must_change_password" boolean NOT NULL DEFAULT 'true',
"can_change_password" boolean NOT NULL DEFAULT 'false'
);
执行命令sqlc generate
后出现错误:
hcms-backend-go/db/query/user.sql:3:20: 列"firstname"不存在
英文:
sqlc.yaml
version: "1"
packages:
- name: "db"
path: "/hcms-backend-go/db/sqlc/"
queries: "/hcms-backend-go/db/query/"
schema: "/hcms-backend-go/db/migrations/"
engine: "postgresql"
# If true, add JSON tags to generated structs. [Defaults to false].
emit_json_tags: true
# If true, include support for prepared queries. [Defaults to false].
emit_prepared_queries: false
# If true, output a Querier interface in the generated package. [Defaults to false].
emit_interface: false
# If true, struct names will mirror table names. Otherwise, sqlc attempts to singularize plural table names. [Defaults to false].
emit_exact_table_names: false
# camel for camelCase, pascal for PascalCase, snake for snake_case or none to use the column name in the DB. [Defaults to none].
json_tags_case_style: "camel"
user.sql
-- name: CreateUser :one
INSERT INTO "user" (
email, password, firstName, lastName
) VALUES (
$1, $2, $3, $4
)
RETURNING *;
000001_init_schema.up.sql
CREATE TABLE "user" (
"id" BIGSERIAL PRIMARY KEY,
"email" varchar(50) NOT NULL,
"password" varchar(70) NOT NULL,
"firstName" varchar(240) NOT NULL,
"lastName" varchar(240) NOT NULL,
"created_at" timestamptz NOT NULL DEFAULT 'now()',
"created_by" bigint NOT NULL,
"modified_at" timestamptz,
"modified_by" bigint,
"must_change_password" boolean NOT NULL DEFAULT 'true',
"can_change_password" boolean NOT NULL DEFAULT 'false'
);
after executing command: sqlc generate
getting error:
hcms-backend-go/db/query/user.sql:3:20: column "firstname" does not exist
答案1
得分: 2
user.sql
文件也需要像在init_schema
文件中一样对标识符加引号。原因是PostgreSQL(我假设你正在使用PostgreSQL作为后端)会将列名折叠为小写,除非它们被引号引起来,这就是为什么错误中的firstName
显示为firstname
的原因。
我建议您使用常规的命名规范(全部使用小写蛇形命名法),这样您就不需要引用标识符(除非您必须使用保留的名称/关键字)。
https://www.postgresql.org/docs/15/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS
关键字和未引用的标识符不区分大小写。
...
引用标识符也使其区分大小写,而未引用的名称始终折叠为小写。例如,标识符
FOO
,foo
和"foo"
在PostgreSQL中被视为相同,但"Foo"
和"FOO"
与这三个以及彼此不同。(在PostgreSQL中,未引用的名称折叠为小写与SQL标准不兼容,该标准规定未引用的名称应折叠为大写。因此,根据标准,foo
应等同于"FOO"
而不是"foo"
。如果您想编写可移植的应用程序,建议始终引用特定的名称或从不引用它。)
英文:
The user.sql
file needs to also quote the identifiers, just like you did in the init_schema file. The reason for this is that postgres (i'm assuming you're using postgres as the backend) will fold the column names to lowercase unless they are quoted, that's why firstName
is showing up as firstname
in the error.
I'd suggest you use the conventional casing (all lower_snake_case), then, you wouldn't need to quote identifiers at all (well, unless you have to use reserved names/keywords).
https://www.postgresql.org/docs/15/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS
> Key words and unquoted identifiers are case insensitive.
>
> . . .
>
> Quoting an identifier also makes it case-sensitive, whereas unquoted names are always folded to lower case. For example, the identifiers FOO
, foo
, and "foo"
are considered the same by PostgreSQL, but "Foo"
and "FOO"
are different from these three and each other. (The folding of unquoted names to lower case in PostgreSQL is incompatible with the SQL standard, which says that unquoted names should be folded to upper case. Thus, foo
should be equivalent to "FOO"
not "foo"
according to the standard. If you want to write portable applications you are advised to always quote a particular name or never quote it.)
答案2
得分: 1
你需要在 firstName
和 lastName
周围添加引号。你的 user.sql
文件应该如下所示:
-- name: CreateUser :one
INSERT INTO "user" (
email, password, "firstName", "lastName"
) VALUES (
$1, $2, $3, $4
)
RETURNING *;
英文:
You need to add quotes around firstName
and lastName
. Your user.sql
file should look like:
-- name: CreateUser :one
INSERT INTO "user" (
email, password, "firstName", "lastName"
) VALUES (
$1, $2, $3, $4
)
RETURNING *;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论