Code Generation: How to generate Pydantic & SQLAlchemy/GinoORM models from SQL DDL?

4 min readApr 23, 2021

Intro

This article demonstrates how to generate ORM & Pydantic models from DDL files with O!MyModels library. With one command or 1 line of code.

Library: https://github.com/xnuinside/omymodels

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 path/to/your_sql.sql

omm — stands for ‘O!MyModels`

In my case it will be a command:

omm db.sql

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 -mor --models_type:

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 --defaults-off.

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.

More examples

If you will need more examples — just check tests on the GitHub: https://github.com/xnuinside/omymodels/tree/main/tests

DDL Parsing

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.

Thanks

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!