Today we will learn how to create the backend that connects to the database. In this case, I will create the to-do for the demo.
- Prerequisite
- Create the project folder
- What is backend
- Backend dependency
- Backend
- Epilogue
- NodeJS
- Docker
- Any HTTP client, ex: Postman, Insomnia, some vs code extension. In this lab, I will use Postman for example.
First, Let's create the folder for our project. For me, I will create a folder named todo-backend. You can now open this folder with any code editor you like. For me, I will use Visual Studio Code.
For dead easy explanation, the backend is the layer that will process the request from any frontend like a website or mobile and respond to the frontend that the request is successful or not.
In this case, we are going to create the to-do application so if the frontend wants to set the to-do status of go washing to completed the backend will process that request from the frontend by checking that if the go washing things have existed in the database or not. If exists, the backend will set the database of that row to completed and tell the frontend that your request is fulfilled. If that thing does not exist, then tell the frontend what happened and the frontend will handle this situation later.
Before we continue to create the backend app. we must have the database to store the data first.
let's create the database first. For convenience, we will use Docker to create the database instance. In this example, we will use MySQL as a database.
TODO: add explanation and URL to the Docker website
first, let's create the docker-compose file docker-compose.yml and add the following content:
version: '3.8'
services:
mysql:
image: mysql:8.3.0
environment:
MYSQL_ROOT_PASSWORD: root
MYSQL_DATABASE: todo
ports:
- 3306:3306 # <LOCAL_PORT>:<CONTAINER_PORT>Let's break down what the file says.
version: '3.8': This is the version of the docker-compose file. This is the latest version at the time of writing.services: This is where we define the services that we want to run.mysql: This is the name of the service. You can name it anything you want.
In the mysql service block, we define that
- we will use the
mysql:8.3.0image. (image) - we set the
MYSQL_ROOT_PASSWORDtorootandMYSQL_DATABASEtotodo. If you want to find more about the environment variables, you can find it here in the sectionEnvironment Variables. - we bind the
portsso that if we access3306in the local machine it will bind to3306in the container (the left number is a local port, and the right number is the container port). Now we can access the database from port 3306.
If you have a database client you can try connecting the database by using username: root, password: root database: todo host: localhost
Don't worry if you don't have any database clients. we will use PHPMyAdmin. PHPMyAdmin is a web-based database client. However, I still recommend using a database client that is not embedded in the server. we will add this service in the docker-compose.yml file.
version: '3.8'
services:
mysql:
image: mysql:8.1.0
environment:
MYSQL_ROOT_PASSWORD: root
MYSQL_DATABASE: todo
ports:
- 3306:3306 # <LOCAL_PORT>:<CONTAINER_PORT>
phpmyadmin:
image: phpmyadmin:5.2.1
ports:
- 8080:80
environment:
PMA_HOST: mysql
PMA_USER: root
PMA_PASSWORD: root
In the code above I added phpmyadmin service that uses the image phpmyadmin:5.2.1 and binds port the host from 8080 to the container port 80 This means we go to http://localhost:8080 it can connect to the PHPMyAdmin web page. (currently not, because we are not running the container)
after we finishing add the file, to run both services just this command in the terminal.
docker compose up -d
-dis make it detached mode, all services will run in the background. For more options please visit here
After we run the command, we can ensure that all the services are running or not by opening a "Docker Desktop". (you can use CLI if you want :p)
If everything is running, now you can access PHPMyAdmin at http://localhost:8080.
As you can see we have the database todo because we define the environment MYSQL_DATABASE: todo in the docker-compose file that we created earlier.
- click the
todotable in the side panel
- click the
SQLbutton in the top section
- after that, we will use SQL to create the new database by using this query
CREATE TABLE `todos` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(255) NOT NULL,
`completed` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
)
The query above will create the table name todos. For more information, please visit this.
- the press
gobutton, or you can pressCTRL+enteron your keyboard
We have successfully create the table
todosas you can see in the left side bar
Now our database is ready
There are so many technologies to create the backend. For example, the popular framework, .net core, SpringBoot (Java), Go (net/http), Fiber, Nest and so on. (there are so many that I can't type all of them)
But today we will use Express to create our to-do app. It's an easy framework to create a simple backend (and also can create more complex functionality backend too).
Express is written in JavaScript so you are required to have the basic knowledge of JavaScript. Don't worry if you don't know anything about it. The "basic syntax" itself is simple.
Javascript itself is the language. But to run the JavaScript code we need the Runtime to run it. The reason why the browser can run the code is because it contains the runtime. For example, Chrome has the V8 engine, Safari has the JavaScriptCore
But there are JavaScript runtime for the server too. The most popular one is NodeJS. But there are also Deno, Bun and much more
In this lab, we will use NodeJS as a JavaScript runtime.
JavaScript is simple because it's a loosely typed language. It's good when creating a simple application. But when the codebase is larger, it will be harder to develop. Worst of all, the bug might appear in the production environment instead of developing one.
So this is why we have TypeScript. TypeScript is JavaScript with syntax for types. (they defined itself on the homepage of their website, lol). But remember, TypeScript is a statically typed language which means it will check type at the time when transpile TypeScript to JavaScript only.
If you don't care about TypeScript or NodeJS things. You can skip to this section.
We will use npm to create a new NodeJS project. The npm is a built-in NodeJS package manager.
There are other package managers than npm, the popular ones are pnPm and yarn. If you want to use other package managers you can just type corepack enable in your terminal. After that, you can now use yarn and pnpm in your terminal.
First, open the terminal and make sure that you are in the directory that we made earlier. and then type this command to create the new NodeJS project.
npm init # if your use npm <== we will npm as a example
yarn init # if you use yarn
pnpm init # if you use pnpmAfter running the command, there will be a prompt to ask for the basic information for creating the new project. In this case, you can just type anything you want.
When after finishing fill in the information in the terminal. There will be a new file in our directory called package.json
.
└── backend-todo/
├── docker-compose.yml
└── package.json
After that, create the file name index.js in the project directory and then add this to the file.
console.log("hello world");and now your project structure should be like this
.
└── backend-todo/
├── docker-compose.yml
├── package.json
└── index.js
After that, in the terminal, run the index.js file by using this command
node index.js
Congratulation!!! we made the "Hello World" app in JavaScript
First, we rename the file from index.js to index.ts and then add this code to the index.ts file.
function sayHello(name: string) {
console.log(`hello "${name}" from method sayHello`);
}
sayHello('world');then run
node ./index.ts
It shows an error because the node cannot run TypeScript file directly. We must transpile TypeScript to JavaScript first. (FYI: Other JavaScript runtime, For example, Bun, Deno can run TypeScript directly)
So Let's install the dependency typescript by running this command.
npm install -D typescript # if you using npm
pnpm install -D typescript # if you using pnpm
yarn add -D typescript # if you using yarnAfter running the command the package.json will show that we have installed the 'typescript'.
.
└── backend-todo/
├── node_modules/ 👈 [NEW FOLDER]
├── docker-compose.yml
├── index.js
├── package.json
└── package-lock.json 👈 [NEW FILE]
What happened after installing the dependency
- the installed package will be stored in the folder
node_modulesand - the 'lock' file will generated, this file will store the exact version of dependencies and sub-dependencies.
- If you use
npm, the file will bepackage-lock.json - If you use
yarn, the file will beyarn.lock - If you use
pnpm, the file will bepnpm-lock.yaml
- If you use
After we install typescript, now we can use tsc command to transpile TypeScript to JavaScript code.
Next, we will initialize the TypeScript project by typing this command
npx tsc --init # if you using npm
pnpm tsc --init # if you using pnpm
yarn tsc --init # if you using yarnAfter running the command, the file tsconfig.json will be generated. This file will control the behavior of the transpiler. For more information, you can visit here.
But in this example, we will use this config
{
"$schema": "https://json.schemastore.org/tsconfig",
"compilerOptions": {
"target": "es2022",
"module": "node16",
"lib": ["ES2022"],
"moduleResolution": "Node16",
"rootDir": ".",
"outDir": "build",
"allowSyntheticDefaultImports": true,
"importHelpers": true,
"alwaysStrict": true,
"sourceMap": true,
"forceConsistentCasingInFileNames": true,
"noFallthroughCasesInSwitch": true,
"noImplicitReturns": true,
"noUnusedLocals": true,
"noUnusedParameters": true,
"noImplicitAny": false,
"noImplicitThis": false,
"strictNullChecks": false,
"skipLibCheck": true,
"baseUrl": "./",
"paths": {
"@/*": ["*"]
}
},
"include": ["**/*", "__tests__/**/*"],
"exclude": ["node_modules", "build"]
}
When you reach this section. You will find that the code editor is alerting the error.
Because the typescript does not know the NodeJS type. So, we must install the NodeJS type first.
npm install -D @types/node # if you using npm
pnpm install -D @types/node # if you using pnpm
yarn add -D @types/node # if you using yarnAfter that, we run this command to transpile the code
npx tsc # if you using npm
pnpm tsc # if you using pnpm
yarn tsc # if you using yarnAfter running the command, you will see that there is a build folder that appears in our directory. Earlier in the tsconfig.json we set "outdir": "build" so the transpiler will output the result to a build folder.
And if we see the content in the folder there is an index.js here.
Try to run the transpiled code by running.
node build/index.js
It's can run now!!!
But it's too long to type node build/index.js every time you want to run the transpile code. so let's make it convenient by opening the package.json file.
in the first place, at the section scripts, there will be a config like this.
"scripts": {
"test": "echo \"Error: no test specified\" && exit 1"
},let's change it to this.
"scripts": {
"build": "tsc",
"start": "node build/index.js"
},so next time if you want to transpile from TypeScript to JavaScript code. Instead of running this.
npx tsc # if you using npm
pnpm tsc # if you using pnpm
yarn tsc # if you using yarnYou can run this instead.
npm run build # if you using npm
pnpm build # if you using pnpm
yarn build # if you using yarnAnd next time if you want to run the transpile code. Instead of running this.
node build/index.jsYou can run this instead.
npm run start # if you using npm
pnpm start # if you using pnpm
yarn start # if you using yarnBut isn't that tedious that every time you change the code, you must run commands npm run build and npm run start? Let's make it more convenient.
Let's install the dependency a tsx. This package makes you run the TypeScript file directly without transpile first. And have the watch mode. That means it will automatically restart an app when you make a change in the code.
To install the dependency, run this command in your terminal.
npm install -D tsx # if you using npm
pnpm install -D tsx # if you using pnpm
yarn add -D tsx # if you using yarnAfter that try using a tsx by running this command.
npx run tsx watch . # if you using npm
pnpm tsx watch . # if you using pnpm
yarn tsx watch . # if you using yarn
let's make some changes to our code to ensure that watch mode is working by adding this code to the index.ts file.
console.log("hello world again")
It's working!!! but the command npm run tsx watch . things isn't that too long? Let's add the script in package.json to make it more convenient.
"scripts": {
"build": "tsc",
"start": "node build/index.js",
+ "dev": "tsx watch ."
},So instead of running this.
npx run tsx watch . # if you using npm
pnpm tsx watch . # if you using pnpm
yarn tsx watch . # if you using yarnYou can just run this.
npm run dev # if you using npm
pnpm dev # if you using pnpm
yarn dev # if you using yarnBefore we start writing our backend application. we must know HOW and WHAT standards the frontend and backend communicate.
TODO: writing about what is API and what is REST and the convention
If you did not set up the project from this section. You can use the finished setup project from the folder start-project.
For using the finished setup project. Just open that folder with any code editor.
After opening the folder. You must install the dependencies first by typing this in the terminal.
corepack enable # run this line if you never run this
pnpm installFrom now on I will only use 'pnpm' as a package manager. But don't worry if you use other package managers. Because the command is similar.
Install the dependency.
npm install <PACKAGE_NAME> # if your use npm
pnpm install <PACKAGE_NAME> # if you use pnpm
yarn add <PACKAGE_NAME> # if you use yarnRunning the script.
npm run <SCRIPT_NAME> # if you use npm
pnpm <SCRIPT_NAME> # if you use pnpm
yarn <SCRIPT_NAME> # if you use yarnInstall Express by using these commands.
pnpm install express
pnpm install -D @types/expressThen let's clear all the content in the index.ts and copy this code.
import express from 'express';
const app = express();
app.listen(3000, () => {
console.log('Server is running at http://localhost:3000');
});
Then open your browser and go to http://localhost:3000 and see what happened
As you can see the server responds to our browser with 'Cannot GET /' because currently, we do not handle the route '/' with a 'GET' method so let's implement to handle it.
To handle the route '/' with a 'GET' method add this line to the code
import express from 'express';
const app = express();
+ app.get('/', async (req, res) => {
+ return res.send('Hello world');
+ });
app.listen(3000, () => {
console.log('Server is running at http://localhost:3000');
});
To connect our backend to the database. We must install the database client first. In this example, We will use mysql2.
Install by using this command
pnpm install mysql2After we install, we will initialize the database connection by adding this code in index.ts.
import express from 'express';
+ import mysql from "mysql2/promise";
+ const connection = await mysql.createConnection({
+ host: "localhost",
+ user: "root",
+ password: "root",
+ database: "todo",
+ port: 3306,
+ })
const app = express();To ensure that our backend can connect to the database, let's modify the code by making our server.
- If the browser request 'GET' to the route '/', then retrieve all the table names of the database.
- And then respond to the above results to the client.
app.get('/', async (req, res) => {
+ const query = `SELECT table_name FROM information_schema.tables WHERE table_schema = 'todo';`
+ const results = await connection.query(query)
+
- return res.send('Hello world')
+ return res.send(results)
});save the file, and then refresh http://localhost:3000 again.
As you can see backend shows the tables' names. So we can ensure that our backend is connected to the database.
Tips: If you use the Chromium-based browser, you can install JSON Formatter to prettify the JSON response. (I think other browsers may have similar extensions that can do that too)
much better!!!
Before we continue, let's add some data to our database. In this example, I will use PHPMyAdmin.
click at left panel at
tododatabase
click at
SQLsection
And then insert the data by using this query.
INSERT INTO todos (title, completed) VALUES ("drink water", 0)
Then press go
To see the result go to the side panel and click on the todos table in the todo database.
So the above query is working, and our data are in the database.
We will make the /todo route that if the frontend accesses with a GET method, it will...
- query in the table 'todos' to get all the to-do list
- response results to the frontend
Modify the index.ts with this code.
const connection = await mysql.createConnection({
host: "localhost",
user: "root",
password: "root",
database: "todo",
port: 3306,
});
+ app.get('/todo', async (req, res) => {
+ const results = await connection.query('SELECT * FROM todos');
+
+ return res.send(results);
+ });Let's save and access http://localhost:3000/todo in the browser.
As you can see, if we access /todo the backend will respond with results that come from querying the database, But we just want the to-do only. (There are in the first element of the outer array)
So let's modify the index.ts
app.get('/todo', async (req, res) => {
const results = await connection.query('SELECT * FROM todos')
- return res.send(results);
+ return res.send(results[0]);
});
now backend only return the data that we actually want
But at this moment the data that is sent from the backend is just the plaintext. And what if we want to send anything else more than the data? let's send it as JSON by modifying the index.ts code.
app.get('/todo', async (req, res) => {
const results = await connection.query('SELECT * FROM todos')
- return res.send(results[0]);
+ return res.json({
+ data: results[0],
+ isSuccess: true,
+ message: 'Retrieve all the todo successfully'
+ });
});Let's save and refresh http://localhost:3000/todo in the browser again.
Now, our getting todo route is done and this is our final code index.ts file.
import express from 'express';
import mysql from "mysql2/promise";
const app = express();
const connection = await mysql.createConnection({
host: "localhost",
user: "root",
password: "root",
database: "todo",
port: 3306,
});
app.get('/todo', async (req, res) => {
const results = await connection.query('SELECT * FROM todos');
return res.json({
data: results[0],
isSuccess: true,
message: 'Retrieve all the todo successfully'
});
return res.send(results[0]);
});
app.listen(3000, () => {
console.log('Server is running at http://localhost:3000');
});From the above example, we request and receive a response from the backend by using a browser. We only use a GET method. But from now on, we will use other HTTP methods. But our browser (URL search box) can only use the GET HTTP method. so we must find a new tool to request to the backend.
You can use something like Insomnia or Postman. But in this example, I will use Insomnia.
Let's make the route that creates todo from the given data.
By accessing this route, the client will request route /todo with the POST method.
Let's create the route by modifying the code in file index.ts.
app.get('/todo', async (req, res) => {
const results = await connection.query('SELECT * FROM todos')
return res.json({
data: results[0],
isSuccess: true,
message: 'Retrieve all the todo successfully'
})
});
+ app.post('/todo', async (req, res) => {
+ return res.json({
+ isSuccess: true,
+ message: 'POST /todo successfully',
+ data: null,
+ })
+ });We will test this route by using Insomnia. Let's open it.
Press 'New HTTP Request'
Let's type http://localhost:3000/todo in the URL box, then change from GET to POST and then press Send.
The response from the backend will show in the right panel
To make the backend have the ability to receive and parse the body data we must add the body parser middleware first.
+ app.use(express.json())
+ app.use(express.urlencoded({ extended: true }))Now the express server can read the body that is sent from the frontend.
We will make the backend response the body that the frontend sent. Now modify the index.ts with this code.
app.post('/todo', async (req, res) => {
- return res.json({
- isSuccess: true,
- message: 'POST /todo successfully',
- data: null,
- })
+ console.log(req.body);
+ return res.json(req.body);
});Let's request to the backend again. But now we will send the title in the body along with the request.
Click the Form button and then select Form URL Encoded.
add the name and key input box anything you want. And then press Send
As you can see in Insomnia, the backend responds to the data that we sent. Let's use that data to store in a database.
app.post('/todo', async (req, res) => {
const title = req.body.title
const sql = 'INSERT INTO todos (title, completed) VALUES (?, ?)'
const valuesToInsert = [title, false]
const result = await connection.query(sql, valuesToInsert)
return res.json({
isSuccess: true,
message: 'the todo has been saved into the database'
})
});Let's request again with Insomnia.
The data sent from the client is successfully saved into the database. However, the HTTP response status code is still 200 (the default response code of Express is 200). For the convention, the backend should respond with code 201 if the resource is created.
To fix this, let's modify the code again.
app.post('/todo', async (req, res) => {
const title = req.body.title
const sql = 'INSERT INTO todos (title, completed) VALUES (?, ?)'
const valuesToInsert = [title, false]
const result = await connection.query(sql, valuesToInsert)
- return res.json({
+ return res.status(201).json({
isSuccess: true,
message: 'the todo has been saved into the database'
})
});Let's send the data from Insomnia again.
Now the status code is 201
But what if we do not send anything from the frontend or Insomnia? Let's try.
The server crashes because the title is null.
Let's fix this by ensuring that the title is not null before saving it into the database. If the title is null, then respond to the client that the sent data is invalid.
app.post('/todo', async (req, res) => {
const title = req.body.title
+ if (!title) {
+ return res.status(400).json({ isSuccess: false, message: 'require the body `title`' })
+ }Let's run the Insomnia again.
You can see that the server is now not crashing when we send nothing to the server.
What if this route not only just wants the title data, but wants very much more than that? Must we have more than 100 if statements? Fortunately, no. There are many data validations in JavaScript. For example, Zod, Yup, Joi, TypeBox and much more.
This is what the index.ts looks like after we finish all the above sections.
import express from 'express';
import mysql from "mysql2/promise";
const connection = await mysql.createConnection({
host: "localhost",
user: "root",
password: "root",
database: "todo",
port: 3306,
})
const app = express();
app.use(express.json())
app.use(express.urlencoded({ extended: true }))
app.get('/todo', async (req, res) => {
const results = await connection.query('SELECT * FROM todos');
return res.json({
data: results[0],
isSuccess: true,
message: 'Retrieve all the todo successfully'
});
});
app.post('/todo', async (req, res) => {
const title = req.body.title
if (!title) {
return res.status(400).json({ isSuccess: false, message: 'require the body `title`' })
}
const sql = 'INSERT INTO todos (title, completed) VALUES (?, ?)'
const valuesToInsert = [title, false]
await connection.query(sql, valuesToInsert)
return res.status(201).json({
isSuccess: true,
message: 'the todo has been saved into the database'
})
});
app.listen(3000, () => {
console.log('Server is running at http://localhost:3000');
});We will create the route that
-
the client requests to route something like
/todo/1or/todo/35by any number is the id of the todo with the HTTP DELETE method. -
when this route is getting called. this route will validate if the id that frontend exists on the database (for preventing the SQL error)
-
response success if some row has been deleted. response fails if there are some errors, or nothing is deleted.
here is the delete route
// we import more with 'RowDataPacket'
import mysql, { RowDataPacket } from "mysql2/promise";
// we want this route access by HTTP DELETE method. So, we use .delete
app.delete('/todo/:id', async (req, res) => {
const id = req.params.id
const selectQuery = 'SELECT * FROM todos WHERE id = ?'
const values = [id]
const results = await connection.query<RowDataPacket[]>(selectQuery, values)
if (results[0].length === 0) {
return res.status(404).json({ message: `todo id: ${id} not found`, isSuccess: false })
}
const deleteQuery = 'DELETE FROM todos WHERE id = ?'
await connection.query(deleteQuery, values)
return res.status(200).json({ message: "delete todo successfully", isSuccess: true })
})If we want to access the values from that dynamic url things, we must define that dynamic value with :. And we can access it with req.params
For more information, please visit the document in the section Route parameters
Then request this route with Insomnia by deleting todo id 2.
And inspect the the tables with PHPMyAdmin again
It's work!!!
What if we delete it again?
Looking good. It's not found because it does not exist.
Let's try writing the update route by yourself.
app.patch('/todo/:id', async (req, res) => {
})At this moment when we request to the backend using Postman or Insomnia or other HTTP client, everything will be running smoothly. But if we send the request to the backend using a browser, we will find the CORS problem.
To solve this problem, we will use CORS middleware. To use this, we must install the dependency first.
pnpm install cors
pnpm install -D @types/cors
Then register the CORS middleware by modifying the code.
import express from 'express';
import mysql from "mysql2/promise";
import bodyParser from 'body-parser';
+ import cors from 'cors';
const app = express();
+ app.use(cors({
+ origin: "*",
+ }))From this code, we allow all hostnames to be requested to our backend. In the real world, you should specify which hostname you will allow.
For more information, please visit the documentation.
Thank you for reading!!! But don't forget that this is just an example of the backend application. In real-world applications, there is much more to concern.
You must be concerned about error handling, logging, etc. This example does not provide information about how to write clean code or look good project structure. You should explore more about this.