Errors using Maven Mojo sql:execute

postgresql maven sql-maven-plugin
Mike picture Mike · Dec 6, 2011 · Viewed 11k times · Source

TL;DR version: I want to be able to use the Maven Mojo SQL Plugin to create/drop any given table in my DB schema (or load data for those tables) at will via the mvn command-line. How?

I'm a long-time Java developer, but for the most part I've been living in an ant-based world. I like the power and explicitness of ant, and the control that it gives me over everything. However, in my new job, there's a push to use maven. So I've decided to learn it using a project I'm working on at home.

One of the things that I have set up on a different personal project is the ability to completely set up and tear down my Postgres database from ant on the command line. I can slice and dice any table, sequence, and integration test data that I please, individually or in concert. Sure, it means that I have about a gajillion ant targets, but it works very well. I like this; it has served me quite well over the years.

In researching how to accomplish this in Maven over the weekend, I found the Mojo SQL Maven Plugin. After looking at the usage page (and I use that term loosely, as it's really just a single semi-example with no explanations) and the example page, I was able to come up with some changes to my pom.xml file. I fixed some obvious typos in the example (postgressql), and referenced the PostgreSQL JDBC page to make sure I had the JDBC connection string correct. I'll paste all of the pom.xml (modified to protect the guilty) below:

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>com.mycompany.myapp</groupId>
    <artifactId>myapp</artifactId>
    <packaging>jar</packaging>
    <version>1.0-SNAPSHOT</version>
    <name>myapp</name>
    <url>http://maven.apache.org</url>

    <repositories>
        <repository>
            <id>JBoss</id>
            <url>https://repository.jboss.org/nexus/content/groups/public/</url>
        </repository>
    </repositories>

    <dependencies>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.10</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-core</artifactId>
            <version>4.0.0.CR7</version>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.codehaus.mojo</groupId>
                <artifactId>sql-maven-plugin</artifactId>
                <version>1.5</version>

                <dependencies>
                    <dependency>
                        <groupId>postgresql</groupId>
                        <artifactId>postgresql</artifactId>
                        <version>8.3-606.jdbc4</version>
                    </dependency>
                </dependencies>

                <configuration>
                    <driver>org.postgresql.Driver</driver>
                    <url>jdbc:postgresql://localhost:5432/myapp</url>
                    <settingsKey>myapp</settingsKey>
                    <!--all executions are ignored if -Dmaven.test.skip=true-->
                    <skip>${maven.test.skip}</skip>
                </configuration>

                <executions>
                    <execution>
                        <id>drop-db-before-test-if-any</id>
                        <phase>process-test-resources</phase>
                        <goals>
                            <goal>execute</goal>
                        </goals>
                        <configuration>
                            <!-- need another database to drop the targeted one -->
                            <url>jdbc:postgresql://localhost:5432/template1</url>
                            <autocommit>true</autocommit>
                            <sqlCommand>drop database myapp</sqlCommand>
                            <!-- ignore error when database is not avaiable -->
                            <onError>continue</onError>
                        </configuration>
                    </execution>

                    <execution>
                        <id>create-db</id>
                        <phase>process-test-resources</phase>
                        <goals>
                            <goal>execute</goal>
                        </goals>
                        <configuration>
                            <url>jdbc:postgresql://localhost:5432/template1</url>
                            <!-- no transaction -->
                            <autocommit>true</autocommit>
                            <sqlCommand>create database myapp</sqlCommand>
                        </configuration>
                    </execution>

                    <execution>
                        <id>create-schema</id>
                        <phase>process-test-resources</phase>
                        <goals>
                            <goal>execute</goal>
                        </goals>
                        <configuration>
                            <autocommit>true</autocommit>
                            <srcFiles>
                                <srcFile>src/main/sql/create_person.sql</srcFile>
                            </srcFiles>
                        </configuration>
                    </execution>

                    <execution>
                        <id>create-data</id>
                        <phase>process-test-resources</phase>
                        <goals>
                            <goal>execute</goal>
                        </goals>
                        <configuration>
                            <orderFile>ascending</orderFile>
                            <fileset>
                                <basedir>${basedir}</basedir>
                                <includes>
                                    <include>src/test/sql/person_data.sql</include>
                                </includes>
                            </fileset>
                        </configuration>
                    </execution>

                    <!-- drop db after test -->
                    <execution>
                        <id>drop-db-after-test</id>
                        <phase>test</phase>
                        <goals>
                            <goal>execute</goal>
                        </goals>
                        <configuration>
                            <url>jdbc:postgresql://localhost:5432/template1</url>
                            <autocommit>true</autocommit>
                            <sqlCommand>drop database myapp</sqlCommand>
                        </configuration>
                    </execution>
                </executions>
            </plugin>
        </plugins>
    </build>
</project>

Now, since I haven't created the database, it doesn't show up in a \l on the PG command-line:

[mike@mike myapp]$ psql template1
Welcome to psql 8.3.5, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

template1=# \l
        List of databases
   Name    |  Owner   | Encoding 
-----------+----------+----------
 postgres  | postgres | UTF8
 template0 | postgres | UTF8
 template1 | postgres | UTF8
(3 rows)

Thus, when I run mvn sql:execute, I expect my database to get created...Or at least not to fail on the drop-db-before-test-if-any task since that is set to continue on error. But of course:

[mike@mike myapp]$ mvn sql:execute
[INFO] Scanning for projects...
[INFO]                                                                         
[INFO] ------------------------------------------------------------------------
[INFO] Building myapp 1.0-SNAPSHOT
[INFO] ------------------------------------------------------------------------
[INFO] 
[INFO] --- sql-maven-plugin:1.5:execute (default-cli) @ myapp ---
[INFO] ------------------------------------------------------------------------
[INFO] BUILD FAILURE
[INFO] ------------------------------------------------------------------------
[INFO] Total time: 1.667s
[INFO] Finished at: Mon Dec 05 20:22:17 CST 2011
[INFO] Final Memory: 3M/81M
[INFO] ------------------------------------------------------------------------
[ERROR] Failed to execute goal org.codehaus.mojo:sql-maven-plugin:1.5:execute (default-cli) on project myapp: FATAL: database "myapp" does not exist -> [Help 1]
[ERROR] 
[ERROR] To see the full stack trace of the errors, re-run Maven with the -e switch.
[ERROR] Re-run Maven using the -X switch to enable full debug logging.
[ERROR] 
[ERROR] For more information about the errors and possible solutions, please read the following articles:
[ERROR] [Help 1] http://cwiki.apache.org/confluence/display/MAVEN/MojoExecutionException

The error page mentioned on the last line there isn't helpful; it just tells me that a plugin caused the error, not Maven itself.

So let's run it with the -X switch. I'll just post the interesting part of the error:

[ERROR] Failed to execute goal org.codehaus.mojo:sql-maven-plugin:1.5:execute (default-cli) on project myapp: FATAL: database "myapp" does not exist -> [Help 1]
org.apache.maven.lifecycle.LifecycleExecutionException: Failed to execute goal org.codehaus.mojo:sql-maven-plugin:1.5:execute (default-cli) on project myapp: FATAL: database "myapp" does not exist
    at org.apache.maven.lifecycle.internal.MojoExecutor.execute(MojoExecutor.java:217)
    at org.apache.maven.lifecycle.internal.MojoExecutor.execute(MojoExecutor.java:153)
    at org.apache.maven.lifecycle.internal.MojoExecutor.execute(MojoExecutor.java:145)
    at org.apache.maven.lifecycle.internal.LifecycleModuleBuilder.buildProject(LifecycleModuleBuilder.java:84)
    at org.apache.maven.lifecycle.internal.LifecycleModuleBuilder.buildProject(LifecycleModuleBuilder.java:59)
    at org.apache.maven.lifecycle.internal.LifecycleStarter.singleThreadedBuild(LifecycleStarter.java:183)
    at org.apache.maven.lifecycle.internal.LifecycleStarter.execute(LifecycleStarter.java:161)
    at org.apache.maven.DefaultMaven.doExecute(DefaultMaven.java:319)
    at org.apache.maven.DefaultMaven.execute(DefaultMaven.java:156)
    at org.apache.maven.cli.MavenCli.execute(MavenCli.java:537)
    at org.apache.maven.cli.MavenCli.doMain(MavenCli.java:196)
    at org.apache.maven.cli.MavenCli.main(MavenCli.java:141)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at org.codehaus.plexus.classworlds.launcher.Launcher.launchEnhanced(Launcher.java:290)
    at org.codehaus.plexus.classworlds.launcher.Launcher.launch(Launcher.java:230)
    at org.codehaus.plexus.classworlds.launcher.Launcher.mainWithExitCode(Launcher.java:409)
    at org.codehaus.plexus.classworlds.launcher.Launcher.main(Launcher.java:352)
Caused by: org.apache.maven.plugin.MojoExecutionException: FATAL: database "myapp" does not exist
    at org.codehaus.mojo.sql.SqlExecMojo.execute(SqlExecMojo.java:618)
    at org.apache.maven.plugin.DefaultBuildPluginManager.executeMojo(DefaultBuildPluginManager.java:101)
    at org.apache.maven.lifecycle.internal.MojoExecutor.execute(MojoExecutor.java:209)
    ... 19 more
Caused by: org.postgresql.util.PSQLException: FATAL: database "myapp" does not exist
    at org.postgresql.core.v3.ConnectionFactoryImpl.readStartupMessages(ConnectionFactoryImpl.java:444)
    at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:99)
    at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:66)
    at org.postgresql.jdbc2.AbstractJdbc2Connection.<init>(AbstractJdbc2Connection.java:124)
    at org.postgresql.jdbc3.AbstractJdbc3Connection.<init>(AbstractJdbc3Connection.java:30)
    at org.postgresql.jdbc4.AbstractJdbc4Connection.<init>(AbstractJdbc4Connection.java:29)
    at org.postgresql.jdbc4.Jdbc4Connection.<init>(Jdbc4Connection.java:24)
    at org.postgresql.Driver.makeConnection(Driver.java:386)
    at org.postgresql.Driver.connect(Driver.java:260)
    at org.codehaus.mojo.sql.SqlExecMojo.getConnection(SqlExecMojo.java:899)
    at org.codehaus.mojo.sql.SqlExecMojo.execute(SqlExecMojo.java:612)
    ... 21 more

