HOWTO Build a health database and FHIR API Server in 15 mins using open source

mayfield.g.kev · 2 July 2017 06:31

If you have maven and java installed, it should run using mvn spring-boot:run

MatthewVita · 5 July 2017 03:22

This is awesome. Thanks @mayfield.g.kev. I will test and report back.

MatthewVita · 5 July 2017 03:37

Hi @mayfield.g.kev, I have successfully spun up the Spring Boot version, but I’m noticing that the “viewer” isn’t there. Is there anyway to integrate this?

mayfield.g.kev · 5 July 2017 06:02

I couldn’t get the the overlay to work with springboot (not sure of the exact reason)

I used a separate webapp as a work around (https://github.com/KevinMayfield/careconnect-hapi-ui-stu3) which is not ideal.

mayfield.g.kev · 19 November 2017 09:04

Update:

To create a FHIR Server, see the HAPI JPA Server notes here: http://hapifhir.io/doc_jpa.html
(please use these instructions in place of those above)

Update on the CareConnect links posted previously:

API Documentation: https://nhsconnect.github.io/CareConnectAPI/index.html
Demo server (of API): http://yellow.testlab.nhs.uk/careconnect-ri/home?serverId=home&pretty=true (currently at release 2 which covers Individuals and Entities plus Observations)

This also uses libraries from the HAPI FHIR open source project but is an example of how an existing PAS, EPR, EDMS, SQL Server, etc, system can be exposed as a FHIR server (The database is SQL and the db model is based on actual systems). Source code can be found here https://github.com/nhsconnect/careconnect-reference-implementation.

juworld · 18 May 2018 03:28

@mayfield.g.kev Thanks for the tutorial. Really helpful esp for someone what is not that fluent in Java. By following your example and the comments I am able to get over connection error to mysql

However, I’m having a bit of a problem with the actual sql statements. I’m getting the following:

jetty_1  | 2018-05-18 03:26:41.570 [scheduledExecutorService-1] WARN  o.h.e.jdbc.spi.SqlExceptionHelper [SqlExceptionHelper.java:129] SQL Error: 1064, SQLState: 42000
jetty_1  | 2018-05-18 03:26:41.571 [scheduledExecutorService-1] ERROR o.h.e.jdbc.spi.SqlExceptionHelper [SqlExceptionHelper.java:131] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'fetch first 1001 rows only' at line 1
jetty_1  | 2018-05-18 03:26:41.575 [scheduledExecutorService-1] ERROR o.s.s.s.TaskUtils$LoggingErrorHandler [TaskUtils.java:96] Unexpected error occurred in scheduled task.
jetty_1  | org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
jetty_1  | 	at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:242)
jetty_1  | 	at ca.uhn.fhir.jpa.config.HapiFhirHibernateJpaDialect.convertHibernateAccessException(HapiFhirHibernateJpaDialect.java:59)
jetty_1  | 	at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:225)
jetty_1  | 	at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:507)
jetty_1  | 	at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:61)
je
...
jetty_1  | Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
jetty_1  | 	at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:63)
jetty_1  | 	at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
jetty_1  | 	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:111)
...
jetty_1  | Caused by: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'fetch first 1000 rows only' at line 1
jetty_1  | 	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:536)
jetty_1  | 	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:513)
...

Could it be because

I don’t have a mysql schema in place?
I’m not using the right driver?
I’m not using the right mysql version?

I’m not sure where to look. Can anyone point me to the right direction?

Thanks in advance,

Justin

mayfield.g.kev · 18 May 2018 05:23

The schema is created automatically by the server.

Which repository/instructions did you use? I’m presuming the one from the hapifhir.io

I suspect it’s the datasource dialect that is wrong, for MySQL 5.7 I’ve used
datasource.dialect=org.hibernate.dialect.MySQL57Dialect

juworld · 18 May 2018 08:27

Thanks Kev! That was it. Got this up and running now

hackerninaritai · 15 October 2018 10:59

Hi @mayfield.g.kev.
In Eclipse Oxygen, I edited FhirServerConfig.java file like this:
Datasource Bean:

