Back to articles list
- 4 minutes read

How to Convert a Data Model Between Two Database Management Systems

Even though Vertabelo doesn’t provide a direct conversion of a data model from one database engine to another, this can be easily done in a few simple steps.

The user’s question

We’ve been developing a web application for a while. Initially, we wanted to use MySQL for our database but finally we decided to choose Postgres. Unfortunately, the entire data model was created for MySQL. How can we quickly migrate it to the new database type? Is there a simple way to do this in Vertabelo?

In Vertabelo, each database model is assigned to a specific database management system – one out of seven supported by the application. You must select the database engine for your project when setting up a new model or importing an existing database structure. After that, you cannot change it. For example, if you choose Oracle database for your new model, you will not be able to switch to another database while working on the diagram, or even generating an SQL script.

Why is that? Because conversion of a physical model between two database management systems is not a trivial task due to different SQL dialects and data type definitions used by particular database vendors. It is hard to automate this process. For example, a logical type boolean in PostgreSQL doesn’t have a substitute in an Oracle database. Therefore, it is the user who must decide what to do with the unsupported data types or index options.

Let’s get back to our user’s question.

Assume that you have originally selected MySQL for your database design. You have created a new model and started drawing an Entity Relationship Diagram (ERD) in Vertabelo. But in the meantime, your development team has decided to use PostgreSQL for your application, instead of MySQL. Unfortunately, the data model for MySQL is already finished.

Initial database model created for MySQL

What can you do now?

First, do not panic. You will not have to create the same model for PostgreSQL from scratch. Follow these few steps to migrate your ERD between two different database management systems. It’s simple and won’t take much time.

You just have to:

  • save your MySQL model as an XML file
  • create a new model for a PostgreSQL database using your XML file as an initial design
  • correct all data type incompatibilities (if there are any)

That’s all. Now, you’ve got exactly the same model migrated to the required new database engine.

Need more guidance? Check out the following instruction:

  1. Save your model as an XML file

    Use the “Export model as XML file” icon from the top menu bar.

    Export your database model to an XML file

  2. Create a new database model

    In the top menu bar, click the “Create new model” icon.

    Note that if you have reached the limit of database models you can store in your account (according to your plan), you must first delete one of your previous models.

    Hint: if you don’t want to lose any of your models, you can delete the one that you’re about to convert to PostgreSQL. Since you’ve got this model saved as an XML file, you can recover it at any time.

    Create a new database model

  3. Set up your new model

    A. Select the new database engine

    When setting up a new model, select the database engine you want your design to be converted to. Since we want to migrate the diagram from MySQL to PostgreSQL, we must choose the “PostgreSQL 9.x” option.

    Choose a new database engine for your db design

    B. Upload an XML file

    Within the “Initial model” section, select the tab “From Vertabelo XML”. Then, upload a previously generated XML file and click “Import XML” to read the file.

    Upload an XML file with your initial database model

    If everything is done correctly, the file will be imported successfully. Now you can click “Start modeling” to convert your initial data model from MySQL to PostgreSQL database.

  4. Check for data type incompatibility problems

    After importing the file, the application will automatically check if there are any incompatibilities between the previous and the new database engine.

    If there are any, you will get hints where problems occurred and which data types from the previous database are not supported by the new one.

    Within your workspace, the problems are indicated in two ways:

    In the “Model structure” section

    In this section, the tables, in which data type incompatibilities were identified, are highlighted with an orange alert icon.

    If you click the name of the highlighted item, the “Table problems” section will appear in the right panel. There, you will find descriptions of all the compatibility problems detected in this particular table.

    'Model structure' section with marked tables having incompatibility problems

    In the “Problems” section

    If there are any issues to be solved, the “Problems” bar becomes orange. Click it to expand the section.

    Here, you will find all the problems detected in your model, collected and described in one place.

    'Problems' section listing all incompatibility issues detected in the model

go to top

Our website uses cookies. By using this website, you agree to their use in accordance with the browser settings. You can modify your browser settings on your own. For more information see our Privacy Policy.