Evaluate SQL approach - sqlc or raw approach

[aponcedeleonch]

We're partially using sqlc right now. We're only using it to generate functions for select queries. Ideally we should use it to generate all the other queries as well: insert and update. However, at a first attempt we failed to make it work, specifically the async generated 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.

[aponcedeleonch]

Background

sqlc takes SQL queriers and generates functions to execute the queries. We have succesfully used it to automatically generate select queries but we haven't been able to use it for insert or update. As an example here is the provided SQL query to insert into prompts table 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 insert something using sqlc auto-generated code

error=(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 dict as input, note the p* 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_PROMPT should be the following. Note the p* 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 at sqlc generate when 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.