Getting the back-end to connect to a SQLite database

I recently cloned the openmrs-distro-platform repo and I’m attempting to connect it to a SQLite database.

I added the following file in the root directory of the project: sqlite/database.db.

And then modified the docker-compose.yml:

version: "3.7"

services:
    db:
        image: mariadb:10.3
        command: "mysqld --character-set-server=utf8 --collation-server=utf8_general_ci"
        healthcheck:
            test: 'mysql --user=${OMRS_DB_USER:-openmrs} --password=${OMRS_DB_PASSWORD:-openmrs} --execute "SHOW DATABASES;"'
            interval: 3s
            timeout: 1s
            retries: 5
        environment:
            MYSQL_DATABASE: openmrs
            MYSQL_USER: ${OPENMRS_DB_USER:-openmrs}
            MYSQL_PASSWORD: ${OPENMRS_DB_PASSWORD:-openmrs}
            MYSQL_ROOT_PASSWORD: ${MYSQL_ROOT_PASSWORD:-openmrs}
        volumes:
            - db-data:/var/lib/mysql

    api:
        image: openmrs/openmrs-platform:${TAG:-nightly}
        depends_on:
            - db
        build: .
        ports:
            - "8080:8080"
        environment:
            OMRS_MODULE_WEB_ADMIN: "true"
            OMRS_AUTO_UPDATE_DATABASE: "true"
            OMRS_CREATE_TABLES: "true"

            OMRS_DB_HOSTNAME: db
            OMRS_DB_NAME: ${OPENMRS_DB_NAME:-openmrs}
            OMRS_DB_USERNAME: ${OMRS_DB_USERNAME:-openmrs}
            OMRS_DB_PASSWORD: ${OMRS_DB_PASSWORD:-openmrs}
            OMRS_ADMIN_USER_PASSWORD: ${OMRS_ADMIN_USER_PASSWORD-Admin123}
        healthcheck:
            test: ["CMD", "curl", "-f", "http://localhost:8080/openmrs"]
            timeout: 5s
        volumes:
            - openmrs-data:/openmrs/data
            - ${PWD}/sqlite:/openmrs/sqlite
            - ${PWD}/sqlite/database.db:/openmrs/sqlite/database.db

volumes:
    db-data:
    openmrs-data:

So far I haven’t disconnected the MariaDB service but once I’m able to connect the back-end to the SQLite database, I will be removing the MariaDB service (I’m not worried about breaking things at the moment).

How do I connect the back-end to this new SQLite database I just mounted? Where should I be making my modifications from?

hi @pgregorio i think going forward you will need to also modify the runtimeproperties of openmrs containers as i suppose they provide connection details.

did you think about the runtimeproperty file?

@dkayiwa

Is there a reason why you do not want to use mysql or postgresql?

Thank you @thembo42 I will look into this.

@dkayiwa Our platform is largely centered around SQLite databases because we require small footprints for the apps that we deploy for cost reasons. Right now my job is to see if I can get OpenMRS running on top of a SQLite database because that would also make it play well within our platform. We currently do not have the resources to allot to create interfaces that would let our platform play well with a MySQL database unfortunately.

I would really appreciate all the guidance I can get. Thank you!

@ibacher @dkayiwa

What exact kind of hardware, environment are you running?

As I said in the other thread, it’s going to be a fair bit of a lift. Concretely, there are a few things that need to happen:

  1. We do not bundle a SQLite JDBC Driver with the OpenMRS application, so you’d have to somehow include a SQLite JDBC Driver (the driver actually embeds the SQLite library, so it’s absolutely necessary). I think there’s a folder in Tomcat that you can dump JARs into to have them automatically included in all applications, but you’re probably going to need to modify the openmrs-core project anyways…
  2. We use Hibernate for most SQL stuff in the running application. Hibernate 5 (which is what we currently use) doesn’t have built-in support for a SQLite Dialect, so you’d have to find one (e.g., this project) and also make the JAR available on the OpenMRS classpath.
  3. In the openmrs-runtime.properties file, you’d have to override the hibernate.connection.driver_class property, the hibernate.connection.url property, and the hibernate.dialect property. The first two are controllable with the standard Docker container using the OMRS_DB_DRIVER_CLASS and OMRS_DB_URL environment variables. The dialect isn’t currently modifiable, but it wouldn’t be too hard to add an environment variable to the startup script.
  4. Some of our code assumes that there is a UUID() function that generates a type-4 UUID as a string, so you’d need to make sure that this extension is enabled. We used to rely on having a SOUNDEX() function as well (so when picking a JDBC driver, you’d need one that’s built with the appropriate extension).
  5. In several modules, we have database-specific liquibase changes (example). These changesets would need to be updated to run on SQLite.
  6. After changes are made, you’d need to verify that the application works as expected… I don’t think we’ve ever really tested the full application against an embedded database, so I don’t really know what to expect here.

