Skip to main content

Database

Development Environments​

For all development purposes, we use a file-based H2 database which is automatically created during the server startup.

Admin UI​

The UI to the H2 DB can be accessed at http://localhost:8080/h2-console/login.jsp. Credentials to this admin UI can be found here.

Cleanup​

Since it's a file-based database, the entries aren't lost with reboot of the server.

To clean up the database, the database files must be simply deleted and the server must be restarted for new fresh DB files to be recreated. Execute the below command on the root of the folder xpanse project.

rm *.db
H2 is only for development environments

The H2 database is purely for development and test purposes only and must be avoided for production installation of xpanse.

Production Environments​

We plan to support multiple RDBMS flavors. By support, we mean configurations for the database added and also all functionalities tested.

MySQL DB​

At the moment, this is the only DB that's fully supported. To use MySQL as the database for xpanse, it must be activated by starting the application with profile mysql and by replacing the placeholders for databaseusername and password as below.

java -jar xpanse-runtime-1.0.0-SNAPSHOT.jar \
          -Dspring.profiles.active=mysql \
          -Dspring.datasource.username=${database-username} \
          -Dspring.datasource.password=${database-password}

Versions Supported​

Current supported version of MySQL is 8.1.0. This is the latest LTS release of MySQL.

Default Configuration​

The above command will start the xpanse runtime with the following default configurations:

  1. MySQL running on the same machine where xpanse is running. (Same localhost)
  2. Database is listening on port 3306.
  3. Name of the database is xpanse. This must be created on the DB server. It won't be automatically created.

The Default configuration file can be found here.

Overriding Default Configuration​

We can override the above two default configurations by starting the application as below by replacing the placeholders with actual values.

java -jar xpanse-runtime-1.0.0-SNAPSHOT.jar \
          -Dspring.profiles.active=mysql \
          -Dspring.datasource.username=<replace-with-db-username> \
          -Dspring.datasource.password=<replace-with-db-password> \
          -D spring.datasource.url=jdbc:mysql://<replace-with-db-hostname>:<replace-with-db-port>/<replace-with-db-name>

secrets better as environment variables

It's safe to provide the database-related properties as environment variables rather than passing them directly in the command line. In case of this, the same property name must be set in UPPERCASE and underscore separated instead of dot for all variables.

network between xpanse and its database

Using this startup command, the database can run on any machine that's reachable from the xpanse runtime application.

MySQL as a Docker Container​

MySQL offers official Docker images for running a database as a container. More details can be found here on the official page of MySQL website here and on DockerHub here.

Starting new container​

While starting the MySQL docker container for the first time, we can configure database-host, database-port and database-name as below and the same can be used in starting the xpanse runtime using the command described

above.

docker pull mysql:8.1.0

By starting the container with the below command, the database is started by automatically configuring the database with xpanse database name, database user and password. In addition to that, password for the DB root user can also be updated to the value of our choice.

docker run --name ${container-name} \
            -e MYSQL_ROOT_PASSWORD=<replace-with-db-root-password> \
            -e MYSQL_DATABASE=<replace-with-db-name> \
            -e MYSQL_USER=<replace-with-db-user> \
            -e MYSQL_PASSWORD=<replace-with-db-password> \
            -p 3306:<replace-with-db-port> -d mysql:8.1.0
Avoid secrets in command line

To avoid passing database related properties in command line, we can use the --env-file option of the docker run command to store all sensitive data.

Database Objects Creation​

The application automatically creates all database objects such as tables needed by the application when it boots up and uses a database for the first time.