How to Use DB Browser for SQLite on Linux

A terminal window on an Ubuntu-style Linux laptop.Fatmawati Achmad Zaenuri / Shutterstock

DB Browser for SQLite allows you to view and edit SQLite databases on Linux. You can design, create, and modify these database files, and learn about the inner workings of other applications. Here is how to use this SQLite GUI.

The SQLite database project

the SQLite database The Library and Tools is a hugely successful open-source Structured Query Language (SQL) database project. So successful, in fact, it can rightly be called the most widely deployed database engine in the world.

Since its initial release in 2000, SQLite has seen absolutely astounding adoption. It’s inside every iPhone and Android phone, and every Windows 10 or Mac computer. It’s also in all internet browsers, including Chrome, Firefox, and Safari, as well as countless other apps.

The incredible reach of the SQLite database is due to its architecture. It is a fast, lightweight library that is integrated (or linked, in developer language) into other applications. The database engine becomes an integral part of the product. This means that you are not required to provide an external SQL database server, such as MySQL, MariaDB, or Microsoft SQL Server.

SQLite also has a command line tool for manipulating databases, but it was the library that made it a triumph. Having a capable, stand-alone database engine hidden in your application removes many issues. It simplifies your application installation routines and reduces the minimum hardware requirements for your application.

Also, because SQLite uses a single cross-platform file for its database tables, indexes, and schemas, the entire database can be moved to another computer. You can even move it to a machine running a different operating system by copying a file.

In fact, SQLite’s database file format is so well regarded that it is one of the few recommended by the Library of Congress for long-term data storage.

However, since SQLite is a developer’s library, there is no front-end, which means it doesn’t have a graphical user interface. It is the application using the library that provides the user interface. The command line utility can run in interactive mode, but it’s still not a GUI.

DB Browser for SQLite (DB4S) fits the bill well. It is a visual tool developed by another open source project to allow creation and manipulation of SQLite databases from a graphical interface.

DB Browser for SQLite

DB Browser for SQLite has been around (in one incarnation or another) since 2003 and has undergone several name changes. Previously it was called SQLite Browser, but it was confusing. People thought it was written by the SQLite team and therefore forwarded feature requests and support requests regarding DB4S to SQLite.

Thus, SQLite Browser has been renamed DB Browser for SQLite. You will still see references to the old name here and there. In fact, the project website still uses “sqlitebrowser” as the domain, and the old name is also used when installing DB4S.

With DB4S, you can:

Create databases.
Import and export schemas, tables and database data in SQL format.
Import and export tables and data in CSV format.
Create, modify, and drop tables and indexes.
Add, edit and delete records.
Browse and search for database records.
Edit and run SQL commands. You can make sure the command does what you think it will do before you hard-code SQL into your application.

Installing DB Browser for SQLite

To install DB4S on Ubuntu, use the following command (again, note that the installation still uses the old name):

sudo apt-get install sqlitebrowser

sudo apt-get install sqlitebrowser in terminal window

On Fedora, you type:

sudo dnf install sqlitebrowser

sudo dnf install sqlitebrowser in a terminal window

On Manjaro, we use pacman:

sudo pacman -Sy sqlitebrowser

sudo pacman -Sy sqlitebrowser in a terminal window

Importing a Database from an SQL File

When DB4S starts up, no database is loaded. We’ll look at two ways to import both data and database table definitions, as well as how you can create your own database.

DB browser for SQLite on first launch

Sometimes you may receive or send a database dump file in SQL format. This contains the instructions necessary to recreate a database and insert its data into a database.

Another commonly used format for importing table definitions and data is the comma separated value format (CSV). You can use a data generation site, such as Database test data, to generate dummy data for practice. You can then export your data in SQL or CSV format.

Below is an SQL file that we created on this site. After exporting it, we edited it and added a line to the top of the file, which is required for SQLite:

START THE TRANSACTION;

An SQL database dump file in the gedit editor

We then saved the file. In DB4S we click on File> Import> Database from SQL file.

