SQLite does not offer a client-server database model, which eliminates the need for installation and management. It runs in memory, which allows you to run the database without any server. SQLite provides an interactive shell that you can use to interact with databases and the SQLite engine.
This tutorial will cover using SQLite shell to create databases, create tables, and insert data.
Getting SQLite Shell
To get SQLite for your Linux system, open your browser and navigate to:
https://www.sqlite.org/download.html
Select the SQLite tools for your system and download them. Once you unzip the archive, you should have sqlite3 binary. To launch the SQLite shell, run the sqlite3 binary.
You can also install SQLite using your package manager. To install it using apt, use the command:
sudo apt-get install sqlite3
The SQLite Shell
The SQLite shell is a simple command-line tool that allows you to run raw SQL queries against a database or zip archives as database files.
Launch the shell with the command:
SQLite version 3.27.2 2019-02-25 16:06:06
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>
Once you are in the SQLite Shell, you can start executing commands. Type the .help command to view the shell command help:
.archive ... Manage SQL archives
.auth ON|OFF Show authorizer callbacks
.backup ?DB? FILE Backup DB (default "main") to FILE
.bail on|off Stop after hitting an error. Default OFF
.binary on|off Turn binary output on or off. Default OFF
.cd DIRECTORY Change the working directory to DIRECTORY
.changes on|off Show number of rows changed by SQL
.check GLOB Fail if output since .testcase does not match
.clone NEWDB Clone data into NEWDB from the existing database
.databases List names and files of attached databases
.dbconfig ?op? ?val? List or change sqlite3_db_config() options
.dbinfo ?DB? Show status information about the database
.dump?TABLE? ... Render all database content as SQL
.echo on|off Turn command echo on or off
.eqp on|off|full|... Enable or disable automatic EXPLAIN QUERY PLAN
----------------------------------------------------------------------
It is good to note that the .help command does not display the SQL queries you can execute against a database. The dot commands are one-line commands that configure or interact with the shell directly.
To get the list of all databases while inside the SQLite shell, use the .databases commands.
It is good to experiment with the SQLite shell to learn how to configure and interact with it. If you need a detailed guide, consider the SQLite documentation:
How to Create An SQLite Database
To create an SQLite database, all you have to do is call the sqlite3 command followed by the name of the database you want to create. Note that if the specified database exists, SQLite will open the database inside the shell.
The general syntax to create a database is as:
For example, to create the database movies.db use the command:
SQLite version 3.27.2 2019-02-25 16:06:06 Enter ".help" for usage hints.
sqlite>
Executing this command will create the database if it does not exist or open it if the database exists. To view the databases, use the .databases command as:
Attaching Databases
SQLite allows you to attach a database to perform specific functions under it. Using the ATTACH DATABASE query, we can attach a database as:
sqlite> .databases
main: /home/debian/movies.db
u\movies.db: /home/debian/movies.db
The as statement sets an Alias Name under which to attach the database. It is good to note that if the attached database does not exist, SQLite creates it automatically.
To detach a database, use the DETACH DATABASE query. For example:
NOTE: Some database names used in this tutorial are for demonstration purposes only and may not include accepted naming conventions.
SQLite Create Table
To create a table in an SQLite database, we use the query CREATE TABLE followed by the table name. The general syntax is:
column_name datatype PRIMARY KEY(column(s)),
column_name2 datatype,
...
column_nameN datatype
);
For example, let us create a database programming and create a table of languages with the relevant information as:
...> id INTEGER PRIMARY KEY AUTOINCREMENT,
...> name TEXT NOT NULL,
...> creator TEXT,
...> year INTEGER NOT NULL,
...> version TEXT ...> );
To verify the successful creation of the table, use the .tables SQLite command to list all the tables in the database:
To get more details about a table, use the command .schema followed by the table name.
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
creator TEXT,
year INTEGER NOT NULL,
version TEXT
);
SQLite Insert Data
To insert new rows of data into a table, we use the INSERT INTO query. The general syntax for this statement is:
For example, to add data to the languages table created above, use the query below:
...> VALUES (1, "Python", "Guido van Rossum", 1991, "0.9.1");
Continue to populate the database tables with information.
...> VALUES (2, "JavaScript", "Brendan Eich", 1995, "ECMA 1");
To confirm the successful creation of the data, you can use the SELECT query:
1 | Python | Guido van Rossum | 1991 | 0.9.1 2 | JavaScript | Brendan Eich | 1995 | ECMA 1
SQLite Remove Data
To remove data in a table, we can use the DELETE query followed by the WHERE and the condition. The general syntax is:
For example, to remove the data where the id is equal to 1, we can use the query.
To confirm the data has been removed successfully, we can use the SELECT query, as seen above.
2 | JavaScript | Brendan Eich | 1995 | ECMA 1
This removes the row where the id = 1, in this case, the Python entry.
Conclusion
In this tutorial, we discussed how to set up and run SQLite. We also covered how to work with the SQLite shell and execute commands as SQL statements. Using what you have learned from this tutorial, you can create databases, create tables, add data, and remove rows.
