How to Connect LibreOffice Base to MySQL or PostgreSQL

LibreOffice Base functions effectively as a graphical frontend for external database management systems like MySQL and PostgreSQL. Connecting Base to an external server allows you to design forms, run queries, and generate reports using a familiar desktop interface while relying on a robust backend for secure data storage, concurrency, and performance. This guide outlines the prerequisites and step-by-step methods to establish this connection using native connectors, JDBC, or ODBC.

Prerequisites

Before connecting LibreOffice Base to an external database, ensure the following components are ready: * An active, accessible MySQL/MariaDB or PostgreSQL server. * The necessary database credentials (hostname/IP, port, database name, username, and password). * A Java Runtime Environment (JRE) installed and enabled in LibreOffice under Tools > Options > LibreOffice > Advanced (required for JDBC connections). * The appropriate driver: * For JDBC: Download the official JDBC driver (.jar file) for MySQL (Connector/J) or PostgreSQL. * For ODBC: Install the system-level ODBC driver and configure the Data Source Name (DSN) via your operating system’s ODBC Data Source Administrator.

Setting Up the Driver in LibreOffice (JDBC Method)

If you are using JDBC: 1. Open LibreOffice and navigate to Tools > Options > LibreOffice > Advanced. 2. Click the Class Path button, then click Add Archive. 3. Browse to and select the downloaded JDBC .jar file (e.g., mysql-connector-j-x.x.x.jar or postgresql-x.x.x.jar). 4. Click OK, then restart LibreOffice to apply the changes.

Step-by-Step Connection Process

  1. Open the Database Wizard: Launch LibreOffice Base. The Database Wizard dialog will appear automatically.

  2. Select Connection Type:

    • Choose Connect to an existing database.
    • In the dropdown menu, select MySQL, PostgreSQL, JDBC, or ODBC, depending on your preferred method. Click Next.

  3. Configure Connection Parameters:

    • For MySQL (via JDBC): Select “Connect using JDBC”, enter the Database name and Server (e.g., localhost or an IP address). Ensure the port is correct (default is 3306). Enter the MySQL JDBC driver class: com.mysql.cj.jdbc.Driver.
    • For PostgreSQL (via JDBC): Enter the Database name, Server, and port (default is 5432). Enter the PostgreSQL JDBC driver class: org.postgresql.Driver.
    • For ODBC: Select the predefined Data Source Name (DSN) configured on your system.

  4. Set Up User Authentication:

    • Enter the User name for database access.
    • Check the Password required box if the database user requires authentication.
    • Click the Test Connection button to verify the configuration. You will be prompted to enter the password; if successful, a confirmation message will display. Click Next.

  5. Save and Finish:

    • Choose whether to register the database inside LibreOffice (which makes it accessible to Writer and Calc).
    • Ensure Open the database for editing is checked, and click Finish.
    • Choose a location to save your local .odb file.

Once saved, LibreOffice Base will display the tables, views, and schemas hosted on your MySQL or PostgreSQL server, allowing you to manage data, build queries, and generate user interfaces directly from your desktop.