Bruno Raljić

A Java Developer's Blog

Carbon 5 – How to keep track of executed SQL scripts

If your application use a database and you often execute scripts manually (extending tables with new fields, adding new values) you need to keep records which scripts are executed and which aren’t. Keep in mind that you usually have more than one database (localhost, one or many production databases) and in addition, there are several developers in a team who can execute those scripts. If you don’t keep the records, soon you’ll end up in a real mess.

The Carbon Five Database Migration framework and Maven plugin for Java provides a simple solution to the problem of managing discrete, incremental changes to databases over time across multiple environments. Each migration is versioned and tracked when applied to the database. Sensible defaults reduce the amount of necessary configuration, though the framework can be configured and extended.Overview on code.google.com

Nikola Čakarević showed me this and now I’m going to post it here because I find it very useful.

Create simple project

Let’s create a simple Maven project. Maybe we should start from creating a database. I created simple schema (`brunoraljic`) with a simple table in it.

CREATE TABLE `brunoraljic`.`post` (
`post_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
`comment_nr` INTEGER UNSIGNED NOT NULL DEFAULT 0,
PRIMARY KEY (`post_id`)
)
ENGINE = InnoDB;

Now, we are going to add few things in our POM file. First, let’s add a plugin and a dependency for mysql connector.

<build>
    <plugins>
        <!-- carbon 5 plugin for db migration -->
        <plugin>
            <groupId>com.carbonfive.db-support</groupId>
            <artifactId>db-migration-maven-plugin</artifactId>
            <version>0.9.9-m5</version>
            <configuration>
                <url>${DB_URL}</url>
                <username>root</username>
                <password>root</password>
                <migrationsPath>${path}</migrationsPath>
                <createSql></createSql>
                <dropSql></dropSql>
            </configuration>
            <dependencies>
                <dependency>
                    <groupId>mysql</groupId>
                    <artifactId>mysql-connector-java</artifactId>
                    <version>5.0.8</version>
                </dependency>
            </dependencies>
        </plugin>
    </plugins>
</build>

As you can see we have few properties. ${DB_URL} will be stored in our profiles, and ${path} will be set in the external configurations. Check in the documentation for further plugin configuration. Now let’s add a profile.

<!-- profiles for carbon 5 db migration tool -->
<profiles>
    <!-- localhost -->
    <profile>
        <id>local</id>
        <activation>
            <property>
                <name>migration</name>
                <value>local</value>
            </property>
        </activation>
        <properties>
            <DB_URL>jdbc:mysql://localhost:3306/brunoraljic</DB_URL>
        </properties>
    </profile>
</profiles>

As you can see, we added a profile for our localhost database. You can add profiles for other databases (e.g. production db). After that, we need to create few folders, where the scripts will be stored (and executed). I created a folder structure src>main>db>scripts for this example.

External configurations

New file

OK, since you are using this tool, you need to create a new file in a specific way. To do it, create a new external configuration in Eclipse.

db-migration:new -Dmigration=local -Dname=DEV- -Dpath=src/main/db/

You may notice the goal :new. That’s for creating new sql file. We also set the profile to be local and name suffix DEV- (so you can easily rename it later e.g. DEV-1, DEV-2). Pay attention to the path. Although I created src/main/db/scripts structure, I put the fresh new files into the src/main/db/. After I finish my sql script, I’ll move it to the scripts folder and then run the execution. After creating, our file name will be something like this 20121223161934_DEV-.sql. Now let’s add some sql in our new file.

ALTER TABLE `brunoraljic`.`post` ADD COLUMN
`new_column` INTEGER UNSIGNED;

Executing scripts

Now, when we have some scripts, we want to execute them. Create new external configuration in eclipse

db-migration:migrate -Dmigration=local -Dpath=src/main/db/scripts/

Now, you can see different goal :migrate. The profile is the same but the path is now pointing to the scripts folder. Move the script you’ve just created with :new goal into the scripts folder and run our new external configuration.

[INFO] Migrating database... applying 1 migration.
[INFO] Running migration 20121223163211_DEV-.sql.
[INFO] Migrated database in 0:00:00.388.
[INFO] ------------------------------------------------------------------------
[INFO] BUILD SUCCESS
[INFO] ------------------------------------------------------------------------
[INFO] Total time: 2.227s
[INFO] Finished at: Sun Dec 23 17:38:17 CET 2012
[INFO] Final Memory: 3M/15M

You can see the number of scripts that is run (applying 1 migration). The good thing about this plugin is that you’ll never execute same scripts twice. If you run it again (without adding anything new), you’ll get the following message:

[INFO] Database is up to date; no migration necessary.

In order to know which script is executed last, carbon 5 added new table in your database. Take a look and you’ll see a table schema_version. If you inspect the data, you’ll see the script name (that’s why it’s important to create a new script with the :new goal), when the script is executed and the executing duration. Now, let’s create another script to remove this new column we just added from our table.

ALTER TABLE `brunoraljic`.`post` DROP COLUMN new_column;

After running the external configuration, you’ll see that the 1 script is executed and schema_version table is updated again.

Conclusion

As you may see in this example, it’s really easy to keep the records which scripts are executed and which are not. You just need to follow the few rules and you’ll never again wonder if you forget to execute some scripts before deployment. I would like to thank Nikola again for sharing his knowledge with me.

, ,

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Show my latest blog post with this comment!