A file selection dialog opens so that we can choose our SQL file. In our example, it is called “database_dump.sql” and is located at the root of our home directory.

Once the file is selected, we click on “Open” and a save file dialog box opens. Now you need to name your new database and decide where to save it. We have named ours “geekbase.sqlite3” and are saving it in our home directory.

Click “Save” when you are ready to continue. We have identified our source SQL file and named our new database. The import process can therefore begin. When the operation is completed, you will see the notification dialog below.

Import Completed Notification Dialog

Since we have added tables and data to the database, we are encouraged to save these changes. We therefore click on “Save” to do this.

Save the edit verification dialog

The main DB4S window now displays the structure of our database.

DB Browser for SQLite showing the structure of the database

There are two tables created, although there is only one in the SQL file. This is because the “id” field has been defined as an auto-increment field. It will be added automatically whenever a new record is added to the database. SQLite creates a table to track the auto-incremented fields.

Click the “Browse Data” tab to see your newly added records.

Database records in DB Browser for SQLite

Of course, the power of a database lies in its ability to find and retrieve records. With an SQL compatible database, we use SQL language to do this. First, the “Execute SQL” tab.

We added the following SQL command:

SELECT * FROM account_details WHERE name_name LIKE “% ll%” ORDER BY status

This will search for people with a double “l” in their last name, and the results will be sorted by state. Click the blue arrow (it looks like a “Read” button) to run your SQL command. The results are displayed in the lower pane.

We have four records that contain the double “l” in the last name, and they are sorted alphabetically by state, from Arizona to Wisconsin.

Importing a Database Table from a CSV File

We can also import tables from appropriate CSV files. The most convenient way to do this is to place the table field names in the CSV file as the first line of text. Below is a short section of a CSV file.

Start of a CSV file with table field names as the first row

The first line contains the field names: first name, last name, created, email, status, and ID. The other rows contain the data values ​​for each record that will be added to the table. These are the same data as before; only the file format has changed.

When you import CSV data, you need to create an empty database to have something to import it into. To do this, click on “New database” in the toolbar.

New database in DB Browser for SQLite toolbar

A save file dialog box opens. Name your new database and decide where to save it. We will call ours “howtogeek.sqlite3” and save it to our home directory.

Save dialog with the file name howtogeek.sqlite3 entered

When the “Edit Table Definition” dialog box appears, click “Cancel”. Back in the DB4S main window, click File> Import> Table from CSV file. A file selection dialog will open where you can choose your CSV file.

File selection dialog box with users.csv file selected

In our example, it is called “users.csv” and is located at the root of our home directory. Click “Open” and a preview dialog box will appear showing you how DB4S will interpret the CSV data.

Data preview dialog box displaying CSV data

The name of the file is used as the name of the table. You can change it if you want. Just be sure to check the box next to “First Row Column Names”.

Click “OK” (it’s offscreen in the image above). The data is imported and, if all is well, you should see the “Import Complete” dialog box; click OK. “

Import Completed Notification Dialog

Click “Browse Data” and you will see the imported data.

Data imported into the Users table in DB Browser for SQLite

We still have a little adjustment to make, however. Click on the “Database structure” tab, select the name of the table, then click “Edit table” in the toolbar.

Database Structure pane in DB Browser for SQLite

In the “Edit Table Definition” dialog box, check the “AI” (auto-increment) box in the “id” field.

Edit Table Definition Dialog Box in DB Browser for SQLite

The “PK” (primary key) checkbox is automatically selected for you; click OK. “This sets the” id “field to increment automatically. We can now add a new record to the database to verify that it is working.

Click on the “Execute SQL” tab, then enter the following SQL code in the top pane (note that we provide values ​​for all fields except “id”):

INSERT IN “users”
(“first_name”, “last_name”, “created”, “email”, “state”)
VALUES (“Dave”, “McKay”, “08/12/2020”, “dave@llk.com”, “Idaho”);

Run the SQL pane in DB Browser for SQLite

