Table from SQLAlchemy/Pydantic model

[1cadumagalhaes]

Please describe the feature you'd like to see I want to be able to create a Table from a SQLAlchemy model, import it's columns with types and constraints. Context: I'm running astro on a database from which the tables and migrations are defined by SQLALchemy models, that are also used by the application.

What happens here is:

1. I declare my tables as models and write the database migrations based on it.
2. The data for some of these tables come from aql.transform (with replace or append) and aql.merge.
3. If I just declare the tables with the name, conn_id and metadata, when replacing the table it will lose all constraints, and might get some types wrong. One common issue with this behaviour is that tables lose their primary key, but also all foreign keys, sequences and etc.
4. On the other hand, if I already have my tables defined as classes, it should not be hard to get every definition about it

Describe the solution you'd like

I'm not sure about the syntax, but we could do something like:

Table(model=MySQLAlchemyClass, conn_id=connection_id)

# Or even

Table(conn_id=connection_id).from_model(MySQLAlchemyClass)

With this, we could import the table name, the schema, all columns with types and constraints

Are there any alternatives to this feature? I don't think so

Additional context There is also sqlmodel which adds annotations to models so they're compatible with both pydantic and SQLAlchemy.

But adding support to python dataclasses would be nice for someone who isn't using SQLAlchemy or a similar ORM, but still wants an easier way of defining their table specifics.

Acceptance Criteria

• [ ] All checks and tests in the CI should pass
• [ ] Unit tests (90% code coverage or more, once available)
• [ ] Integration tests (if the feature relates to a new database or external service)
• [ ] Example DAG
• [ ] Docstrings in reStructuredText for each of methods, classes, functions and module-level attributes (including Example DAG on how it should be used)
• [ ] Exception handling in case of errors
• [ ] Logging (are we exposing useful information to the user? e.g. source and destination)
• [ ] Improve the documentation (README, Sphinx, and any other relevant)
• [ ] How to use Guide for the feature (example)

[1cadumagalhaes]

For now I'll try to create a function/decorator to do this conversion and if it works I'll add it here.