Modern Approach to Database Development
This article describes how to create a database project in dbForge Studio for MySQL and dbForge Fusion for MySQL, how to deploy a project,
and benefit from them. We will use a project based on a sample database Sakila to
demonstrate project handling and MySQL project management.
Creating a database project
Creating an Empty Project
You can create either an empty database project or one with MySQL database objects imported from
an existing database.
To create an empty project:
1. On the File menu, point to New and then click Project.
2. In the New Database Project Wizard that opens, enter a name and a location of the project.
3. Select a connection to use for the project deployment. By default, the current connection is selected.
Optionally, clear Create a folder for the project to store the project in the chosen path without creating a folder for a project.
4. To close the wizard, click Create. The project you've created will be displayed in
the Project Explorer window.
5. To save the project, click Save on the toolbar.
Creating a database project from an existing database
You can populate a project with MySQL database objects by importing schema objects from an existing database.
To import database objects into a project:
1. In the New Database Project Wizard, select the Import schema objects from the existing database check
and click Next.
2. Specify a connection and a database where the imported database objects are located.
3. You can select only required database objects from the list of database objects
on the next wizard's page.
4. To close the wizard, click Create. The project with the imported objects will be displayed
in the Project Explorer window.
5. To save the project, click Save on the toolbar.
Note that when creating a new project from an existing database, an object DDL is formatted according to the formatting options.
Saving a database project
To save the project, in Project Explorer, right-click the project and select Save Project on the shortcut menu.
Alternatively, on the View menu, click Project Explorer to activate the Project Explorer view.
After that, on the File menu, click Save < project name >.
Opening a database project
To open a project, on the File menu, click Open Project.
When you create a new project, the project you opened before will be closed, and all opened documents will be closed too.
Managing project’s files
Using Project Explorer
You can view and manage your project’s files using Project Explorer,
which allows you to:
View and quickly access all SQL and query files of the project, as they are accurately
organized as a tree.
Add new files to the project.
Add existing SQL and query files to the project.
Edit or delete files in the project.
Drag-and-drop files to ensure their best location within the project tree.
Create and then rename folders and subfolders in the project to neatly distribute
Delete folders and subfolders from the project.
Move folders by drag-and-dropping them to include one into another, to create subfolders
and get the desired folder structure.
Note that you can right-click in Project Explorer and select the required option
to manage the project's files.
Using Object View
In the Object View window, you can view and easily navigate through database
objects, created by scripts in your project, sort them either alphabetically
or by object type, and rename them, if needed. To apply these options, use the shortcut menu.
The Object View allows you to quickly find and see the CREATE statement of each
object in the script. Double-click the object or right-click it and select Go To Definition
on the menu. The script opens with the carriage at the beginning of the required line.
You can edit project files in a convenient MySQL editor, which provides a
wide range of options for quick and efficient SQL code editing. For more information, see Advanced features: Code Snippets, SQL Syntax Checking, and Code Formatting.
Building a project with different configurations
Building a project
After you have updated your database project, you should build it to create a script that will deploy all the project changes
to the database on the server. A project can be built to a single ready-to-deploy script or a set of scripts with a batch
to run it in the MySQL Command-Line Tool or in dbForge Studio or dbForge Fusion.
To build a project:
1. On the Project menu, click Properties.
2. In the Project Properties window that opens, switch to the Build tab and choose a project configuration to build.
3. Right-click in Project Explorer and select Build on the shortcut menu.
Project building is enhanced with the following features:
Syntax check in project scripts. If any errors are found, the build is stopped.
Check of references to schema objects in all statements.
Check for any object duplicates.
Configuring project settings
Before building the project, you can set project options to customize build results.
To set project options:
1. In Project Explorer, select Properties on the shortcut menu of your project,
or use the Project menu. The Project Properties
window with three tabs: Build, Build Order, and Database, opens.
2. On the Build tab, define the settings for the generated SQL file with
project changes. For example, you can select whether to generate a single SQL script
file or a batch script file, etc.
3. (In dbForge Fusion for MySQL) On the Build Order tab, indicate the order of files in the build script
and deploy execution. You can manage dependencies between objects in different files.
For example, table film2 in film_text.Table.sql references table
film1 in film_Table.sql, so you should move film_text.Table.sql to locate it under
film_Table.sql on the Build Order tab.
(In dbForge Studio for MySQL) On the Build Order tab, the order of files is set automatically and cannot be changed manually.
4. On the Database tab, manage target database settings. For example, you can set a server version
to generate SQL statements, using the syntax of this server version.
For more information about how to work with database projects, see the database project tutorial.
Each new project has one configuration named Default. Configurations can be created,
renamed, and deleted using the Configuration Manager. You can have several
configurations each one with its own options. They will allow you to deploy the
same database project to various servers. For example, you can test the project
on one server and deploy the final version to another one.
Deploying a project to a server
You can deploy either the entire project or only the changes in the project to the
database on a server.
Deploying the entire project
To deploy the entire project:
1. Check if a connection was assigned to the project; otherwise, the project deployment fails.
2. In Project Explorer, right-click the project and select Deploy. Alternatively,
on the Project menu, click Deploy < Project name >.
Use the Always re-create a database option to guarantee database integrity.
Deploying changes from your project
When you want to deploy only the changes from your project to a server, you should
compare your project with the database on the server and create a special
schema synchronization script.
The script will deploy only required changes to the server.
To deploy the changes from your project:
1. In Project Explorer, right-click the project and select the
Synchronize Project with Server option. The schema comparison document with comparison results opens.
2. By default, the synchronization actions are automatically selected for each object to be compared.
You can change them, for example, by selecting Skip in order not to apply the change.
3. On the Comparison menu, click Synchronize objects to the target database to deploy the project’s
changes to the database.
Synchronizing and comparing the project
with a database or a scripts folder
Synchronizing database changes with the project
You can synchronize the changes from the database on the server or a scripts folder with your project.
To synchronize a database with the project:
1. In Database Explorer, right-click the database you want to synchronize the project with and select Tools > New Schema Comparison.
2. In the New Schema Comparison wizard, select the database on the server as Source and your project as Target.
3. (Optional) On the Options tab, specify options of schema comparison.
4. To proceed with schema comparison, click Compare.
5. In the comparison results document, analyze the differences. By default, the synchronization actions are automatically
selected for each compared object, however, you can change them if needed.
6. To synchronize the database with the project, click Synchronize objects to the target database on the toolbar.
While comparing, the project can be either Source or Target. Note that the project is
automatically built during comparison, so if any errors occur during this process,
the comparison fails.
Creating a scripts folder
A scripts folder is a set of scripts that represent a database schema. In dbForge Studio for MySQL, you can synchronize your project with the scripts folder.
Scripts folders can be used either as a source or target for comparison and deployment.
At first, create a scripts folder if you have not done that yet. For this, follow the steps:
1. On the Database menu, click Create Scripts Folder.
2. In the Create Scripts Folder dialog that opens, select Source: either a database or a scripts folder.
3. Depending on the source type you have chosen, specify:
- For a database: Connection and Database.
- For a scripts folder: Folder
4. In the Destination > Path field, indicate a path to store the scripts folder.
5. (Optional): To create a scripts folder structure, click Scripts Folder Structure.
6. To proceed with the folder creation, click Create.
Synchronizing the project with a scripts folder
To compare the project with a database scripts folder:
1. In Project Explorer, right-click the project you want to compare and select New Schema Comparison.
Hover over the Comparison menu, click New Schema Comparison, and select your project as a source type in the New Schema Comparison wizard.
2. On the Source and Target tab, select a scripts folder from the Target type drop-down list.
3. In the Database scripts folder field, browse a folder from the drop-down list.
4. Select a server version and a default collation (if needed), and click Next.
5. On the Options tab, select options for schema comparison, and then click Compare.
6. After that, follow the steps in Synchronizing database changes with the project.
Projects greatly facilitate database development. They organize your scripts in one logical structure
that is convenient for navigation and management. The Projects feature offers offline database development
with a set of useful features, such as syntax check and MySQL code completion, refactoring,
schema comparison and synchronization, etc. You can generate either a single SQL script file or a set of files
(a batch script file) for deploying to the server. Moreover, you can deploy the project changes you need.
To sum it up, empower yourself with database projects, while developing databases, and feel the difference.