SQLite Database: How to Create, Open, Backup & Drop Files

SQLite databases are very lightweight. Unlike other database systems, no configuration or installation is required to start working on an SQLite database. All you need is the SQLite library, which is under 500 KB, so you can begin working with SQLite databases and tables directly.

SQLite CREATE Database

Unlike other database management systems, there is no CREATE DATABASE command in SQLite. Here is how you can create a new database:

• Open the Windows Command Line tool (cmd.exe) from the Start menu, type “cmd”, and open it.
• The cmd window will open in the default user folder. On my machine, it is “C:\Users\MGA”:

• From the Installation and packages tutorial, you should now have created an SQLite folder in the “C” directory and copied sqlite3.exe into it.
• Next, navigate to where sqlite3.exe is located using the following SQLite command line:

Following is the basic syntax of the sqlite3 command to create a database:

sqlite3 SchoolDB.db

This will create a new database named “SchoolDB.db” in the same directory where you copied your .exe file.

If you navigate to the directory “c:\sqlite”, you will find that the file “SchoolDB.db” has been created, as in the following screenshot:

You can confirm that the database is created by writing the following SQLite command:

.databases

This gives you the list of databases created, and you should see the new database “SchoolDB.db” listed there:

SQLite CREATE Database in a Specific Location using Open

If you want to create the database file in a specific location rather than in the folder where sqlite3.exe is located, here is how to open an SQLite database file:

• Navigate manually to the folder where sqlite3.exe is located, “C:\sqlite”.
• Double-click sqlite3.exe to open the SQLite command line.
• The command to open a database file is:

.open c:/users/mga/desktop/SchoolDB.db

This creates the database “SchoolDB.db” and stores the file in the specified location. The same command also opens an existing database, so running it again simply reopens that database:

.open c:/users/mga/desktop/SchoolDB.db

SQLite checks whether “SchoolDB.db” exists in that location. If it exists, SQLite opens it; otherwise, a new database is created with that file name.

SQLite create a database and populate it with tables from a file

If you have a .SQL file that contains the table schema and you want to create a new database with those same tables, the following example explains how to do this.

Example:

Here we create the sample database used throughout this SQLite tutorial, named “SQLiteTutorialsDB”, and populate it with tables as follows:

• Open a text file and paste the following SQLite commands into it:

CREATE TABLE [Departments] ( 
	[DepartmentId] INTEGER  NOT NULL PRIMARY KEY, 
	[DepartmentName] NVARCHAR(50)  NOT NULL  
); 
CREATE TABLE [Students] (  
	[StudentId] INTEGER  PRIMARY KEY NOT NULL,
	[StudentName] NVARCHAR(50) NOT NULL, 
	[DepartmentId] INTEGER  NULL,   
	[DateOfBirth] DATE  NULL  
);     
CREATE TABLE [Subjects] (  
	[SubjectId] INTEGER  NOT NULL PRIMARY KEY,  
	[SubjectName] NVARCHAR(50)  NOT NULL  
); 
CREATE TABLE [Marks] (  
	[StudentId] INTEGER  NOT NULL,  
	[SubjectId] INTEGER  NOT NULL,  
	[Mark] INTEGER  NULL  
);

The code above creates four tables, as follows:

• “Departments” table, with the following columns:
  • “DepartmentId”, an integer department id, declared as a PRIMARY KEY.
  • “DepartmentName”, a string department name that does not allow null values (NOT NULL).
• “Students” table, with the following columns:
  • “StudentId”, an integer declared as a PRIMARY KEY.
  • “StudentName”, the student name, which does not allow a null value.
  • “DepartmentId”, an integer referring to the department id in the Departments table.
  • “DateOfBirth”, the date of birth of the student.
• “Subjects” table, with the following columns:
  • “SubjectId”, an integer declared as a PRIMARY KEY.
  • “SubjectName”, a string value that does not allow null values.
• “Marks” table, with the following columns:
  • “StudentId”, an integer indicating a student id.
  • “SubjectId”, an integer indicating a subject id.
  • “Mark”, the mark a student gets in a subject, an integer that allows null values.

• Save the file as “SQLiteTutorialsDB.sql” in the same location as sqlite3.exe.
• Open cmd.exe and navigate to the directory where sqlite3.exe is located.
• Write the following command:

sqlite3 SQLiteTutorialsDB.db < SQLiteTutorialsDB.sql

A new database “SQLiteTutorialsDB” is created, and the file “SQLiteTutorialsDB.db” is placed in the same directory, as follows:

You can confirm the tables are created by opening the database we just created:

.open SQLiteTutorialsDB.db

Then write the following command:

.tables

This gives you the list of tables in “SQLiteTutorialsDB”, and you should see the four tables we just created:

With the sample database and its four tables in place, you can move on to running SQLite INSERT, UPDATE, and DELETE queries against the data.

SQLite Backup & Database

To back up a database, you have to open that database first, as follows:

• Navigate to the “C:\sqlite” folder, then double-click sqlite3.exe to open it.
• Open the database using the following query:

.open c:/sqlite/sample/SchoolDB.db

This command opens a database located in the directory “c:/sqlite/sample/”.

If it is in the same directory as sqlite3.exe, then you do not need to specify a location, like this:

.open SchoolDB.db

Then, to back up the database, write the following command. This backs up the whole database into a new file “SchoolDB.db” in the same directory:

.backup SchoolDB.db

If you do not see any errors after executing that command, the backup has been created successfully.

SQLite Drop Database

Unlike other database management systems, there is no DROP DATABASE command in SQLite. If you want to drop a database in SQLite, all you have to do is delete the database file.

Notes:

• You cannot create two databases in the same location with the same name; the database name is unique within a directory.
• Database names are case insensitive.
• There are no privileges required to create the databases.

FAQs