One of the apps offered in the free and open source LibreOffice suite is called Base. Base is a front-end application for creating, connecting or reading databases (including those created with Microsoft Access). This guide will walk you through the process of using Base to create your own simple but useful database and start entering data.
1. Install LibreOffice Base
The LibreOffice suite , an alternative to the Microsoft Office suite , is available for Windows, macOS and Linux. There are several ways to install it on your system (particularly if you are a Linux user) but you can download the official installation packages from libreoffice.org/download . In this article we will use LibreOffice version 7.0.2.2.
2. Start Base and create a database
Once LibreOffice is installed, start Base from the desktop. Whenever you open Base, you will see this dialog asking if you want to start a new database or open an existing one. Select the Create a new database radio button .
Base comes with a relational database management system called HSQLDB (HyperSQL Database Management System) ready to use and is the default option.
HSQLDB is ideal for your first project, as it is simple and easy to manage. Make sure the HSQLDB Embedded option is selected from the list and click the Next> button .
3. Register and save your database
Base will ask you if you want to register the database. Registration simply makes your database accessible to other apps in the LibreOffice suite on your device, such as Calc and Writer. This feature is local to your device – you don’t have to worry about it being accessible by anyone else.
Unless you are sure that you will never want to access your new database with those other apps, you can leave Yes, register the selected database for me .
Make sure you have selected Open database for editing, then click Finish . Base will ask you to save your database as an .ODF file. Choose a location and file name, and then click on Save .
4. Create a table and set a primary key
The first thing you need to do with a new database is create a table. Tables are the most essential part of a database and you need at least one to accomplish anything.
The default view when opening a database is the Tables section. Select Create Table in Design View… from the task menu.
The Table Design dialog will open with several blank cells under the labels Field Name, Field Type , and Description . This is where you will choose and name the fields you want in your table.
These are the different “categories” of data that you plan to enter in your database. For example, we plan to create a database of our movie collection, so we’ll include fields like Title, Director, and ReleaseYear.
The first field you should create, however, should be some sort of unique identifier, such as a number or UPC code. This field will differentiate each entry, even if all other fields have duplicate data. In our example, we called the first field MovieID and selected the field type Integer [INTEGER] to make that field a simple number.
Whatever you end up creating the identification field, right-click on that row and check the Primary key box in the drop-down menu. If you don’t select a field as the primary key, Base will throw an error when you try to save the table.
In our example, in Field Properties , we also set the AutoValue option for our primary key field to Yes so we don’t have to manually choose a new ID number every time we create an entry. With the AutoValue function, Base will automatically insert the next incremental number for each new entry.
5. Complete the data fields
Keep adding as many fields as you need and make sure you select the appropriate field types. For most basic uses, we recommend using VARCHAR for text, INTEGER for numbers, and DATE for calendar dates.
If you already have data in a spreadsheet or .csv file that you plan to import into your database, it is very useful when adding fields to make sure your field names are easily matched to the field names in your data file .
Note that the fields you create can be rearranged during the initial creation of a table, but you cannot rearrange fields after saving the table. However, this shouldn’t cause much trouble, and you can still add and delete fields later.
6. Save your first table
Click the Save button or press Ctrl + S to save the table and Base will ask you to name the table. Choose whatever name you prefer (we chose the default, Table1, in our example).
After saving the table, be sure to save the database file itself to make sure you don’t lose your work. Your .ODF file will need to be saved every time you create or modify a table, query, form or report.
If you need to change the fields in the table, you need to right-click the table and click Edit from the drop-down menu.
7. Enter or import data
Now that you have a table, your table needs data. There are several ways to enter data into the database, but today we will see how to manually enter it in the table view and how to import it from a spreadsheet.
Manual entry
Double-click the table or right-click and select Open . In this dialog you will see all the fields you just created. You can manually create entries by entering data one field at a time, much like entering information into spreadsheet cells.
When you reach the final field of an entry, press Tab to move to the next entry. Base will automatically save the data you’ve entered, so you don’t have to click the save button every time you enter data. Also, if you set the ID field to AutoValue, Base will automatically fill in the ID field when you move to the next entry.
Continue until you have entered all the data you want.
Import from a spreadsheet
If you have a spreadsheet that already contains the data you want to insert, you can easily import it into your table, although you have to follow some rules.
To import from a spreadsheet you need to have a column for each field you have in the table, even if the field is set to AutoValue or you don’t have data for it yet. Also, the data must be present in every row of every column you are importing whose destination is not set to AutoValue.
In our example, there is a column with more or less the same name for every field in our table and all the rows are filled except the rows in the ID field, which Base will automatically fill when we import. The names don’t have to match exactly and the columns don’t have to be in the same order as the database fields; you will have the option to rearrange your data during import.
To start the import, highlight all the data in the worksheet you want to import, including the labels for each column, and copy it with Ctrl + C.
Then, open Basic and make sure you are on the View Table screen. Click Edit> Paste or press Ctrl + V. This will open the Copy Table dialog . Leave the options as they are and click the Next> button .
In the Assign Columns dialog , you will need to align the columns you are importing with the fields in the table. Use the up and down buttons to adjust the position of each column and deselect the columns you copied that you don’t want to import. Click the Create button when done.
If there were no errors during the import, the dialog will simply close and you will return to the main table view. Double-click the table to view the data and verify that nothing was imported incorrectly.
A database ready for action
Congratulations! Now that you’ve completed the process of creating a database, creating a table, and entering data, you have a usable database file at your fingertips. Some additional tasks you can perform in Base are querying SQL, designing forms, and reporting with the database.
