How to Install DB Browser for SQLite in Ubuntu Server 20.04. SQLite is a command line database. It is one of the most popular databases that can be easily installed and used. It was intended to be pretty lightweight. It finds uses in mobile apps, IoT devices, sensors, etc. It can be installed on Linux, Windows, Mac OS, and more. SQLite is a C library based relational database management system, not a client server application.
What is SQLite Browser?
The SQLite browser manages databases graphically. SQLite server browser is used to design, create and edit SQLite compatible databases. This program helps developers create databases, search for information and edit data. SQLite tool is suitable for all types of users. Users need not know the SQL to create databases and tables or write queries. The SQLite browser provides all the features to create a database, create tables, write queries, and update records visually.
Pros and Cons of SQLite Browser (DB4S)
Here about how to Install DB Browser for SQLite in Ubuntu Server 20.04 we will review pros and cons of the SQLite browser.
Pros
- It is open source and is available for Windows, Linux, and macOS.
- Creates and compacts database files.
- Allows you to view the data that is stored in an SQLite Database.
- Imports and exports records as text.
- Imports and exports tables from/to CSV files.
- Issues SQL queries and inspects the results.
- Examines logs of all the SQL commands issued by the application.
- A simple visual tool that anyone with no SQL background can use to create databases and tables and execute queries.
Cons
- The only cons users have complained about are that it cannot handle large databases and freezes. It is obvious that large databases are a rarity in SQLite, so if someone is trying to create a large database, SQLite Browser will freeze.
Install DB Browser for SQLite in Ubuntu Server 20.04
Pre-Requisites
Installation of SQLite
SQLite3 can be installed on Ubuntu by executing command in the terminal.
1. Before installing SQLite3 ubuntu, packages need to be updated using the terminal.
$ sudo apt update
2. Now, we will install SQLite3 using the terminal.
$ sudo apt install sqlite3
3. Please check the version of the SQLite
$ sqlite3 --version
Install SQLite Browser
Using Ubuntu's apt repository
SQLite browser can be installed using the apt repository by running the following command:
$ sudo apt install sqlitebrowser
Using Snapstore
Usually, the snap store is bundled in all releases of Ubuntu; however, if someone is using the minimal installation of Ubuntu, then the snap store can be installed using the following command:
$ sudo apt install snapd
If the snap store is already installed, then the above command will update the snap store. To install SQLite browser using snap store following command can be used:
$ sudo snap install sqlitebrowser
After the installation in how to Install DB Browser for SQLite in Ubuntu Server 20.04 is to find the DB Browser in the list of applications.
Uninstall SQLite Browser
If you don’t want to use SQLite Browser, you can uninstall it using the command shown below. The command will remove the SQLite browser along with all the configuration settings.
$ sudo apt --purge remove sqlitebrowser
Using the DB Browser
Now we will create a database and a table. We will fill in some data and then use the DB Browser or SQLite browser.
Create a Database in SQLite
We will create a myhome database that will contain the attributes like home furniture and their attributes.
sqlite3 myhome.db
This will create a database with the name myhome. If the database already exists, then a connection will be created.
As soon as the DB is created, an SQLite prompt will appear. It is worth noting that if you exit the SQLite prompt without running any query, then the database will not be created.
Create a Table
Next, we will create a furniture table in myhome database. The following command can be used for the creation of a table.
sqlite> CREATE TABLE Furniture(id integer NOT NULL, name text NOT NULL, furnituretype text not null, material text not null);
You can see that after the execution of the command, the SQLite prompt will be returned. This indicates the table is created.
Inserting values in the table
Now, we will insert values into the furniture table.
sqlite> INSERT INTO furniture VALUES (1, "Ikea Coffee Table", "drawing room furniture", "Plywood");
sqlite> INSERT INTO furniture VALUES (2, "Ikea Dining Table", "Dining room furniture", "Plywood");
sqlite> INSERT INTO furniture VALUES (3, "Ikea Sofa Cum Bed", "Drawing room furniture", "Plywood");
Start SQLite Browser
Next part about how to Install DB Browser for SQLite in Ubuntu Server 20.04 is to start SQLite Browser. There are two ways to star tit. It can be launched by executing the following command or can be launched by double-clicking on the icon.
$ sqlitebrowser
By executing the above command, the SQLite browser will open, as shown below.
You can also create a new database using the SQLite browser. However, since we have already created our database, we will provide the path to our database file.
If you click on the Open Database button as shown in the image above, a window will open where we will provide the database file myhome.db
Database Structure
After providing the database file, the default tab that will load is Database Structure. The Database structure will display all the tables, lists, views, etc.
Browse Table Data
If you click on the Browse Data tab, all the table data can be seen. This tab allows the user to modify tabular data as well. You can print the data records, apply filters, refresh data and select another table from the drop-down list.
Creating New Record
It is easy to create a new record without writing the insert query, and this helps a newbie to insert data into a table quite easily without the need to know SQL.
The user must click on the New Record button to insert a new record. An empty row will be created below the existing rows of records, and then the user can enter data in the blank row.
Execute Queries
This tab allows the user to write all types of queries. It is suitable for those who love to write SQL. People with no knowledge of SQL won’t find it useful. It is also ideal for writing triggers, functions, and complex queries. Other features include saving the SQL file and executing specific lines of query and the entire query. The results of the executed query are also shown below the SQL editor.
Delete Record
Similar to creating a record, it’s easy to delete a record without writing a query.
User has to select a record and then click on Delete Record button
Create New Database and Table using SQLite Browser
Previously, we created a database using the command line, and now we will create a database using SQLite Browser. It is easy for any user to create a database using SQLite Browser.
To create a new database, the user must click the New Database Button.
A window will open, give the name of the database and then save it. Another window will open, which will ask you to create a table as shown here.
Great effort at following the whole article about How to Install DB Browser for SQLite in Ubuntu Server 20.04.
How to Install DB Browser for SQLite in Ubuntu Server 20.04 Conclusion
We’ve just finished installing the SQLite browser on Ubuntu 20.04 in this article. We learned to create a database using the command line and the SQLite browser. We also created tables using the command line and the SQLite browser. We also saw how the SQLite browser provides a SQL editor, where users can write a query and execute it. With SQL editor, the entire query can be saved in an SQL file, and the users can also upload an existing SQL file.
