Design the Database Schema as a Team Using GIT

Managing a complex database project across various environments: development, testing, and production, can lead to inconsistencies without a reliable synchronization method. This is where DbSchema’s integration of GIT becomes essential. By keeping every change in sync, regardless of the contributor or environment, GIT ensures that all updates are tracked and versioned, providing a seamless distribution process.

With this integration, you gain control and reliability in rolling out updates, because each adjustment is documented, allowing your team to manage database evolution confidently. DbSchema and GIT work together to maintain alignment within your team and accuracy in your data at all project stages.

GIT Server

For a step-by-step video tutorial, click here

For written documentation, please continue reading below.

Main Topics:

Manage Database Schema Changes in a Development Workflow

Databases are essential for storing and sharing data among multiple users. Any change to the database schema, such as creating new tables or columns, becomes visible to all users.

Databases Workflow

In some situations, this immediate visibility may not be recommended. For example, consider a software development company where developers frequently add or delete tables as they work on new features. These changes should only be implemented in production systems after a development cycle is complete, and testers have validated the release.

To achieve this, developers typically work with a separate database environment distinct from testing or production databases.

Databases Workflow

So, how can developers effectively communicate database changes to the testing team and then to the production database administrators? Typically, this is done by sending SQL scripts to update the database, such as:

  • CREATE TABLE newfeature(featureid INTEGER PRIMARY KEY, name VARCHAR(100));
  • ALTER TABLE sometable ADD COLUMN details VARCHAR(100);

However, when multiple teams are working on various features simultaneously, each providing its own set of changes, tracking these updates becomes complex and challenging.

Simplifying Database Schema Management with DbSchema and GIT

Managing database schema changes can be simplified with DbSchema, a tool that saves the database design as an XML file (.dbs), wich can be edited with any text editor.

With this schema design tool, you can store the database schema both in the actual database (e.g., MySQL, PostgreSQL) and as a text-based .dbs file.

Databases Workflow

This file can be loaded into DbSchema to compare it with the live database schema. The comparison reveals the differences and generates the SQL commands needed to synchronize them.

Using DbSchema, these differences can be applied to the actual database or updated in the loaded model, which is then saved as a file. The model in DbSchema can also be compared with another database or a .dbs file saved on disk, facilitating the transfer of database structures between teams or across various databases.

Synchronization Dialog

In the next phase, storing the .dbs file in a GIT repository offers several benefits, including easier file sharing among team members and maintaining different schema versions.

Let’s look at the practical advantages GIT provides:
  • When a developer creates a new feature, they can work on a MySQL database on their computer. Once the feature is complete, they can push the updated .dbs file to the Git server.
    • Pushing Changes to GIT Repository
  • The testing team can pull the latest .dbs file to update their local version.
  • The testing team then applies these changes to their database using DbSchema's sync feature, ensuring consistency between the database model and the actual database.
    • Pulling Changes from GIT Repository
  • After testing, the modifications are similarly sent to the production database administrators for implementation.

In this sync process, DbSchema can directly update the database to match the .dbs file or generate an SQL script with the necessary commands, which can be executed in any SQL client.

DbSchema has implemented GIT as a solution for team collaboration, focusing primarily on data security and protection. GIT servers can be hosted on the company’s internal network, keeping the database model and other essential files within the organization.

GIT Security

Configure GIT in DbSchema

To get started, you will need a repository URL from a remote hosting provider (GitHub, BitBucket, GitLab, Amazon, etc) or a GIT server within your organization.

Follow the next steps:

  • Run DbSchema and navigate to the Model menu.
  • Choose GIT-Collaborative Design to access the GIT Dialog.

GIT Configure

Clone a Remote Repository

  • In DbSchema, enter the repository URL in the GIT Dialog and choose an empty local directory to clone the project.

GIT Clone

Open the Design Model

  • After cloning the repository, open the .dbs file from the Model menu. You can have multiple .dbs files in the same repository.
  • Now you can see the model and you can make any necessary changes or updates to your database schema.
GIT Open using DbSchema

Send changes to the GIT Repository

To share your changes with the repository server, allowing other users to access the updated design file, follow these steps: stage, commit, and push your modifications.

Staging

  1. Select the files you want to stage.
  2. Click Stage to move these files to the Staging Area.
  3. The files are now in the Staged Area and ready to be commited.

GIT Stage using DbSchema


Commit

  1. Enter a descriptive commit message to document the changes you made.
  2. Click Commit to save these changes in your local repository.
  3. Once the commit is completed, the changes are reflected in the diagram on the main branch.
GIT Commit using DbSchema

Push

  • After committing your changes, you can push them to the remote repository to share your updates with the team.
  • Use the Push option in the GIT dialog.
  • Some hosting providers, like GitHub and Bitbucket, require authentication credentials to be provided before pushing changes to the remote repository, ensuring secure access and proper authorization for updates.
GIT Push using DbSchema


Download Updates from the Remote Repository

Pull

    To download the latest changes from the repository server and incorporate them into your local environment, you need to perform a pull operation.

  • In the Git dialog, click on the Pull option.This initiates the process of retrieving updates from the remote repository.
  • If there are changes available, DbSchema will automatically merge them into your local working environment. Review any conflicts that may arise and resolve them as necessary.
  • Check the or file history to verify the changes made by your colleagues.
  • Check the model diagram or you can compare the differences using the Compare with Current option, which opens a Synchronization Dialog that enables you to view the changes made by your colleagues.

GIT Pull using DbSchema

DbSchema also offers several key features:

  • The Stash feature temporarily saves your current changes without committing them to the repository, allowing you to revert to a clean working state or switch branches without losing your modifications.
  • The Create Branch feature simplifies the process of managing various development tasks and feature implementations.
  • The Pop feature retrieves the most recent stashed changes and applies them back to your working directory, enabling you to seamlessly continue working on the changes you previously set aside.
GIT Features using DbSchema

Conclusion and Takeaways

  • Using GIT within DbSchema for collaborative schema design significantly enhances team efficiency by meticulously tracking changes and preserving a comprehensive project history.
  • The seamless integration of essential GIT features such as: staging, committing, and synchronization ensures that all modifications are secure and thoroughly documented.
  • Integrating these practices facilitates the streamlining of development workflows for teams, allowing them to focus on innovation while maintaining high-quality standards in their projects.

Authentication

  • You can authenticate in GIT using username and password. For GitHub and Bitbucket the password is a token which can be generated in their platforms.
  • If you use the private key, and you get any issue related to the key, please convert the key to classic OpenSSH format using:

1
ssh-keygen -p -f <privateKeyFile> -m pem -P passphrase -N passphrase

GIT Authentication using DbSchema