Reverse Engineering in a whole new way
Reverse Engineering is very important in data modeling. Either you want to create a schema, for the first time, out of the existing model, either updated existing model, you’ll need reverse engineering. Today we’ll take a look at this great new feature in sqlDBM.
What is SqlDBM?
SqlDBM is an online data modeling tool which allows you to design your data model, store it in the cloud, share your models with your team and collaborate. But it offers much more. You can forward engineer your model, generate scripts and create objects in your RDBMS. And now you can also do the opposite by using the reverse engineer feature. You’ll create scripts in your RDBSM and use them to create or update your SqlDBM model. You will be able to create a completely new data model, update existing or update existing partially. Today, we’ll talk about the reverse engineering feature.What is reverse engineering?
Usually, a data modeling process would start with modeling in SqlDBM and then forward engineering (exporting code and running it on your RDBMS) and continuing your work from there. You could make changes, if and when needed, in your RDBMS and SqlDBM data model at the same time and everything would be synchronized. But there is great possibility it won’t go that way. Maybe you’ll implement some changes in your RDBMS and won’t make them in SqlDBM.
Another common situation is when you start working on existing RDBMS and you need to create a model out of it. Trying to do that by looking at the tables and detecting how they are related, would be a very slow and painful job.
This is where reverse engineering jumps in. You’ll simply generate a script in your RDBMS and use it to create the model. We’ll do it on a simple SQL Server database.
Creating an SQL Server script
The first step is to create an SQL Server script. To do it so we’ll:
- Right click on our database
- Select “Tasks”
Click on “Generate Scripts…”
After that,we’ll choose objects.
After clicking on a few “Next”buttons and on the “Finish”button, thescriptis exported into the file. Now we’re ready for the next step.
Reverse Engineer for a completely new project
We have two options and these are creating a completely new project and updating existing. In this section, we’ll discuss how to create a completely new project. We’ll use the script created in the previous step.
In the SqlDBM Dashboard,we’ll select “Create Project”and then select “Microsoft SQL Server”under the “CHOOSE DATABASE TYPE”.
After clicking on the “CREATE NEW PROJECT BUTTON”, we’ll need to click on the “Reverse Engineer – Import existing database”in the lower left part of the screen.
This will open a new screen as the on the picture below.
We need to upload our DDL script now. We can simply drag-and-drop it.
If everything was OK with the script you should see something like this, a message with a number of imported objects. In case there were some problems with your model, you’ll see the list of warnings (we’ll talk about that later). Now just click the “IMPORT”button and it’s there.
Only these few simple steps and voila – your DB has been reversed engineered. Striving for simplicity is one the key components of SqlDBM and its team After rearranging tables a little our model is imported in the SqlDBM. And yes, don’t forget to save your project.
Reverse Engineer for existing database
Now we’ll try to do the same on the existing database. If you already have an existing SqlDBM model, maybe you want to update it. This could happen if you (or any member of your team) made changes in your RDBMS and you haven’t applied them to your SqlDBM model. This could often be the case if you have the team working on the RDBMS and you simply lack the time to document it at that moment.
Luckily, we can do this pretty simple in SqlDBM. The whole process is exactly the same as in previous steps. We’ll create a DDL script in SQL Server and drag-and-drop it in SqlDBM. If the script was OK, we’ll see the message as on the screen below.
You can notice that we can see how many changes happened left of the main window (Add (1 selected), Update (3 selected), Delete (1 selected). Of course, that these numbers reflect the number of changes we made in our RDBMS. We’ll again click on the “IMPORT”button and our model is here.
You can notice that I’ve removed 1 table (“report”), added 1 new (“new_table”) and the remaining 3 tables are present in both models (with or without changes).
Note: You could have only selected just changes (please take a look at the picture below) you want to perform in your model. You don’t need to make all the changes, but you could update your model partially. This could be very useful if you have partial DDL scripts.
In the previous two situations,we discussed how everything goes, if our scripts are OK. But making mistakes is the normal part of real life. In these cases, it would be cool if our tool can point out that we made some. And yes, it can.
I’ve intentionally deleted the whole “employee”table from the script. Since we still have the definition of the foreign key constraint, something should happen. When I try to import such script, the result will be as the one on the screen below.
In conclusion, Reverse engineer is a very powerful feature that should help you, especially in situations when you inherit a database and you lack the model. It will save you hours and maybe even days of work you should otherwise make almost completely manually. We really hope this feature will help you a lot and make you happy. Please tell us what do you think in the comments.