@Bean(destroyMethod = "close")
public DataSource dataSource() {
	BasicDataSource retVal = new BasicDataSource();
	try {
		retVal.setDriver(new com.mysql.jdbc.Driver());
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	retVal.setUrl("jdbc:mysql://localhost:3306/dbhapi");
	retVal.setUsername("root");
	retVal.setPassword("root");
	return retVal;
}

dbhapi is an empty database - no table is created.

jpaProperties:

private Properties jpaProperties() {
	Properties extraProperties = new Properties();
	extraProperties.put("hibernate.dialect", "org.hibernate.dialect.MySQL57InnoDBDialect");
	extraProperties.put("hibernate.format_sql", "true");
	extraProperties.put("hibernate.show_sql", "false");
	extraProperties.put("hibernate.hbm2ddl.auto", "update");
	extraProperties.put("hibernate.jdbc.batch_size", "20");
	extraProperties.put("hibernate.cache.use_query_cache", "false");
	extraProperties.put("hibernate.cache.use_second_level_cache", "false");
	extraProperties.put("hibernate.cache.use_structured_entries", "false");
	extraProperties.put("hibernate.cache.use_minimal_puts", "false");
	// the belowing properties are used for ElasticSearch integration
	extraProperties.put(ElasticsearchEnvironment.ANALYZER_DEFINITION_PROVIDER, ElasticsearchMappingProvider.class.getName());
	extraProperties.put("hibernate.search.default.indexmanager", "elasticsearch");
	extraProperties.put("hibernate.search.default.elasticsearch.host", "http://127.0.0.1:9200");
	extraProperties.put("hibernate.search.default.elasticsearch.index_schema_management_strategy", "CREATE");
	extraProperties.put("hibernate.search.default.elasticsearch.index_management_wait_timeout", "10000");
	extraProperties.put("hibernate.search.default.elasticsearch.required_index_status", "yellow");
	return extraProperties;
}

I used Maven build goals: clean compile package.
Build successfully but I think there is something wrong in the created .war file: browseing to API server http://localhost:8080/hapi-fhir-jpaserver-example/ gives me status code 404.

mayfield.g.kev · 16 October 2018 06:53

If you look in your tomcat installation you should have a logs folder. In there you should find a tomcat8-stdout.*.log files.

This probably has details on the actual fault.

hackerninaritai · 16 October 2018 07:48

Hi @mayfield.g.kev.
I open localhost.2018.10.16.log as you said and found a file “localhost.2018.10.16.logs”. I am reading but i don’t think i can debug by myself. Please help. Thank you

16-Oct-2018 12:36:31.665 INFO [ContainerBackgroundProcessor[StandardEngine[Catalina]]] org.apache.catalina.core.ApplicationContext.log No Spring WebApplicationInitializer types detected on classpath
16-Oct-2018 12:36:31.698 INFO [ContainerBackgroundProcessor[StandardEngine[Catalina]]] org.apache.catalina.core.ApplicationContext.log Initializing Spring root WebApplicationContext
16-Oct-2018 12:36:38.554 SEVERE [ContainerBackgroundProcessor[StandardEngine[Catalina]]] org.apache.catalina.core.StandardContext.listenerStart Exception sending context initialized event to listener instance of class [org.springframework.web.context.ContextLoaderListener]
org.springframework.beans.factory.BeanCreationException: Error creating bean with name ‘entityManagerFactory’ defined in ca.uhn.fhir.jpa.demo.FhirServerConfig: Invocation of init method failed; nested exception is javax.persistence.PersistenceException: [PersistenceUnit: HAPI_PU] Unable to build Hibernate SessionFactory
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1710)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:583)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:502)
at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:312)
at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:228)
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:310)
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:200)
at org.springframework.context.support.AbstractApplicationContext.getBean(AbstractApplicationContext.java:1085)
at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:858)
at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:549)
at org.springframework.web.context.ContextLoader.configureAndRefreshWebApplicationContext(ContextLoader.java:409)
at org.springframework.web.context.ContextLoader.initWebApplicationContext(ContextLoader.java:291)
at org.springframework.web.context.ContextLoaderListener.contextInitialized(ContextLoaderListener.java:103)
at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:4643)
at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5109)
at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183)
at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:743)
at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:719)
at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:703)
at org.apache.catalina.startup.HostConfig.deployWAR(HostConfig.java:986)
at org.apache.catalina.startup.HostConfig$DeployWar.run(HostConfig.java:1858)
at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
at java.util.concurrent.FutureTask.run(Unknown Source)
at org.apache.tomcat.util.threads.InlineExecutorService.execute(InlineExecutorService.java:75)
at java.util.concurrent.AbstractExecutorService.submit(Unknown Source)
at org.apache.catalina.startup.HostConfig.deployWARs(HostConfig.java:772)
at org.apache.catalina.startup.HostConfig.deployApps(HostConfig.java:426)
at org.apache.catalina.startup.HostConfig.check(HostConfig.java:1629)
at org.apache.catalina.startup.HostConfig.lifecycleEvent(HostConfig.java:304)
at org.apache.catalina.util.LifecycleBase.fireLifecycleEvent(LifecycleBase.java:123)
at org.apache.catalina.core.ContainerBase.backgroundProcess(ContainerBase.java:1176)
at org.apache.catalina.core.ContainerBase$ContainerBackgroundProcessor.processChildren(ContainerBase.java:1398)
at org.apache.catalina.core.ContainerBase$ContainerBackgroundProcessor.processChildren(ContainerBase.java:1402)
at org.apache.catalina.core.ContainerBase$ContainerBackgroundProcessor.run(ContainerBase.java:1370)
at java.lang.Thread.run(Unknown Source)
Caused by: javax.persistence.PersistenceException: [PersistenceUnit: HAPI_PU] Unable to build Hibernate SessionFactory
at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.persistenceException(EntityManagerFactoryBuilderImpl.java:970)
at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:895)
at org.hibernate.jpa.HibernatePersistenceProvider.createContainerEntityManagerFactory(HibernatePersistenceProvider.java:151)
at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.createNativeEntityManagerFactory(LocalContainerEntityManagerFactoryBean.java:365)
at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.buildNativeEntityManagerFactory(AbstractEntityManagerFactoryBean.java:388)
at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.afterPropertiesSet(AbstractEntityManagerFactoryBean.java:377)
at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.afterPropertiesSet(LocalContainerEntityManagerFactoryBean.java:341)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1769)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1706)
… 34 more
Caused by: org.hibernate.exception.GenericJDBCException: Unable to open JDBC Connection for DDL execution
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:47)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:111)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:97)
at org.hibernate.resource.transaction.backend.jdbc.internal.DdlTransactionIsolatorNonJtaImpl.getIsolatedConnection(DdlTransactionIsolatorNonJtaImpl.java:69)
at org.hibernate.tool.schema.internal.exec.ImprovedExtractionContextImpl.getJdbcConnection(ImprovedExtractionContextImpl.java:60)
at org.hibernate.tool.schema.internal.exec.ImprovedExtractionContextImpl.getJdbcDatabaseMetaData(ImprovedExtractionContextImpl.java:67)
at org.hibernate.tool.schema.extract.internal.InformationExtractorJdbcDatabaseMetaDataImpl.getTables(InformationExtractorJdbcDatabaseMetaDataImpl.java:329)
at org.hibernate.tool.schema.extract.internal.DatabaseInformationImpl.getTablesInformation(DatabaseInformationImpl.java:120)
at org.hibernate.tool.schema.internal.GroupedSchemaMigratorImpl.performTablesMigration(GroupedSchemaMigratorImpl.java:65)
at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.performMigration(AbstractSchemaMigrator.java:207)
at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.doMigration(AbstractSchemaMigrator.java:114)
at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.performDatabaseAction(SchemaManagementToolCoordinator.java:183)
at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.process(SchemaManagementToolCoordinator.java:72)
at org.hibernate.internal.SessionFactoryImpl.(SessionFactoryImpl.java:312)
at org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:460)
at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:892)
… 41 more
Caused by: java.sql.SQLException: Cannot create PoolableConnectionFactory (Client does not support authentication protocol requested by server; consider upgrading MySQL client)
at org.apache.commons.dbcp2.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:2294)
at org.apache.commons.dbcp2.BasicDataSource.createDataSource(BasicDataSource.java:2039)
at org.apache.commons.dbcp2.BasicDataSource.getConnection(BasicDataSource.java:1533)
at org.hibernate.engine.jdbc.connections.internal.DatasourceConnectionProviderImpl.getConnection(DatasourceConnectionProviderImpl.java:122)
at org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentInitiator$ConnectionProviderJdbcConnectionAccess.obtainConnection(JdbcEnvironmentInitiator.java:180)
at org.hibernate.resource.transaction.backend.jdbc.internal.DdlTransactionIsolatorNonJtaImpl.getIsolatedConnection(DdlTransactionIsolatorNonJtaImpl.java:43)
… 53 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Client does not support authentication protocol requested by server; consider upgrading MySQL client
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
at com.mysql.jdbc.Util.getInstance(Util.java:381)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:984)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3491)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3423)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:910)
at com.mysql.jdbc.MysqlIO.secureAuth411(MysqlIO.java:3923)
at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1273)
at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2031)
at com.mysql.jdbc.ConnectionImpl.(ConnectionImpl.java:718)
at com.mysql.jdbc.JDBC4Connection.(JDBC4Connection.java:46)
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:302)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:282)
at org.apache.commons.dbcp2.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:39)
at org.apache.commons.dbcp2.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:256)
at org.apache.commons.dbcp2.BasicDataSource.validateConnectionFactory(BasicDataSource.java:2304)
at org.apache.commons.dbcp2.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:2290)
… 58 more

