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:
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
On Fedora, you type:
sudo dnf install sqlitebrowser
On Manjaro, we use pacman:
sudo pacman -Sy sqlitebrowser
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.
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;
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.
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.
The main DB4S window now displays the structure of our 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.
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.
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.
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.
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.
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.
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. “
Click “Browse Data” and you will see the imported data.
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.
In the “Edit Table Definition” dialog box, check the “AI” (auto-increment) box in the “id” field.
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”, “firstname.lastname@example.org”, “Idaho”);
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.”
When asked to confirm your choices, click “Save” again.
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.
We added a text field to hold the eagle’s name and a real (floating point) number field to hold the wing.
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.
We can also use the “Browse Data” tab to view our new records.
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.
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.
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.