在使用sqlc生成gocode时遇到错误。

huangapple go评论79阅读模式
英文:

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

关键字和未引用的标识符不区分大小写。

...

引用标识符也使其区分大小写,而未引用的名称始终折叠为小写。例如,标识符FOOfoo"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

你需要在 firstNamelastName 周围添加引号。你的 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 *;

huangapple
  • 本文由 发表于 2023年2月23日 22:39:52
  • 转载请务必保留本文链接:https://go.coder-hub.com/75546308.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定