Database Projects - Modern Approach in Database Development

Database projects in brief

Database projects have significantly improved database development, giving developers a number of great opportunities.

A database project is an offline representation of a database schema with extended opportunities for offline schema development. It is a collection of logically organized SQL scripts and query files, which are used both to create a new database and update the existing one.

Database development is clear and logical within a database project. It takes you to do the following:

1. Create a database project guided by the wizard, either empty or with database objects imported from a required database.

2. Manage SQL scripts and query files in the project and edit their contents.

3. Build the ready project to create a script that will deploy all the project changes to the database on the server.

4. Deploy either the entire project or only required changes to the server.

This article describes how you can use database projects, provided in dbForge Studio for MySQL and dbForge Fusion for MySQL, and benefit from them. We will use a project based on a sample database Sakila to illustrate project handling.

Benefits of database projects

We recommend using database projects as they fully meet modern database development standards. Projects allow using version control system for database team development, which is critical for efficient development.

As much work in database development accounts for working with SQL scripts, database projects offer a great number of features for quick and convenient SQL editing. You will appreciate automatic syntax check in SQL scripts, check of schema objects references in all statements, check of any object duplicates, etc.

You can leverage more features while working with objects. One of them is refactoring, a form of editing aimed at improving readability while preserving meaning. You can use the refactoring option to accurately rename tables, views, columns of tables and views, aliases, stored routines, local variables, triggers, events, and users. Statements that reference a renamed object will be automatically updated in all the project scripts. Text strings and comments, containing the object's name, will be updated too.

Settings and syntax differences in various versions of SQL servers can put obstacles in the phase of deploying the ready tested database to the server. Projects can have multiple configurations , they allow you to customize the database project for deploying to various versions of SQL server. You should select the required connection and settings in a project configuration once, and next time just use the required configuration.

Creating a database project

You can create either an empty project or one with database objects imported from an existing database.

To create an empty project:

1. On the File meu, point to New, and then click Project. The New Database Project Wizard opens.

2. Enter a name and a location of the project.

3. You can also select a connection to use for the project deployment. (By default, the current connection is selected.)

4. Click the Create button to close the wizard and see the created project in the Project Explorer window. To save the project, click the Save button on the toolbar.

To import database objects into a project:

1. Select the Import schema objects from the existing database check box in the wizard and click the Next button to specify a connection and a database where the imported database objects are located.

2. You can select only required database objects from the list of database objects on the next wizard's page.

3. Click the Create button to close the wizard and see the created project with imported objects in the Project Explorer window. To save the project, click the Save button on the toolbar.

Managing project’s files

You can see and manage your project’s files using Project Explorer. It is a special window where you can work with your project with clear understanding at a first glance. Project Explorer allows you to do the following:

See 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 into them.
Delete folders and subfolders from the project.
Move folders by drag-and-dropping them to include one into another, creating subfolders and getting the desired folder structure.

Note, you can right-click in Project Explorer and select the required option to manage project's files.

In the Object View window, you can see and easily navigate through database objects, created by the scripts in your project, sort them either alphabetically or by object type, and rename. To apply these options, use the shortcut menu.

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 from the menu. The script opens with the carriage at the beginning of the required line.

You can edit project’s files in a convenient SQL editor, which provides a wide range of options for quick and efficient SQL code editing. Read the details in Advanced features: Code Snippets, SQL Syntax Checking and Code Formatting article.

Building a project

After you have edited 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. Choose a project configuration to build on the Build tab of the Project Properties window. (To open the window, click Properties on the Project menu.)

2. Right-click in the Project Explorer window and select Build from the 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 of any object duplicates.

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.

Project options to customize build results

Before building the project, you can set project options to customize build results.

1. In the Project Explorer window, select Properties from the shortcut menu, or use the Project menu. The Project Properties window opens. It contains three tabs: Build, Build Order, and Database.

2. Use the Build tab to 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. The Build Order tab lets you manage the order of files in the build script and deploy execution. You can manage dependencies between objects in different files using build order. 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 under film_Table.sql on the Build Order tab.

4. Move to the Database tab to manage target database settings. For example, you can set Server version to generate SQL statements, using syntax of this server version.

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.

To deploy the entire project:

1. Check if the project has a connection assigned for deployment, otherwise, the deployment fails.

2. Right-click the project in Project Explorer and select Deploy. Alternatively, on the Project menu, click Deploy.

Use Always re-create a database option to guarantee database integrity.

To deploy the 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.

1. Right-click in the Project Explorer window and select the Synchronize Project with Server option. The document with comparison results opens.

2. By default, the synchronization actions are automatically selected for each compared object. You can change them, for example, selecting the Skip action to not apply the change.

3. On the Comparison menu, click Synchronize to deploy the project’s changes to the database.

You can synchronize the changes from the database on the server with your project.

It is also simple. Do the following:

1. Right-click the required database in Database Explorer and select the New Schema Comparison option. The wizard opens.

2. Select the database on the server as Source and your project as Target.

3. Click OK and see the comparison results in the opened window.

4. Analyze the differences. By default, the synchronization actions are automatically selected for each compared object, but you can change them, if required.

5. Click the Synchronize button on the toolbar to synchronize the database with the project.

While comparing, the project can be either Source or Target. Note, the project is automatically built during comparison, so if any errors occur during this process, the comparison fails.

Summary

Projects greatly facilitate database development. They organize your scripts in one logical structure, good for navigation and management, offers offline database development with a set of useful features, such as syntax check and code completion in SQL code, refactoring, etc. You can generate either a single SQL script file or a set of files (a batch script file) for deploying to the server. Deployment of only required project changes is one more benefit. Empower yourself with database projects, while developing databases, and feel difference.