Generating SQL from Python

I’ve wanted to get more experience writing programs that analyze programs, and after using Prisma schema I decided to try writing a program to convert a Python dataclass to a SQL schema.

At the time, I was starting up a project to extract and load Philadelphia city data into my own database. In the early stages of the project, I found myself toiling through “drop, recreate, rehydrate” cycles of rebuilding my tables and “rehydrating” my local db with test data. Any change to a data model meant updating at least two definitions: my application code and the database schema. While tedious, I didn’t quite want to bring in a full ORM just for this, so I decided to see what it’d be like if I tried solving this problem myself.

The idea is to treat a Python class definition as the canonical data model definiton. My tool would then generate SQL DDL statements from the Python source code. I called my attempt norm. At a high level, it takes one Python input file, looks for classes annotated with @dataclass, and tries to generate a SQL DDL statement from the type annotations. Unless a class property is optional, not null is added to the table field definition.

While it’s certainly not going to be used for a real project any time soon, I feel hopeful that I’ll be able to come back to this from time to time and add new features as they’re needed. At the very least, it was a fun way to start on the journey of writing Python programs that analyze Python programs.