Creating a Test Database with Navicat 16

Feb 7, 2022 by Robert Gravelle

Recently, we learned how to generate test data using Navicat 16’s new Data Generation tool. It can help produce a large volume of complex testing data over multiple related tables, all guided by a multi-step wizard. In today’s follow-up, we’ll go through the process of creating a MySQL test database – all using Navicat 16.

For optimal testing, the best approach is to duplicate the structure of your production databases (DBs), while replacing the “real” data with sanitized test values. For the purposes of this tutorial, we’ll use an instance of the MySQL classicmodels Sample Database as the source that is the basis for our test database. Here it is in Navicat Premium 16:

There is usually more than one way to accomplish a task in Navicat. Duplicating a database is no exception. There are several ways to do it; here are a couple:

Create a New Database

Rather than copy the database, we can create a brand new one and then generate the test data for it. To do that:

• In the Navigation pane, right-click your connection and select New Database:

  

• Enter the database properties in the pop-up window:

  

  Hint: if you aren’t sure what Character Set and Collation to use, you can open the Edit Database dialog on the source DB to see their values.

• To copy over the table structures without data, simply select all of the tables in the Objects pane and drag them over to the new DB. A popup menu will appear asking you whether to copy over the Structure and Data or the Structure only:

  

  Choose the latter option.

Generate Tables From Model

Most organizations maintain model diagrams of their databases. Navicat’s Modeling tool can generate database objects from a model (forward engineering) as well as generate a model from an existing DB (reverse engineering). Let’s use it now to generate our test tables.

• Follow the two first steps from the last exercise to create the classicmodels_test database.
• Click the Model button on the main toolbar to see available models:

  

  Hint: if you don’t have a model for your database, you can generate one by right-clicking the database in the Navigation Pane and choosing Reverse Schema to Model… from the popup menu.

• Open the model in the Modeling tool by selecting the model in the Objects pane and clicking the Design Model button in the Objects pane toolbar:

  

• In the Modeling tool, select File -> Synchronize to Database… from the main menu.
• In the Synchronize to Database dialog, designate classicmodels_test as the target database and click the Compare button:

  

• Navicat will then determine which objects to create, update, or drop to synchronize both databases. In our case, it will generate all of the necessary tables:

  

• On the next screen, we can review the SQL statements that will be executed:

  

  Click the Start button to run the script.

• We’ll get a detailed progress report as the script runs:

  

From there, we only need to follow the steps outlined in the Create a Model from a Database in Navicat blog.

Navicat 16 provides numerous options for duplicating an existing database, either on the same server or in a completely different environment.

Speaking of Navicat 16, if you’d like to give it a test drive, you can download a 14 day trial here.