What's new
HTML Forums | An HTML and CSS Coding Community

Welcome to HTMLForums; home of web development discussion! Please sign in or register your free account to get involved. Once registered you will be able to connect with other members, send and receive private messages, reply to topics and create your very own. Our registration process is hassle-free and takes no time at all!

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

Sophia

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

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

SQLite CREATE Database​

Unlike other database management systems, there is no CREATE DATABASE command in SQLite. In this SQLite tutorial, 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 Package tutorial, you should now have created an SQLite folder in the “C” directory and copied the sqlite3.exe on it.
  • After that we should select the SQLite file and navigate where the sqlite3.exe is located by the following SQLite command line:
Sqlite Database Tutorial


  • Following is the basic syntax of the sqlite3 command to create a databasesqlite3 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 select SQLite file and navigate to the directory: “c:\sqlite”, you will find the file “SchoolDB.db”is created as the following screenshot:
    Sqlite Database Tutorial
  • You can ensure that the database is created by writing the following SQLite commands:.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 learn how to open a SQLite file and create the database file in a specific location rather than in the same location where the sqlite3.exe is located, here is how to view SQLite database:
  • Navigate manually to the folder where sqlite3.exe is located “C:\sqlite”.
    Sqlite Database Tutorial
  • 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 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 creates a database and populate it with tables from a file​

If you have a . SQL file that contains the table's 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 tutorial, with the name “SQLiteTutorialsDB” and populate it with the tables. As following:

  • Open a text file and paste the following SQLite 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 follows:
  • “Departments” table with the following columns:
  • DepartmentId” an integer number that 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 SQLite command. If you want to drop database SQLite, 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.
 
Back
Top