mayfield.g.kev · 22 June 2019 18:03

Code for a two versions of this is available here

https://github.com/project-wildfyre/clinical-data-repository-sql

This is a hapi server with custom SQL implementation (similar to most EPR databases and shows how a fhir service can be added on to an existing system)

This is the standard HAPI JPA Server wrapped up in docker and spring boot.
I would look at this is you’re starting out with a system/app or just want a fhir server to explore.

See the docker-compose file for running with docker.

The other projects in this repo mostly take existing api’s (soap, hl7v3, ITK, LDAP) and add a Fhir service to them.

yhamla · 25 June 2019 18:39

Any has SQL DDL for me to create complete tables for FHIR resources? I will really appreciate if you can share. Thanks.

P

riksmithies · 25 June 2019 19:16

Someone may, but in general this is difficult for the reasons mentioned here:

(free registration required.)

It says “there is no standard DDL like this because everyone makes different decisions about how they want their tables laid out. Most of the resources have 3-4 levels of nested structure, though there may be more (and some are recursive). You have to decide this for yourself”

There is also an initiative called SQL-on-FHIR that looks at this issue. I don’t know how far it has got:

github.com

FHIR/sql-on-fhir/blob/master/sql-on-fhir.md

# Simplified SQL Projection of FHIR Resources
An SQL-based projection of FHIR resources would open up large, portable datasets to a number of analytic tools. For example, here is a query over FHIR data that computes the average HDL levels for the full system:

