sqlc-gen-better-python

A WASM plugin for SQLC allowing the generation of Python code.

The generated code requires Python 3.12 or newer (it uses PEP 695 type aliases and generics, and enum.StrEnum).

Note

Every Release before v1.0.0, including this one is an beta release. These versions are primarly released for interested people who want to test this plugin and help make it better.

Everything that is implemented works and is being used in production environments already. Since v0.5.0 this includes full support for PostgreSQL enums and a fourth model type, pydantic. Feel free to lmk any wanted features and I'm going to do my best on implementing them with the time I have rn.

Example Config

# filename: sqlc.yaml
version: "2"
plugins:
  - name: python
    wasm:
      url: https://github.com/rayakame/sqlc-gen-better-python/releases/download/v0.5.0/sqlc-gen-better-python.wasm
      sha256: af56cc6f85376b473e8b8ac534d239238ec0362cf0b9c85a14ec406e1dbe8df3
sql:
  - engine: "postgresql"
    queries: "query.sql"
    schema: "schema.sql"
    codegen:
      - out: "app/db"
        plugin: python
        options:
          package: "db"
          emit_init_file: true
          sql_driver: "asyncpg"
          model_type: "msgspec"

More options at the sqlc config reference

Configuration Options

Name	Type	Required	Description
package	string	yes	The name of the package where the generated files will be located
emit_init_file	bool	yes	If set to to false there will be no __init__.py file created in the package that you specified. Only set this to false if you know that you already have a __init__.py file otherwise the generated code wont work.
sql_driver	string	yes	The name of the sql driver you want to use. Valid options are listed here
model_type	string	no	The model type you want to use. This can be one of dataclass, msgspec, attrs or pydantic. Defaults to dataclass
initialisms	list[string]	no	An array of initialisms to upper-case. For example, app_id becomes AppID. Defaults to ["id"].
emit_exact_table_names	bool	no	If true, model names will mirror table names. Otherwise sqlc attempts to singularize plural table names.
emit_classes	bool	no	If true, every query function will be put into a class called Querier. Otherwise every function will be a standalone function.
inflection_exclude_table_names	list[string]	no	An array of table names that should not be turned singular. Only applies if emit_exact_table_names is false.
omit_unused_models	bool	no	If set to true and there are models/tables that are not used in any query, they wont be turned into models.
omit_typechecking_block	bool	no	If set to true, will not wrap all non-builtin types behind a typing.TYPE_CHECKING block. Defaults to false
docstrings	string	no	If set, there will be docstrings generated in the selected format. This can be one of google, numpy, pep257 and none. none will not generate any docstrings.
docstrings_emit_sql	bool	no	If set to false the SQL code for each query wont be included in the docstrings. This defaults to true but is not used when docstrings is not set or set to none
query_parameter_limit	integer	no	If set to a non-negative value, queries with more parameters than the limit get them bundled into a single params: <Query>Params argument instead of expanded arguments. If unset or negative, parameters are never bundled (:copyfrom always uses a Params class).
omit_kwargs_limit	integer	no	This can be used to set a limit where any query with less or equal amounts of parameters will not require kwargs for the parameters. This defaults to 0 which makes every query require kwargs for their parameters.
speedups	bool	no	If set to true the plugin will use other librarys for type conversion. Needs extra dependecys to be installed. This option currently only affects sqlite3 & aiosqlite and uses the library ciso8601
overrides	list[Override]	no	A list of type overrides.
debug	bool	no	If set to true, there will be debug logs generated into a log.txt file when executing sqlc generate. Defaults to false

Type Overrides

Similar to sqlc-gen-go this plugin supports overriding types with your own. You can either override the type of every column that has a specific sql type, or you can overwrite the type of specific columns.

# filename: sqlc.yaml
# ...
options:
  # ...
  overrides:
    - db_type: text
      py_type:
        import: collections
        package: UserString
        type: UserString
    - column: table_name.text_column
      py_type:
        import: collections
        type: collections.UserString

Enums

PostgreSQL enum types generate an enums.py module containing enum.StrEnum classes:

class Mood(enum.StrEnum):
    SAD = "sad"
    OK = "ok"
    HAPPY = "happy"

Enum columns are typed with these classes in models and query functions, and values read from the database are coerced into them. Enums in non-default schemas get schema-qualified class names (e.g. CustomMood for custom.mood), so same-named enums never collide.

Identifier sanitization

SQL identifiers that are not valid Python names are sanitized: invalid characters become underscores, digit-leading columns/parameters get a column_ prefix, table names that would produce a digit-leading, keyword or empty class name get a Model prefix, digit- or underscore-leading enum values get a VALUE_ prefix, and colliding results are deduplicated with numeric suffixes. Field names also prefix leading underscores, since attrs and pydantic treat such fields as private.

One caveat: sanitization checks characters with Go's Unicode tables, which differ from Python's identifier rules in exotic cases (e.g. characters that may not START an identifier in Python, or two names that Python normalizes to the same identifier via NFKC). Such schemas are not detected; stick to ASCII identifiers if in doubt.

Pydantic models

With model_type: pydantic, models are generated as pydantic.BaseModel subclasses (requires pydantic >= 2.9). Two things to be aware of:

• Every generated class sets model_config = pydantic.ConfigDict(arbitrary_types_allowed=True). This is required for field types pydantic has no core schema for (e.g. memoryview for bytea/blob columns and custom override types); those fields are still validated with an isinstance check, and all other fields get full pydantic validation on construction.
• Unlike the other model types, pydantic resolves field annotations at runtime, so the generated files import the referenced modules at runtime instead of inside if typing.TYPE_CHECKING: blocks. If you lint generated code with ruff, set lint.flake8-type-checking.runtime-evaluated-base-classes = ["pydantic.BaseModel"].

SQLite type conversion

For the sqlite3 and aiosqlite drivers, the generated code registers adapters and converters (for date, datetime/timestamp, decimal, bool and blob columns) via register_adapter/register_converter. For these to work, you must open your connection with declared-type parsing enabled:

conn = sqlite3.connect(dsn, detect_types=sqlite3.PARSE_DECLTYPES)

Feature Support

Every sqlc macro is supported. The supported query commands depend on the SQL driver you are using, supported commands are listed below.

Every :batch* command is not supported by this plugin and probably will never be.

Prepared Queries are not planned for the near future, but will be implemented sooner or later

	:exec	:execresult	:execrows	:execlastid	:many	:one	:copyfrom
aiosqlite	yes	yes	yes	yes	yes	yes	no
sqlite3	yes	yes	yes	yes	yes	yes	no
asyncpg	yes	yes	yes	no	yes	yes	yes
psycopg2	no	no	no	no	no	no	no
mysql	no	no	no	no	no	no	no

Development

A roadmap of what is planned & worked on can be found here.

Contributions are very welcome, for more information and help please read the contribution guidelines.

Changelog

Can be found here

Credits

Because of missing documentation about creating these plugins, this work is heavily inspired by:

• sqlc-gen-go
• sqlc-gen-java

Special thanks to tandemdude for answering my questions on discord.