Database Query Server using Quarkus

We are updating our application to Quarkus and I have this requirement of supporting datasouces that users of our application can create.

This is already supported in Widlfly, but does not seem to be supported on Quarkus. Before implementing this in our system we created a PoC which we share in this post.

The problem

Agroal is the part of Quarkus responsible for DataSources and pool connections. It is easy to configure datasources in Quarkus, but some properties only works during development time, after the JAR for distribution is built then you can't change some properties.

The solution is programatically create datasources so the application is flexible in a way that we can add datasets using system properties. We also must make sure that all supported data base drivers are in the application so users just needs to setup properties and no other action is required.

All drivers dependencies

Datasource from property

We can create datasources using a map of properties. The properties keys are defined in class AgroalPropertiesReader. When creating a properties reader we can specify a prefix which will be used to read the properties from the source.

To build the prefix we need all datasources mapped by the user followed by the properties setup for each mapped datasource. We can simply support the same properties as AgroalPropertiesReader. Here's a sample configuration

From Java we use Microprofile config to collect the datasource properties to a map, set the correct prefix and then build the properties that will be used to create a datasource. This is done right after the application is started so any error in configuration prevents the service to run.

	import java.sql.SQLException;
	import java.util.HashMap;
	import java.util.List;
	import java.util.Map;
	import java.util.Optional;
	import java.util.Set;
	import java.util.stream.StreamSupport;
	import javax.enterprise.context.ApplicationScoped;
	import javax.enterprise.event.Observes;
	import javax.inject.Inject;
	import javax.sql.DataSource;
	import io.agroal.api.AgroalDataSource;
	import io.agroal.api.configuration.supplier.AgroalPropertiesReader;
	import io.quarkus.runtime.StartupEvent;
	import org.eclipse.microprofile.config.Config;
	import org.eclipse.microprofile.config.inject.ConfigProperty;
	@ApplicationScoped
	public class DataSourceLoader {
	private static final String DATASOURCES = "datasources";
	private static final String DATASOURCE = "datasource";
	private static final String PREFIX_TEMPLATE = DATASOURCE + ".%s.";
	@Inject
	Config config;
	@ConfigProperty(name = DATASOURCES, defaultValue = "")
	Optional<List<String>> datasourcesProp;
	Map<String, DataSource> registeredDataSources;
	void load(@Observes StartupEvent startup) throws SQLException {
	var allProps = new HashMap<String, String>();
	var datasources = datasourcesProp.orElse(List.of());
	registeredDataSources = new HashMap<>();
	StreamSupport.stream(config.getPropertyNames().spliterator(), false)
	.filter(p -> p.startsWith(DATASOURCE))
	.forEach(k -> allProps.put(k, config.getValue(k, String.class)));
	for (String ds : datasources) {
	var prefix = PREFIX_TEMPLATE.formatted(ds, AgroalPropertiesReader.JDBC_URL);
	var agroalProps = new AgroalPropertiesReader(prefix);
	agroalProps.readProperties(allProps);
	registeredDataSources.put(ds, AgroalDataSource.from(agroalProps.get()));
	}
	}
	public Optional<DataSource> getDataSource(String name) {
	return Optional.ofNullable(registeredDataSources.get(name));
	}
	public Set<String> datasources() {
	return registeredDataSources.keySet();
	}
	}

view raw DataSourceLoader.java hosted with ❤ by GitHub

Running Queries

Now that we map all datasources we simply have to expose it via REST to run queries using pure JDBC code. The trick is to transform any query result to a suitable data structure that can be translated to JSON, for this we use a MAP of LIST (the best data structure ever), where the key is the column name and the list are the rows for that specific column:

	import java.sql.Connection;
	import java.sql.SQLException;
	import java.util.ArrayList;
	import java.util.Collection;
	import java.util.HashMap;
	import java.util.List;
	import javax.inject.Inject;
	import javax.ws.rs.GET;
	import javax.ws.rs.POST;
	import javax.ws.rs.Path;
	import javax.ws.rs.PathParam;
	import javax.ws.rs.Produces;
	import javax.ws.rs.core.MediaType;
	import javax.ws.rs.core.Response;
	import javax.ws.rs.core.Response.Status;
	import org.fxapps.datasource.DataSourceLoader;
	@Path("datasource")
	@Produces(MediaType.APPLICATION_JSON)
	public class DataSourceResource {
	@Inject
	DataSourceLoader loader;
	@GET
	public Collection<String> list() {
	return loader.datasources();
	}
	@POST
	@Path("{dsName}/query")
	public Response executeQuery(@PathParam("dsName") String dsName, String query) throws SQLException {
	var dataSourceOp = loader.getDataSource(dsName);
	if (dataSourceOp.isEmpty()) {
	return Response.status(Status.NOT_FOUND).build();
	}
	var ds = dataSourceOp.get();
	var connection = ds.getConnection();
	var result = extractResult(query, connection);
	connection.close();
	return Response.ok(result).build();
	}
	private HashMap<String, List<String>> extractResult(String query, Connection connection) throws SQLException {
	var result = new HashMap<String, List<String>>();
	try (var stmt = connection.createStatement()) {
	var rs = stmt.executeQuery(query);
	var meta = rs.getMetaData();
	var nColumns = meta.getColumnCount();
	while (rs.next()) {
	for (int i = 1; i <= nColumns; i++) {
	var column = meta.getColumnName(i);
	var value = rs.getString(i);
	result.putIfAbsent(column, new ArrayList<String>());
	result.get(column).add(value);
	}
	}
	}
	return result;
	}
	}

view raw DataSourceResource.java hosted with ❤ by GitHub

Now we can start our application and send requests to execute queries using HTTP requests:

curl --silent --data 'select id, variableId, value from VariableInstanceLog' http://localhost:8080/datasource/ds1/query

Finally you can run the built JAR using system properties and it will connect to any database:

java -Ddatasources=ds1,ds2 \

-Ddatasource.ds1.jdbcUrl=jdbc:mariadb://localhost:3306/jbpmdb \

-Ddatasource.ds1.providerClassName=org.mariadb.jdbc.Driver \

-Ddatasource.ds1.maxSize=10 \

-Ddatasource.ds1.principal=jbpm \

-Ddatasource.ds1.credential=jbpm \

-Ddatasource.ds2.jdbcUrl=jdbc:mariadb://localhost:3306/test \

-Ddatasource.ds2.providerClassName=org.mariadb.jdbc.Driver \

-Ddatasource.ds2.maxSize=10 \

-Ddatasource.ds2.principal=repasse \

-Ddatasource.ds2.credential=repasse \

-jar target/configurable-datasource-1.0.0-SNAPSHOT-runner.jar

Conclusion

In this post we show how we can use Agroal and quarkus to create an application that can run queries on any of the supported databases. The code is in my github. 

This application has room for a lot of improvements:

• Create a UI
• Better return error messages from REST endpoint
• Add tests
• Extend for other commands (INSERT, UPDATE and so on)
• Support new data sources creation after the application is running (This could be useful for creating a generic Database Client)

Feel free to send PRs if you implement any of these improvements!