Initialize a database on application startup

1. Create the database initialization ETL

The ImageDB model contains only one table, but its vendor-specific large-object types and binary content make a portable setup script useful. The following ETL creates a metadata table, chooses a vendor-specific schema, loads data, and leaves a place for later upgrades:

<!DOCTYPE etl SYSTEM "http://scriptella.org/dtd/etl.dtd">
<etl>
  <properties>
    <include href="webinit.etl.properties"/>
  </properties>
  <connection driver="$driver" url="$url"
              user="$user" password="$password"/>
  <script>
    CREATE TABLE Metainf (buildnum INTEGER);
    INSERT INTO Metainf VALUES (1);

    <dialect name="h2">
      <include href="h2-schema.sql"/>
    </dialect>
    <dialect name="oracle">
      <include href="oracle-schema.sql"/>
    </dialect>
    <dialect name="mysql">
      <include href="mysql-schema.sql"/>
    </dialect>

    <include href="data.sql"/>
    <onerror message=".*Metainf.*"/>
  </script>

  <query>
    SELECT * FROM Metainf
    <script if="buildnum lt 1">
      <!-- Apply upgrades, then record the new build. -->
      UPDATE Metainf SET buildnum=1;
    </script>
  </query>
</etl>

The *-schema.sql files contain database-specific DDL. The shared data.sql file can reference BLOB content in external files:

INSERT INTO imagedb(image_name, content, description) VALUES (
  'scriptella-logo.png',
  ?{file 'blobs/scriptella-logo.png'},
  'Scriptella ETL logo'
);

On later startups, creating Metainf fails because the table already exists. The matching <onerror> handler lets execution continue to the upgrade query.

2. Package the files with the web application

Place these files in a directory inside the WAR, such as /WEB-INF/db:

• webinit.etl.xml — the database initialization file.
• webinit.etl.properties — connection configuration.
• *-schema.sql — schema creation scripts for each database.
• data.sql — the initial dataset.
• blobs/ — binary files referenced by data.sql.

Also place the required JDBC driver, such as h2.jar, in WEB-INF/lib.

3. Run it from a servlet context listener

In a web application, use the Jakarta Servlet API. The @WebListener annotation registers a listener that executes the ETL when the application starts:

import jakarta.servlet.ServletContext;
import jakarta.servlet.ServletContextEvent;
import jakarta.servlet.ServletContextListener;
import jakarta.servlet.annotation.WebListener;
import java.net.URL;
import scriptella.execution.EtlExecutor;
import scriptella.execution.EtlExecutorException;

@WebListener
public class WebDbInitializer implements ServletContextListener {
  static void initDatabase(URL etlUrl) throws EtlExecutorException {
    EtlExecutor.newExecutor(etlUrl).execute();
  }

  @Override
  public void contextInitialized(ServletContextEvent event) {
    ServletContext context = event.getServletContext();
    try {
      initDatabase(context.getResource("/WEB-INF/db/webinit.etl.xml"));
      context.log("DB script executed");
    } catch (Exception e) {
      throw new IllegalStateException("Database initialization failed", e);
    }
  }
}

Failing application startup is the safer production default: the application should not serve requests if schema creation or migration did not complete. Use best-effort initialization only when the application can safely operate without the database, and make that behavior explicit.

You can register the listener in a Jakarta Servlet 6.1 web.xml deployment descriptor instead if your application does not scan servlet annotations.

For an H2 deployment on Tomcat 11, webinit.etl.properties can contain:

driver=h2
url=jdbc:h2:file:${catalina.home}/db/imagedb
user=sa
password=

Alternative: integrate with Spring

For a Spring application, prefer Java configuration. Place the ETL files under src/main/resources/db, then define an EtlExecutorBean that starts after Spring has supplied its configuration:

import java.io.IOException;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.ClassPathResource;
import scriptella.driver.spring.EtlExecutorBean;

@Configuration
public class DatabaseConfiguration {
  @Bean
  EtlExecutorBean databaseInitializer() throws IOException {
    EtlExecutorBean executor = new EtlExecutorBean();
    executor.setConfigLocation(new ClassPathResource("db/webinit.etl.xml"));
    executor.setAutostart(true);
    return executor;
  }
}

The corresponding properties use the Spring-managed data source:

driver=spring
url=dataSource