Database schema serialization and deserialization

Question

I'm looking for a library (Python ideally) that can implement changes to a database schema based on schema definitions in YAML (or any other serialized format).

I would like to define the schema of a SQL database in a serialized format such as YAML, and then use a well-supported library to implement the schema as tables and columns in the database for me. The Doctrine project seems to have something like this, but it's a PHP ecosystem and I'm looking for something Python or potentially Java or C++.

My schema files can change over time, and this needs to update the SQL database tables by adding new columns and tables. New many-to-many relationships also involve adding new association tables, and one-to-many relationships require foreign key columns to be added. These migrations should also be handled by the library (when the YAML file updates, the changes are implemented on the SQL DB).

Answer 1

You can have a look at SchemaCrawler the Free database schema discovery and comprehension tool.

Schemacrawler which is a tool that can reverse-engineer databases. It lets you store its results in various textfile formats, YAML is one of them (example).

According to its documentation It also has a Java API, and can be scripted in JavaScript. This way you can run your scripts to create database objects, not just reverse engineer them. (Of course you can use commandline tools to read in SQL scripts)

I think schema changes are easiest as long as your tables are empty and as long as there are no complexities involved such as cascading foreign key contraints (and many other DB features).