Database in NodeJS

Now that we know the basics of SQL we can actually use it in our existing project.

Preparation

Parts of an SQL database

When working with SQL, it helps to understand the key parts that make up a database:

• Database - A database is the main container that holds all of your data. You usually have one database per project.
  💡 Think of it like a drawer—inside it, you can have different compartments for different types of information.

• Table - A table lives inside a database and stores data grouped by category.
  For example: users, posts, comments are all different tables that serve different purposes.
  Think of a table as a specific section in your drawer. One section holds all your pencils, another all your notebooks.

• Row - A row is a single entry in a table—one piece of data.
  If you’re looking at the users table, each row is one specific user (like Alice or Bob).
  Think of a row as one specific item in your drawer section, like one individual pencil from the pencil section.

• Column - A column is an attribute or property of the data in the table.
  For a users table, common columns would be: id, name, email, created_at.
  Think of columns as labels for what you want to remember about each item—like color or size for each pencil.

  Apart from that every table needs to have a column that is PRIMARY KEY. This is a unique ID for each row and its used to identify and connect data.

Working with SQL

Here we will learn how to write simple SQL for our current needs which for now is creating database and tables, and CRUD(Create, read, update, delete) from table. Later on we will learn advanced querying, relationships, indexes and other fun stuff.

Create Database

CREATE DATABASE mydatabase;

Creating database with a specific name(mydatabase).

Create Table

CREATE TABLE users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name VARCHAR(100),
  email VARCHAR(255) UNIQUE NOT NULL
);

Creating a table with specific name (users) and we are defining 3 different columns. Notice that each column needs to have its type.

• id - we define this as type INTEGER meaning its a full number. We also set this value to be PRIMARY KEY but also as AUTOINCREMENT meaning this value will be automatically incremented on each new inserted row. We don’t have to define NOT NULL because PRIMARY KEY is by default not nullable.
• name - this is a type VARCHAR meaning string but with a max allowed length of 100 characters. This field can be null
• email - this is a type VARCHAR with a max allowed length of 255 characters. We are also enforcing this field to be unique in all rows. This field can not be null due to NOT NULL

Keep in mind that depending on the database engine that you are using some types are named differently or have some specifics on how its stored. But in this tutorial, we’re using SQLite, which is lightweight, beginner-friendly, and flexible enough for learning but for some intermediate work as well.

Insert data

INSERT INTO users (name, email)
VALUES ('Alice', 'alice@example.com')

or if you want to insert multiple values at once:

INSERT INTO users (name, email)
VALUES
  ('Bob', 'bob@example.com'),
  ('Charlie', 'charlie@example.com');

In both cases we are inserting in table users, and in first () we define which properties we are inserting then in VALUES(...) we define values for those properties.
Note that id doesn’t need to be defined as its set to AUTOINCREMENT and as such will be auto defined and auto incremented. If you wanted you could define it but its best to leave it like this(in 99% of cases).

Search data

SELECT *
FROM users
WHERE id = 1;

• SELECT- Defines which columns you want to return. You can specify exact columns like SELECT id, name or use * to return all columns.
• FROM - Tells the database which table to get the data from (users). WHERE- Filters the data based on a condition (e.g., only return the row where id = 1).

More example with only WHERE part:

WHERE id > 1;
WHERE name = "test" AND id < 250 AND email LIKE char%;

Unlike JS where we have == for a comparison, in SQL we use only = for comparison.

• AND allows you to combine multiple conditions.
• LIKE is used for partial string matching. Use % as a wildcard:
  • char% means “starts with char”
  • %char means “ends with char”
  • %char% means “contains char anywhere”

Update data

UPDATE users
SET name = 'Alice Johnson', email = 'alice.johnson@example.com'
WHERE id = 1;

• UPDATE – Specifies the table you want to update the data in.

• SET – Defines which columns to update and their new values. If you’re updating multiple columns, you separate each pair with a comma.

  Example: name = ‘Alice Johnson’ and email = ‘alice.johnson@example.com’.

• WHERE – Filters which rows to update. It’s just like a WHERE clause in a SELECT statement. This ensures you’re only updating the row(s) that meet your conditions (e.g., id = 1).

  Note: Without the WHERE clause, every row in the table would be updated! Always be careful when using UPDATE to include a WHERE clause unless you truly want to update all rows.

  Delete data

DELETE FROM users
WHERE id = 1;

• DELETE – Tells the database you want to delete a row.

• FROM – Specifies the table you want to delete the data in.

• WHERE – Filters which rows to delete. It’s just like a WHERE clause in a SELECT statement. This ensures you’re only deleting the row(s) that meet your conditions (e.g., id = 1).

  Note: Without the WHERE clause, every row in the table would be deleted! Always be careful when using DELETE to include a WHERE clause unless you truly want to delete all rows.