Springboot application.properties for postgres not working - FATAL: database "null" does not exist

Question

The problem I am experiecning is that the auto configuration from Springboot version: 1.5.9.RELEASE seems not to be effective.

Note, I probably will be able to overcome my current problem if I define a bean that returns out a DataSource bean.

Probably the DataSource bean could look very similar to the: Example in the following snippet: https://www.atomikos.com/Documentation/ConfiguringPostgreSQL

I am switching my application.properties configuration to stop using the default H2 database used by spring boot and switching it to postgres.

The configuration snippet illustrated in the following page: https://dzone.com/articles/configuring-spring-boot-for-postgresql

Is definitely not working. Here is what my configuration looks like:

spring.datasource.url=jdbc:postgresql://localhost:5432/dummydb
spring.datasource.username=DUMMYDB
spring.datasource.password=DUMMYDB
spring.jpa.hibernate.ddl-auto=create-drop

This does not work, and why? Apparently for a very simple reason. While the user name and password are definitely of use and getting transferred over at the time that the connection factory tries to build a connection for the database. The URL attribute is of no use when it comes to configuring the data source.

Let me start by giving you the final stack trace:

Caused by: org.postgresql.util.PSQLException: FATAL: database "null" does not exist
    at org.postgresql.core.v3.ConnectionFactoryImpl.readStartupMessages(ConnectionFactoryImpl.java:469) ~[postgresql-9.1-901.jdbc4.jar:na]
    at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:112) ~[postgresql-9.1-901.jdbc4.jar:na]
    at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:66) ~[postgresql-9.1-901.jdbc4.jar:na]
    at org.postgresql.jdbc2.AbstractJdbc2Connection.<init>(AbstractJdbc2Connection.java:125) ~[postgresql-9.1-901.jdbc4.jar:na]
    at org.postgresql.jdbc3.AbstractJdbc3Connection.<init>(AbstractJdbc3Connection.java:30) ~[postgresql-9.1-901.jdbc4.jar:na]
    at org.postgresql.jdbc3g.AbstractJdbc3gConnection.<init>(AbstractJdbc3gConnection.java:22) ~[postgresql-9.1-901.jdbc4.jar:na]
    at org.postgresql.jdbc4.AbstractJdbc4Connection.<init>(AbstractJdbc4Connection.java:30) ~[postgresql-9.1-901.jdbc4.jar:na]
    at org.postgresql.jdbc4.Jdbc4Connection.<init>(Jdbc4Connection.java:24) ~[postgresql-9.1-901.jdbc4.jar:na]
    at org.postgresql.Driver.makeConnection(Driver.java:393) ~[postgresql-9.1-901.jdbc4.jar:na]
    at org.postgresql.Driver.connect(Driver.java:267) ~[postgresql-9.1-901.jdbc4.jar:na]
    at java.sql.DriverManager.getConnection(DriverManager.java:664) ~[na:1.8.0_112]
    at java.sql.DriverManager.getConnection(DriverManager.java:247) ~[na:1.8.0_112]
    at org.postgresql.ds.common.BaseDataSource.getConnection(BaseDataSource.java:91) ~[postgresql-9.1-901.jdbc4.jar:na]
    at org.postgresql.xa.PGXADataSource.getXAConnection(PGXADataSource.java:47) ~[postgresql-9.1-901.jdbc4.jar:na]
    at org.postgresql.xa.PGXADataSource.getXAConnection(PGXADataSource.java:32) ~[postgresql-9.1-901.jdbc4.jar:na]
    at com.atomikos.jdbc.AtomikosXAConnectionFactory.createPooledConnection(AtomikosXAConnectionFactory.java:60) ~[transactions-jdbc-3.9.3.jar:na]
    ... 44 common frames omitted

Why this does not work is because the postgres base data source does not care at all for being configured via URL. Rather it wants to be configured via setters that pass over: databaseName, port, etc... Please have a look at the following class:

org.postgresql.ds.common.BaseDataSource

