In the next article we are going to take a look at how we can install SQLite 3 and SqliteBrowser on Ubuntu. To begin with, it must be said that SQLite is a RDBMS light and little. While DB Browser for SQLite (DB4S) or SQLiteBrowser, is a high quality, visual and open source tool for creating, designing and editing SQLite compliant database files.
Other popular databases, such as MySql or PostgreSQL, run on the client-server model and have a dedicated process that runs and controls all aspects of operations on the database. But SQLite doesn’t have any running processes and it doesn’t have a client-server model. SQLite DB is simply a file with extension .sqlite3 / .sqlite / .db. The SQLite code is distributed so that it can be used without restrictions and free of charge for any purpose.
DB4S or SQLiteBrowser is very valid for both users and developers who want to create, search and edit databases. DB4S uses a familiar spreadsheet-like interface, and makes it unnecessary to learn the more complicated SQL commands.
SQLiteBrowser controls and wizards are available for a user to:
- Create and compact database files.
- Define, create, modify and delete tables.
- Create, define and delete indexes.
- Browse, edit, add and delete records.
- Perform searches.
- Import and export records as text or tables from / to CSV files.
- Import and export databases from / to SQL dump files.
- Issue SQL queries and inspect the results.
- Examine a log of all SQL commands issued by the application.
- Plot simple graphs, based on table or query data.
Install SQLite 3 and SQLiteBrowser on Ubuntu
Install SQLite 3
To start we will install this RDBMS. Setting up SQLite is simple compared to other popular databases like MySql, Postgresql, etc. Before proceeding with the installation, we will have to update the list of available software. To do this we will open a terminal (Ctrl + Alt + T) and we will execute the command:
sudo apt update
For install the package necessary, the next command that we are going to execute is the following:
sudo apt install sqlite3
After the installation, we can validate the installation by starting the sqlite 3 session. To do this, in the same terminal you just have to write:
As can be seen in the image above, SQLite 3 installed successfully and runs with version 3.31.1. Although today there are more current versions, this is the one that was installed on my computer from the Ubuntu repository
Create sample database and table
The SQLite 3 database is going to be stored as a file on our local file system. We will be able to create a database when starting the sqlite session, mentioning the name of the database as an argument.
On launching the command, if the database is available, it will open that database. If we do not include the name of the database as an argument, a temporary in-memory database will be created that will be deleted once the session ends.
For this example we are going to create a database called test in the / home / entreunosyceros folder (which is the name of my user’s home folder)
Once created, we can see which database session you are connected to with this other command:
To continue with the example, let’s create a sample table running the following queries:
CREATE TABLE sistemas(Nombre String,version Real); insert into sistemas(Nombre, version) VALUES ('Ubuntu',16.04), ('Ubuntu',18.04),('Ubuntu',20.04);
Now we can run the command .tables to list the tables available in the database to which we are connected:
At this point we can we can print the contents of the table created for this example:
.headers on SELECT * FROM sistemas;
Once we have installed and created a sample database with sqlite3, we are going to install SQLiteBrowser. Is about a simple GUI tool to manage our sqlite databases. To do this, we will execute in the terminal (Ctrl + Alt + T):
sudo apt install sqlitebrowser
After the installation, we can start the app from the start menu. We can also launch it by opening a terminal (Ctrl + Alt + T) and executing the command:
After launching the program, the GUI will open from which we can select the database that we created before from the terminal:
Uninstall SQLite 3 and SQLiteBrowser
For remove both SQLite and SQLiteBrowser, we only need to open a terminal (Ctrl + Alt + T) and execute the command in it:
sudo apt --purge remove sqlite3 sqlitebrowser; sudo apt autoremove