Database Projects: 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 multiple files.
• 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.

Project configurations

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:

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.