```sql
SELECT subject.reference,
       AVG(value.quantity.value) avg_hdl
FROM observation o,
     UNNEST(o.code.coding) c
WHERE c.system = 'http://loinc.org' AND
      c.code = '2085-9' AND
      o.effective.datetime > '2017'
GROUP BY subject.reference
```

Importantly, this approach preserves the nested structures and arrays of FHIR resources using ANSI SQL standards. The results of these queries can then be used as arbitrary tables for further analysis in R or other tools.

Example queries using this pattern can be found in the [examples](examples/) directory of this project.

## Logical Representation for SQL Users
This proposal focuses on a logical representation of FHIR as seen from an SQL user. This may either be a direct reflection of a physical data store, or a dynamic view over a different physical representation, depending on the design of the underlying system. This allows a logical view to best fit the intuitions of a user querying the data while preserving potential physical optimization underneath.

This file has been truncated. show original

But many FHIR implementations either don’t use FHIR natively (FHIR as a facade onto your own non-FHIR RDBMS) or they use NoSQL storage, so there are no tables and no DDL.

Rik

mayfield.g.kev · 25 June 2019 20:04

The HAPI JPA Server (the CDR above is a version of this) uses RDBMS storage but it stores all the resources in a column in one table. This is for the reasons Rik just listed.
If you get HAPI running locally with a hibernate compatible database (postgresql, mysql, sql server, etc) it will build the database for you.

The CCRI (which is also the CDR-Sql above) will also build a database (again it uses hibernate but has only been tested on postgresql). It is not a complete set of FHIR resources but these do get broken down into a traditional RDBMS structure.

Both need some familiarity with java to configure.

Tim_Harsch · 19 February 2020 21:00

links in this blog are 404?
http://jamesagnew.github.io/hapi-fhir/doc_jpa.html
and
https://github.com/KevinMayfield/AyeUp/tree/master/hapi-fhir-jpaserver

mayfield.g.kev · 20 February 2020 06:25

I would recommend using this repository instead