Creating a database schemas

On this page, you’ll learn:

How to create a database schema

Creating a database schema

We’ve just learned about installers and how to create initial/test data. Another frequent use case for installers is to create the database schema for an application. Manually creating a database schema allows a developer to have more fine-grained control over the table structures and their optimizations.

In this section, we’ll create a database schema for the Product entity for which we have used in Initializing application data. To do so, we’ll start by creating an xml file in which we use a liquibase database changelog to create the table. This file should be placed in the resources directory of the project.

Liquibase changelog

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
		xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
		xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
		xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
         http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd">

	<property name="table.product" value="table_product"/> (1)

	<changeSet id="201805231424" author="stg"> (2)
		<preConditions onFail="MARK_RAN"> (3)
			<not>
				<tableExists tableName="${table.product}"/>
			</not>
		</preConditions>

		<comment>Creates product table</comment>

		<createTable tableName="${table.product}"> (4)
			<column name="id" type="java.sql.Types.BIGINT">
				<constraints nullable="false" primaryKey="true" primaryKeyName="pk_tbl_product"/>
			</column>
			<column name="name" type="java.sql.Types.NVARCHAR(255)" />
		</createTable>
	</changeSet>
</databaseChangeLog>

1	Definition of global properties to be used within the changesets. Properties are replaced by their value during execution.
2	A changeset is defined. Changesets always require a unique identifier (`id`) and an author.
3	Preconditions are defined as to when the changeset should be executed. In this example, we check for the existence of the table we are going to create. Should the table exist, then it does not have to be created and this changeset may be marked as ran.
4	A create table statement is defined.

Through the use of an installer, we can now execute the aforementioned liquibase script when the application is started.

Take a look at the liquibase documentation to learn more about its features.

The final step is to add an installer to the project which will execute the liquibase changelog.

SchemaInstaller.java

@Order(1) (1)
@Installer(name = "schema-installer", runCondition = InstallerRunCondition.VersionDifferent, description = "Installs the required database tables") (2)
public class SchemaInstaller extends AcrossLiquibaseInstaller (3)
{
	public SchemaInstaller() {
		super( "classpath:installers/demo/schema/DemoSchemaInstaller.xml" ); (4)
	}
}

1	Installers are executed in the order they are picked up. An explicit ordering can be defined using the `@Order` annotation.
2	The `@Installer` annotation is present along with various attributes are defined. The `version` attribute is not specified because the default value is 1. As soon as we’d make changes to our changelog, we would need to specify a higher version number if the installer should be executed again.
3	For ease of use, an `AcrossLiquibaseInstaller` class has been defined. This is a base class for Installers to execute liquibase changelogs based on a given xml file.
4	Define the path to the xml file that contains the liquibase changelog.

Whilst the application is being started, the installer is automatically picked up and executed.

Startup logging - schema installer is executed

INFO --- [ost-startStop-1] f.a.c.i.AcrossBootstrapInstallerRegistry : Executing installer schema-installer for module DemoApplicationModule (1)
INFO --- [ost-startStop-1] liquibase                                : Successfully acquired change log lock
INFO --- [ost-startStop-1] liquibase                                : Reading from PUBLIC.DATABASECHANGELOG
INFO --- [ost-startStop-1] liquibase                                : classpath:installers/demo/schema/DemoSchemaInstaller.xml: classpath:installers/demo/schema/DemoSchemaInstaller.xml::201805231424::stg: Table table_user created
INFO --- [ost-startStop-1] liquibase                                : classpath:installers/demo/schema/DemoSchemaInstaller.xml: classpath:installers/demo/schema/DemoSchemaInstaller.xml::201805231424::stg: ChangeSet classpath:installers/demo/schema/DemoSchemaInstaller.xml::201805231424::stg ran successfully in 8ms

1	The schema installer is executed.

Just like in Initializing application data, our database table has been created. To check whether our liquibase script has executed, we can also check the DATABASECHANGELOG table, where we can find the changeset we’ve created.

Changeset is present in DATABASECHANGELOG table

The DATABASECHANGELOG table is automatically created by liquibase to keep track of changesets

See the reference documentation for more information on the AcrossLiquibaseInstaller.