SQLite: error parsing schema with `UNIQUE`

[sybrenstuvel]

Version

1.25.0

What happened?

I'm trying to generate code for an existing SQLite database. The schema.sql file was generated simply by asking SQLite for the schema:

echo .schema --indent | sqlite3 my-database.sqlite > internal/manager/persistence/sqlc/schema.sql

The database schema below is what was generated with the command above, but then trimmed down to have only the one table that causes this error.

Running sqlc generate produced lots of errors about extraneous input and mismatched input. The database is from an actually in-use system that I want to migrate to sqlc, so as far as sqlite is concerned, the schema SQL is valid.

Relevant log output

line 5:25 extraneous input 'DEFAULT' expecting {')', ','}
line 5:33 mismatched input '""' expecting VALUES_
line 6:29 mismatched input '""' expecting VALUES_
line 7:29 mismatched input '""' expecting VALUES_
line 8:27 mismatched input '0' expecting VALUES_
line 9:30 mismatched input '50' expecting VALUES_
line 10:31 mismatched input '""' expecting VALUES_
line 14:34 mismatched input '""' expecting VALUES_

Database schema

CREATE TABLE `tasks`(
  `id` integer,
  `created_at` datetime,
  `updated_at` datetime,
  `uuid` char(36) UNIQUE DEFAULT "",
  `name` varchar(64) DEFAULT "",
  `type` varchar(32) DEFAULT "",
  `job_id` integer DEFAULT 0,
  `priority` smallint DEFAULT 50,
  `status` varchar(16) DEFAULT "",
  `worker_id` integer,
  `last_touched_at` datetime,
  `commands` jsonb,
  `activity` varchar(255) DEFAULT "",
  PRIMARY KEY(`id`)
);

SQL queries

-- name: GetTask :one
SELECT * FROM tasks
WHERE uuid = $1 LIMIT 1;

Configuration

version: "2"
sql:
  - engine: "sqlite"
    schema: "internal/manager/persistence/sqlc/schema.sql"
    queries: "internal/manager/persistence/sqlc/query.sql"
    gen:
      go:
        package: "tutorial"
        out: "internal/manager/persistence/sqlc/tutorial"

Playground URL

Unfortunately this issue doesn't seem to be reproducible on the playground, as that chokes in a different way on the schema.sql file: https://play.sqlc.dev/p/95f8dab5469e323ed02269a3757eef4b6170d635e6a7f584079a8b57a39dbbd6

sqlc generate failed.
# package 
schema.sql:1:15: syntax error at or near "`"

What operating system are you using?

Windows

What database engines are you using?

SQLite

What type of code are you generating?

Go

[jakoguta]

@sybrenstuvel in your case sqlc does not like seeing the "" double quotes. Try this workaround:

CREATE TABLE tasks(
  id integer,
  created_at datetime,
  updated_at datetime,
  uuid char(36) UNIQUE DEFAULT '',
  name varchar(64) DEFAULT '',
  type varchar(32) DEFAULT '',
  job_id integer DEFAULT 0,
  priority smallint DEFAULT 50,
  status varchar(16) DEFAULT '',
  worker_id integer,
  last_touched_at datetime,
  commands jsonb,
  activity varchar(255) DEFAULT '',
  PRIMARY KEY(id)
);

-- name: GetTask :one
SELECT * FROM tasks
WHERE uuid = ? LIMIT 1;

It requires some work to clean up your schema, but you will have your codes generated.

[sybrenstuvel]

Thanks, this helps!