Skip to content

Instantly share code, notes, and snippets.

@maxsei
Last active June 13, 2024 03:30
Show Gist options
  • Save maxsei/74c4f58288cfc351ae773a6d521d8229 to your computer and use it in GitHub Desktop.
Save maxsei/74c4f58288cfc351ae773a6d521d8229 to your computer and use it in GitHub Desktop.
configuration for sqlc issue: Support multiple conflict targets in upsert statements for SQLite parser

output

$ sqlc verify
line 5:0 extraneous input 'ON' expecting {<EOF>, ';', ALTER_, ANALYZE_, ATTACH_, BEGIN_, COMMIT_, CREATE_, DEFAULT_, DELETE_, DETACH_, DROP_, END_, EXPLAIN_, INSERT_, PRAGMA_, REINDEX_, RELEASE_, REPLACE_, ROLLBACK_, SAVEPOINT_, SELECT_, UPDATE_, VACUUM_, VALUES_, WITH_}
line 7:0 extraneous input '<EOF>' expecting {';', ALTER_, ANALYZE_, ATTACH_, BEGIN_, COMMIT_, CREATE_, DEFAULT_, DELETE_, DETACH_, DROP_, END_, EXPLAIN_, INSERT_, PRAGMA_, REINDEX_, RELEASE_, REPLACE_, ROLLBACK_, SAVEPOINT_, SELECT_, UPDATE_, VACUUM_, VALUES_, WITH_}
# package
query.sql:1:1: extraneous input '<EOF>' expecting {';', ALTER_, ANALYZE_, ATTACH_, BEGIN_, COMMIT_, CREATE_, DEFAULT_, DELETE_, DETACH_, DROP_, END_, EXPLAIN_, INSERT_, PRAGMA_, REINDEX_, RELEASE_, REPLACE_, ROLLBACK_, SAVEPOINT_, SELECT_, UPDATE_, VACUUM_, VALUES_, WITH_}
error verifying: errored

schema.sql

PRAGMA foreign_keys = ON;

CREATE TABLE IF NOT EXISTS sets (
	id UUID PRIMARY KEY,
	name TEXT UNIQUE NOT NULL
);

CREATE TABLE IF NOT EXISTS amounts (
	id UUID PRIMARY KEY,
	amount_name_id UUID UNIQUE NOT NULL,
	amount INTEGER NOT NULL,
	set_id UUID NOT NULL,

	CONSTRAINT unique_amount_per_set UNIQUE (amount, set_id),
	CONSTRAINT unique_name_per_set UNIQUE (amount_name_id, set_id),
	FOREIGN KEY (amount_name_id) REFERENCES amount_names (id)
);

CREATE TABLE IF NOT EXISTS amount_names (
	id UUID PRIMARY KEY,
	name TEXT NOT NULL,
	set_id UUID NOT NULL,

	CONSTRAINT pk UNIQUE (name, set_id),
	FOREIGN KEY (set_id) REFERENCES sets (id)
)

queries.sql

-- name: PutAmount :one
INSERT INTO amounts (id, amount_name_id, amount, set_id)
  VALUES (?, ?, ?, ?)
ON CONFLICT (amount, set_id) DO NOTHING
ON CONFLICT (amount_name_id, set_id) DO NOTHING
RETURNING *;

sqlc.yaml

version: 2
sql:
  - engine: "sqlite"
    schema: "schema.sql"
    queries:
      - "query.sql"
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment