Posted onNov 5

Introduction to SQL using SQLite: Create Table

#javascript #beginners #api #database

Objectives

Create A SQLite Database
Import Database Into an SQL Client
SQLite Data Types
Constraints
Create Table

Introduction

Previously, we discussedDatabases briefly, and in this excerpt, we will look intoSQL.

In this excerpt, we will discuss creating tables.

Create A SQLite Database

Creating a SQLite database is quite simple and straightforward. Create a file with a.sqlite extension. I will make one calledapp.sqlite. Open the newly created database withBeekeeper Studio orSQLite Browser.

InBeekeeper Studio, under theNew Connection text, click on the drop-down,Select a connection type.... ChooseSQLite. Now, click on theChoose File button and navigate to where your new SQLite database was created and choose it.

Then click onConnect, and this should be what you will see. The database name will appear in the very lower left corner.

InSQLite Browser, you can create a new database withNew Database orOpen Database. We will open a new database. Click on theOpen Database button, then navigate to where your new SQLite database was created and choose it.

After choosing it, the path to the selected database will be displayed in the title bar.

SQLite Data Types

InJavaScript Essentials: Part 1, we discussed data types. In JavaScript, we haveNumber,Boolean, andString. InSQLite we haveINTEGER,REAL,TEXT,BLOB andNULL.

INTEGER andREAL arenumbers.
SQLite doesn't have a direct boolean. As we are all aware, aboolean value is eithertrue orfalse, which can be represented as1 or0. Hence, we can use anINTEGER for aboolean.
TEXT forstring.
BLOB - "Binary Large Object" is used to store raw binary data such as images, files, etc.
NULL mean no data. When you fill a form and you omit or leave an input blank, it isnull.

Constraints

These are limitations, guards or characters of a field.

NOT NULL:NULL means no data, soNOT NULL means noNULL or data is expected or required.
UNIQUE KEY: A field declared unique will have a unique key; as such, any other values will have a different value. A common use case is for emails or usernames. You can't have two users on your platform with the same emails. Imagine you send a verification code to one, and the other also receives it.
PRIMARY KEY: A primary key uniquely identifies a row (record) in a table. Generally, as soon as a field is declared as a primary key, it becomes the primary key, regardless of its type. However, in most cases, the primary key is the row ID.
DEFAULT: It is the value to be used when no value is passed when creating the row. When a field is notNOT NULL constrained, the default value will beNULL.
CHECK: It is a condition that the value must satisfy.
FOREIGN KEY: It is a field used to reference another table. This is usually the primary key from the other table.

Create Table

InIntroduction to Databases, we talk about tables and what they are.