This class provides multiple setters. For username, for password etcc.. But there is no setter for an URL. The individual elements that compose the URL such as the database name need to go into the dedicated fields.

So instead what the class offers is this getURL() method that is retuning the strange URL where the datbase is null. Here is a code snippet for the method.

/**
     * Generates a DriverManager URL from the other properties supplied.
     */
    private String getUrl()
    {
        StringBuffer sb = new StringBuffer(100);
        sb.append("jdbc:postgresql://");
        sb.append(serverName);
        if (portNumber != 0) {
            sb.append(":").append(portNumber);
        }
        sb.append("/").append(databaseName);
        sb.append("?loginTimeout=").append(loginTimeout);
        sb.append("&socketTimeout=").append(socketTimeout);
        sb.append("&prepareThreshold=").append(prepareThreshold);
        sb.append("&unknownLength=").append(unknownLength);
        sb.append("&loglevel=").append(logLevel);
        if (protocolVersion != 0) {
            sb.append("&protocolVersion=").append(protocolVersion);
        }
        if (ssl) {
            sb.append("&ssl=true");
            if (sslfactory != null) {
                sb.append("&sslfactory=").append(sslfactory);
            }
        }
        sb.append("&tcpkeepalive=").append(tcpKeepAlive);
        if (compatible != null) {
            sb.append("&compatible="+compatible);
        }
        if (applicationName != null) {
            sb.append("&ApplicationName=");
            sb.append(applicationName);
        }

        return sb.toString();
    }

So then the question for me was. Ok... if the postgres data source does not wished to be configured via url but rather via the various different setters, perhaps I can get away by adding to the application.properties file fields such as:

#spring.datasource.databaseName=dummydb

Nope, it turns out this seems to be of no use at all.

After some debugging, I a managed to find out this spring boot auto configuration class:

org.springframework.boot.autoconfigure.jdbc.XADataSourceAutoConfiguration

The above class is very interesting, since it is at the route of the configuration of the atomics JTA transaction subsytem. IT will setup the data source:

org.postgresql.xa.PGXADataSource

But the only problem is that this auto configurator only cares for three attributes in our application.properties file. See the code snippet bellow:

private void bindXaProperties(XADataSource target, DataSourceProperties properties) {
        MutablePropertyValues values = new MutablePropertyValues();
        values.add("user", this.properties.determineUsername());
        values.add("password", this.properties.determinePassword());
        values.add("url", this.properties.determineUrl());
        values.addPropertyValues(properties.getXa().getProperties());
        new RelaxedDataBinder(target).withAlias("user", "username").bind(values);
    }

As you can see, it appears that I am "toasted". The auto configurator has no notion of any other data source properties that could be relevant. Which I find surprising to say the least. I was expecting some sort of class that would not have any thing such as hard-coded subset of properties such as user name and password. I was expecting some sort of code that would trivially be looping all of the applicaton.properties and hunting for any property whose key would start with:

spring.datasource.whatever

And call the datasource.setWhatever on the data source class.

Seems not to be the case.

So I am certain I must be messing up something. I cannot believe that springboot would not be able to configure postgres database without forcing a developer to create his own datasource bean. Postgress is simply too main stream...

So I was wondering if someone could help me figure out how to properly configure the application.properties file to ensure that indeed I can connect to postgres with springboot.

Meanwhile, I will see if I acn work-around this problem by creating programtically a class like:

@AutoConfigureBefore(DataSourceAutoConfiguration.class)
@EnableConfigurationProperties(DataSourceProperties.class)
@ConditionalOnClass({ DataSource.class, TransactionManager.class,
        EmbeddedDatabaseType.class })
@ConditionalOnBean(XADataSourceWrapper.class)
@ConditionalOnMissingBean(DataSource.class)
public class XADataSourceAutoConfiguration implements BeanClassLoaderAware {

But in my case the class would know how to programatically build the postgres data source with all necessary properties to connect.

Many thanks for the help.

Answer 1

The problem is that you didn't create the database in pgAdmin correctly. Create it in the database option, not as a new server: