Should I define datasources in application or in app server?

Question

I've developed applications (running on Jboss server) with two different teams. One team had datasource configuration inside of the application WAR file and another one had it inside of the application server's standalone.xml. And I'm not sure which approach is better.

So, here are some advantages that I've found in defining datasource inside of the server's standalone.xml.

Defining datasource in server's standalone.xml is more secure than in war file. If the database connection credentials stored in the server's standalone.xml, which is almost never modified, it is safer than having the password inside of the war file, which is often transferred from developer's machines to the server and database configuration is spread by all developers computers.
By having datasource in standalone.xml, we can make war file smaller, as JDBC driver could be installed as a module and can be removed from war file. In addition, loading JDBC as module is more efficient as from classpath.
We can put datasource inside of the standalone.xml if we don't want the application development team be aware of database connection settings.

By having datasource configurations in application WAR file, I see the following advantages, which are:

Development team doesn't have permission to change Jboss configuration files in environment where Jboss is running. So DB connection only can be defined in application.
It is useful in development state, when developers often need to switch between different database connections. For example, the developer can specify connection when building a WAR file.

So I'd like to know if there are another advantages in both approaches. And on your opinion which approach is better?

rest_day · Answer 1 · 2015-08-12T11:50:44.367

5

In addition to the points noted in the question, another advantage in having the datasources outside the application is that it allows for using the same war file in different regions. This would allow the team to have different databases for different environments, like Test, Perf and Prod while using the same war file.

You can do a deployment once and then the war file which has been tested by your QA team can be promoted to the production region. This would make sure that no untested code goes into higher regions while avoiding the need for SCM forks and code freezes.

edited Aug 12 '15 at 11:50

answered Aug 12 '15 at 11:40

rest_day

858
2
13
28

You've covered the key points that I feel make putting the DS outside the app advantageous. Another useful, though not directly related, approach I've had success with on a smaller project is parameterizing the datasource domain (assuming same DB setup for staging, prod, etc) in the server config via something like environment variables. This allows the additional benefit of moving the full stack around including the application server, potentially for something like containerization. I am by no means an expert on this, but it seemed to work well in my limited usage. – whitlaaa Aug 26 '15 at 19:00

score 1 · Answer 2 · answered Aug 26 '15 at 11:31

I favour having the datasource exposed by the application server with a caveat.. You need your development team to at least know their way around with your application server or to have at least access to the jboss console to review the configuration or change it.. Reason being that for example they need to monitor the connection usage of the datasource connection pool.. Since you're speaking about jboss, I don't know if the "live" bean for the datasource with jboss AS exposes the same information natively of for example oracle ucp (ucp .getStatistics is a godSend for more than one reason..).

Consider that EVEN if you internalize the datasource in xml, using the concept of profiles you can make some field of the xml being "filled" with some specific value in one or another property file based on the profile the application is loaded with..

e.g with spring you can surely do

    <beans profile="local">
    <bean id="dataSource" class="oracle.ucp.jdbc.PoolDataSourceFactory" factory-method="getPoolDataSource">
        <property name="URL" value="jdbc:oracle:thin:@db00-ccea.labucs.int:1521:CCEA"/>
        <property name="user" value="myuser_DCI"/>
        <property name="password" value="mypassword_DCI"/>
        <property name="connectionFactoryClassName" value="oracle.jdbc.pool.OracleConnectionPoolDataSource"/>
        <property name="connectionPoolName" value="${name.connection.pool}"/>
        <property name="minPoolSize" value="5"/>
        <property name="maxPoolSize" value="1000"/>
        <property name="maxIdleTime" value="3000"/>
        <property name="maxStatements" value="3000"/>
        <property name="validateConnectionOnBorrow" value="true" />
        <property name="inactiveConnectionTimeout" value="3000" />
        <property name="connectionWaitTimeout" value="3000"/>
        <property name="abandonedConnectionTimeout" value="3000"/>
        <qualifier value ="dataSourceDCI" />
    </bean>
    <orcl:pooling-datasource id="dataAltSource"
        url="jdbc:oracle:thin:@db00-ccea.labucs.int:1521:CCEA" username="some_OWN" password="some_OWN"/>
        <util:properties id="flyway.prop" location="classpath:config_local.properties"/>
