How to Create, Open, Backup Database in SQLite

Details

SQLite databases are very lightweight. Unlike other database systems, there is no configuration, installation required to start working on an SQLite database.

What you need is the SQLite library which is less than 500KB size. We will jump start working on SQLite databases and tables directly.

In this tutorial, you will learn-

SQLite CREATE Database

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

  • Open the Windows Command Line tool (cmd.exe) from the start, type "cmd" and open it.
  • The "cmd" will open in the default user folder, on my machine, it is "C:\Users\MGA":

    Sqlite Database Tutorial

  • From the Installation and packages tutorial, you should now have created an SQLite folder in the "C" directory and copied the sqlite3.exe on it.
  • After that we should navigate to where the sqlite3.exe is located by the following command:

Sqlite Database Tutorial

  • Following is the basic syntax of sqlite3 command to create a database 
    sqlite3 SchoolDB.db
  • This will create a new database with the name "SchoolDB.db"in the same directory where you have copied your .exe file.

Sqlite Database Tutorial

  • If you navigate to the directory: "c:\sqlite", you will find the file "SchoolDB.db"is created as the following screen shot:

    Sqlite Database Tutorial

  • You can ensure that the database is created by writing the following command: 
    .databases

    This will give you the list of databases created, and you should see the new database "SchoolDB.db" listed there:

    Sqlite Database Tutorial

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 same location where the sqlite3.exe is located, you can do this:

  • Navigate manually to the folder where sqlite3.exe is located "C:\sqlite".

    Sqlite Database Tutorial

  • Double click sqlite3.exe to open the command line.
  • Run the following command: 
    .open c:/users/mga/desktop/SchoolDB.db
  • This will create a new database with the name "SchoolDB.db" and store the database file in the location specified.

    Note that, the same command will be used to open the database file if the database file is already created. So if you write the same exact command again you will open the database itself: 

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

    SQLite will check the file name "SchoolDB.db" whether it is found in the same location or not. If the file exists, it will open it. Otherwise, a new database will be created with the same file name specified in the specified location.

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

If you have a .SQL file that contains the tables schema and you want to create a new database with the same tables from that file, in the following example, we will explain how to do this.

Example:

In the following example, we will create the sample database. We will use this sample database throughout the SQLite tutorials, with the name "SQLiteTutorialsDB" and populate it with the tables. As following:

  • Open a text file and paste the following commands on 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 will create four tables as following:

  • "Departments" table with the following columns:
    • "DepartmentId" an integer number indicates the department id, and it is declared as a PRIMARY KEY (explained later in Column Constraints Section).
    • "DepartmentName" – a string name for the department, and it doesn't allow null values using NOT NULL constraint.
  • "Students" table with the following columns:
    • "StudentId" an integer number, and it is declared as a PRIMARY KEY.
    • "StudentName" – the name of the student and it doesn't allow a null value using NOT NULL constraint.
    • "DepartmentId" Integer number that refers to the department Id to the department id column in the departments table.
    • "DateOfBirth" The date of birth of the student.
  • "Subjects" table with the following columns:
    • "SubjectId" an integer number and it is declared as a PRIMARY KEY.
    • "SubjectName" a string value and it doesn't allow null values.
  • "Marks" table with the following columns:
    • "StudentId" integer indicates a student id.
    • "SubjectId" integer indicates a subject Id.
    • "Mark" the mark a student gets in a specific subject it is also integer and it does allow null values.
  • Save the file to SQLite as "SQLiteTutorialsDB.sql" in the same location where sqlite3.exe is located.
  • Open cmd.exe, and navigate to the directory where sqlite3.exe is located.
  • Write the following command: 
    sqlite3 SQLiteTutorialsDB.db < SQLiteTutorialsDB.sql
  • Then, a new database "SQLiteTutorialsDB" should be created, and the file "SQLiteTutorialsDB.db" will be located in the same directory. As following:

    Sqlite Database Tutorial

  • You can ensure that this table is created by opening the database we just created like this: 
    .open SQLiteTutorialsDB.db
  • Then write the following command: 
    .tables
  • This command will give you the list of tables in the "SQLiteTutorialsDB", and you should see the four tables we had just created:

    Sqlite Database Tutorial

SQLite Backup & Database

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

  • Navigate to "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 will open a database that is located on the following directory "c:/sqlite/sample/"

  • If it is in the same directory where sqlite3.exe is located, then you don't need to specify a location, like this: 
    .open SchoolDB.db
  • Then to back up a database write the following command:
  • This will back up the whole database into a new file "SchoolDB.db" in the same directory: 
    .backup SchoolDB.db
  • If you don't see any errors after executing that command, this means that the backup is 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, all you have to do is to delete the database file.

Notes:

  • You can't create two databases in the same location with the same name, the database name is unique in the same directory.
  • Database names are case insensitive.
  • There are no privileges required to create the databases.

 

YOU MIGHT LIKE: