Why Are There Multiple Database Options?
Leap exists to enable rapid development of system integrations and custom services with a reliable and simplified process. In order to live up to that statement, Leap needs to be flexible while maintaining high quality default configurations. Configurations are not limited to values in a text file, they include the set of supporting technologies deployed along with Leap at both development and production phases.
The databases in question only contain Leap Framework data and is not designed to be used by individual features for data storage.
Over the last few years, the Leap development team ran into a specific problem over and over again: the client infrastructure was not prepared, either by limited physical resources or lack of permissions granted to the team installing Leap.
We saw this manifest itself in many ways, usually it meant that the entire Leap technology stack must be installed on a single server. That is not a problem in itself, but when user accounts lack permission to download and install the tools necessary for a successful and timely deployment, problems arise.
The second complication occurs on a more regular basis, when developing Leap projects a developer must install a specific database to their local system and configure it accordingly. This leads to the possibility of:
- Database version mismatches between team members
- Additional system resources required for optimal project performance
- Using a single large schema for all leap projects present on a workstation
Solution: Reduce Database Overhead and Complexity
In an attempt to resolve a subset of the problem listed above, Leap now uses HSQLDB as the default database and supports MySQL when properly configured. Because HSQLDB runs as an executable JAR, it is portable with Leap projects.
HSQLDB does not work as an in-memory DB with Leap
At first the goal was to use HSQLDB in-memory, however, due to the lack of persistence between restarts it is advised to use it as a Server. Although HSQLDB does not fix all of the issues, it’s a step in the right direction. In the following sections you will learn about HSQLDB, MySQL, and which scenarios each may serve you better.
HSQLDB with Leap
As described by the authors, HSQLDB is a RDBMS built entirely in Java; it can be embedded into Java applications or run in Client-Server mode and supports transactions. Leap uses HSQLDB in Client-Server mode as the default configuration. It was discovered that running in-memory was not applicable for Leap and Permastore related data.
Each Leap project has a separate HSQLDB instance
In order to make Leap projects portable while using HSQLDB we chose to include a copy of the database and client with each Leap project created by the Leap CLI with the command leap-cli -configDB. By doing this, we remove the need for installing the database and client when we try to deploy a Leap application to a destination.
Developers can check if HSQLDB is running by using the command leap-cli -serverStatus -port 9001. For more interactions with Leap CLI review the documentation.
Important note: this configuration is designed for small deployments and development only. The primary reason is that the database cannot be scaled and managed independently.
MySQL with Leap
MySQL is an open-source RDBMS provided by Oracle with both Community and Enterprise editions. To this point, we’ve found that the community edition serves our needs – it is possible that we may swap this with MariaDB in the future depending on licensing and divergence in performance at scale.
While creating a new project using the Leap CLI MySQL can be used as the default database by using the command leap-cli -configDB -dbType mysql. Prior to version 1.12.x MySQL 5.7.x was the default database for Leap.
In order to use MySQL with Leap it is required that MySQL 8.0 is installed on the host system or remote server prior to creating the leap project.
Due to the fact that MySQL is installed either on the host or remote database server, it is considered external from the Leap application. This excellent for scalability and maintenance in deployments, but leads to a potentially bloated shared data store when use for development.
Choosing the Right Database for Leap
Depending on the type of development and the destination of the Leap application there are different sets of guidelines to keep in mind. Each of which are described in the lists below.
Development Considerations
- Multiple different projects on the development machine
- It’s up to developer preference, however, we’ve found that HSQLDB is preferred since the DB is contained within the project and there will not be a chance of sharing data between projects.
- Resource Consumption
- Leap developers are often required to run a multitude of services in the development environment in order for the application to function properly. Because HSQLDB uses fewer system resources than MySQL the former is preferred.
- Project Specification
- If it is known ahead of time that the application will need to scale up, it’s a good idea to start with MySQL.
Deployment Considerations
- Data Resilience
- In production it is often required that the database is replicated and hosted remotely. In this case, MySQL offers better compatibility and a rich feature-set for maintenance. At this time HSQLDB is only configured to run within the project directory of Leap, so MySQL is a clear winner in this regard.
- A subset of Leap applications deployed to customer servers are designed specifically to have a small footprint and operate with low overhead. This includes applications like Elastic Communicator & Leap Forwarding Feature. For these scenarios HSQLDB is preferred.
Container Deployments
At this time we’re still evaluating the functionality of MySQL and HSQLDB as it relates to containerized Leap deployments. However, a few of the key concepts still apply – expect this list to update regularly.
- Replication
- Because HSQLDB is embedded in the application we would have one new DB for each replica of the Leap Feature Container. This is not ideal as it does not add resilience to the deployment or enable load balancing.
- MySQL has pre-built containers that we can easily communicate with and deploy in replica sets. This allows for horizontal scaling of the database without impacting the application containers.
- File System
- When we create images for Leap applications, they should rarely rely on the filesystem of the pod in order to store data as pods can come and go regularly. In order to appropriately use HSQLDB we would need to find a way to bind the database files with a remote file store using NFS. This is not impossible, but it is not a standard practice for HSQL.
- MySQL has pre-configured containers that are easily mounted with a remote file store such as NFS, data loss and fragility are not an issue.