Back to articles list
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.
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:
Save your model as an XML file
Use the “Export model as XML file” icon from the top menu bar.
-
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.
-
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.
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.
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.
-
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.
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.
