How to Install And Use SQLite on Ubuntu

Recently, I was developing a new open-source project, Amazing Endemic Species. And I need a database to store the increasing number of species in the future. I choose to use Turso as database. To facilitate testing, I plan to set up a local SQLite environment.
First update your package list:
sudo apt update
Install SQLite:
sudo apt install sqlite3
To verify the installation, check the software’s version:
sqlite3 --version
# 3.37.2 2022-01-06 13:25:41 872ba256cbf61d9290b571c0e6d82a20c224ca3ad82971edc46b29818d5dalt1
To create the database, open terminal and run this sqlite3 command:
sqlite3 amazing-endemic-species.db
That means I create a database named "amazing-endemic-species" and I will receive an output like this:
SQLite version 3.37.2 2022-01-06 13:25:41
Enter ".help" for usage hints.
sqlite>
Now I will create a schema for amazing-endemic-species.json
as below:
[
{
"id": 712,
"sciName": "Ailuropoda melanoleuca",
"status": "VU",
"group": "MAMMALIA",
"code": "CN",
"commonName": "Giant Panda"
},
{
"id": 16892,
"sciName": "Phascolarctos cinereus",
"status": "VU",
"group": "MAMMALIA",
"code": "AU",
"commonName": "Koala"
}
// ...
]
CREATE TABLE IF NOT EXISTS aes (
id INTEGER PRIMARY KEY,
sciName TEXT NOT NULL UNIQUE,
status TEXT NOT NULL,
"group" TEXT NOT NULL, -- Escaped keyword 'group'
code TEXT NOT NULL,
commonName TEXT NOT NULL
);
Once the table is created, I will insert the data from the JSON objects into the aes
table.
INSERT INTO aes (id, sciName, status, "group", code, commonName) VALUES
(712, 'Ailuropoda melanoleuca', 'VU', 'MAMMALIA', 'CN', 'Giant Panda'),
(16892, 'Phascolarctos cinereus', 'VU', 'MAMMALIA', 'AU', 'Koala');
To verify that the data has been imported correctly, you can run a simple SELECT
query:
SELECT * FROM aes;
-- 712|Ailuropoda melanoleuca|VU|MAMMALIA|CN|Giant Panda
-- 16892|Phascolarctos cinereus|VU|MAMMALIA|AU|Koala
If I want to drop the schema and table.
sqlite3 amazing-endemic-species.db "DROP TABLE IF EXISTS aes;"