Notes on Databases

Notes on Databases#

This chapter describes how to set up an empty database for Allegra for the officially supported database systems.

Supported Database systems#

Allegra has been tested with the following database systems:

  • PostgreSQL: 18.x (recommended)

  • Microsoft SQL Server: 2017, 2019, 2022

  • MySQL/MariaDB: MySQL 8.4 LTS

  • Oracle: 19c

Hint

Allegra comes with JDBC drivers for MySQL/MariaDB, Postgres and MS SQL Server.

PostgreSQL#

Allegra requires its own PostgreSQL database and a dedicated database user. Run the following commands directly in psql as a superuser (for example, postgres).

1) Create the user role

CREATE ROLE allegra LOGIN PASSWORD '<STRONG_PASSWORD>';

2) Create the database (UTF-8, owned by allegra)

CREATE DATABASE allegra
  WITH OWNER = allegra
       ENCODING = 'UTF8'
       LC_COLLATE = 'C.UTF-8'
       LC_CTYPE   = 'C.UTF-8'
       TEMPLATE   = template0;

3) Connect to the new database

\c allegra

4) Set schema ownership and privileges (required)

GRANT ALL PRIVILEGES ON DATABASE allegra TO allegra;

ALTER SCHEMA public OWNER TO allegra;

GRANT ALL PRIVILEGES ON ALL TABLES    IN SCHEMA public TO allegra;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO allegra;
GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public TO allegra;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT ALL PRIVILEGES ON TABLES    TO allegra;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT ALL PRIVILEGES ON SEQUENCES TO allegra;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT ALL PRIVILEGES ON FUNCTIONS TO allegra;

Note

Replace <STRONG_PASSWORD> with a secure password of your choice. If the locale C.UTF-8 is not available on your system, use any existing UTF-8 locale such as en_US.UTF-8 or de_DE.UTF-8. All steps above are mandatory to ensure Allegra can fully manage its database.

MS SQL Server#

Allegra supports Microsoft SQL Server via the official JDBC driver (com.microsoft.sqlserver.jdbc.SQLServerDriver).

For database provisioning, follow your organization’s standard SQL Server best practices (create a dedicated login/user, create a database, assign the appropriate role, configure backups/monitoring).

MySQL and MariaDB#

Allegra supports MySQL and MariaDB. For database provisioning, follow your organization’s standard SQL best practices (create a dedicated login/user, create a database, assign the appropriate role, configure backups/monitoring).

Oracle#

To use Oracle databases, you must provide a compatible JDBC driver. Copy the driver file to the <INSTALLATION_DIRECTORY>/lib directory and restart Allegra.

Database Connection Configuration#

Allegra connects to the database you created using the configuration settings stored in the <INSTALLATION_DIRECTORY>/conf/application.properties file.

You can set up or update the database connection interactively using:

./allegra.sh setup-db     # Configure database connection
#allegra.bat setup-db

This command writes the key database properties to the configuration file.

Example configuration:

# Database configuration
allegra.db.adapter  = postgresql
allegra.db.driver   = org.postgresql.Driver
allegra.db.url      = jdbc:postgresql://localhost:5432/allegra
allegra.db.username = allegra
allegra.db.password = secret

Make sure the database name, user, and password match the credentials you created earlier. The JDBC driver class and URL must correspond to your chosen database type (PostgreSQL, MySQL, SQL Server, Oracle, etc.).

After modifying the configuration, restart Allegra for the changes to take effect.