Code Generation: How to generate Pydantic & SQLAlchemy/GinoORM models from SQL DDL?
This article demonstrates how to generate ORM & Pydantic models from DDL files with O!MyModels library. With one command or 1 line of code.
I hope it can be useful for you too (but in very rarely cases :))!
All code samples (ddl & results) you can find here: https://github.com/xnuinside/omymodels/tree/main/example
Get DDL files from Database Schema
To demonstrate how the generating of models is working I will create new DDLs, for this, let use https://dbdiagram.io/ it is a simple tool for creating DB Structure diagrams using the simple DSL language — https://www.dbml.org/docs/ .
And create our DB schema, I will add tables with different types, including arrays & json, Enums, also adding references between tables & defaults, some fields will be nullable, some of them aren’t. I tries to keep structure of tables more ‘real’.
At the end I have structure like this: https://dbdiagram.io/embed/6041ce89fcdcb6230b22b6c4
Export schema as DDL
One of the most useful things about DB Diagrams, that you can export it as .DDL.
Click on Export -> Export to PostgreSQL
And you will get the file with valid .DDL. I renamed it as ‘db.sql’.
Generate ORM models
Now time to create models. Create new Python virtualenv or use already existed project env.
Install omymodels package:
pip install omymodels
When run a command:
omm — stands for ‘O!MyModels`
In my case it will be a command:
Output will be in file ‘models.py’ with models inside it:
By default O!MyModels generated GinoORM models.
Now let’s generate SQLAlchemy Models, for this you need just provide argument
omm db.sql -m sqlalchemy
And let’s check our models.py:
Now let’s do the same for Pydantic
omm db.sql -m pydantic
And result will be:
One important moment:
Pydantic models often used as serialization models, for example, in API (FastAPI as sample) and you not need in them defaults from DDL (server defaults from DB). So you can turn off them during generation with flag
Output will be this (without defaults):
Use library from python script
You also can use omymodels not only as cli, but integrate it in your python scripts with call create_models method. Let’s take a look at the example:
from omymodels import create_modelsddl = """ CREATE TABLE "materials" ( "id" int PRIMARY KEY, "title" varchar NOT NULL, "description" varchar, "link" varchar, "created_at" timestamp, "updated_at" timestamp );"""result = create_models(ddl, models_type='sqlalchemy_core')# result returned as dict with 2 keys: 'metadata' - simple-ddl-parser output with full information about your ddl and 'code' - string with generated models code
Or if you want to parse models from file — just pass `ddl_path=` arg.
from omymodels import create_modelsresult = create_models(ddl_path='example/db.sql', models_type='sqlalchemy_core')
Supported ORM & Models
Today (end of Apr 2021) library can generate 5 types of models: GinoORM (https://github.com/python-gino/gino) , SQLAlchemy (models) (https://docs.sqlalchemy.org/en/14/), SQLAlchemy Core (tables) (http://docs.sqlalchemy.org/en/latest/core/), Pydantic (https://pydantic-docs.helpmanual.io/)& Python Dataclasses (https://docs.python.org/3/library/dataclasses.html).
Also, need to understand, that now it generate base things: Column, Column type, size, Foreign Key, onupdate, ondelete, defaults, nullable, primary key, indexes, unique indexes, unique column property, autoincrement. But if you need something that not supported yet — feel free to open issue with example. It will really helps to the project.
In plans — to add more Python ORMs and more features to already supported ORMs (for example, add sequences) if you want to have something at the first — just open the issue.
If you will need more examples — just check tests on the GitHub: https://github.com/xnuinside/omymodels/tree/main/tests
All metadata (information from DDL) that is used to generate models are taken from https://github.com/xnuinside/simple-ddl-parser. So O!MyModels understand all .DDL that parsed correctly by simple-ddl-parser. If something not supported, but you need this — again, just open the issue. If it valid DDL statement — I will add support for this. Or if you familiar with lex & yacc (ply) — you can open PR by yourself, feel free on it.
Also I want to say BIG THANKs for unknown to me people who started to open the issues on the first week of projects — this very helps to understand that your code is also useful to someone else.
For support with .DDL samples in /simple-ddl-parser big thanks goes to https://github.com/geob3d and https://github.com/nitin-kakkar and in /omymodels big thanks to https://github.com/hieulw . Thank you, guys!