To create a table in SQLite, we can either use the client (Beekeeper studio,SQLite, etc as a GUI or script, a script that you will have to execute or directly on the terminal (with SQLite).

Generally, the format of creating a table is:

CREATETABLE<TABLE_NAME>(field1type[constraints],field2type[constraints],...,fieldNtype[constraints]);

Human tables

Let's create a table for a human. Boring the knowledge fromJavaScript - knowledge transfer, a human has some characteristics (properties), which will turn into fields (columns) inSQL. Let's say we have a human with a name, date of birth, social security number, email, cars and has a pet, just to keep it short.

Now, what will be the types of the following properties:firstName,lastName,middleName,dateOfBirth,socialSecurityNumber,email,numberOfCars, andhasPet?.

In a real application or API, we don't save SSNs because it is very sensitive information.

The same types would translate to the field type in SQL(ite).

firstName,lastName,middleName,socialSecurityNumber andemail are allstring, as such they becomeTEXT
dateOfBirth is aDate object, but we don't have a direct date type inSQLite, so we can parse thedateOfBirth as astring, which meansdateOfBirth can be saved as aTEXT. TheDate object has a method,valueOf() orgetTime(), with this, we can convert the date to anumber, which we can either choose to store as anINTEGER orTEXT
numberOfCars is anumber and so we will use anINTEGER type
hasPet is aboolean. Since there is noboolean type in SQLite, we can use1 fortrue and0 forfalse.

Since the subject of this is a human, we can name our tablehuman.

We can choose another format for the way we write out our field names. We will be usingCamel Casing
Double quote fields and single quote string/text values

CREATETABLE"human"("firstName"TEXT,"lastName"TEXT,"socialSecurityNumber"TEXT,"email"TEXT,"dateOfBirth"TEXT,"hasPet"INTEGER,"numberOfCars"INTEGER,);

This table has no constraints. Apart from the email and SSN, which can be used to identify a unique human, we can also introduce a numeric field for record ID,id. Thisid will be a primary key.

CREATETABLE"human"("id"INTEGERPRIMARYKEY,...);

Now we have a primary key that will be used to identify a row. In practice, thisid, which is an integer, must be different for each row to be a primary key. We can add one to the currentid to get the next validid. This will mean we will have to manually or programmatically take care of this and maintain it. However, the database has this constraint calledAUTOINCREMENT, which will "automatically" increment the "ids" for uniqueness.

CREATETABLE"human"("id"INTEGERPRIMARYKEYAUTOINCREMENT,...);

Primary keys are not null and unique, so we don't have to specify that
Comment in Javascript is// or/* */. In SQL, it is-- for a single-line comment

For the table we have above, by default, all the fields are nullable. This means they are not required. When a field is required (needed not to be null), then we have to specify that it is not null.firstName,lastName,middleName,socialSecurityNumber andemail are fields whose values are required, so we will have to set them toNOT NULL. This way, the database will require that values be explicitly passed for these fields.

CREATETABLE"human"("id"INTEGERPRIMARYKEYAUTOINCREMENT,"firstName"TEXTNOTNULL,"lastName"TEXTNOTNULL,"socialSecurityNumber"TEXTNOTNULL,"email"TEXTNOTNULL,"dateOfBirth"TEXT,"hasPet"INTEGER,"numberOfCars"INTEGER,);

dateOfBirth in our case here will be nullable; as such, we have to handle it appropriately. Not every human has a pet. In a case like this, we set the default value forhasPet tofalse. The same applies tonumberOfCars but to0.

Why won't null be necessarily a "good" value?

CREATETABLE"human"(..."hasPet"INTEGERDEFAULT0,"numberOfCars"INTEGERDEFAULT0,);

socialSecurityNumber andemail are supposed to be unique values. We can programmatically handle this as well via code before the data reaches the database.

CREATETABLE"human"(..."socialSecurityNumber"TEXTNOTNULLUNIQUE,"email"TEXTNOTNULLUNIQUE,...);

You can ride the updated script viaBeekeeper studio orSQLite. We can also run this via the terminal.

$sqlite3 app.sqliteSQLite version 3.43.2 2023-10-10 13:08:14Enter".help"forusage hints.sqlite>

Even when not create,sqlite3 <DATABASE NAME.sqlite> will create a new SQLite database with name<DATABASE NAME.sqlite>
Create a file with a.sqlite extension. I will create one calledapp.sqlite.

sqlite>CREATETABLE"human"((x1...>"id"INTEGERPRIMARYKEYAUTOINCREMENT,(x1...>"firstName"TEXTNOTNULL,(x1...>"lastName"TEXTNOTNULL,(x1...>"socialSecurityNumber"TEXTNOTNULLUNIQUE,(x1...>"email"TEXTNOTNULLUNIQUE,(x1...>"dateOfBirth"TEXT,(x1...>"hasPet"INTEGERDEFAULT0,(x1...>"numberOfCars"INTEGERDEFAULT0(x1...>);sqlite>.tableshumansqlite>

CREATE,TABLE,INTEGER,DEFAULT, etc can all be in lower case
Analyze and create a table for the profile object below.

constprofile={name:"John Doe","date of birth":"2000-12-25",profession:"Software Engineer","number of pets":2,"weight of protein in grams":12.5,"has a job":true,};