How to Create SQL Views in LibreOffice Base

Creating reusable virtual tables—commonly known as SQL views—in LibreOffice Base allows users to encapsulate complex queries, simplify reporting, and establish persistent data abstractions across forms and reports. This article covers the primary built-in features and external database management tools used to generate and maintain SQL views within the LibreOffice Base environment.

1. The Native View Design Interface (GUI)

LibreOffice Base includes an integrated graphical interface for constructing views without manually writing SQL code.

• How it works: Under the Tables section in the main database window, selecting Create View… opens the visual query designer. Users can add multiple tables, define relationships via drag-and-drop, apply criteria, and select output fields.
• Result: Saving the design stores the virtual table directly in the underlying database catalog (such as the default embedded Firebird or HSQLDB engine), making it immediately available alongside standard tables.

2. Direct SQL Command Execution

For complex logic, subqueries, or engine-specific functions that the graphical designer cannot parse, LibreOffice Base provides direct SQL execution.

• How it works: Access the command window via Tools > SQL…. Enter standard Data Definition Language (DDL) syntax:

  CREATE VIEW "v_CustomerOrders" AS 
  SELECT "Customers"."Name", "Orders"."Total" 
  FROM "Customers" 
  JOIN "Orders" ON "Customers"."ID" = "Orders"."CustomerID";

• Result: Executing this statement bypasses the Base graphical parser and directly commands the database engine to register the view. Once executed, selecting View > Refresh Tables updates the object pane to display the new view.

3. Converting Queries to Views

Base allows converting standard user queries into database-level views.

• How it works: Users can draft a query in the Queries tab using either SQL mode or the Design View. Right-clicking the saved query object offers the option Create as View.
• Result: This operation promotes a client-side LibreOffice query into a server-side virtual table, ensuring faster execution and compatibility with external front-ends.

4. External Database Management Tools (Split/External Databases)

When LibreOffice Base is used as a front-end connected via JDBC or ODBC to external relational systems (such as PostgreSQL, MySQL/MariaDB, or SQLite), external SQL tools can be used to generate views that Base consumes:

• DBeaver / DataGrip: Multi-platform universal database tools that provide visual schema navigation, script generation, and advanced syntax validation for view creation.
• pgAdmin (for PostgreSQL): Provides dedicated schema management to define standard and materialized views with custom security roles.
• phpMyAdmin / MySQL Workbench (for MySQL/MariaDB): Provides visual view-generation wizards with index and privilege management.

Views created in these external tools are automatically imported into LibreOffice Base upon refreshing the connection, functioning as native virtual tables for forms, queries, and reports.