SQLite | Database | Creating and Connecting to a Database
This page explains how to create a new SQLite database and how to connect to a database that has already been created.
Creating a Database
To create a new database, enter the following command at the command prompt.
sqlite3 database-name
For example, suppose you want to create a database named myfriend.sqlite3. Start a command prompt, move to the directory that contains the sqlite3 file, and run the following command.
$ sqlite3 myfriend.sqlite3
$ sqlite3 myfriend.sqlite3
SQLite version 3.19.3 2017-06-27 16:48:08
Enter ".help" for usage hints.
sqlite>
If the myfriend.sqlite3 database does not exist, a new database named myfriend.sqlite3 is created and SQLite immediately connects to it.
Because you are now connected to the database, you can create tables and other objects in it. If you do not create anything, the database file is not actually created, so create a simple table here. The detailed method for creating tables is explained on another page.
$ sqlite3 myfriend.sqlite3
SQLite version 3.19.3 2017-06-27 16:48:08
Enter ".help" for usage hints.
sqlite> create table customer (id, name);
$
To exit the command-line tool, enter .exit. This disconnects you from the connected database.
.exit
Now check it in practice. First, connect to the myfriend.sqlite3 database and enter .exit.
$ sqlite3 myfriend.sqlite3
SQLite version 3.19.3 2017-06-27 16:48:08
Enter ".help" for usage hints.
sqlite>.exit
$
Next, check the database file that sqlite3 created.
$ ls
myfriend.sqlite3 sample.sqlite3 sqldiff sqlite3 sqlite3_analyzer
A file with the same name as the specified database name has been created. In SQLite, when you create a database this way, a file with the same name is created and all data contained in the database is stored in that file.
If you want to create the database file somewhere other than the directory that contains sqlite3, specify the path together with the database name as follows.
sqlite3 path/database-name
For example, to create a sample.sqlite3 database in the absolute path /Users/devkuma/sqlite/test, run the following command.
$ sqlite3 /Users/devkuma/sqlite/test/sample.sqlite3
About Database Names
You can specify any name you want for a database. In the example above, myfriend.sqlite3 was used, but any format is acceptable.
myfriend
myfriend.db
myfriend.sqlite
myfriend.sqlite3
When you create a database, SQLite creates a file with the same name as the database and stores the data there. For example, if the database name is myfriend, the created file is also named myfriend without an extension, which can make it hard to tell what kind of file it is later.
For that reason, it is a good idea to choose the database name with the stored file name in mind. There are several extensions in common use, but this site uses the .sqlite3 extension consistently.
Connecting to a Database
To perform various operations on a database, you must first connect to it. When you create a new database, SQLite connects to it at the same time. To connect to an already created database, enter the following command at the command prompt.
sqlite3 database-name
As you can see, the format is the same as when creating a new database. If you specify the name of an existing database as the argument, SQLite connects to that database instead of creating a new one.
Now try connecting to the myfriend.sqlite3 database created earlier. Start a command prompt, move to the directory that contains sqlite3, and run the following command.
$ sqlite3 myfriend.sqlite3
$ sqlite3 myfriend.sqlite3
SQLite version 3.19.3 2017-06-27 16:48:08
Enter ".help" for usage hints.
sqlite>
To confirm that a new database was not created and that you are connected to the existing database, run the .tables command to check the tables created in the existing database.
.tables
Now check it in practice. First, connect to the myfriend.sqlite3 database and enter .tables.
$ sqlite3 myfriend.sqlite3
SQLite version 3.19.3 2017-06-27 16:48:08
Enter ".help" for usage hints.
sqlite> .tables
customer
sqlite>
You can confirm that the customer table exists. Creating a new database and connecting to an existing database use the same command and the same format, so keep this behavior in mind.