Creating and deleting databases and tables with SQLite
In this article, we are going to cover the creation and destruction of databases and tables in SQLite. We can quickly refresh our memory of these two terms:
- Databases: divide different sets of structures and data from one another
- Tables: define the data structure and store the actual data values within the database
SQLite utilizes the command line for working with your database files. To follow along, you will need to download the respective SQLite CLI for your machine. Let's begin.
Create a database
To begin, start a new SQLite shell by typing
sqlite3 into your command prompt. The result will look similar to the following:
>sqlite3SQLite version 3.32.3 2020-06-18 17:32:03Enter ".help" for usage hints.Connected to a transient in-memory database.Use ".open FILENAME" to reopen on a persistent database.sqlite>
By default, a SQLite session begins using an in-memory database. This means that it is not currently reading from a file.
If you already have a persistent database, you can open its existing file by using the
.open <FILENAME> command. For example, in the following command, the pre-existing
test.db database is opened.
Note: If you indicate a file name that does not already exist, the sqlite3 tool will create the database file.
To create a new database more explicitly, add
--new to the
.open <FILENAME> command. Here we demonstrate the command by creating the
.open --new people.db
This will save your existing changes to your given database file for the remainder of the session.
List database connections
Now that you have created databases, you can check your connections by using the
The following illustrates what the return of the command will look like resulting in the
main database being displayed.
sqlite> .databasemain: /Users/user/people.dbsqlite>
For some use cases, you may want to add other databases to the current connection. This can be done using the
ATTACH DATABASE statement as demonstrated below by the addition of
test.db to our active connection.
ATTACH DATABASE "test.db" AS test;
Now when we run the
.database command we will get the following two connections returned, the
sqlite> .databasemain: /Users/user/people.dbtest: /Users/user/test.dbsqlite>
Creating tables within a database in SQLite
With your databases created and connections verified, you can begin introducing data structure to your database(s).
How to use SQLite's
CREATE TABLE command
In this section, we will add the data structure to our newly created
people.db database by creating a
student table. To create a table within your database, you will utilize the
CREATE TABLE statement with the following syntax:
CREATE TABLE student (id INTEGER PRIMARY KEY,first_name TEXT,last_name TEXT,age INTEGER,student_email TEXT NOT NULL,class TEXT);
We can break down the above statement into the following pieces:
CREATE TABLE <table name>: This is the basic command statement. In the example, the
studentand should be whatever you intend to name your table.
<column name> <data type>: This syntax defines a basic column within the table. In the example, a column name would be
first_nameand its corresponding data type defined by SQLite Data Types is
<column constraint>: Column constraints are optional restraints adding additional requirements for the data entering your table. In the example, the column constraint
Not Nullis added to the
student_emailcolumn. This ensures no entry is made without this column being populated.
<table constraint>: Like a column constraint, table constraints are optional to add additional requirements to your data. There is an exception if the constraint affects the interaction of multiple columns instead of a singular column. In our example, the addition of
PRIMARY KEYto the
idfield is an example of a table constraint.
It is important to note that the
CREATE TABLE statement will create the table you specify in the
main database by default. If you have multiple connections open to databases, you need to specify
<database>.<table> in your statement to create the table in a database other than
main. Adjusting the previous example will look like this to create the table in
CREATE TABLE test.student (id INTEGER PRIMARY KEY,first_name TEXT,last_name TEXT,age INTEGER,student_email TEXT NOT NULL,class TEXT);
How to create tables only if they do not already exist
To ensure that you are creating a table that does not already exist, the optional
IF NOT EXISTS clause can be added to the previous example as follows:
CREATE TABLE IF NOT EXISTS student (id INTEGER PRIMARY KEY,first_name TEXT,last_name TEXT,age INTEGER,student_email TEXT NOT NULL,class TEXT);
SQLite will throw an error when attempting to create an already present table without the
IF NOT EXISTS clause by default. By adding this clause, the default behavior is overridden with a warning instead of an error. The rest of the command's behavior remains the same.
How to validate your table with
After creating your table, you can verify the structure of your table by using the
.schema command. If we want to make sure the previously created
student table is structured the way we intended, we can check using the following syntax:
The returned result will look as follows for the
sqlite> .schema studentCREATE TABLE student (id INTEGER PRIMARY KEY, first_name TEXT, last_name TEXT, age INTEGER, student_email TEXT NOT NULL, class TEXT);sqlite>
To get a more easily read result, you can use the
.fullschema --indent command. This will show you the schema of the connected database with better spacing:
sqlite> .fullschema --indentCREATE TABLE student (id INTEGER PRIMARY KEY,first_name TEXT,last_name TEXT,age INTEGER,student_email TEXT NOT NULL,class TEXT);sqlite>
.fullschema command will also include dumps of the statistics tables if they exist. We won't cover that for now, but it can be useful to have this output in some cases.
Dropping a table
To drop a table from a database in SQLite, you will use the
DROP TABLE statement. This statement is used as follows for dropping the
DROP TABLE IF EXISTS student;
IF EXISTS statement is optional when dropping a table. The behavior it adds makes sure that the command only runs if the table exists. If it does not exist, then the statement is simply ignored and nothing happens.
Dropping a database
Because SQLite does not have a separate server process like other relational databases such as MySQL or PostgreSQL, there is not a need for a
DROP DATABASE statement. SQLite is an embedded database engine, so in order to drop a database you have to delete the file from the machine. This action will make the database no longer accessible.
This article covers the basics of creating and dropping databases and tables in SQLite. The commands walked through are some of the most basic to get started with SQLite and allow you to start organizing and structuring your data.
Within the statements mentioned, like
CREATE TABLE and
DROP TABLE, many additional parameters that can be considered depending on the use case. You can read into more detail on statements like these in the official SQLite documentation.
When using Prisma, you can use the Prisma Migrate to create your databases and tables. Developing with Prisma Migrate generates migration files based on the declarative Prisma schema and applies them to your database.