PS, in your docker-compose.yml file, you probably only need to mount the database itself and not the local sqlite folder. You’d probably need to mount the database inside the OpenMRS Application Directory (/openmrs/data) and ensure the file is writable by the containers openmrs user (which is the permissions the container runs under.

2 Likes

Thank you for the very detailed guide @ibacher! I’ll give these things a try and yes I did find that same SQLite dialect beforehand which is what I was planning to use.

@ibacher Do you mind telling me if I did the first step correctly? There are just way too many repos for OpenMRS that I can’t definitely say which repo to work in.

What I did was to clone the openmrs-distro-platform repo and then modified the Dockerfile to download the JDBC driver to the Tomcat directory:

...
# Clean up after copying needed artifacts
RUN mvn clean $MVN_ARGS_SETTINGS

# Download the SQLite JDBC driver
ADD --chown=root:root https://github.com/xerial/sqlite-jdbc/releases/download/3.41.0.0/sqlite-jdbc-3.41.0.0.jar /usr/local/tomcat/lib/
...

This puts the JAR file inside of /usr/local/tomcat/lib/ which I believe is the correct directory to put the driver into.

Is this more or less what your first instruction is about?

Yeah, I think that’s more or less right :slight_smile:.

@ibacher Could you perhaps go into more detail about step 2?

We use Hibernate for most SQL stuff in the running application. Hibernate 5 (which is what we currently use) doesn’t have built-in support for a SQLite Dialect, so you’d have to find one (e.g., this project) and also make the JAR available on the OpenMRS classpath.

What I’ve tried so far is to modify the pom.xml file in the root directory of openmrs-distro-platform and added the sqlite-dialect as a dependency:

...
<dependency>
    <groupId>com.github.gwenn</groupId>
    <artifactId>sqlite-dialect</artifactId>
    <version>0.1.2</version>
</dependency>
...

I then did the following:

# Rebuild the image
docker compose build

# Create and run the application (container)
docker compose up -d

Once the container is up, I went inside to investigate:

# Enter the container
docker exec -it openmrs-distro-platform-api-1 /bin/bash

# Search for the sqlite-dialect
find / -iname "*sqlite-dialect*" 2> /dev/null
find / -iname "*sqlite*" 2> /dev/null

Unfortunately I can’t find any traces of the sqlite-dialect anywhere on the container. What’s the proper way to install the dialect? Am I installing it on the correct file? I’d even go as far as to ask am I installing it on the correct repo?

Also, what do you mean by:

…and also make the JAR available on the OpenMRS classpath.

What file should I be modifying to achieve this?

My apologies for so many questions - I’m just not overly familiar with the OpenMRS ecosystem yet.

Edit:

I’m currently attempting to modify the pom.xml file for the openmrs-core repo instead. And then use that image from the docker-compose.yml file from openmrs-distro-platform.

So, you’ll need to edit that POM and also probably the one in the api subfolder (dependencies in the main POM generally aren’t part of the package). But, I think this is broadly the right course. In general the distro repos are just packaging, not actual code and this needs to be embedded in the OpenMRS WAR, which is built from openmrs-core.

Thank you @ibacher - I had a hunch that I was making my modifications on the wrong file.

I can now see:

sh-4.2# find / -iname "*sqlite-dialect*" 2> /dev/null
/root/.m2/repository/com/github/gwenn/sqlite-dialect
/root/.m2/repository/com/github/gwenn/sqlite-dialect/0.1.2/sqlite-dialect-0.1.2.pom.sha1
/root/.m2/repository/com/github/gwenn/sqlite-dialect/0.1.2/sqlite-dialect-0.1.2.jar
/root/.m2/repository/com/github/gwenn/sqlite-dialect/0.1.2/sqlite-dialect-0.1.2.jar.sha1
/root/.m2/repository/com/github/gwenn/sqlite-dialect/0.1.2/sqlite-dialect-0.1.2.pom
/usr/local/tomcat/webapps/openmrs/WEB-INF/lib/sqlite-dialect-0.1.2.jar
/openmrs_core/webapp/target/openmrs/WEB-INF/lib/sqlite-dialect-0.1.2.jar

So I think this should be half of what I need to do. The only thing left is when you said make the JAR available on the OpenMRS classpath - can you direct me to where I should be looking to make this change?

If it’s in the webapp’s WEB-INF/lib folder, that’s the job done.

Should I have then installed the SQLite JDBC driver in the same POM file? Basically installing both the JDBC driver and the SQLite dialect as dependencies inside the openmrs-core/api/pom.xml file instead of using the ADD directive inside the Dockerfile?

So, if you’re willing to contribute this back to the community, it would be best to do the POM modification, but just for getting something working, it doesn’t matter either way.

1 Like

Contributing this back to the community isn’t our top priority at the moment as we are currently just assessing the possibility of getting OpenMRS running on top of a SQLite database but my boss did mention before that if this project goes well then we plan to make it available eventually. I might as well do this the proper way since it’s not that difficult. Thanks a lot for your help! I expect to have more questions for you so I apologize for taking some of your time!

@ibacher I have been attempting to do the third item on your guide:

I believe I set the connection.driver_class and connection.url correctly (at least based on this guide):

services:
  api:
    ...
    environment:
      OMRS_DB_URL: ${OMRS_DB_URL:-jdbc:sqlite:/openmrs/data/database.sqlite3}
      OMRS_DB_DRIVER_CLASS: ${OMRS_DB_DRIVER_CLASS:-org.sqlite.JDBC}
    volumes:
      - openmrs-data:/openmrs/data/
      - ${PWD}/sqlite/database.sqlite3:/openmrs/data/database.sqlite3
    ...

This uses a mounted volume into /openmrs/data/database.sqlite3 and the DB_URL is jdbc:sqlite:/openmrs/data/database.sqlite3 which I think should be correct.

Could you confirm if I made the expected modifications or should I redo this? I haven’t started doing the hibernate.dialect variable yet. Mostly because I’m not sure if it’s supposed to have a prefix of hibernate. The strings you provided me were:

  • hibernate.connection.driver_class
  • hibernate.connection.url
  • hibernate.dialect

However when I looked at the generated strings I got:

  • connection.driver_class
  • connection.url

Should I do it as hibernate.dialect, or hibernate.connection.dialect, or connection.dialect?

Yes that looks right. And yeah, you’re right. The Hibernate property is called hibernate.driver_class but I think we populate that with connection.driver_class. Dialect, though, needs to be hibernate.dialect.

@ibacher For the hibernate environment variable, I added this on the docker-compose.yml file:

OMRS_HIBERNATE_DIALECT: ${OMRS_HIBERNATE_DIALECT:-org.sqlite.hibernate.dialect.SQLiteDialect}

And on the startup-init.sh file, I added the following modifications:

...
# SQLite dialect for Hibernate
OMRS_HIBERNATE_DIALECT=${OMRS_HIBERNATE_DIALECT:-}
...
echo "Writing out $OMRS_SERVER_PROPERTIES_FILE"

cat > $OMRS_SERVER_PROPERTIES_FILE << EOF
add_demo_data=${OMRS_ADD_DEMO_DATA}
admin_user_password=${OMRS_ADMIN_USER_PASSWORD}
auto_update_database=${OMRS_AUTO_UPDATE_DATABASE}
connection.driver_class=${OMRS_DB_DRIVER_CLASS}
connection.username=${OMRS_DB_USERNAME}
connection.password=${OMRS_DB_PASSWORD}
connection.url=${OMRS_DB_URL}
create_database_user=${OMRS_CREATE_DATABASE_USER}
create_tables=${OMRS_CREATE_TABLES}
has_current_openmrs_database=${OMRS_HAS_CURRENT_OPENMRS_DATABASE}
hibernate.dialect=${OMRS_HIBERNATE_DIALECT}   <------- added this line
install_method=${OMRS_INSTALL_METHOD}
module_web_admin=${OMRS_MODULE_WEB_ADMIN}
module.allow_web_admin=${OMRS_MODULE_WEB_ADMIN}
EOF

And I believe that should wrap up step 3 on your guide?

Also, when I docker compose up -d, a file named openmrs-merged.properties gets created. I added it to the .gitignore file because I don’t think that should be commited to the repo. Is this a correct assumption?