Skip to content

# Database Configuration

ejabberd uses its internal Mnesia database by default. However, it is possible to use a relational database, key-value storage or an LDAP server to store persistent, long-living data.

ejabberd is very flexible: you can configure different authentication methods for different virtual hosts, you can configure different authentication mechanisms for the same virtual host (fallback), you can set different storage systems for modules, and so forth.

## Supported storages

The following databases are supported by ejabberd:

- `Mnesia`. Used by default, nothing to setup to start using it

- `MySQL`. Check the tutorial Using ejabberd with MySQL

- `PostgreSQL`

- `MS SQL Server/SQL Azure`. Check the Microsoft SQL Server section

- `SQLite`

- Any `ODBC` compatible database

- `Redis`(only for transient data). Check the Redis section

- `LDAP` is documented in the LDAP section

## Virtual Hosting

If you define several host names in the `ejabberd.yml` configuration file, probably you want that each virtual host uses a different configuration of database, authentication and storage, so that usernames do not conflict and mix between different virtual hosts.

For that purpose, the options described in the next sections must be set inside the host_config top-level option for each virtual host).

For example:

    host_config:
      public.example.org:
        sql_type: pgsql
        sql_server: localhost
        sql_database: database-public-example-org
        sql_username: ejabberd
        sql_password: password
        auth_method: [sql]

## Default database

You can simplify your configuration by setting the default database with the `default_db` top-level option:

- it sets the default authentication method when the `auth_method` top-level option is not configured
- it defines the database to use in ejabberd modules that support the `db_type` option, when that option is not configured.

## Database Schema

updated in 24.06

update

Modify the database schema and all its tables to match the installed ejabberd version. Not to be confused with upgrade ejabberd or switch schema.

The update_sql_schema top-level option allows ejabberd to create and update the tables automatically in the SQL database when using MySQL, PostgreSQL or SQLite. That option was added in ejabberd 23.10, and enabled by default in 24.06. If you can use that feature:

1. Create the database in your SQL server
2. Create an account in the SQL server and grant it rights in the database
3. Configure in ejabberd the SQL Options that allow it to connect
4. Start ejabberd ...
5. and it will take care to create the tables (or update them if they exist from a previous ejabberd version)

If that option is disabled, or you are using a different SQL database, or an older ejabberd release, then you must create the tables in the database manually before starting ejabberd. The SQL database schema files are available:

- If installing ejabberd from sources, sql files are in the installation directory. By default: `/usr/local/lib/ejabberd/priv/sql`

- If installing ejabberd from Process-One installer, sql files are in the ejabberd's installation path under `<base>/lib/ejabberd*/priv/sql`

See ejabberd SQL Database Schema for details on database schemas.

## Default and New Schemas

If using MySQL, PostgreSQL, Microsoft SQL or SQLite, you can choose between two database schemas:

- the default schema is preferable when serving one massive domain,
- the new schema is preferable when serving many small domains.

The default schema stores only one XMPP domain in the database. The XMPP domain is not stored as this is the same for all the accounts, and this saves space in massive deployments. However, to handle several domains, you have to setup one database per domain and configure each one independently using host_config, so in that case you may prefer the new schema.

The new schema stores the XMPP domain in a new column `server_host` in the database entries, so it allows to handle several XMPP domains in a single ejabberd database. Using this schema is preferable when serving several XMPP domains and changing domains from time to time. However, if you have only one massive domain, you may prefer to use the default schema.

To use the new schema, edit the ejabberd configuration file and enable new_sql_schema top-level option:

    new_sql_schema: true

When creating the tables, if ejabberd can use the update_sql_schema top-level option as explained in the Database Schema section, it will take care to create the tables with the correct schema.

On the other hand, if you are creating the tables manually, remember to use the proper SQL schema! For example, if you are using MySQL and choose the default schema, use `mysql.sql`. If you are using PostgreSQL and need the new schema, use `pg.new.sql`.

switch

Change the database schema and all its tables from `default` schema to `new` schema. Not to be confused with upgrade ejabberd or update schema.

If you already have a MySQL or PostgreSQL database with the default schema and contents, you can switch it to the new schema:

- MySQL: Edit the file `sql/mysql.old-to.new.sql` which is included with ejabberd, fill DEFAULT_HOST in the first line, and import that SQL file in your database. Then enable the `new_sql_schema` option in the ejabberd configuration, and restart ejabberd.