</beans>

meaning on local profile load the properties from the config_loca.properties file inside the classpath

and have as well

    <beans profile="qa">
    <bean id="dataSource" class="oracle.ucp.jdbc.PoolDataSourceFactory" factory-method="getPoolDataSource">
        <property name="URL" value="jdbc:oracle:thin:@db00-ccea.labucs.int:1521:CCEA"/>
        <property name="user" value="myuserQA_DCI"/>
        <property name="password" value="myuserQA_DCI"/>
        <property name="connectionFactoryClassName" value="oracle.jdbc.pool.OracleConnectionPoolDataSource"/>
        <property name="connectionPoolName" value="${name.connection.pool}"/>
        <property name="minPoolSize" value="5"/>
        <property name="maxPoolSize" value="1000"/>
        <property name="maxIdleTime" value="3000"/>
        <property name="maxStatements" value="3000"/>
        <property name="validateConnectionOnBorrow" value="true" />
        <property name="inactiveConnectionTimeout" value="3000" />
        <property name="connectionWaitTimeout" value="3000"/>
        <property name="abandonedConnectionTimeout" value="3000"/>
        <qualifier value ="dataSourceDCI" />
    </bean>
    <bean id="dataAltSource" class="oracle.ucp.jdbc.PoolDataSourceFactory" factory-method="getPoolDataSource">
        <property name="URL" value="jdbc:oracle:thin:@db00-ccea.labucs.int:1521:CCEA"/>
        <property name="user" value="myuserQA_OWN"/>
        <property name="password" value="myuserQA_OWN"/>
        <property name="connectionFactoryClassName" value="oracle.jdbc.pool.OracleConnectionPoolDataSource"/>
        <property name="connectionPoolName" value="${name.connection.pool}"/>
        <property name="minPoolSize" value="5"/>
        <property name="maxPoolSize" value="1000"/>
        <property name="maxIdleTime" value="3000"/>
        <property name="maxStatements" value="3000"/>
        <property name="validateConnectionOnBorrow" value="true" />
        <property name="inactiveConnectionTimeout" value="3000" />
        <property name="connectionWaitTimeout" value="3000"/>
        <property name="abandonedConnectionTimeout" value="3000"/>
    </bean>     
    <util:properties id="flyway.prop" location="file:///${prefix.iam.dir}/${filecert.iam.path}/external.properties"/>
</beans>

thus in your QA environment or other non-dev environment you instead refer to the external xml file and not to the one integrated in the war.. You can even include username and password to be "filled" via the internal//external properties file to enhance the security if you're concerned.

score 1 · Answer 3 · answered Aug 26 '15 at 18:49

In order to properly verify your application works, you should try in a staging server before sending it to production.

In that scenario, the war file you install into production, should be the same that you tested, so you shouldn't need to change anything for te application to work in a different environment with different database connections.

So, the database configuration shouldn't be in the war file but in the application server. Additionally, you make the system administrator's live easier because they don't need to manipulate (uncompress and change) your war to install it in a server.

In the very early development live of the application, it can be useful to add the database (and any other development configuration) to reduce the time a developer can put his/her hands on the project and starts programming without having to configure the application in a development application server.

Prashant · Answer 4 · 2015-08-27T04:24:14.780

1

For me, the number one benefit of having all data-source configuration out of the war file is ease of deployment.

If I read your question correctly, you can't ever deploy the exact same build to multiple environments if you include any configuration in the build. A direct implication is you can never deploy you DEV build to QA and more importantly, you can't deploy your QA build to PROD or UAT. This might cause headaches if your process is audited.

If I mis-understood your question, please let me know, otherwise, I hope this helps.

edited Aug 27 '15 at 04:24

answered Aug 27 '15 at 04:19

Prashant

1,002
13
29

You have understood the question correctly. And I agree with your statement, that we can't deploy the same build in different environments. – andriy Aug 27 '15 at 07:38
Thanks! Glad I could contribute. Good luck with your project. – Prashant Aug 27 '15 at 15:50

Should I define datasources in application or in app server?

4 Answers4

Linked