SQLite | Installing SQLite | Testing the SQLite Command-Line Tool

This page shows how to try the downloaded SQLite command-line tool and confirm that it works.

Create a Database and Table with the Command-Line Tool

First, start a command prompt. Because PATH has not been configured separately, move to the directory where the sqlite3 executable is located.

Create a database. To create a database with the command-line tool, use the following format.

sqlite3 database-name

When you specify a database name and run the sqlite3 program, SQLite connects to the database with that name if it already exists. If the database with that name does not exist, SQLite creates a new database and then connects to it.

The database name can be anything, but a file is created with the specified database name. For example, you can use sampledb.sqlite3 or sampledb.db. A name without an extension, such as sampledb, is also fine. Here, use sample.sqlite3.

sqlite3 sample.sqlite3

$ sqlite3 sample.sqlite3
SQLite version 3.19.3 2017-06-27 16:48:08
Enter ".help" for usage hints.
sqlite> 

A new database named sample.sqlite3 has been created and connected. However, the actual file is created only after you create an object such as a table in the database. While connected to SQLite, the sqlite> prompt is displayed.

Next, create one table in the database. Run the following statement.

create table user(id, name);

$ sqlite3 sample.sqlite3
SQLite version 3.19.3 2017-06-27 16:48:08
Enter ".help" for usage hints.
sqlite> create table user(id, name);
sqlite> 

The table has been created. This example created a database and then created a table in that database.

To close the connection to the database, enter .exit.

.exit

$ sqlite3 sample.sqlite3
SQLite version 3.19.3 2017-06-27 16:48:08
Enter ".help" for usage hints.
sqlite> create table user(id, name);
sqlite> .exit

The connection to the database has been closed.

The File Where the Database Is Stored

After you create a database, data is stored in the database file when you create an object such as a table. Because no location was specified separately, the database file is created in the directory where the sqlite3 executable is located.

$ ls
sample.sqlite3   sqldiff          sqlite3          sqlite3_analyzer

You can see that a new file named sample.sqlite3 has been created. This file stores the data for the sample.sqlite3 database you just created. In this way, SQLite creates and manages one file for each database, although in some cases it may use multiple files.