- PostgreSQL: First enable `new_sql_schema` and mod_admin_update_sql in your ejabberd configuration:

      new_sql_schema: true
      modules:
        mod_admin_update_sql: {}

  then restart ejabberd, and finally execute the update_sql command:

      ejabberdctl update_sql

## SQL Options

The actual database access is defined in the options with `sql_` prefix. The values are used to define if we want to use ODBC, or one of the two native interface available, PostgreSQL or MySQL.

To configure SQL there are several top-level options:

- sql_type
- sql_server
- sql_port
- sql_database
- sql_username
- sql_password
- sql_ssl, see section SQL with SSL connection
- sql_ssl_verify
- sql_ssl_cafile
- sql_ssl_certfile
- sql_pool_size
- sql_keepalive_interval
- sql_odbc_driver
- sql_start_interval
- sql_prepared_statements
- update_sql_schema, see section Database Schema
- new_sql_schema, see section Default and New Schemas

Example of plain ODBC connection:

    sql_server: "DSN=database;UID=ejabberd;PWD=password"

Example of MySQL connection:

    sql_type: mysql
    sql_server: server.company.com
    sql_port: 3306 # the default
    sql_database: mydb
    sql_username: user1
    sql_password: "**********"
    sql_pool_size: 5

## SQL with SSL Connection

The sql_ssl top-level option allows SSL encrypted connections to MySQL, PostgreSQL, and Microsoft SQL servers.

Please notice that ejabberd verifies the certificate presented by the SQL server against the CA certificate list. For that reason, if your SQL server uses a self-signed certificate, you need to setup sql_ssl_verify and sql_ssl_cafile, for example:

    sql_ssl: true
    sql_ssl_verify: false
    sql_ssl_cafile: "/path/to/sql_server_cacert.pem"

This tells ejabberd to ignore problems from not matching any CA certificate from default list, and instead try to verify using the specified CA certificate.

## SQL Authentication

You can authenticate users against an SQL database, see the option `auth_method` in the Authentication section.

To store the passwords in SCRAM format instead of plaintext, see the SCRAM section.

## SQL Storage

Several ejabberd modules have options called `db_type`, and can store their tables in an SQL database instead of internal.

In this sense, if you defined your database access using the SQL Options, you can configure a module to use your database by adding the option `db_type: sql` to that module.

Alternatively, if you want all modules to use your SQL database when possible, you may prefer to set SQL as your default database.

## Microsoft SQL Server

For now, MS SQL is only supported in Unix-like OS'es. Your Erlang/OTP must be compiled with ODBC support.

Also, in some cases you need to add the machine name to `sql_username`, especially when you have `sql_server` defined as an IP address, e.g.:

    sql_type: mssql
    sql_server: 1.2.3.4
    sql_username: user1@host

By default, ejabberd will use the `FreeTDS` driver. You need to have the driver file `libtdsodbc.so` installed in your library PATH on your system.

If the FreeTDS driver is not installed in a standard location, or if you want to use another ODBC driver, you can specify the path to the driver using the sql_odbc_driver option, available in ejabberd 20.12 or later. For example, if you want to use Microsoft ODBC Driver 17 for SQL Server:

    sql_odbc_driver: "/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.3.so.1.1"

Note that if you use a Microsoft driver, you may have to use an IP address instead of a host name for the `sql_server` option.

If hostname (or IP address) is specified in `sql_server` option, ejabberd will connect using a an ODBC DSN connection string constructed with:

- SERVER=sql_server
- DATABASE=sql_database
- UID=sql_username
- PWD=sql_password
- PORT=sql_port
- ENCRYPTION=required (only if sql_ssl is true)
- CLIENT_CHARSET=UTF-8

Since ejabberd 23.04, it is possible to use different connection options by putting a full ODBC connection string in `sql_server` (e.g. `DSN=database;UID=ejabberd;PWD=password`). The DSN must be configured in existing system or user `odbc.ini` file, where it can be configured as desired, using a driver from system `odbcinst.ini`. The sql_odbc_driver option will have no effect in this case and `unixODBC` must be installed on your machine.

If specifying an ODBC connection string, an ODBC connection string must also be specified for any other hosts using MS SQL DB, otherwise the auto-generated ODBC configuration will interfere.

## Redis

`Redis` is an advanced key-value cache and store. You can use it to store transient data, such as records for C2S (client) sessions.

The available top-level options are:

- redis_server
- redis_port
- redis_password
- redis_db
- redis_connect_timeout

Example configuration:

    redis_server: redis.server.com
    redis_db: 1