On 09/22/2016 02:51 AM, Tom Walter wrote:
I work in a team of people collaborating on database development and
we'd like to start version controlling and deploy our changes with Alembic.
I was wondering though if there is a way to generate the DDL for the
current state of the whole schema as of a given revision... something
like the 'offline' SQL generation, however rather than every
intermediate revision, just output the CREATE statements of the final
states of all the objects.
You might ask why anyone would want to do this... the reason is that we
generally like to do code reviews or pull requests when someone deploys
a change. If the only artifacts stored in source control are alembic
migrations, it can be difficult for a reviewer to get an idea of the
overall context and impact of a given migration or set of migrations.
this is also a common use case because of the desire to "squash" all the
migrations up to a certain point that are no longer needed individually.
If we could use alembic to update a file or set of files representing
the current state of the entire DB, then that could be checked into
version control along with the migrations. Granted it would be useless
for migrating a given instance of a database from one state to another,
but it would be a useful and more readable way for a dev to understand
all the objects in a database, short of logging into a live instance of
one and poking around.
If the purpose of the file here is strictly one of documentation, I
would first note that a traditional SQLAlchemy application has the
tables also expressed in Python, either as Table objects or as ORM
mapped objects that will show off the table names, columns, and general
constraints. Just from that level, there are tools that can generate
ER diagrams from a SQLAlchemy model. As well as, you can take any
collection of SQLAlchemy tables and just use metadata.create_all() to
emit the CREATE statements, which can be directed to string output. So
even without a database, the structure of your schema is apparent just
from the Python code in the normal case.It would not be hard to
write a short script to generate this from the state of the model.
To document the current state of the database itself without consulting
the Python structure, IMO that's not even an Alembic thing, most simply
you can use your database's dump feature to just dump out the CREATE
statements to a text file. All relational databases have such a tool
available, such as pg_dump or mysqldump. Then there are also an
endless number of both free and commercial database inspection /
diagramming tools as well that can generate all kinds of views of a
relational database schema.
If, as a third case, you want to generate from the database schema
itself, *and* use only SQLAlchemy / Alembic, you would create a MetaData
object, do a metadata.reflect() of that engine, then run a
metadata.create_all() into a string buffer. This would not be nearly as
complete as that of using the database dumper tools, however you would
see the basic table structures as well as foreign key constraints.
The create_all() into a string buffer is described at
http://docs.sqlalchemy.org/en/latest/faq/metadata_schema.html#how-can-i-get-the-create-table-drop-table-output-as-a-string.
Usually, people want this "state of the database" to be squashed into
one big Alembic migration, which would be an additional step to the
techniques above, but if you just want a readable text file, running
metadata.create_all() into a text file is the most expedient way, it's
just that you won't see any special directives or constraints that are
present in your migrations only. To get a fully accurate view I
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy-alembic" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy-alembic+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
