Skip to main content
Question

DB migration and ORM with Python (ideally)

  • October 12, 2018
  • 2 replies
  • 13 views
Bryan_H
Forum|alt.badge.img+2

We have a request from a customer about database migration and schema synchronization. They’d like recommendation on a tool or process for ORM so they can keep the Vertica schema up to date with the rest of the workflow.

Some of the discussion follows. It looks like they’ve tried Python + SQLAlchemy and Alembic. We’ve suggested a Postgres-compatible tool but I am not sure our DDL is close enough. I suspect we’ve dealt with object mapping elsewhere - any thoughts on this?

Follow up from the original email: I've suggested Sqitch (https://sqitch.org/) as a possible migration and deployment tool. Does anyone have any experience with this?

Also, I see in the summit talks that Criteo spoke about catalog management and schema deployment using Jenkins. Do we have more details on how they're doing this?

    2 replies

    marcothesane
    Forum|alt.badge.img+1
    • marcothesane
    • Participating Frequently
    • October 12, 2018

    Hi Bryan -

    You could just as well use odb for that purpose. It can generate DDL appropriate for a plethora of databases, and we have been using it since pre-Vertica days (Neoview replacement projects) for migrating from and to all sorts of different database platforms. Just refer to a copy of odb_HOWTO*.pdf to see how it could work ...

    Marco

    Bryan_H
    Forum|alt.badge.img+2
    • Bryan_H
    • Author
    • Participating Frequently
    • October 14, 2018
    Apparently odb and other DB migration/replication tools will not fit the bill as they appear to be serializing and deserializing Python objects to the database. Here is their comment:

    "The reason why we use and would prefer to stay with Alembic is because it integrates nicely with our ORM (SQLAlchemy). For example, if we modify our data models through our Python application, Alembic is capable of detecting the changes in our code and can generate the corresponding SQL to allow us to keep our database under version control without the need to write extra code. And as much as we would like to, we are not capable of running Alembic with Vertica support to give you any sort of error or issue log."

    I've pulled Alembic and the Vertica SQLAlchemy bits to see if I can make the examples work and figure out what they are doing. It sounds a little like what Hibernate does in Java though.
    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement