SQLite How to create a database and insert data

SQLite is an open-source relational database management system developed for embedded systems. SQLite is simple and powerful, providing rich features in other major DBMS systems such as MySQL, PostgreSQL, and many more with minimal or no configuration.

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 update
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:

# sqlite3
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:

sqlite> .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.

sqlite> .databases main:

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:

https://sqlite.org/docs.html

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:

sqlite3 dbName.db

For example, to create the database movies.db use the command:

sqlite3 movies.db
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:

sqlite> .databases main: /home/debian/movies.db

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> ATTACH DATABASE "movies.db" as "u\movies.db";
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:

DETACH DATABASE “u\movies.db”;

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:

CREATE TABLE db_name.tb_name(
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:

sqlite3 programming.db sqlite> CREATE TABLE languages (
… > 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:

sqlite> .tables languages

To get more details about a table, use the command .schema followed by the table name.

sqlite> .schema languages CREATE TABLE languages (
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:

INSERT INTO TABLE_NAME VALUES (VAR1,VAR2,VAR3,… VARN);

For example, to add data to the languages table created above, use the query below:

sqlite> INSERT INTO languages
… > VALUES (1, "Python", "Guido van Rossum", 1991, "0.9.1");

Continue to populate the database tables with information.

sqlite> INSERT INTO languages
… > VALUES (2, "JavaScript", "Brendan Eich", 1995, "ECMA 1");

To confirm the successful creation of the data, you can use the SELECT query:

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:

DELETE FROM tb_name WHERE condition;

For example, to remove the data where the id is equal to 1, we can use the query.

sqlite> DELETE FROM languages WHERE id = 1;

To confirm the data has been removed successfully, we can use the SELECT query, as seen above.

sqlite> SELECT * FROM languages;
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.