Does alembic support multiple databases?

[limodou]

I can't find how to enable alembic support multiple databases.

--
I like python!
UliPad <<The Python Editor>>: http://code.google.com/p/ulipad/
UliWeb <<simple web framework>>: http://code.google.com/p/uliweb/
My Blog: http://hi.baidu.com/limodou

[Michael Bayer]

You would assemble a multi-database scheme of your choosing in env.py. If you do "alembic init multidb" you'll see an example of one. How env.py is organized depends greatly on the relationship of the databases to each other, that is, to what degree they are mirrors of each other versus storing different schemas.

> --
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To post to this group, send email to sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
>

[limodou]

On Sun, Apr 29, 2012 at 10:42 PM, Michael Bayer
<mik...@zzzcomputing.com> wrote:
> You would assemble a multi-database scheme of your choosing in env.py.   If you do "alembic init multidb" you'll see an example of one.     How env.py is organized depends greatly on the relationship of the databases to each other, that is, to what degree they are mirrors of each other versus storing different schemas.
>

If I ran the command:

alembic init multidb

It'll create multidb folder and copy files in it. But I saw the
alembic.ini will be the same one. So if I should change it myself? And
how to let alembic know different database when executing commands
like: revision, upgrade, etc. It seems that no database parameter
existed.

And if I can manage different databases in one directory or in one ini file?

[limodou]

On Sun, Apr 29, 2012 at 10:56 PM, limodou <lim...@gmail.com> wrote:
> On Sun, Apr 29, 2012 at 10:42 PM, Michael Bayer
> <mik...@zzzcomputing.com> wrote:
>> You would assemble a multi-database scheme of your choosing in env.py.   If you do "alembic init multidb" you'll see an example of one.     How env.py is organized depends greatly on the relationship of the databases to each other, that is, to what degree they are mirrors of each other versus storing different schemas.
>>
>
> If I ran the command:
>
>    alembic init multidb
>
> It'll create multidb folder and copy files in it. But I saw the
> alembic.ini will be the same one. So if I should change it myself? And
> how to let alembic know different database when executing commands
> like: revision, upgrade, etc. It seems that no database parameter
> existed.
>
> And if I can manage different databases in one directory or in one ini file?
>

BTW, I manage different databases in different directory now.

[Michael Bayer]

On Apr 29, 2012, at 10:56 AM, limodou wrote:

> On Sun, Apr 29, 2012 at 10:42 PM, Michael Bayer
> <mik...@zzzcomputing.com> wrote:
>> You would assemble a multi-database scheme of your choosing in env.py. If you do "alembic init multidb" you'll see an example of one. How env.py is organized depends greatly on the relationship of the databases to each other, that is, to what degree they are mirrors of each other versus storing different schemas.
>>
>
> If I ran the command:
>
> alembic init multidb
>
> It'll create multidb folder and copy files in it. But I saw the
> alembic.ini will be the same one. So if I should change it myself? And
> how to let alembic know different database when executing commands
> like: revision, upgrade, etc. It seems that no database parameter
> existed.
>
> And if I can manage different databases in one directory or in one ini file?

multidb has a different alembic.ini as an example. If you already had an alembic.ini there it wouldn't overwrite it.

if you really wanted two completely independent sets of migration scripts, then you'd run two migration environments.

They can share the same alembic.ini like this:

[my_db_one]
sqlalchemy.url =

[my_db_two]
sqlalchemy.url =

you then run alembic with "alembic -n my_db_one" or "alembic -n my_db_two". The "default" config area is set by -n.

A single env.py script can get multiple database URLs in any way it wants, as it determines how config is accessed. If you look in the multidb/env.py script, you'll see it's pulling multiple database urls from one section using config.get_section(name) - config file:

[alembic]
# path to migration scripts
script_location = ${script_location}

# template used to generate migration files
# file_template = %%(rev)s_%%(slug)s

databases = engine1, engine2

[engine1]
sqlalchemy.url = driver://user:pass@localhost/dbname

[engine2]
sqlalchemy.url = driver://user:pass@localhost/dbname2

usage:

config = context.config

db_names = config.get_main_option('databases')

for name in re.split(r',\s*', db_names):
engines[name] = rec = {}
rec['engine'] = engine_from_config(
config.get_section(name),
prefix='sqlalchemy.',
poolclass=pool.NullPool)

Over here I have both forms of multi db at the same time. There's two migration environments, and one migration environment does two databases that are largely mirrored, so three databases total. All three make use of a common env.py script that's in my application as a library, they then implement an env.py in the migration environment that draws upon the myapp/lib/env.py script for common features.

You can pass instructions to a single env.py that may be controlling multiple databases using --tag:

"alembic --tag my_tag"

"my_tag" is available in env.py as context.get_tag_argument(). You can use that to conditionally run migrations on one database or the other.

This is all DIY. Multi-database migrations can happen in many different ways so you'd need to build the approach that suits your situation best.

[limodou]

On Sun, Apr 29, 2012 at 11:13 PM, Michael Bayer

thank you very much.

[Ruslan Skira]

If you have git example, share, please.

[thavha tsiwana]

wondering if there is more clearer information or a code snippet for this, I am also struggling to use 2 databases (1 dev, 1 prod) in my alembic project, I have successfully ran the migrations on the dev database, now I want to run my migrations to the prod database, I have no idea on how to modify alembic.ini and env.py file,,, please help

[Simon King]

There are a few possibilities. You could have separate configuration files for each database (eg. alembic-dev.ini and alembic-prod.ini), and choose between them with the "--config" command line option.

If you want to stick to a single configuration file, you could put both connection strings in your config file with different prefixes. For example:

sqlalchemy.dev.url = driver://user:pass@localhost/dbname
sqlalchemy.prod.url = driver://user:pass@localhost/dbname

Then, in your env.py file, locate the lines that say:

    connectable = engine_from_config(
        config.get_section(config.config_ini_section),
        prefix="sqlalchemy.",
        poolclass=pool.NullPool,
    )

...and change it to use either "sqlalchemy.dev." or "sqlalchemy.prod." as the prefix based on some condition. For example, you could use "context.get_x_argument" and pass the environment name on the command line: https://alembic.sqlalchemy.org/en/latest/api/runtime.html#alembic.runtime.environment.EnvironmentContext.get_x_argument Hope that helps, Simon

--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
 
http://www.sqlalchemy.org/
 
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.
---

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/e7177036-152b-4a34-a052-bec6a176aed4n%40googlegroups.com.