Details

mod_storage_sql allows Prosody to connect to an SQL database to store data. Officially supported databases are SQLite3 and PostgreSQL. MySQL / MariaDB may work but is not recommended.

Usage

Firstly, ensure you have the LuaDBI library available on your system. For information on how, refer to our documentation on dependencies.

Then add the following to your config file:

    storage = "sql"
    sql = {
        driver = "PostgreSQL"; -- May also be "SQLite3" or "MySQL" (case sensitive!)
        database = "prosody"; -- The database name to use. For SQLite3 this the database filename (relative to the data storage directory).
        host = "localhost"; -- The address of the database server (delete this line for Postgres)
        port = 3306; -- For databases connecting over TCP
        username = "prosody"; -- The username to authenticate to the database
        password = "secretpassword"; -- The password to authenticate to the database
    }

Table management

If Prosody has the necessary permissions it will automatically initialize the database (creating tables and indexes) when you point it to a new empty database. Any schema upgrades will also be automatic.

In particular it is recommended that you grant Prosody the following SQL permissions on its database: SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, INDEX.

If you do not wish Prosody to attempt automatic table and schema management, set sql_manage_tables to false in your config.

For information on the database schema, please see our mod_storage_sql developer documentation.

Configuration