dump sqlalchemy database schema in python

dumpdbschema.py

from sqlalchemy import MetaData
from sqlalchemy.sql.ddl import CreateTable
import typer

from app.helpers import app_path
from database.connection import engine

app = typer.Typer()


@app.command()
def main() -> None:
    metadata = MetaData()
    metadata.reflect(engine)
    to_skip = ['alembic_version']
    table_definitions = []
    for table_name in metadata.tables:
        if table_name in to_skip:
            continue
        model = metadata.tables[table_name]
        table_definitions.append(CreateTable(model).compile(engine).string)
    with open(app_path('../database/schema.sql'), 'w') as f:
        f.write('-- This sql file is auto-generated from SQLAlchemy models\n')
        f.write(
            '-- It is intended to be used for testing and developing purposes only\n')
        f.write('\n-- BEGIN OF SQL --')
        f.write('\n'.join(table_definitions))
        f.write('-- END OF SQL --\n')

    typer.secho('dump db DDL successfully to database/schema.sql',
                fg=typer.colors.GREEN,
                bg=typer.colors.BLACK)


if __name__ == '__main__':
    app()