Click the blue arrow (which looks like a Read button) to run your SQL command. Click “Browse Data” and scroll down. You should see your newly added record with an automatically supplied “id” field containing a value one greater than the previous highest “id” value.

Create a database manually

If you don’t have an SQL or CVS file to import, you will need to create your database manually. To start, click on “New Database” and a save file dialog box will appear. Type the name of your new database and the location where you want to save it.

We named ours “geeksrock.sqlite3” and we save it in the “Documents” directory. Once you have named your database and navigate to the location where you want to save it, click “Save.”

Save file dialog with the database named geeksrock.sqlite3 input

When asked to confirm your choices, click “Save” again.

A save confirmation dialog in DB Browser for SQLite

The “Edit Table Definition” dialog box appears. Name your new table (we called ours “eagles”), then click “Add Field”. You can now type a name for the field and select the type of information it will contain from the “Type” drop-down menu.

Edit DB Browser for SQLite Table Definition Dialog Box

We added a text field to hold the eagle’s name and a real (floating point) number field to hold the wing.

Edit Table Definition dialog box with two fields added to new table DB Browser for SQLite

Checkboxes and other options next to each field allow you to add the following behaviors:

NN (not zero): When this option is set, the field cannot be left blank. If you try to add a record without providing a value for this field, it will be rejected.
PK (Primary key): A field (or group of fields) that provides a unique identifier for the records in the table. It can be a simple numeric value, like the auto-incrementing integer fields we covered above. In a user accounts table, however, it can be a user name. There can only be one primary key in a table.
AI (Auto-increment): Numeric fields can be automatically populated, with the next highest unused value. We used it in the “id” field in the example we covered above.
U (Single): This field in each record must contain a unique value, which means that there can be no duplicates in this field of the table.
Default: A default value will be provided if a record is added that has no value in this field.
Check: A field can be controlled when adding a record. For example, you can verify that the value in a phone number field is at least 10 characters long.
Snack: Sqlite can use different methods of comparing strings. The default is BINARY. The other options are NOCASE, which is case insensitive, and RTRIM, which ignores trailing white space. In most cases, you can leave the default.
Foreign key: A field or group of fields in a record that must match a key in another table. For example, in a workshop tool database, you might have an individual tool table and another tool category table. If the tool categories are “hammer”, “wrench” and “screwdriver”, you cannot add a “chisel” type record.

Click “OK” after adding the desired fields. Once your database is created and your first table is added, you can add records.

In the “Execute SQL” tab, we have used the SQL INSERT statement several times to add records to the table.

An INSERT SQL statement in the Execute SQL tab in DB Browser for SQLite

We can also use the “Browse Data” tab to view our new records.

Browse data tab with new records listed DB Browser for SQLite

If you prefer to add new records via the user interface, click on “New record” in the toolbar. You can then enter the values ​​for the new record without understanding SQL.

Databases of other applications

You can also use DB4S to view SQLite databases owned by other applications. Examining the structure or content of other databases can be informative (or just interesting). However, it is important that you do not make any changes to databases that are owned by other applications, otherwise you may interfere with the operation of that application.

Let’s take a look at one of the SQLite databases that Firefox creates and manages. Click “Open Database” on the toolbar, and an open file dialog box will appear. Firefox keeps its files in a directory called “firefox”, which is located in a hidden directory called “.mozilla” which is located in your home directory.

On our test machine, we found the Firefox SQLite databases at this location: “home / dave / .mozilla / firefox / vpvuy438.default-release”; yours will be in a similar location.

Open file dialog box with the coockies.sqlite file highlighted

We are going to open the “cookies.sqlite” database, so we highlight the file, then click “Open”. Once the database is open, you can examine its table structure, field definitions, and data.

Cookie data in the Firefox cookies.sqlite DB Browser for SQLite database

Here we can see a variety of Google and YouTube cookies.

Export provides information

Exporting a database (by clicking File> Export> Database to SQL File) is also useful. By looking at the SQL dump of a database, you can see the entire database schema rendered in SQL statements.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.