Switch between local database and Google Cloud SQL programmatically

Question

I'm working on a project in Spring Boot that until now only used a local Postgres database. However, we are now working on deploying the application to the Google cloud Platform which involves using Cloud SQL. I have found several guides on how to connect to Cloud SQL and decided to follow this one.

However, we can't afford to have separate development databases also running in Cloud SQL so would like to continue using local Postgres databases for development. For this, I wrote the following code:

@Configuration
@EnableTransactionManagement
public class PersistenceContext {
    @Bean
    public DriverManagerDataSource dataSource() {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setUrl(System.getenv("DB_URL"));
        dataSource.setUsername(System.getenv("DB_USER"));
        dataSource.setPassword(System.getenv("DB_PASS"));

        String dbInstance = System.getenv("DB_INSTANCE");

        if (dbInstance != null && !dbInstance.isEmpty()) {
            Properties connectionProperties = new Properties();
            connectionProperties
                .setProperty("socketFactory", "com.google.cloud.sql.postgres.SocketFactory");
            connectionProperties.setProperty("cloudSqlInstance", dbInstance);

            dataSource.setConnectionProperties(connectionProperties);
        }

        return dataSource;
    }
}

With this I hoped that simply not specifying the cloud SQL socket factory and dbInstance would allow me to use a local database. However, when attempting to run the application with only a DB url, user, and password variable set, I run into the following exception:

java.lang.IllegalArgumentException: An instance connection name must be provided in the format <PROJECT_ID>:<REGION>:<INSTANCE_ID>.

The full stacktrace can be found here

the DB URL is configured as follows:

DB_URL: jdbc:postgresql://localhost:5432/soundshare

What can I do to switch between the databases programmatically? (I'd rather not store database details in config files if it can be avoided)

Thanks!

Answer 1

So doing it yourself in code is not a good idea as mentioned by @Thomas Andolf, basically re-inventing the wheel. There are several methods for "externalising" your configurations.

https://docs.spring.io/spring-boot/docs/1.2.2.RELEASE/reference/html/boot-features-external-config.html

Personally I like using os environmental variables when I can because I can easily carry swap it when I deploy as containers as well.

This is a cross section of the application.properties file of a project I'm working on:

spring.datasource.url=${ospec_db_url} spring.datasource.username=${ospec_db_user:somedefault} spring.datasource.password=${ospec_db_password}

On my dev system, I just set the environmental variable and it gets picked up. In my case, I have a text file with my variables like this:

export ospec_db_url=jdbc:postgresql://localhost:5432/ospec_db
export ospec_db_password=somebadasspassword

I just source the text-file and it's applied. I can easily switch between projects doing this. When you package for say K8s, you can read from a secrets file to your env variable and when you go to the cloud, you can pass the values in as variables in your startup scripts.