MySQL Parameter Binding Issue with sqlc Python Plugin

[santinalbrowns]

Getting MySQL syntax error when trying to register users - Parameter binding issue

What I'm trying to do

I'm building a FastAPI application with MySQL and using sqlc to generate my database repository code. Every time I try to register a user through my /register endpoint, I get a MySQL syntax error about the ? placeholders.

The Problem

Whenever I call my user registration endpoint, my application crashes with this error. It seems like the generated sqlc code isn't working properly with MySQL parameter binding.

Environment

• sqlc version: 1.27.0
• Plugin: sqlc-gen-python_1.3.0.wasm
• Database: MySQL 8.0+
• Python: 3.10
• SQLAlchemy: 2.x
• MySQL Driver: mysql-connector-python (also tested with PyMySQL)

Configuration

version: "2"
plugins:
  - name: py
    wasm:
      url: https://downloads.sqlc.dev/plugin/sqlc-gen-python_1.3.0.wasm
      sha256: fbedae96b5ecae2380a70fb5b925fd4bff58a6cfb1f3140375d098fbab7b3a3c
sql:
  - engine: mysql
    schema: "database/migrations"
    queries: "database/queries"
    codegen:
      - out: repository
        plugin: py
        options:
          package: repository
          emit_sync_querier: true
          emit_async_querier: true

SQL Query

-- name: InsertUser :execresult
INSERT INTO users (firstname, lastname, email, password) VALUES (?, ?, ?, ?);

Generated Code (Problematic)

INSERT_USER = """-- name: insert_user \\:execresult
INSERT INTO users (firstname, lastname, email, password) VALUES (?, ?, ?, ?)
"""

def insert_user(self, *, firstname: Any, lastname: Any, email: Any, password: Any) -> sqlalchemy.engine.Result:
    return self._conn.execute(sqlalchemy.text(INSERT_USER), {
        "p1": firstname,
        "p2": lastname,
        "p3": email,
        "p4": password,
    })

Error

mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?, ?, ?, ?)' at line 2

sqlalchemy.exc.ProgrammingError: (mysql.connector.errors.ProgrammingError) 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?, ?, ?, ?)' at line 2
[SQL: -- name: insert_user :execresult
INSERT INTO users (firstname, lastname, email, password) VALUES (?, ?, ?, ?)
]

What I Think is Happening

From my debugging, it looks like sqlc generates SQL with ? placeholders but then the Python code tries to pass parameters as a dictionary like {"p1": firstname, "p2": lastname}. From what I can tell, MySQL with SQLAlchemy doesn't like this combination.

What I Expected

I expected the generated code to just work out of the box with MySQL, like it probably does with PostgreSQL.

What I've Tried

1. Switching from mysql-connector-python to PyMySQL - same error
2. Looking at the generated code and trying to understand why it's not working
3. Manually editing the generated code (but this gets overwritten when I run sqlc generate again)

Impact on Me

This is pretty frustrating because:

• I can't use sqlc with MySQL without manually fixing the generated code every time
• I have to either:
  • Manually edit the generated files after every sqlc generate (not sustainable)
  • Create wrapper classes to fix the parameter binding (extra work)
  • Switch to a different tool (defeats the purpose of using sqlc)

Is this a known issue?

I'm not sure if this is a bug or if I'm doing something wrong with my configuration. Any help would be appreciated!

Possible Solutions (from my research)

Maybe the generated code could:

1. Use positional parameters: [firstname, lastname, email, password] instead of the dictionary
2. Or generate SQL with :p1, :p2, :p3, :p4 instead of ?, ?, ?, ?
3. Or add a config option to choose the parameter style for different databases

[devstein]

Hey @santinalbrowns - MySQL isn't supported for this plugin. Contributions are welcome! I saw you made a PR already #91