This repository was archived by the owner on Jun 5, 2025. It is now read-only.
Repository navigation
This repository was archived by the owner on Jun 5, 2025. It is now read-only.
Evaluate SQL approach - sqlc or raw approach #512
Description
Activity
Background
sqlc takes SQL queriers and generates functions to execute the queries. We have succesfully used it to automatically generate
selectqueries but we haven't been able to use it forinsertorupdate. As an example here is the provided SQL query to insert intopromptstable and the sqlc generated python code.-- name: create_prompt :one INSERT INTO prompts ( id, timestamp, provider, request, type ) VALUES ( ?, ?, ?, ?, ? ) RETURNING *
CREATE_PROMPT = """-- name: create_prompt \\:one INSERT INTO prompts ( id, timestamp, provider, request, type ) VALUES ( :p1, :p2, :p3, :p4, :p5 ) RETURNING id, timestamp, provider, request, type """" def create_prompt(self, *, id: Any, timestamp: Any, provider: Optional[Any], request: Any, type: Any) -> None: self._conn.execute(sqlalchemy.text(CREATE_PROMPT), { "p1": id, "p2": timestamp, "p3": provider, "p4": request, "p5": type, })
Analysis
No matter what I do I am getting the following error when trying to
insertsomething using sqlc auto-generated codeerror=(sqlite3.ProgrammingError) Incorrect number of bindings supplied. The current statement uses 5, and there are 0 supplied. [SQL: -- name: create_prompt :one INSERT INTO prompts ( id, timestamp, provider, request, type ) VALUES ( ?, ?, ?, ?, ? ) RETURNING id, timestamp, provider, request, type ] (Background on this error at: https://sqlalche.me/e/20/f405)The error comes from the fact that the auto-generated code is trying to use a
dictas input, note thep*as dict keys and the attributes of the function as dict values.def create_prompt(self, *, id: Any, timestamp: Any, provider: Optional[Any], request: Any, type: Any) -> None: self._conn.execute(sqlalchemy.text(CREATE_PROMPT), { "p1": id, "p2": timestamp, "p3": provider, "p4": request, "p5": type, })
For the above to work,
CREATE_PROMPTshould be the following. Note thep*instead of?which is what the auto-generated code contains.CREATE_PROMPT = """-- name: create_prompt \\:one INSERT INTO prompts ( id, timestamp, provider, request, type ) VALUES ( :p1, :p2, :p3, :p4, :p5 ) RETURNING id, timestamp, provider, request, type """"
On the other hand, according to sqlc docs we should be able to use
$*on our query as placeholders. However, they're not accepted by sqlite. The following error happens atsqlc generatewhen using$1, $2, ...instead of?, ?, ...% sqlc generate line 30:2 no viable alternative at input 'VALUES (\n $' line 30:2 extraneous input '$' expecting {'(', '+', '-', '~', ABORT_, ACTION_, ADD_, AFTER_, ALL_, ALTER_, ANALYZE_, AND_, AS_, ASC_, ATTACH_, AUTOINCREMENT_, BEFORE_, BEGIN_, BETWEEN_, BY_, CASCADE_, CASE_, CAST_, CHECK_, COLLATE_, COLUMN_, COMMIT_, CONFLICT_, CONSTRAINT_, CREATE_, CROSS_, CURRENT_DATE_, CURRENT_TIME_, CURRENT_TIMESTAMP_, DATABASE_, DEFAULT_, DEFERRABLE_, DEFERRED_, DELETE_, DESC_, DETACH_, DISTINCT_, DROP_, EACH_, ELSE_, END_, ESCAPE_, EXCEPT_, EXCLUSIVE_, EXISTS_, EXPLAIN_, FAIL_, FOR_, FOREIGN_, FROM_, FULL_, GLOB_, GROUP_, HAVING_, IF_, IGNORE_, IMMEDIATE_, IN_, INDEX_, INDEXED_, INITIALLY_, INNER_, INSERT_, INSTEAD_, INTERSECT_, INTO_, IS_, ISNULL_, JOIN_, KEY_, LEFT_, LIKE_, LIMIT_, MATCH_, NATURAL_, NO_, NOT_, NOTNULL_, NULL_, OF_, OFFSET_, ON_, OR_, ORDER_, OUTER_, PLAN_, PRAGMA_, PRIMARY_, QUERY_, RAISE_, RECURSIVE_, REFERENCES_, REGEXP_, REINDEX_, RELEASE_, RENAME_, REPLACE_, RESTRICT_, RETURNING_, RIGHT_, ROLLBACK_, ROW_, ROWS_, SAVEPOINT_, SELECT_, SET_, STRICT_, TABLE_, TEMP_, TEMPORARY_, THEN_, TO_, TRANSACTION_, TRIGGER_, UNION_, UNIQUE_, UPDATE_, USING_, VACUUM_, VALUES_, VIEW_, VIRTUAL_, WHEN_, WHERE_, WITH_, WITHOUT_, FIRST_VALUE_, OVER_, PARTITION_, RANGE_, PRECEDING_, UNBOUNDED_, CURRENT_, FOLLOWING_, CUME_DIST_, DENSE_RANK_, LAG_, LAST_VALUE_, LEAD_, NTH_VALUE_, NTILE_, PERCENT_RANK_, RANK_, ROW_NUMBER_, GENERATED_, ALWAYS_, STORED_, TRUE_, FALSE_, WINDOW_, NULLS_, FIRST_, LAST_, FILTER_, GROUPS_, EXCLUDE_, IDENTIFIER, NUMERIC_LITERAL, NUMBERED_BIND_PARAMETER, NAMED_BIND_PARAMETER, STRING_LITERAL, BLOB_LITERAL} line 30:6 extraneous input '$' expecting {'(', '+', '-', '~', ABORT_, ACTION_, ADD_, AFTER_, ALL_, ALTER_, ANALYZE_, AND_, AS_, ASC_, ATTACH_, AUTOINCREMENT_, BEFORE_, BEGIN_, BETWEEN_, BY_, CASCADE_, CASE_, CAST_, CHECK_, COLLATE_, COLUMN_, COMMIT_, CONFLICT_, CONSTRAINT_, CREATE_, CROSS_, CURRENT_DATE_, CURRENT_TIME_, CURRENT_TIMESTAMP_, DATABASE_, DEFAULT_, DEFERRABLE_, DEFERRED_, DELETE_, DESC_, DETACH_, DISTINCT_, DROP_, EACH_, ELSE_, END_, ESCAPE_, EXCEPT_, EXCLUSIVE_, EXISTS_, EXPLAIN_, FAIL_, FOR_, FOREIGN_, FROM_, FULL_, GLOB_, GROUP_, HAVING_, IF_, IGNORE_, IMMEDIATE_, IN_, INDEX_, INDEXED_, INITIALLY_, INNER_, INSERT_, INSTEAD_, INTERSECT_, INTO_, IS_, ISNULL_, JOIN_, KEY_, LEFT_, LIKE_, LIMIT_, MATCH_, NATURAL_, NO_, NOT_, NOTNULL_, NULL_, OF_, OFFSET_, ON_, OR_, ORDER_, OUTER_, PLAN_, PRAGMA_, PRIMARY_, QUERY_, RAISE_, RECURSIVE_, REFERENCES_, REGEXP_, REINDEX_, RELEASE_, RENAME_, REPLACE_, RESTRICT_, RETURNING_, RIGHT_, ROLLBACK_, ROW_, ROWS_, SAVEPOINT_, SELECT_, SET_, STRICT_, TABLE_, TEMP_, TEMPORARY_, THEN_, TO_, TRANSACTION_, TRIGGER_, UNION_, UNIQUE_, UPDATE_, USING_, VACUUM_, VALUES_, VIEW_, VIRTUAL_, WHEN_, WHERE_, WITH_, WITHOUT_, FIRST_VALUE_, OVER_, PARTITION_, RANGE_, PRECEDING_, UNBOUNDED_, CURRENT_, FOLLOWING_, CUME_DIST_, DENSE_RANK_, LAG_, LAST_VALUE_, LEAD_, NTH_VALUE_, NTILE_, PERCENT_RANK_, RANK_, ROW_NUMBER_, GENERATED_, ALWAYS_, STORED_, TRUE_, FALSE_, WINDOW_, NULLS_, FIRST_, LAST_, FILTER_, GROUPS_, EXCLUDE_, IDENTIFIER, NUMERIC_LITERAL, NUMBERED_BIND_PARAMETER, NAMED_BIND_PARAMETER, STRING_LITERAL, BLOB_LITERAL} line 30:10 extraneous input '$' expecting {'(', '+', '-', '~', ABORT_, ACTION_, ADD_, AFTER_, ALL_, ALTER_, ANALYZE_, AND_, AS_, ASC_, ATTACH_, AUTOINCREMENT_, BEFORE_, BEGIN_, BETWEEN_, BY_, CASCADE_, CASE_, CAST_, CHECK_, COLLATE_, COLUMN_, COMMIT_, CONFLICT_, CONSTRAINT_, CREATE_, CROSS_, CURRENT_DATE_, CURRENT_TIME_, CURRENT_TIMESTAMP_, DATABASE_, DEFAULT_, DEFERRABLE_, DEFERRED_, DELETE_, DESC_, DETACH_, DISTINCT_, DROP_, EACH_, ELSE_, END_, ESCAPE_, EXCEPT_, EXCLUSIVE_, EXISTS_, EXPLAIN_, FAIL_, FOR_, FOREIGN_, FROM_, FULL_, GLOB_, GROUP_, HAVING_, IF_, IGNORE_, IMMEDIATE_, IN_, INDEX_, INDEXED_, INITIALLY_, INNER_, INSERT_, INSTEAD_, INTERSECT_, INTO_, IS_, ISNULL_, JOIN_, KEY_, LEFT_, LIKE_, LIMIT_, MATCH_, NATURAL_, NO_, NOT_, NOTNULL_, NULL_, OF_, OFFSET_, ON_, OR_, ORDER_, OUTER_, PLAN_, PRAGMA_, PRIMARY_, QUERY_, RAISE_, RECURSIVE_, REFERENCES_, REGEXP_, REINDEX_, RELEASE_, RENAME_, REPLACE_, RESTRICT_, RETURNING_, RIGHT_, ROLLBACK_, ROW_, ROWS_, SAVEPOINT_, SELECT_, SET_, STRICT_, TABLE_, TEMP_, TEMPORARY_, THEN_, TO_, TRANSACTION_, TRIGGER_, UNION_, UNIQUE_, UPDATE_, USING_, VACUUM_, VALUES_, VIEW_, VIRTUAL_, WHEN_, WHERE_, WITH_, WITHOUT_, FIRST_VALUE_, OVER_, PARTITION_, RANGE_, PRECEDING_, UNBOUNDED_, CURRENT_, FOLLOWING_, CUME_DIST_, DENSE_RANK_, LAG_, LAST_VALUE_, LEAD_, NTH_VALUE_, NTILE_, PERCENT_RANK_, RANK_, ROW_NUMBER_, GENERATED_, ALWAYS_, STORED_, TRUE_, FALSE_, WINDOW_, NULLS_, FIRST_, LAST_, FILTER_, GROUPS_, EXCLUDE_, IDENTIFIER, NUMERIC_LITERAL, NUMBERED_BIND_PARAMETER, NAMED_BIND_PARAMETER, STRING_LITERAL, BLOB_LITERAL} line 30:14 extraneous input '$' expecting {'(', '+', '-', '~', ABORT_, ACTION_, ADD_, AFTER_, ALL_, ALTER_, ANALYZE_, AND_, AS_, ASC_, ATTACH_, AUTOINCREMENT_, BEFORE_, BEGIN_, BETWEEN_, BY_, CASCADE_, CASE_, CAST_, CHECK_, COLLATE_, COLUMN_, COMMIT_, CONFLICT_, CONSTRAINT_, CREATE_, CROSS_, CURRENT_DATE_, CURRENT_TIME_, CURRENT_TIMESTAMP_, DATABASE_, DEFAULT_, DEFERRABLE_, DEFERRED_, DELETE_, DESC_, DETACH_, DISTINCT_, DROP_, EACH_, ELSE_, END_, ESCAPE_, EXCEPT_, EXCLUSIVE_, EXISTS_, EXPLAIN_, FAIL_, FOR_, FOREIGN_, FROM_, FULL_, GLOB_, GROUP_, HAVING_, IF_, IGNORE_, IMMEDIATE_, IN_, INDEX_, INDEXED_, INITIALLY_, INNER_, INSERT_, INSTEAD_, INTERSECT_, INTO_, IS_, ISNULL_, JOIN_, KEY_, LEFT_, LIKE_, LIMIT_, MATCH_, NATURAL_, NO_, NOT_, NOTNULL_, NULL_, OF_, OFFSET_, ON_, OR_, ORDER_, OUTER_, PLAN_, PRAGMA_, PRIMARY_, QUERY_, RAISE_, RECURSIVE_, REFERENCES_, REGEXP_, REINDEX_, RELEASE_, RENAME_, REPLACE_, RESTRICT_, RETURNING_, RIGHT_, ROLLBACK_, ROW_, ROWS_, SAVEPOINT_, SELECT_, SET_, STRICT_, TABLE_, TEMP_, TEMPORARY_, THEN_, TO_, TRANSACTION_, TRIGGER_, UNION_, UNIQUE_, UPDATE_, USING_, VACUUM_, VALUES_, VIEW_, VIRTUAL_, WHEN_, WHERE_, WITH_, WITHOUT_, FIRST_VALUE_, OVER_, PARTITION_, RANGE_, PRECEDING_, UNBOUNDED_, CURRENT_, FOLLOWING_, CUME_DIST_, DENSE_RANK_, LAG_, LAST_VALUE_, LEAD_, NTH_VALUE_, NTILE_, PERCENT_RANK_, RANK_, ROW_NUMBER_, GENERATED_, ALWAYS_, STORED_, TRUE_, FALSE_, WINDOW_, NULLS_, FIRST_, LAST_, FILTER_, GROUPS_, EXCLUDE_, IDENTIFIER, NUMERIC_LITERAL, NUMBERED_BIND_PARAMETER, NAMED_BIND_PARAMETER, STRING_LITERAL, BLOB_LITERAL} line 30:18 extraneous input '$' expecting {'(', '+', '-', '~', ABORT_, ACTION_, ADD_, AFTER_, ALL_, ALTER_, ANALYZE_, AND_, AS_, ASC_, ATTACH_, AUTOINCREMENT_, BEFORE_, BEGIN_, BETWEEN_, BY_, CASCADE_, CASE_, CAST_, CHECK_, COLLATE_, COLUMN_, COMMIT_, CONFLICT_, CONSTRAINT_, CREATE_, CROSS_, CURRENT_DATE_, CURRENT_TIME_, CURRENT_TIMESTAMP_, DATABASE_, DEFAULT_, DEFERRABLE_, DEFERRED_, DELETE_, DESC_, DETACH_, DISTINCT_, DROP_, EACH_, ELSE_, END_, ESCAPE_, EXCEPT_, EXCLUSIVE_, EXISTS_, EXPLAIN_, FAIL_, FOR_, FOREIGN_, FROM_, FULL_, GLOB_, GROUP_, HAVING_, IF_, IGNORE_, IMMEDIATE_, IN_, INDEX_, INDEXED_, INITIALLY_, INNER_, INSERT_, INSTEAD_, INTERSECT_, INTO_, IS_, ISNULL_, JOIN_, KEY_, LEFT_, LIKE_, LIMIT_, MATCH_, NATURAL_, NO_, NOT_, NOTNULL_, NULL_, OF_, OFFSET_, ON_, OR_, ORDER_, OUTER_, PLAN_, PRAGMA_, PRIMARY_, QUERY_, RAISE_, RECURSIVE_, REFERENCES_, REGEXP_, REINDEX_, RELEASE_, RENAME_, REPLACE_, RESTRICT_, RETURNING_, RIGHT_, ROLLBACK_, ROW_, ROWS_, SAVEPOINT_, SELECT_, SET_, STRICT_, TABLE_, TEMP_, TEMPORARY_, THEN_, TO_, TRANSACTION_, TRIGGER_, UNION_, UNIQUE_, UPDATE_, USING_, VACUUM_, VALUES_, VIEW_, VIRTUAL_, WHEN_, WHERE_, WITH_, WITHOUT_, FIRST_VALUE_, OVER_, PARTITION_, RANGE_, PRECEDING_, UNBOUNDED_, CURRENT_, FOLLOWING_, CUME_DIST_, DENSE_RANK_, LAG_, LAST_VALUE_, LEAD_, NTH_VALUE_, NTILE_, PERCENT_RANK_, RANK_, ROW_NUMBER_, GENERATED_, ALWAYS_, STORED_, TRUE_, FALSE_, WINDOW_, NULLS_, FIRST_, LAST_, FILTER_, GROUPS_, EXCLUDE_, IDENTIFIER, NUMERIC_LITERAL, NUMBERED_BIND_PARAMETER, NAMED_BIND_PARAMETER, STRING_LITERAL, BLOB_LITERAL} line 32:0 extraneous input 'RETURNING' 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 33: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 python sql/queries/queries.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_}Most probably there is a compatibility problem between sqlite and sqlc. All the examples and tests in sqlc docs are executed using Postgres as DB. Potentially the problems doesn't exist there.
I propose to drop sqlc and use raw queries since migrating DB engine just to use sqlc seems like a lot and our DB requirements aren't that big.
- added a commit that references this issue
on Jan 8, 2025
Metadata
Metadata
Assignees
Labels
No labels
We're partially using sqlc right now. We're only using it to generate functions for
selectqueries. Ideally we should use it to generate all the other queries as well:insertandupdate. However, at a first attempt we failed to make it work, specifically theasyncgenerated queries were not working out-of-the-box. We should evaluate if it's worth to keep sqlc or drop it completely in favor of having raw SQL queries.