getting error while generating gocode with sqlc

Question

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

Answer 1

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.)

Answer 2

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 *;