Allow configuration of database URL in maven

[agentgt]

First let me just say I love the idea of this project and I had contemplated making something similar awhile back but it would have been a too much work. I may sound critical below but believe me I'm very much liking this project and appreciate the work.

That being said it would be nice if the system-config.xml interpolated properties or if there was an override of the JDBC URL for the Maven plugin (or in general).

For example:

<dbSystemConfig type="POSTGRESQL">
  <schemas>
    <schema name="public" />
  </schemas>
  <environments>
    <dbEnvironment name="test" cleanBuildAllowed="true" jdbcUrl="${v2.database.url}">
    </dbEnvironment>
  </environments>
</dbSystemConfig>

And in Maven or command line you would essentially do -Dv2.database.url (or <properties>) passed in (this would also allow maven profile usage as well).

The user and password are separated out why can't the host and port (aka url)?

Particularly the port as often times our developers will use docker or SSH tunnel (the host can easily be changed by adding an entry in /etc/hosts).

I suppose the work around for now that I will probably implement is to use the maven resources plugin and have the variable expanded on copy prior to running this plugins "deploy" goal.

Speaking of which the goal "deploy" is also extremely confusing as it conflicts with maven's goal of deploy which will actually deploy the jar.

Anyway to give you our use case(s):

Originally we were using Flyway (and we will probably go back to it) to migrate the database and then after that we used jOOQ to generate Java sources.

I understand the environments for enterprise like companies but we treat everything like its basically production. So environments literally makes no since to us.

The only difference between production, testing, staging etc is configuration which is pulled from a configuration server (and in some cases environment variables for bootstrapping). The dbEnvironments completely violates are practices as we do not ship environment specific code but I can see the need for others.

[shantstepanian]

Hi, we can take your use case a few ways:

1. There is a Java API you can use to create your dbEnvironment and execute the deployment, without a system-config.xml. I can point you to that doc, though I understand you are doing this from a maven build
2. I've contemplated the ability to create a system config fully from properties passed in via command line (again to avoid the system config entirely). We can apply this from the maven config as well
3. Lastly, we can allow users to provide a system config that has properties defined, and then to separately pass in key/value pairs to replace the values at deploy time

I believe you are requesting option 3 in your issue. That should be no problem to do, but I wanted to list out the other options too just in case they fit your use case better. (we are looking to add a native Gradle API too in case that would help you). Let us know which you'd prefer to go with for your immediate use case. (We'd probably consider building option 3 anyway, as it is relatively simple to do)

Regarding the deploy goal name, I'm open to adding a new name to execute the deploy (we will still keep the old name for backward compatibility)

FYI, we can support a yaml representing of the configs as well (we do use it internally, but haven't documented it yet and there are a couple things I'd like to clean up, also mentioned in #102 ). If that is a big win for you, we can get that prioritized as well

[agentgt]

There is a Java API you can use to create your dbEnvironment and execute the deployment, without a system-config.xml. I can point you to that doc, though I understand you are doing this from a maven build

Yes that was my plan for the non maven side.

Basically we have two things that do the deployment:

1. The Maven plugin for building. This is because we do code generation (jOOQ) based on the database objects. This is done on the build machines version of the database which is very much similar to the production database but with changes that haven't been deployed.
2. Programmatically when the app is deployed on boot up of the app.

The second point requires cluster support.

We use Postgresql and the canonical way to do this is an Advisory Lock (see postgresql documentation). Thus I could fork the project and add this specific code as mentioned in #111 but for it to be done truly correct requires some re-architecture.

As for YAML support I don't care much for it. I think YAML is vastly overrated. I don't have a problem editing XML... even plain property files work for me. I much rather have point 2 fixed aka #111 then the configuration issues particularly because I can just use the Maven resource filter as a workaround (I think).

[shantstepanian]

Acknowledged; will take a look at #111 first and then we can come back to this

[jrgarlick]

Is this still a priority? It would be incredibly useful to provide DB username, password, hostname, and database name as variables in the system-config.xml file. I'd like to use Obevo in a Continuous Deployment situation where those values are generated at runtime.

It seems like this project has been very quiet for a few months. I hope it's still alive!

[jrgarlick]

I'm guessing this project has gone dark. I hope it's not forever. I did find a way to provide variable names to the Obevo system-config.xml file. You can use the resources plugin to move the database schema files to a temp folder in target/ and filter the system-config.xml file to contain the correct values.

In the src/main/database/system-config.xml file, set some parameters where you want them:

        <dbEnvironment name="default"
            type="POSTGRESQL"
            cleanBuildAllowed="true"
            jdbcUrl="jdbc:postgresql://${DB_HOSTNAME}:${DB_PORT}/${DB_NAME}"
            defaultUserId="postgres"
            defaultPassword="postgres">
            <includeSchemas>public</includeSchemas>
        </dbEnvironment>

In the POM, define some properties:

  <properties>
    <da.noPrompt>true</da.noPrompt>
    <da.env>default</da.env>
    <DB_HOSTNAME>localhost</DB_HOSTNAME>
    <DB_NAME>postgres</DB_NAME>
    <DB_PORT>5432</DB_PORT>
    <DB_USERNAME>postgres</DB_USERNAME>
    <DB_PASSWORD>postgres</DB_PASSWORD>
    <obevo-outputDirectory>target/obevo-${DB_HOSTNAME}-${DB_NAME}</obevo-outputDirectory>
  </properties>

Note the obevo-outputDirectory. This was to support potential parallel deployments into different databases. Let me know if this works or not.

Next, setup the resources plugin to read from src/main/databases and place the files in the temp directory:

      <plugin>
        <artifactId>maven-resources-plugin</artifactId>
        <version>3.0.2</version>
        <executions>
          <execution>
          <id>copy-config</id>
          <phase>process-resources</phase>
          <goals>
            <goal>copy-resources</goal>
          </goals>
          <configuration>
            <outputDirectory>${obevo-outputDirectory}</outputDirectory>
            <resources>        
              <resource>
                <directory>src/main/database</directory>
                <filtering>false</filtering>
              </resource>
              <resource>
                <directory>src/main/database</directory>
                <includes>
                  <include>system-config.xml</include>
                </includes>
                <filtering>true</filtering>
              </resource>
            </resources>          
          </configuration>        
          </execution>
        </executions>
      </plugin>

Finally, configure the Obevo plugin to use those files:

      <plugin>
        <groupId>com.goldmansachs.obevo</groupId>
        <artifactId>obevo-maven-plugin</artifactId>
        <version>8.1.1</version>
        <configuration>
          <user>${DB_USERNAME}</user>
          <password>${DB_PASSWORD}</password>
          <sourcePath>${obevo-outputDirectory}</sourcePath>
          <noPrompt>${da.noPrompt}</noPrompt>
          <env>${da.env}</env>
        </configuration>
        <dependencies>
          <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <version>42.2.16</version>
          </dependency>
        </dependencies>
      </plugin>

This should let you do migrations on databases based on runtime configs. Works on my machine! :P

[sandeep-chekuri]

We are going to start actively working on this project.
Can you submit an MR and follow CONTRIBUTING.md