But, but, but...<onError>continue</onError>!

So, to the questions:

  1. What am I doing wrong? Is it my expectations, or my code?
  2. You'll notice that I have a create-person.sql file. I know from the examples that I can have multiple files there, such as create-address.sql. But in ant, I have the ability to create the address table separately from the person table, so long as I perform the ant tasks keeping in mind the order of referential integrity. Is something like that possible with maven? If so, how?

Sorry for the verbosity, and thanks in advance for any assistance.

Answer

Raghuram picture Raghuram · Dec 6, 2011

Failed to execute goal org.codehaus.mojo:sql-maven-plugin:1.5:execute (default-cli)

default-cli is the special executionId when the plugin is invoked from command-line. See this.

You have bound all the sql plugin execution to maven lifecycle phases, but you are trying to invoke the plugin directly.

mvn test should work.

Here is a related SO discussion.

Related questions

How do you add PostgreSQL Driver as a dependency in Maven?

I'm trying to develop a Java application with Maven while using Hibernate with a PostgreSQL database for persistence. I don't understand how I'm supposed to connect the PostgreSQL drivers to my application. I get that you add dependencies in Maven's …

Read More
Embedded PostgreSQL for Java JUnit tests

Is there an embedded PostgreSql so that we could unit test our PostgreSql driven application? Since PostgreSql has some dialects, it's better to use embedded PostgreSql itself than other embedded databases. Embedded does not necessarily mean it must be embedded …

Read More
Cannot find database driver: org.postgresql.Driver

Im trying to change a project a bit, by upgrading it with Liquibase. Its a Java EE project. So im using the liquibase-maven-plugin. So far i have in my pom.xml: <plugin> <groupId>org.liquibase</…

Read More