How to Install SQLite on Ubuntu Server 20.04 (SQLite3 Command)
How to Install SQLite on Ubuntu Server 20.04 (SQLite3 Command). SQLite is a free, cross platform relational database management system (RDBMS) in a C library. It has an interface to work with many different programming languages. QLite is not a client server database engine. It sits at the end of program. Most programming languages support SQLite and those languages embed the program with a file with .sqlite3/.sqlite/.DB extension.
The software is a great choice for local/client storage such as web browsers. In this tutorial we will install SQLite on Ubuntu 20.04. We will then create a database, a table, then update the table in SQlite. We will also backup and restore SQlite database. So let’s start.
What is SQLite
SQLite is a software library or database engine that provides its users with a relational database management system. It is lightweight, which means it is less than 500 kb in terms of setup, database administration and required resources. This way, users find it highly convenient in comparison to other database management systems.
It stores all the data in a single cross platform file providing a greater deal of accessibility in copying a database or sharing an email attachment. The entire program is stored in a C library that gets embedded into applications. It makes the database a crucial part of the program, thereby eliminating resource incentive standalone processes.
Moreover, since there is no dedicated server or specialized file system in SQLite, deploying becomes a straightforward process. Also, it contains the code in the public domain, which makes it free for use for any purpose, be it commercial or private. It can be used on all devices, including computers, mobile phones, etc., and comes altogether with countless other applications that users use every day.
Features of SQLite
Serverless – Generally, an RDBMS like MySQL, PostgreSQL, etc., uses a separate server process to operate. On the other hand, the applications that want to access the database server use TCP/IP protocol to send and receive requests. This process is known as client or server architecture.
Self Contained – SQLite is self contained. It means that it requires minimum support from the operating system or external library. This way, it becomes convenient for any environment, especially in embedded devices like iPhones, Android Phones, game consoles, etc.
Zero Configuration – Its serverless architecture restricts the need for installing SQLite before using it. It means you do not have to configure, start, or stop any server process. It also does not use any configuration files.
Transactional – The transactions in SQLite are completely ACID complaint. It means that the entire changes and queries in this database management system are Atomic, Consistent, Isolated, and Durable. It means that the changes within the transaction will either take place completely or not at all, even when unexpected situations like application crash, power failure, or operating system crash occur.
Other Distinctive Features – SQLite uses dynamics for tables. This way, it allows its users to store any value in any column, no matter whatever the data type is. It also enables a single database connection to access several database files simultaneously. It is capable of creating in memory databases that make the process faster.
Benefits Of SQLite
SQLite provides its users with multiple benefits. They are as follows:
- Provides a sturdy SQL compatible database without any overheads or dependencies.
- It is a lightweight solution that can run on almost everything that supports C and persistent file storage.
- Its plain files make it highly portable and easy to backup (available on both UNIX (Linux, Mac OS-X, Android, iOS) and Windows.
- Facilitates API for a large range of programming languages.
- It does not contain any server component, which makes it effortless to set up, even if the user is in a fully fledged development environment.
- Applications that use SQLite increase resiliency and reduce development time.
- When users use SQLite database instead of text files for configurations and storage, it unifies data access across multiple devices and also helps in maintaining consistent performance.
- Atomic and transactional, which helps users in avoiding partial success events. Therefore, if one operation in a transaction fails, the successful one is reverted, thereby putting the database back into its usual state.
- It is resilient to storage errors and out of memory scenarios.
- Helps databases to recover from complete system crashes and power outages. This way, the data remains completely safe.
- Provides complete coverage to the codebase using a remarkably pervasive test suite.
- Preserves the backwards compatibility.
Follow this post to learn how to Install SQLite on Ubuntu Server 20.04 (SQLite3 Command).
SQLite Azure
SQLite AWS
How to Install SQLite on Ubuntu Server 20.04 (SQLite3 Command)
Install SQLite on Ubuntu 20.04
By default, the SQLite package is included in the Ubuntu 20.04 default repository. You can install it by running the following command:
apt install sqlite3 -y
Once the installation is completed, verify the SQLite version with the following command:
sqlite3 --version
You should see the following output:
3.31.1 2020-01-27 19:55:54 3bfa9cc97da10598521b342961df8f5f68c7388fa117345eeb516eaa837balt1
Create a Database in SQLite
The basic syntax to create a database in SQLite is shown below:
sqlite3 database-name
To create a database named persons.db, run the following command:
sqlite3 persons.db
You will get the following output:
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
sqlite>
You can verify your created database with the following command:
sqlite> .database
You should see the following output:
main: /root/persons.db
Create a Table in SQLite
SQLite databases are organized into tables and tables store information in rows and columns.
To create a table named persons with some columns, run the following command:
sqlite> CREATE TABLE persons(id integer NOT NULL, name text NOT NULL, persontype text NOT NULL, length integer NOT NULL);
To verify the table, run the following command:
sqlite> .tables
You should see the following output:
persons
Now, insert some data into the table using the following command:
sqlite> INSERT INTO persons VALUES (1, "Hitesh", "Junagadh", 528);
sqlite> INSERT INTO persons VALUES (2, "Jayesh", "Surat", 734);
sqlite> INSERT INTO persons VALUES (3, "Vyom", "London", 1200);
Next, verify the inserted data using the following command:
sqlite> SELECT * FROM persons;
You should see the following output:
1|Hitesh|Junagadh|528
2|Jayesh|Surat|734
3|Vyom|London|1200
To view the data based on ID, use the following command:
sqlite> SELECT * FROM persons WHERE id IS 1;
You should see the following output:
1|Hitesh|Junagadh|528
Update a Table in SQLite
You can use the ALTER TABLE command to change the table. For example, to add a new column named age, run the following command:
sqlite> ALTER TABLE persons ADD COLUMN age integer;
Add the new age value for each rows using the following command:
sqlite> UPDATE persons SET age = 80 WHERE id=1;
sqlite> UPDATE persons SET age = 50 WHERE id=2;
sqlite> UPDATE persons SET age = 90 WHERE id=3;
Verify the table data using the following command:
sqlite> SELECT * FROM persons;
You should see the following output:
1|Hitesh|Junagadh|528|80
2|Jayesh|Surat|734|50
3|Vyom|London|1200|90
To display the table data in table format, you will need to enable the headers in SQLite. You can enable it using the following command:
sqlite> .headers ON
sqlite> .mode column
Next, verify the table data using the following command:
sqlite> SELECT * FROM persons;
You should see the following output:
id name persontype length age
---------- ---------- ---------- ---------- ----------
1 Hitesh Junagadh 528 80
2 Jayesh Surat 734 50
3 Vyom London 1200 90
To exit from the SQLite shell, run the following command:
sqlite> .quit
Backup and Restore SQLite Database
To backup database to a text file, run the following command:
sqlite3 persons.db .dump > persons.dump.sql
Another option is to create a backup in sqlite format:
sqlite3 persons.db ".backup persons.backup.sqlite"
To restore a backup from the SQL formated dump file, run the following command:
sqlite3 persons.db < persons.dump.sql
To restore a backup from the SQLite formated dump file, run the following command:
sqlite3 persons.db ".restore persons.backup.sqlite"
Great effort! We have learned how to Install SQLite on Ubuntu Server 20.04 (SQLite3 Command).
How to Install SQLite on Ubuntu Server 20.04 (SQLite3 Command) Conclusion
SQLite 3 is a free and efficient database engine and a useful tool for database management. You can quickly create a database and make changes with various commands. Reading this tutorial you now have a basic understanding of SQLite and their database management system. In this post, we explained how to install SQLite on Ubuntu 20.04 server. We also explained how to create and manage database and tables in SQLite. I hope this guide will helps you to use the SQLite database.
Why don’t you check another overview of our database systems and how MongoDB, PostgreSQL and MySQL compare in this article: Choosing the Right Database – MongoDB vs PostgreSQL vs MySQL for Web Applications
