Here we will learn the most commonly used commands of SQLite3 command line program to show tables, database, indexes, the schema of tables, etc. with examples.
The SQLite project provides a command-line tool called SQLite3 to allow users to interact with SQLite databases to perform insert, update, delete, etc. operations by writing SQLite statements based on our requirements.
The SQLite3 command-line tool provides some special commands which are called as “dot (.) commands” to define output format for tables, examine databases and for other administrative operations.
These SQLite3 dot commands always start with “dot (.)”. Here we need to remember that we should not use a semicolon (;) to define termination of the statement.
To see all the available commands in SQLite run following command.
sqlite> .help
The above “.help” command will list all the available commands in SQLite like as shown below.
| Command | Description |
|---|---|
| .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 |
| .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 |
| .dbinfo ?DB? | Show status information about the database |
| .dump ?TABLE? ... | Dump the database in an SQL text format. If TABLE specified, only dump tables matching LIKE pattern TABLE. |
| .echo on|off | Turn command echo on or off |
| .eqp on|off|full | Enable or disable automatic EXPLAIN QUERY PLAN |
| .exit | Exit this program |
| .explain ?on|off|auto? | Turn EXPLAIN output mode on or off or to automatic |
| .fullschema ?--indent? | Show schema and the content of sqlite_stat tables |
| .headers on|off | Turn display of headers on or off |
| .help | Show this message |
| .import FILE TABLE | Import data from FILE into TABLE |
| .imposter INDEX TABLE | Create an imposter table TABLE on index INDEX |
| .indexes ?TABLE? | Show names of all indexes. If TABLE specified, only show indexes for tables matching LIKE pattern TABLE. |
| .limit ?LIMIT? ?VAL? | Display or change the value of an SQLITE_LIMIT |
| .lint OPTIONS | Report potential schema issues. Options: fkey-indexes. Find missing foreign key indexes |
| .load FILE ?ENTRY? | Load an extension library |
| .log FILE|off | Turn logging on or off. FILE can be stderr/stdout |
| .mode MODE ?TABLE? |
Set output mode where MODE is one of:
|
| .nullvalue STRING | Use STRING in place of NULL values |
| .once FILENAME | Output for the next SQL command only to FILENAME |
| .open ?--new? ?FILE? | Close existing database and reopen FILE. The --new starts with an empty file |
| .output ?FILENAME? | Send output to FILENAME or stdout |
| .print STRING... | Print literal STRING |
| .prompt MAIN CONTINUE | Replace the standard prompts |
| .quit | Exit this program |
| .read FILENAME | Execute SQL in FILENAME |
| .restore ?DB? FILE | Restore content of DB (default "main") from FILE |
| .save FILE | Write in-memory database into FILE |
| .scanstats on|off | Turn sqlite3_stmt_scanstatus() metrics on or off |
| .schema ?PATTERN? | Show the CREATE statements matching PATTERN. Add --indent for pretty-printing |
| .separator COL ?ROW? | Change the column separator and optionally the row separator for both the output mode and .import |
| .shell CMD ARGS... | Run CMD ARGS... in a system shell |
| .show | Show the current values for various settings |
| .stats ?on|off? | Show stats or turn stats on or off |
| .system CMD ARGS... | Run CMD ARGS... in a system shell |
| .tables ?TABLE? | List the names of tables. If TABLE specified, only list tables matching LIKE pattern TABLE. |
| .testcase NAME | Begin redirecting output to 'testcase-out.txt' |
| .timeout MS | Try opening locked tables for MS milliseconds |
| .timer on|off | Turn SQL timer on or off |
| .trace FILE|off | Output each SQL statement as it is run |
| .vfsinfo ?AUX? | Information about the top-level VFS |
| .vfslist | List all available VFSes |
| .vfsname ?AUX? | Print the name of the VFS stack |
| .width NUM1 NUM2 ... | Set column widths for "column" mode. Negative values right-justify |
Now we will see some of the most commonly used commands in SQLite3 to work with SQLite databases.
In SQLite, by using the “.databases” command we can list names and files of the attached databases.
Following is the simple example of getting all the available databases.
sqlite> .databases
seq name file
--- --------------- ------------------------
0 main d:\sqlite\db\users.db
By using the SQLite “.backup” command we can easily take the backup of available databases.
Following is the example of taking backup of the database (default "main") to a file.
C:\>cd sqlite
C:\sqlite>sqlite3 users.db
SQLite version 3.13.0 2016-05-18 10:57:30
Enter ".help" for usage hints.
sqlite> .databases
seq name file
--- --------------- ---------------------
0 main C:\sqlite\users.db
sqlite> .backup d:/sqlite/backup/user_backup
If you observe the above example we are opening SQLite3 tool from the command prompt and taking backup of available database.
Now, Here you can see back up is taken at d:/sqlite/backup/user_backup.
In SQLite, by using the “.clone” command we can easily clone a new database from the existing database.
Following is the example of cloning new database from existing database.
C:\>cd sqlite
C:\sqlite>sqlite3 users.db
SQLite version 3.13.0 2016-05-18 10:57:30
Enter ".help" for usage hints.
sqlite> .open d:/sqlite/backup/user_backup.db
sqlite> .tables
ADDRESS_MASTER USER_MASTER
sqlite> .clone d:/sqlite/backup/user_backup2.db
USER_MASTER... done
ADDRESS_MASTER... done
sqlite>
If you observe the above example we are cloning new database user_backup2.db from existing database user_backup.db in the same folder.
In SQLite, by using “.exit” command we can easily exit sqlite3 program.
sqlite> .exit
When we execute above command we will come out of the sqlite3 program.
By using the “.import” command we can easily import data from file to tables based on our requirements.
Following is the syntax of .import command in SQLite to import data from file to table.
sqlite> .import FILE TABLE
Now we will see how to import CSV file data into the SQLite table using ".import" command. The ".import" command takes two arguments one is the name of the file from which CSV file data need to be read and another one is name of the SQLite table to insert CSV data.
It is important to set the "mode" to "CSV" before running the ".import" command.
Following is the example of importing data from CSV file to database table using the “.import” command.
sqlite> .tables
ADDRESS_MASTER USER_MASTER
sqlite> .mode csv
sqlite> .import d:/sqlite/backup/tempuser.csv user_table
sqlite> .tables
ADDRESS_MASTER USER_MASTER user_table
sqlite> .mode column
sqlite> SELECT * FROM user_table;
U_TYPE U_NAME
---------- ----------
1 ADMIN
2 REGISTERED
3 GUEST
In the above example, we are able to see all the available tables using .table command. After that we imported all data of tempuser.csv file to the user_table table and again we fire .table command to see the list of tables in the database.
By using the SQLite “.quit” command, we can exit from the current database. Following is the simple example of quitting from the current database.
sqlite> .quit
When we run above command sqlite3 tool will exit from current database session.
By using the “.schema” command we can easily check the schema of the tables and indexes which we created on table columns in the database.
Following is the syntax of SQLite Schema command to check the schema of tables.
.schema ?TABLE?
This command is used to see the schema of a table i.e. create statement of the table which we have passed in ?TABLE? parameter and it also shows the indexes which we created on the table columns.
Following is the example of executing the .schema command to see the database schema information.
sqlite>.schema
CREATE TABLE USER_MASTER(U_TYPE INTEGER PRIMARY KEY, U_NAME VARCHAR(50));
CREATE TABLE ADDRESS_MASTER(ADDRESS1 VARCHAR(100),CITY VARCHAR(20),PINCODE VARCHAR(6));
CREATE INDEX id_index ON USER_MASTER(U_TYPE);
The above command will return all the available tables schema details. In case if you want to check the schema of a particular table means we need to write the command like as shown following.
sqlite> .schema user_master
CREATE TABLE USER_MASTER(U_TYPE INTEGER PRIMARY KEY, U_NAME VARCHAR(50));
The above command return only schema of “user_master” table.
By using the “.tables” command we can easily check available tables in the SQLite database.
Following is the syntax of the SQLite tables command to see all the available tables.
.tables ?TABLE?
The above syntax list the names of all available tables in the database. In case if we specify table name only matching table will be returned.
Following is the example of using the “.tables” command to see available tables in the database.
sqlite> .tables
ADDRESS_MASTER USER_MASTER table1 table2
sqlite> .tables ADDRESS_MASTER
ADDRESS_MASTER
This is how we can use commands in SQLite to get the required information.