We'll also deploy the application on Azure App Service and make it production-ready by setting up features like autoscaling and multiple environments.

You'll learn how Neon Postgres can make your development and deployment processes easier along the way.

Here's what we'll cover:

• Setting up a Neon Postgres database and exploring its features
• Building a CRUD application using Spring Boot and deploying the application on Azure App Service
• Why Neon is a good fit for infrastructure that auto-scales
• Database branching in Neon Postgres and how it can ease the development workflow

Prerequisites

• Working knowledge of Java, Maven, and Spring Boot
• Basics of SQL databases
• Understanding of serverless and cloud services
• Familiarity with testing and deployment processes

Table of Contents

• What is Neon Postgres?
• How to Set Up the Database
  • Create the Database
• How to Build the Spring Boot CRUD App
  • Create an Entity Class
  • Create a Repository
  • Create a REST Controller
  • Configure the Database
• How to Deploy on Azure App Service
  • Create a New Web App
  • Deploy the Application
  • Access the Application
• How to Set Up Autoscaling
  • Autoscaling in Azure
  • Autoscaling in Neon
• How to Configure Database Branches in Neon
• Summary

What is Neon Postgres?

Neon is a fully managed serverless Postgres database platform. It offers features such as high availability, automatic backups, and scaling options to handle varying traffic levels.

Neon is designed to be cost-efficient and developer-friendly, and it focuses on providing a seamless experience for developers.

In addition to the standard Postgres features, it provides capabilities like database branching, allowing you to create Git-like branches of the database for different purposes.

How to Set Up the Database

To begin with, let's explore how you can set up a Neon database for your application.

Firstly, you'll need to create an account on the Neon website. It doesn't require a credit card to sign up, and you're automatically set up with the free tier to get started.

Here's a pricing and features comparison of Neon plans:

Neon pricing plans

In the free tier, we get 0.5 GB of storage with basic computing which is enough for playing around with the database and building small applications.

Create the Database

Once you've signed up, you can access the dashboard and create a new project.

Star by filling in the project name, region, and Postgres version options. In addition to this, we can choose two additional options:

• compute size – You can choose a min and max compute size for the database. This is useful for autoscaling the database based on the load.
• suspend time – You can set a time after which the database will be suspended if not being used. This is useful for saving costs when the database is not being used.

Creating a database project in Neon

Once you submit the form, Neon will create the database and provide the connection details.

Neon Dashboard

As you can see, the database was set up in 3.3 seconds (compared to hours of installing and setting up your own infrastructure). You can choose multiple ways to connect to the database. For this tutorial, select Java as your programming language and get the JDBC connection string.

How to Build the Spring Boot CRUD App

Next, let's set up our CRUD application. We'll use Spring Boot, as it provides easy bootstrapping and configuration for building web applications.

We can use the Spring Initializr to generate a new Spring Boot project with the necessary dependencies:

• Spring Web – for building web applications
• Spring Data JPA – for working with databases using JPA
• PostGres Driver – for connecting to the Postgres database

Creating a Spring Boot project using Spring Initializer

You can generate, download, and import the project into your favorite IDE.

Create an Entity Class

Let's create an entity class to represent the data in the application. First, create a User class:

@Entity(name = "users")
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String name;
    private String email;

    // Constructors, Getters and Setters
}

The entity name users is the name of the table you want to use in your database.

Create a Repository

Next, create a repository interface to interact with the database. You'll extend the JpaRepository interface provided by Spring Data JPA:

@Repository
public interface UserRepository extends JpaRepository<User, Long> {
}

You need to annotate the interface with @Repository to mark it as a Spring bean. The JpaRepository interface provides methods for CRUD operations like save, findAll, findById, delete, and so on, so you don't need to write the queries manually.

You'll provide your entity class User and the type of the primary key Long as type arguments to the JpaRepository interface.

Create a REST Controller

Finally, create a REST controller to handle the CRUD operations. You'll inject the UserRepository into the controller and implement the necessary endpoints:

@RestController
@RequestMapping("/users")
public class UserController {
    private final UserRepository userRepository;

    public UserController(UserRepository userRepository) {
        this.userRepository = userRepository;
    }

    @GetMapping
    public List<User> getUsers() {
        return userRepository.findAll();
    }

    @PostMapping
    public User createUser(@RequestBody User user) {
        return userRepository.save(user);
    }

    @PutMapping("/{id}")
    public User updateUser(@PathVariable Long id, @RequestBody User user) {
        user.setId(id);
        return userRepository.save(user);
    }

    @DeleteMapping("/{id}")
    public void deleteUser(@PathVariable Long id) {
        userRepository.deleteById(id);
    }
}

Here are a few things to note:

• You're using the @RestController annotation to mark the class as a controller that handles REST requests.
• The @RequestMapping annotation specifies the base URL for the endpoints.
• You're injecting the UserRepository into the controller using constructor injection.
• Finally, you're implementing your API endpoints for CRUD operations using the @GetMapping, @PostMapping, @PutMapping, and @DeleteMapping annotations.

Configure the Database

To connect your Spring Boot application to the Neon Postgres database, you need to configure the database URL, username, and password in the application.properties file:

spring.datasource.url=jdbc:postgresql://<db-url>/<db-name>?sslmode=require
spring.datasource.username=<username>
spring.datasource.password=<password>
spring.jpa.hibernate.ddl-auto=update

Here, you configured the database URL, username, and password provided by Neon when you created the database. The spring.jpa.hibernate.ddl-auto=update property tells Spring Boot to automatically create the necessary tables or columns based on the entity classes when the application starts.

How to Deploy on Azure App Service

Now that your Spring Boot application is ready, it's time to deploy it on Azure App Service.

Create a New Web App

To deploy your Spring Boot application on Azure App Service, you'll first create a new Web App. You can do this through the Azure portal by following these steps:

• Log in to the Azure portal.
• Click on the Create a resource button.
• Search for Web App and select the Create option.
• Fill in the necessary details like resource group, app name, runtime stack, and region.
• Click the Review + create button.

Creating a Web App in Azure

Deploy the Application

The Web App takes a couple of minutes to create. Once done, you can deploy your Spring Boot application to Azure App Service.

One of the easiest ways to deploy is to package your Spring Boot application as a JAR file and deploy it to Azure App Service using the Azure CLI.

To do this, run the below commands:

mvn package
az webapp deploy --src-path neon-demo-0.0.1-SNAPSHOT.jar --resource-group learn-ba1a439c-71ca-4cab-9bb1-f5b1331bab04 --name neon-app

Here, you're packaging your Spring Boot application using Maven and deploying the JAR file to Azure App Service using the Azure CLI. You've provided the path to the JAR file, the resource group, and the app name you previously configured.

Access the Application

Once the deployment is complete, you can access your Spring Boot application on Azure App Service by navigating to the URL of the Web App. Your app is available at neon-app.azurewebsites.net

Let's use _curl _to test the endpoints.

Create a User

curl -X POST -d '{"name":"John Doe","email":"john@gmail.com"}' https://neon-app.azurewebsites.net/users

Here you provide user data in JSON format to create a new user.

Get Users

You can also can test that the user was created by fetching all users:

curl -X GET https://neon-app.azurewebsites.net/users

How to Set Up Autoscaling

A production application may experience varying levels of traffic, and it's important to scale the application dynamically based on the load.

Let's explore how you can autoscale your application when needed.

Autoscaling in Azure

Azure App Service provides autoscaling options that let you automatically adjust the number of instances as needed.

You can configure autoscaling rules in the Azure portal by following these steps:

• Navigate to the Web App in the Azure portal.
• Click the Scale out (App Service Plan) option from the left menu.
• Configure the autoscaling rules – you can choose predefined rules like traffic or create custom rules based on metrics like CPU usage, memory usage, or custom metrics.
• Save.

Azure will automatically scale the application based on the configured rules.

Autoscaling in Neon

Since your application is automatically scaled based on the load, you'll want to ensure that the database can handle the increased traffic.

Neon provides autoscaling options to scale the database dynamically based on the load. You can configure autoscaling rules in the Neon dashboard to ensure the database can handle the increased load.

Follow the below steps to configure autoscaling in Neon:

1. Navigate to the Neon dashboard and select the database. Then select the branch to configure autoscaling.

Selecting a branch from Neon project dashboard

2. Click on the Edit button next to the Compute section. Configure the autoscaling rules based on metrics like CPU usage, memory usage, or custom metrics.

Branch details view in Neon

3. Configure the min-max compute size and Save. Neon will automatically scale the database based on the configured rules when needed.

Setting up autoscaling for compute

Ensuring that both the application and the database can scale dynamically based on the load will help you handle varying levels of traffic efficiently.

How to Configure Database Branches in Neon

In a typical development workflow, multiple databases may be used for different purposes like development, testing, and production.

Neon Postgres provides database branching to create multiple branches for different purposes. Each branch is an instance of the database that you can use independently.

This Git-like feature helps set up a copy of the database for different environments like development, staging, and production. It also helps preserve data for different versions of the application.

Let's explore how you can create and manage branches in Neon Postgres:

• Navigate to the Neon dashboard and select the database.
• In the Branches section, click on the View All button.
• You can create a new branch from an existing one by clicking on the Create Branch button. You'll need to provide the branch name and what data to copy from the parent branch.

Create branch option

• You can either copy all the data or copy until a point in time or a specific record. This is useful for multiple purposes like restoring data, creating a new environment, or testing new features.

Creating a new branch

• Neon will create a new branch of the database that can be used independently. You can find the URL, username, and password for the new branch in the dashboard. And this happens in real time without any downtime and delays.

Branch-specific connection details

Now you can use your dev branch for local development and testing, and the main branch for production. This helps in keeping the data separate and ensures that changes in one branch do not affect the other branches.

Summary

In this article, we built a CRUD application using Spring Boot, Neon Postgres, and Azure App Service.

We explored how to set up the Neon Postgres database, build a basic CRUD application using Spring Boot, deploy the application on Azure App Service, and configure autoscaling for the application and the database.

We also learned about how the database branching feature in Neon Postgres helps you create branches of the database for different environments and purposes.

Go is a fast programming language with a relatively simple syntax. While learning Go, it is important to learn how to build APIs and how to use them to communicate with databases. In the process of learning, I decided to take on a project that helped me in that regard: a simple inventory tracking API.

While working with an SQL database like Postgres, I learnt that it is important to make changes to the database in a timely manner. So if you have a schema that you may modify in the future, the best way to do that is with database migrations. It ensures that changes to the database are made accurately without affecting existing data.

In this article, you will learn about database migrations using Docker and Postgres.

Table of Contents

• What is Database Migration?
• How to Start and Run a Docker Container
• How to Create and Run a Schema Using TablePlus
• How to Install golang-migrate
• How to Create a New Migration
• How to Create and Drop the Database Inside and Outside a Docker Postgres Container
• How to View the Database in TablePlus
• How to Run the Migrations
• Conclusion

What is Database Migration?

What is database migration and why should you use it? Well, as a backend developer, when working on a project that requires you to store data in a database, you will need to develop a schema for the data you want to store.

Database migrations help you manage the structure of data within a database and in this case, a relational database. Migrations help modify schemas from a current state to a specific/desired state. It may involve the addition of tables and columns, removing elements or changing types and constraints.

One importance of database migration is to make changes in a database repeatable and seamless without the concern of data loss.

It is advisable to use migrations if you are not sure of what your final data schema would look like. In this sense, you can incrementally implement changes to it.

How to Start and Run a Docker Container

Open your terminal and create a new directory mkdir tracking-inventory-app.

Then pull a postgres image from Docker Hub. I used the postgres:14-alpine tag. You can use any tag you want.

In your terminal, paste the following and press enter:

$ docker pull postgres:14-alpine

After installing it, start the container by using the docker run command:

$ docker run --name postgres14 -e POSTGRES_USER=root -e POSTGRES_PASSWORD=passwordd -p 5432:5432 -d postgres:14-alpine

The --name flag refers to the name of the container. The -e flag refers to the environment variables. The -p flag means publish. You should run your container on a specified port. The -d flag means you want to run it in detached mode.

After you have pressed enter, open your Docker Desktop if you have it installed. If you don't, you can download it from the docker website.

In your Docker Desktop, you should see that the container has been started:

You can establish a connection with the the database using TablePlus:

Test the connection. If it says ok, then connect. If you are on Windows and it shows an authentication error, navigate to your start button and click on Run. In the popup, type services.msc and press enter. Look for postgres and click on stop service. Then try connecting again.

How to Create and Run a Schema Using TablePlus

I have created a predefined schema/model for the tracking-inventory project with db diagram. This tracking-inventory should allow you add an item, serial number and value. So the schema will have an item, serial_number, id and created_at fields.

CREATE TABLE "inventory" (
  "id" uuid PRIMARY KEY,
  "item" varchar NOT NULL,
  "serial_number" varchar NOT NULL,
  "user" uuid NOT NULL,
  "created_at" timestamptz NOT NULL DEFAULT 'now()'
);

CREATE TABLE "user" (
  "id" uuid PRIMARY KEY,
  "name" varchar NOT NULL,
  "email" varchar UNIQUE NOT NULL,
  "password" varchar NOT NULL,
  "created_at" timestamptz NOT NULL DEFAULT 'now()'
);

CREATE INDEX ON "inventory" ("item");

ALTER TABLE "inventory" ADD FOREIGN KEY ("user") REFERENCES "user" ("id");

This is how mine looks. You can open your TablePlus and add the generated PostgreSQL code and run it.

How to Install golang-migrate

The next step is to install golang-migrate on your system. I am using Linux on Windows for this tutorial.

To install it, visit this documentation.

I am using Linux so I will use curl:

$ curl -L https://github.com/golang-migrate/migrate/releases/download/v4.12.2/migrate.linux-amd64.tar.gz | tar xvz

Once it has been successfully installed, on your terminal, run the command migrate -help to see its various commands.

How to Create a New Migration

After installing golang-migrate, you can create a new migration script.

Firstly, in your terminal and within the tracking-app directory, open VS code with the code command.

Once that is done, create a new folder named db and another folder inside the db folder named migrations.

Then in your terminal, run the following command:

 $ migrate create -ext sql -dir db/migration -seq tracking_inventory_schema

The -ext flag refers to the extension you want the migration to be created with. In this case, it is sql. The -dir flag refers to the directory you want to create the files in. The -seq flag refers to the sequential number for the migration files.

Inside your VS code, there should be two files: one for up and another for down. The former is used to make forward changes to the directory while the latter is for reversing the changes.

In the up file, you are going to paste your schema to the file.

My schema looks like this:

CREATE TABLE "inventory" (
  "id" uuid PRIMARY KEY,
  "item" varchar NOT NULL,
  "serial_number" varchar NOT NULL,
  "user" uuid NOT NULL,
  "created_at" timestamptz NOT NULL DEFAULT 'now()'
);

CREATE TABLE "user" (
  "id" uuid PRIMARY KEY,
  "name" varchar NOT NULL,
  "email" varchar UNIQUE NOT NULL,
  "password" varchar NOT NULL,
  "created_at" timestamptz NOT NULL DEFAULT 'now()'
);

CREATE INDEX ON "inventory" ("item");

ALTER TABLE "inventory" ADD FOREIGN KEY ("user") REFERENCES "user" ("id");

Yours may look different depending on what project you are building.

For the down file, just paste this in:

DROP TABLE IF EXISTS inventory;
DROP TABLE IF EXISTS user;

The inventory table should be dropped first because it references the user table.

How to Create and Drop the Database Inside and Outside a Docker Postgres Container

Check if your docker container is running using the command:

$ docker ps

If it is not, use the command docker start ${container name} to start it.

Next step is to access postgres shell using the following command since I'm on Linux:

$ docker exec -it postgres14 bin/bash

The -it flag stands for interactive shell/terminal. Inside this shell, you can run the createdb command:

/# createdb --username=root --owner=root tracking_inventory

Once created, you can run the psql command to interact with the db:

/# psql tracking-inventory
psql (14.12)
Type "help" for help.

tracking_inventory=#

You can also delete the database with the dropdb command.

To leave the shell, use the exit command.

To create the database outside the postgres container, paste the following command:

$ docker exec -it postgres14 createdb --username=root --owner=root tracking_inventory

How to View the Database in TablePlus

To view the database that you have created, connect using the previous connection we established earlier. It'll take you to the root database and then click on the db icon on top.

The database created will appear, then just click on open to open it

How to Run the Migrations

To run the migrations, run this command in your terminal:

$ migrate -path db/migration -database "postgresql://root:passwordd@localhost:5432/tracking_inventory?sslmode=disable" -verbose up

The -path flag specifies the path that contains the migration files. The -database option specifies the url to the database.

Inside the url, the driver is postgresql. The username and pasword is root and passwordd respectively. It is also important to add the sslmode=disable option because Postgres does not enable SSL by default.

Now run the migrations:

$ migrate -path db/migration -database "postgresql://root:passwordd@localhost:5432/tracking_inventory?sslmode=disable" -verbose up
calhost:5432/tracking_inventory?sslmode=disable" -verbose up
2024/06/25 00:13:25 Start buffering 1/u tracking_inventory_schema
2024/06/25 00:13:25 Read and execute 1/u tracking_inventory_schema
2024/06/25 00:13:26 Finished 1/u tracking_inventory_schema (read 43.186044ms, ran 255.501635ms)
2024/06/25 00:13:26 Finished after 312.928488ms
2024/06/25 00:13:26 Closing source and database

The migration is successful!

Refresh the database and see the new tables:

##Conclusion

Throughout this tutorial, you have learnt how to seamlessly write and run database migrations in Go using Docker and Postgres. I hope you have learnt much from this article.

You can connect with me on twitter or on linkedin.

In this fast-paced development landscape, deploying via containers, particularly with Kubernetes, is becoming increasingly popular. Some companies perform numerous deployments daily, so it's crucial for you to learn these technologies.

Kubernetes is a popular choice to deploy containerized applications like web servers, databases, and APIs. You can set up Kubernetes either locally or in the cloud. In this tutorial, we'll explore setting up Kubernetes on a cloud platform, specifically Azure.

I'll walk you through the process of setting up Kubernetes using Azure Kubernetes Service (AKS). You'll configure your YAML file using StatefulSet, Persistent Volume, and Services to deploy a PostgreSQL database on Azure Kubernetes. Then, you'll obtain the PostgreSQL database credentials running inside the AKS and use them to establish a connection with a Node.js application.

We'll cover key concepts such as deployment, stateful sets, persistent volumes, and services, preparing you to deploy a Postgres container effectively on AKS. I'll also help you connect your Node.js Express app to the Postgres container within the AKS cluster.

So find a comfortable seat and get ready, as we're about to dive in.

Prerequisites

Before you begin, it's important to understand some basic concepts in Kubernetes like pods, deployments, services, and nodes.

If you're new to this, I recommend checking out the Stashchuk freeCodeCamp video for a beginner-friendly tutorial.

You'll also need an active Azure account and subscription to follow along.

Table of Contents

• Challenges We're Trying to Solve
  – Deployments
  – StatefulSets
  – Persistent Volumes

• Azure Kubernetes Service (AKS)
  – Sign in to Your Azure Portal
  – Create a Resource
  – Create a new container
  – Create a new Azure Kubernetes Service(AKS)
  – Create a new resource group
  – Give your Kubernetes cluster a name
  – Navigate to the node pool page
  – Enable container logs and set up alerts
  – Advanced Section
  – Tags

• Connect to Your AKS Cluster
  – Download Azure CLI and kubectl
  – Verify if Azure CLI is installed
  – Verify if kubectl is installed
  – Login to Azure account
  – Configure kubectl

• How to Create Resources with YAML
  – Clone the Repository
  – Open the Repository
  – Install Dependencies

• YAML Configuration
  – StorageClass
  – PersistentVolumeClaim
  – ConfigMap
  – StatefulSet
  – Service

• How to Deploy YAML Resource to Azure
  – Deploy the YAML resource

• Node.js Application
  – Configure Nodejs
  – Run Nodejs Application
  – Test the Application
  – Open Postman
  – Confirm the Data
  – Delete Pod
  – Data Persistence

• Conclusion

Challenges We're Trying to Solve

Firstly, what is Kubernetes? Well, it's like a manager for your software containers. It helps you run and manage lots of containers like web servers, databases, microservices, and APIs which are like little packages holding your applications.

Kubernetes takes care of things like starting, stopping, and scaling these containers, so your apps run smoothly even when there is more load on your application. It's popular because it makes running software in the cloud easier and more reliable.

Now, let's talk about how to tackle some challenges you might face with a real-world application running Postgres in a Kubernetes production cluster.

Imagine that the infrastructure hosting your Postgres crashes, causing you to lose all the services and data stored in the database. Or, picture a scenario where the Postgres database becomes corrupted, leading to data loss.

In both cases, you need a way to back up your application so you can restore it to a working state if disaster strikes.

So, how do you capture a comprehensive application backup that includes all the necessary data? This backup should allow you to restore the entire application, including the database, if you lose your cluster or encounter data loss.

In Kubernetes, think of a Pod as the tiniest unit that you can deploy. It's like a small box that holds one thing, like a web server or a database. So, if your Pod isn't running, your web server or database isn't either.

This means that if the cluster where your Pod runs gets destroyed, all the data in the Pod disappears too. All the nodes (virtual machines that run your application over the network) will also be wiped out.

How can you make a pod stay on one specific node where the data is and never move? And how can you make sure that each pod can be found separately when you're using a load balancer?

One solution is to consider how you deploy your application on Kubernetes. Typically, you create a deployment and expose it using a service, specifying the service type as either Cluster type, NodePort, or LoadBalancer.

But not all applications are the same when it comes to state. Some applications, known as stateless applications, don't rely on storing data locally, so losing their state isn't a big issue.

But for applications like databases or caches, maintaining state is crucial because they rely on storage. In Kubernetes, deploying stateful applications like databases using just deployment isn't ideal. You need a solution that ensures your application's data is safely stored and can be recovered in case of failure.

Deployments

You might be wondering why we can't just use a Kubernetes deployment to deploy Postgres in the Kubernetes cluster? Well, the thing is, many people aren't aware of the difference between a deployment and a stateful set.

Let's imagine you have a pod running in your cluster that you created using a deployment. Then you scaled up to two pods, so you now have Pod A and Pod B.

The problem arises because, by default, pods created as part of the same deployment share the same persistent volume (PV) across the cluster. So, when you scaled up, both instances of Postgres would write to the same storage, which could lead to data corruption.

Another issue arises from a networking perspective. Pods A and B don't have a dependable way to communicate with each other over the network. By default, Kubernetes pods don't have their own DNS names. Instead, you rely on services to expose ports to other applications in the cluster.

If you take a closer look at pod names, you'll notice that pods are assigned a random hash at the end of their names. Because of this, pods lack a consistent network identity. Every time a pod is destroyed and recreated, it receives a new randomized name. This inconsistency isn't ideal for reliable networking.

Postgres isn't naturally made for Kubernetes, and Kubernetes can be tough when handling stateful tasks. To set up a Postgres instance, you've got to know the right Kubernetes setup. You can't just throw it in a pod, because if the pod goes down, so does your data. But, for a quick integration, a pod could work fine.

Deployments aren't ideal either, since you don't want your pod randomly placed on a node. But for testing, deployments are handy if you just need a Postgres instance to run temporarily.

What you really want is a pod that sticks to a particular node where your data resides, and stays put. Plus, you also want your pod to be individually addressable. for this we need what we called a statefulSet.

StatefulSets

When you update your deployment to become a StatefulSet, Kubernetes introduces some improvements for deploying stateful workloads. One major change is how it handles scaling.

If you specify that you want three replicas of your StatefulSet, Kubernetes won't create all three pods at once. Instead, it creates them one by one. Each pod gets its own unique DNS name, starting with the pod's name followed by an ordinal number starting from zero. So, when you scale up, the ordinal number increases for each new pod.

Here's the cool part: if a pod like Pod-0 is destroyed and needs to be remade, it will return with the same name. This means each pod has a specific address, even if it's replaced.

And here's another cool feature: each pod in a StatefulSet gets its own persistent volume (PV). This lets you keep the same storage even if you scale up or down. This brings us to another concept called persistent volumes.

Persistent Volumes

Let's forget about pods, deployment, and containers for a moment. What exactly is "state"? In simple terms, state is the data that your applications need to work properly.

Now, when we talk about processes, there are two types: stateless and stateful. Stateless processes don't rely on any data to work. They just do their thing without needing any specific information. On the other hand, stateful processes need data or state to function properly.

Now, where do you store this state? There are two main places: memory and disk. Memory allows for quick access to data, which is great for applications like Redis, MongoDB, Postgres, or MySQL. They store their state on memory for quick access. But for persistent, they store it on disk on the file system (for more permanent storage).

Why the file system? Because it's the only way to keep the state persistent even when the system reboots. So, when a process dies and gets recreated, it can read its state from the file system.

I like breaking things down because I used to teach tech stuff. Now, let's get into setting up Kubernetes in Azure.

Azure Kubernetes Service (AKS)

In this section, I'll guide you through setting up a Kubernetes cluster on Azure.

Step 1: Sign in to your Azure portal

To begin, you will have to sign into your Azure portal. Once logged in, you should see a dashboard similar to this:

Azure portal homepage

Step 2: Create a resource

Click on "create a resource" to create a resource.

Resources are the various services, components, and assets that you can create and manage within the Azure cloud platform. These resources can include virtual machines, databases, storage accounts, networking components, web applications, and more.

Creating a resource in Azure portal

Step 3: Create a new container

Next, navigate to the "Containers" category from the options available on the left pane. Click on Containers as shown by the arrow in the screenshot.

Again, Kubernetes is a container orchestration platform. It manages and orchestrates the deployment, scaling, and operation of application containers across clusters of machines. Kubernetes provides a framework for automating the deployment, scaling, and management of containerized applications.

Creating new container (Kubernetes) in Azure

Step 4: Create a new Azure Kubernetes Service (AKS)

Select "Azure Kubernetes Service (AKS)" from the list of available container services and click Create. This will take you to the AKS creation page.

Creating a new Azure Kubernetes service

Step 5: Create a new resource group

In the "Resource group" section, click on "Create new" to create a new resource group for your Azure Kubernetes Service (AKS) deployment.

In Azure, a "resource group" is a logical container used to group together related Azure resources. It serves as a way to organize and manage these resources collectively, rather than individually.

When you create resources such as virtual machines, databases, storage accounts, or any other Azure service, you typically associate them with a resource group.

Creating a new resource group in azure portal

Let's name the resource group "AZURE-POSTGRES-RG" as shown below. You can name it anything you like. Then click ok.

Inputting name for the resource group

Step 6: Give your Kubernetes cluster a name

Now let's name the session for configuring the Kubernetes cluster "Kubernetes Cluster Name".

In Azure, a Kubernetes cluster is a managed container orchestration service provided by Azure Kubernetes Service (AKS). It allows you to deploy, manage, and scale containerized applications using Kubernetes without having to manage the underlying infrastructure.

Give it a name like "AZURE-POSTGRES-KC" and and select a region that's close to you. In my case I select (Asia Pacific) East Asia and click next.

Naming the Kubernetes cluster name

Step 7: Navigate to the node pool page

Now it's time to configure the node pool session by clicking on the agentpool.

In Azure, a node pool is a group of virtual machines (VMs) that are provisioned and managed together within an Azure Kubernetes Service (AKS) cluster. Each node pool runs a specific version of Kubernetes and has its own set of configurations, such as VM size, OS image, and node count.

Editing agentpool

Set the minimum node count to 1, maximum node count to 2, and the maximum pods per node to 30 to minimise cost. Then click update.

These parameters help control the size and behavior of the node pool in an Azure Kubernetes Service (AKS) cluster:

1. Minimum Node Count: Ensures a minimum number of nodes are always available for consistent performance and availability, even during low-demand periods.

2. Maximum Node Count: Sets an upper limit on the number of nodes in the node pool to manage costs and prevent over-provisioning.

3. Maximum Pods per Node: Defines the maximum number of pods that can run on each node, optimizing resource utilization and preventing overcrowding.

Updating agentpool details

Once you've clicked "Update," you'll be directed to the "Networking" section as shown below. Keep the page as is and proceed by clicking "Next." This will take you to Integration session.

Navigating to Next Page

Azure Container Registry (ACR) is a fully managed private Docker registry service provided by Microsoft Azure. It enables developers to store, manage, and deploy Docker container images securely within their Azure environment.

You will need a place to store the Docker image that's pulled.

To begin, select "Create New" to set up a new container registry. This action will bring up a page where you can input the necessary details, as illustrated on the right side of the image below. Enter the details as indicated by the arrows and then click "Okay." Once you're done, proceed by clicking "Next."

Naming and editing Azure Container Registry details

Step 8: Enable container logs and set up alerts

The Enable Container Logs option allows you to turn on logging for your containers. Logging records important information about what's happening inside your containers, like errors, warnings, and other events. It's useful for troubleshooting and monitoring your applications.

Choosing container logs

Step 9: Advanced section

Keep the Monitoring section unchanged and proceed by clicking "Next."

Navigating to Next Page

Step 10: Tags

Keep the Tags section unchanged and proceed by clicking "Next."

Navigating to Next Page

Step 11: Click "Review + create" to finalize the deployment

Once completed, your resource group, Azure Kubernetes Service (AKS), Azure Container Registry, and Kubernetes cluster will be created.

Completing Azure Kubernetes Setup

The screenshot below shows that the deployment was successful.

Successful Deployment

You've just successfully created an Azure Kubernetes Service from the Azure portal. Congrats!

How to Connect to Your AKS Cluster Using the Command Line

After successfully creating a new AKS in the Azure portal, the next step is to establish a connection to that cluster.

In this section, I'll guide you through Azure login, configuring kubectl to use the current context, and creating the YAML file for our Postgres container. This file will include StatefulSet, persistent volume, persistent volume claim, config map, and using Azure File for data storage.

I'll also show you how to run a Node.js Express application locally, use Postman to test the endpoints, and receive a response confirming that data was sent to the database successfully.

Download Azure CLI and kubectl

To start, you'll need to download the Azure CLI and kubectl.

• Azure CLI (Command-Line Interface): a command-line tool provided by Microsoft for managing Azure resources. It allows users to interact with Azure services and resources directly from the command line, making it easy to automate tasks, create scripts, and manage Azure resources programmatically.

• kubectl: a command-line tool for managing Kubernetes clusters, used to deploy, scale, and manage containerized applications. It allows users to perform operations like deploying applications, managing pods, services, and deployments, inspecting cluster resources, scaling applications, and debugging issues, simplifying management of containerized workloads in a Kubernetes environment.

I'm using the warp terminal. Warp is the terminal reimagined with AI and collaborative tools for better productivity. You can run the command using PowerShell on Windows or Terminal on Mac. I'm using a MacBook.

Verify if the Azure CLI is installed by typing the command az --version

Once the download finishes, verify whether Azure CLI is installed on your computer by running the command az --version. If the installation is successful, you should see an output similar to this:

Verifying Azure CLI Installation

Verify if kubectl is installed

To check if kubectl is installed, just type kubectl version in the command line.

Verifying Kubectl Installation

Login to your Azure account

Enter az login in the command line. This will open your browser and prompt you to sign in to your Azure account.

Logging into Azure

After signing in, it shows details about your Azure subscription, including the subscription name, ID, and user information.

Select an Azure subscription

Azure subscriptions are logical containers used to provision resources in Azure. You'll need to locate the subscription ID that you plan to use in this module. Use the command to list your Azure subscriptions:

az account list --output table

Use the following command to ensure you're using an Azure subscription that allows you to create resources for the purpose of this module, substituting your subscription ID (SubscriptionId):

az account set --subscription "Name of the subscription"

Configure kubectl to connect to your Azure Kubernetes

Replace Your_Azure_Resource_groups_name in the code below with the name you chose when creating a resource group. Also, replace your_azure_kubernetes_service_name with the name of your Kubernetes cluster. Then, execute the following command:

az aks get-credentials --resource-group [Your_Azure_Resource_groups_name] --name [your_azure_kubernetes_service_name]

The output should look like this:

Merging kubectl with Azure Kubernetes Service

Verify if kubectl has been merged successfully

Run the following command kubectl get nodes:

Verifying if merged is successful

When you run this command, Kubernetes communicates with the cluster's control plane to fetch a list of all the nodes that are part of the cluster you created. As you can see, this is the node that was running in the Kubernetes cluster we created inside Azure.

Virtual Node running in AKS cluster

Run the command kubectl get pods

Displaying Pod Information

When you run the command kubectl get pods, Kubernetes attempts to retrieve information about all pods within the default namespace of your cluster. But in this case, the output indicates that there are no resources (pods) found within the default namespace, implying that no pods currently exist in that namespace.

A namespace in Kubernetes is a virtual cluster environment within which resources like pods, services, and deployments are organized and isolated. It's a way to divide cluster resources between multiple users, teams, or projects. Namespaces provide a scope for names and make it easier to manage and control access to resources.

By default, Kubernetes starts with a "default" namespace, but you can create additional namespaces to organize and manage resources more effectively. Namespaces help prevent naming conflicts and provide a logical separation of resources, allowing different teams or projects to work independently within the same Kubernetes cluster.

Create a namespace

Creating Namespace

When you run the command kubectl create namespace database, Kubernetes creates a new namespace named "database." The output "namespace/database created" confirms that the namespace has been successfully created.

You can now use this namespace to organize and manage resources related to databases within the Kubernetes cluster.

Confirm the namespace

The command kubectl get namespace lists all namespaces in the Kubernetes cluster including the database namespace we just created, showing their names, status (active), and age.

Confirming namespace

Get pod information in database namespace

Displaying Pod Information related to database namespace

This command, kubectl get pods -n database, attempts to fetch information about pods specifically within the "database" namespace. But the output No resources found in database namespace indicates that there are currently no pods deployed in the "database" namespace.

How to Create Resources with YAML

Let's explore creating resources with YAML to provision our PostgreSQL database running in an Azure Kubernetes cluster. But first, what exactly is YAML?

Kubernetes YAML is a configuration file written in YAML (YAML Ain't Markup Language). They define how Kubernetes resources like pods, deployments, and services should be set up within a cluster. These files are easy to read and specify details like resource names, types, specifications, labels, and annotations. They're crucial for deploying applications and infrastructure on Kubernetes clusters.

YAML is what you will use to create Kubernetes resources that will run Postgres.

First, you need to clone this GitHub repository. Inside, you'll find a Node.js Express application and a YAML file. The Node.js app allows users to register with their email, password, and full name, and also enables them to log in by verifying their details in the database. If their details are found, it displays a success message.

Clone the repository

Create a new folder on your computer and then clone this repository into it.

Open your terminal or PowerShell, go to the folder you want, and use the command below to clone the repository into your computer in that location.

git clone https://github.com/ayowilfred95/Azure-k8s-postgres.git

Open the cloned repository in any text editor

I'm using Visual Studio Code, but feel free to use any text editor you prefer. Here's the structure of the project:

Project folder structure

Install project dependencies

Open the terminal in VS Code and go to the main directory of the project. Next, execute the command npm install to install all the required packages and dependencies for the project:

npm install

Since the backend application is a Node.js Express app, you use npm to install dependencies (similar to how we use maven clean install in Java).

After the dependencies are installed, open the file named "postgres.yaml". It holds all the YAML configurations required to set up your PostgreSQL database that will run in the Kubernetes cluster.

YAML Configuration

In the postgres.yaml file, there are five configurations separated by ---. It's important to use this "---" symbol when declaring different types of Kubernetes resources. If you forget to do this, you'll encounter an error.

StorageClass

The first one is the StorageClass. This YAML configuration defines a StorageClass in Kubernetes for managing storage resources.

kind: StorageClass
apiVersion: storage.k8s.io/v1
metadata:
  name: azuredisk-premium-retain
provisioner: kubernetes.io/azure-disk
reclaimPolicy: Retain   # Retain or Delete
volumeBindingMode: WaitForFirstConsumer   # WaitForFirstConsumer or Immediate
allowVolumeExpansion: true    # true or false
parameters:
  storageaccounttype: Premium_LRS   # Premium or Standard
  kind: Managed

Let's break down what each part means:

• kind: StorageClass: Indicates the type of Kubernetes resource being defined, which is a StorageClass. A StorageClass defines the class of storage offered by a cluster.

• apiVersion: storage.k8s.io/v1: Specifies the Kubernetes API version being used for this resource.

• metadata: name: azuredisk-premium-retain: Provides metadata for the StorageClass, including its name, which in this case is "azuredisk-premium-retain".

• provisioner: kubernetes.io/azure-disk: Specifies the provisioner responsible for provisioning storage. In this case, it's "kubernetes.io/azure-disk", indicating that Azure Disk will be used as the storage provisioner.

• reclaimPolicy: Retain: Defines the reclaim policy for the storage resources. It specifies what action should be taken when the associated persistent volume is released. Here, it's set to "Retain", meaning the volume is retained even after it's no longer used by a pod.

• volumeBindingMode: WaitForFirstConsumer: Specifies the volume binding mode, which determines when volume binding should occur. In this case, it's set to "WaitForFirstConsumer", meaning the volume will be bound when the first pod using it is created.

• allowVolumeExpansion: true: Indicates whether volume expansion is allowed. Setting it to "true" means that the size of the volume can be increased if needed.

• parameters: Contains additional parameters specific to the provisioner. Here, it specifies the storage account type as "Premium_LRS" and the kind of storage as "Managed".

Overall, this configuration sets up a StorageClass named "azuredisk-premium-retain" using Azure Disk as the provisioner, with specific policies and parameters tailored for Azure storage.

PersistentVolumeClaim

The second configuration in the postgres.yaml file is the persistent volume claim.

This YAML configuration defines a PersistentVolumeClaim (PVC) in Kubernetes, which is used to request storage resources.

apiVersion: v1
kind: PersistentVolumeClaim
metadata:
  name: azure-managed-disk-pvc
spec:
  accessModes:
  - ReadWriteOnce   # ReadWriteOnce, ReadOnlyMany or ReadWriteMany
  storageClassName: azuredisk-premium-retain
  resources:
    requests:
      storage: 4Gi

Let's break down what each part means:

• apiVersion: v1: Specifies the Kubernetes API version being used for this resource.

• kind: PersistentVolumeClaim: Indicates the type of Kubernetes resource being defined, which is a PersistentVolumeClaim. A PVC is used by pods to request storage resources.

• metadata: name: azure-managed-disk-pvc: Provides metadata for the PersistentVolumeClaim, including its name, which is "azure-managed-disk-pvc".

• spec: Describes the desired state of the PersistentVolumeClaim.

• accessModes: - ReadWriteOnce: Specifies the access mode for the volume. Here, it's set to "ReadWriteOnce", meaning the volume can be mounted as read-write by a single node at a time.

• storageClassName: azuredisk-premium-retain: Specifies the StorageClass to use for provisioning the volume. This PVC will use the StorageClass named "azuredisk-premium-retain" defined previously.

• resources: requests: storage: 4Gi: Specifies the desired storage capacity for the volume. Here, it requests 4 gigabytes (Gi) of storage.

Overall, this configuration sets up a PersistentVolumeClaim named "azure-managed-disk-pvc" requesting storage resources with specific access modes, storage class, and storage capacity.

ConfigMap

The third configuration in the postgres.yaml file is the config map. This YAML configuration defines a ConfigMap in Kubernetes, which is used to store configuration data in key-value pairs.

apiVersion: v1
kind: ConfigMap
metadata:
  name: postgres-config
  labels:
    app: postgres
data:
  POSTGRES_DB: freecodecamp
  POSTGRES_USER: freecodecamp1
  POSTGRES_PASSWORD: freecodecamp@
  PGDATA: /var/lib/postgresql/data/pgdata

Let's break down what each part means:

• apiVersion: v1: Specifies the Kubernetes API version being used for this resource.

• kind: ConfigMap: Indicates the type of Kubernetes resource being defined, which is a ConfigMap. A ConfigMap is used to store non-confidential data in key-value pairs.

• metadata: name: postgres-config: Provides metadata for the ConfigMap, including its name, which is "postgres-config".

• labels: app: postgres: Labels are key-value pairs used to organize and select resources. Here, a label "app" with the value "postgres" is applied to the ConfigMap.

• data: Contains the key-value pairs of configuration data.

• POSTGRES_DB: pisonitsha: Specifies the name of the PostgreSQL database as "pisonitsha".

• POSTGRES_USER: pisonitsha1: Specifies the username for accessing the PostgreSQL database as "pisonitsha1".

• POSTGRES_PASSWORD: pisonitsha@: Specifies the password for accessing the PostgreSQL database as "pisonitsha@".

• PGDATA: /var/lib/postgresql/data/pgdata: Specifies the location of PostgreSQL data directory as "/var/lib/postgresql/data/pgdata".

Overall, this configuration sets up a ConfigMap named "postgres-config" containing key-value pairs of configuration data, such as database name, username, password, and data directory location, which can be used by other Kubernetes resources.

Note: It's recommended to avoid hardcoding secret variables such as POSTGRES_DB, POSTGRES_PASSWORD,PGDATA and instead store them in secret files, for the sake of simplicity in this tutorial, we'll keep them hardcoded.

StatefulSet

The fourth configuration is the stateful set.This YAML configuration defines a StatefulSet in Kubernetes, which is used to manage stateful applications like databases.

apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: postgres
spec:
  serviceName: postgres
  selector:
    matchLabels:
      app: postgres
  replicas: 1
  template:
    metadata:
      labels:
        app: postgres
    spec:
      containers:
        - name: postgres
          image: postgres:10.4
          imagePullPolicy: "IfNotPresent"
          ports:
          - containerPort: 5432
          envFrom:
          - configMapRef:
              name: postgres-config
          volumeMounts:
          - name: azure-managed-disk-pvc
            mountPath: /var/lib/postgresql/data
      volumes:
      - name: azure-managed-disk-pvc
        persistentVolumeClaim:
          claimName: azure-managed-disk-pvc
    ```

Let's break down what each part means:

* `apiVersion: apps/v1`: Specifies the Kubernetes API version being used for this resource.
* `kind: StatefulSet`: Indicates the type of Kubernetes resource being defined, which is a `StatefulSet`. `StatefulSets` are used to manage stateful applications by providing unique **identities** and stable **network** identities to each pod.
* `metadata: name: postgres`: Provides metadata for the `StatefulSet`, including its name, which is "postgres".
* `spec`: Describes the desired state of the `StatefulSet`.
* `serviceName: postgres`: Specifies the name of the Kubernetes service that will be used to access the `StatefulSet` pods.
* `selector: matchLabels: app: postgres`: Selects the pods controlled by this `StatefulSet` based on the label "app: postgres".
* `replicas: 1`: Specifies the desired number of replicas (instances) of the StatefulSet, which is 1 in this case.
* `template`: Defines the pod template used to create pods managed by the `StatefulSet`.
* `metadata: labels: app: postgres`: Labels applied to the pods created from this template.
* `spec`: Describes the specification of the containers within the pod.
* `containers`: Specifies the containers running in the pod.
* `name: postgres`: Defines the name of the container as "postgres".
* `image: postgres:10.4`: Specifies the Docker image used for the container, which is "postgres:10.4".
* `imagePullPolicy: "IfNotPresent"`: Specifies the policy for pulling the container image, which is "IfNotPresent", meaning it will only pull the image if it's not already present on the node.
* `ports: containerPort: 5432`: Specifies the port that the PostgreSQL service inside the container is listening on.
* `envFrom: configMapRef: name: postgres-config`: Injects environment variables from a ConfigMap named "**postgres-config**" that you defined earlier.
* `volumeMounts: name: azure-managed-disk-pvc mountPath: /var/lib/postgresql/data`: Mounts a persistent volume claim named "azure-managed-disk-pvc" to the container at the specified path.
* `volumes: name: azure-managed-disk-pvc persistentVolumeClaim: claimName: azure-managed-disk-pvc`: Defines the persistent volume claim named "azure-managed-disk-pvc" to be used by the pod.

Overall, this configuration sets up a StatefulSet named "postgres" with one replica, running a PostgreSQL container with specific settings and mounted persistent storage.

### Service

The fifth configuration is the **service**. This YAML configuration defines a **Service** in Kubernetes, which is used to expose the `StatefulSet` we declared earlier as a network service.

```bash
apiVersion: v1
kind: Service
metadata:
  name: postgres
  labels:
    app: postgres
spec:
  type: LoadBalancer
  selector:
    app: postgres
  ports:
    - protocol: TCP
      name: https
      port: 5432
      targetPort: 5432

Let's break down what each part means:

• apiVersion: v1: Specifies the Kubernetes API version being used for this resource.

• kind: Service: Indicates the type of Kubernetes resource being defined, which is a Service. Services allow pods to be accessed by other pods or external users.

• metadata: name: postgres: Provides metadata for the Service, including its name, which is "postgres".

• labels: app: postgres: Labels are key-value pairs used to organize and select resources. Here, a label "app" with the value "postgres" is applied to the Service.

• spec: Describes the desired state of the Service.

• type: LoadBalancer: Specifies the type of Service, which is "LoadBalancer". This type allows the Service to be exposed externally with a cloud provider's load balancer.

• selector: app: postgres: Selects the pods controlled by the Service based on the label "app: postgres".

• ports: Specifies the ports that the Service will listen on.

• protocol: TCP: Specifies the protocol used for the port, which is TCP.

• name:https : Specifies a name for the port, which is "https".

• port: 5432: Specifies the port number on which the Service will listen, which is 5432.

• targetPort: 5432: Specifies the target port on the pods to which traffic will be forwarded, which is also 5432. This means that traffic received on port 5432 of the Service will be forwarded to port 5432 on the pods.

Overall, this configuration sets up a Service named "postgres" with a LoadBalancer type, forwarding traffic on port 5432 to pods labeled with "app: postgres".

How to Deploy YAML Resource to Azure Kubernetes Service (AKS)

You've previously connected "kubectl" with the Azure Kubernetes Service (AKS) you set up. Let's double-check it.

In your VS Code terminal, rerun the command kubectl get nodes. You'll see an output like this, though your node's value will be different.

Displaying node information running in Azure Kubernetes cluster

Next, verify the namespace you previously created by executing the command: kubectl get namespace database. Your output should resemble this:

Retrieving Namespace Information

Deploy the YAML resource

Once you've confirmed everything is set, you can deploy the YAML resource. This will establish your PostgreSQL database in the Azure Kubernetes cluster you've configured.

Run the below command in the main directory where the configuration file is located. Currently, I'm in the project's root directory (azure-k8s-postgres). To deploy the database, just execute this command below:

kubectl apply -n database -f postgres.yaml

Your output should look like this. This output confirms that all these components have been successfully created in Kubernetes.

Applying Configuration to Namespace

Execute the command below to verify that the pod is running:

kubectl get pods -n database

Your output should look like this:

Fetching Pods in Namespace

This output confirms that a pod name "postgres-0" is running in your Azure Kubernetes Cluster. But it was not the only pod you created. As I said earlier, to connect to a pod, you need what is called service. And you have declared a service resource in our configuration file which has also been deployed into your Kubernetes.

To get the status of the service, run this command:

kubectl get services -n database

Your output should look like this:

Retrieving Services in Namespace

This output displays the services in the "database" namespace, including a service named "postgres" with the type "LoadBalancer," its internal cluster IP, external IP, and port mappings. You'll utilize the external IP along with the Postgres port "5432" to connect your database with the Node.js application. Note that your external IP will differ from mine.

Node.js Application

In this section, I'll guide you through setting up your Node.js app to connect to a PostgreSQL database in your Azure Kubernetes Service.

We'll cover sending data into the database and retrieving it using Postman. Also, I'll demonstrate how to check if the data remains in the database even if the pod running PostgreSQL in the cluster is deleted.

Configure your Node.js application

Go to the database folder and open the database.js file. Replace the host with your EXTERNAL-IP obtained from the service, and leave the rest unchanged since you've already defined those variables in your config map.

Your database.js file should resemble the CodeSnap below:

CodeSnap of database.js Configuration

Run your Node.js application

In your VS Code terminal, execute this command to start the Node.js application locally:

npm start

Your output should look like this if the connection is established successfully.

Server Listening on Port 4000

If your output looks the same as mine, it indicates that you've successfully connected your Node.js application to the PostgreSQL database running in your Azure Kubernetes cluster. Congratulations! 🎉

Test the application

Testing is a fundamental principle in DevOps operations. It helps us understand the state of the application we've built before releasing it to users. Any application that doesn't pass the testing stage will not be deployed. This is a rule in DevOps.

For this tutorial, you'll be using Postman. You can download Postman here. Postman enables you to test API endpoints by receiving status responses.

Check out this post on how to use Postman to test APIs. If you want to learn more, here's a full course on the subject.

Open your Postman application

To begin using Postman, start by creating a new API request in your preferred workspace. Choose POST. POST requests add new data to the database or server. Then, paste the endpoint URL (localhost:4000/api/v1/admin/register) for your Postman test.

The below screenshot illustrates how you will create a POST request.

Postman URL Endpoint Configuration

In the body, paste the JSON data shown below inside it as shown below:

{   
    "fullName":"Azure postgres freecodecamp",
    "email":"freecodecamp@gmail.com",
    "password":"freecodecamp"
}

Postman Request Body

Once you've set up the request, just click the "Send" button to send it. Postman will then show you status codes, and the response payload as shown below.

Postman API Response

Confirm the data

To confirm that the data you sent into the database exists, make a GET request to this endpoint URL: localhost:4000/api/v1/admin/freecodecamp@gmail.com

Postman GET Request URL Endpoint

When you click send, Postman will then show you status codes and the response payload as shown below. Notice that we didn't put anything in the body because this is a GET request.

Postman GET Request by Email Retrieval Response

Delete the pod to confirm data persistence

We chose to create our PostgreSQL database using a StatefulSet to ensure that data persists even if the pod is destroyed. Let's test this by deleting the pod and checking if the data remains intact.

In your VS Code terminal, execute the command: kubectl delete pod -n database postgres-0.

This command deletes a pod named "postgres-0" in the "database" namespace from your Kubernetes cluster. Your output should look like this.

Deleting Pod in Namespace:

Pod recreation

Kubernetes has a built-in feature called replication controllers or replica sets that ensure a specified number of pod replicas are running at any given time. If a pod is deleted, Kubernetes will automatically recreate it to maintain the desired number of replicas, ensuring high availability

If you run kubectl get pods -n database, you'll notice that Kubernetes has created a new pod with the same name, "postgres-0", to replace the one that was deleted. This ensures that the application remains available and continues to function as expected.

Pod Recreated back in Namespace

Data persistence

Navigate back to Postman and make a GET request to the endpoint URL localhost:4000/api/v1/admin/freecodecamp@gmail.com.

You should get the same response as before. So under the hood, when we delete the pod, the storage disk was not deleted. The storage disk is inside the Azure disk. How do we know that? If you run this command:

kubectl get pvc -n database

you should get this output:

Persistennce Volume Claim details in namespace

This shows details about a storage called "azure-managed-disk-pvc" in your Kubernetes. It's currently in use and has 4 gigabytes of space available. It's set up to be read and written to by one system at a time. This storage is provided by a service called "azuredisk-premium-retain" that we configured earlier.

Clean up resources

In this tutorial, you created Azure resources in a resource group. If you won't need these resources later, delete the resource group from the Azure portal or run the following command in your terminal:

az group delete --name AZURE-POSTGRES-RG --yes

This command might take a minute to run.

Conclusion

We've gone on quite a journey here! You've learned how to deploy a Postgres container in Azure Kubernetes Service (AKS) and integrate it with a Node.js application.

In this tutorial, I guided you through the process of configuring Kubernetes using Azure Kubernetes Service (AKS). You learned to customize YAML files utilizing StatefulSet, Persistent Volume, and Services to deploy a PostgreSQL database on Azure Kubernetes. You also acquired PostgreSQL database credentials running within AKS to establish connectivity with a Node.js application. I then provided detailed instructions on connecting your Node.js Express app to the Postgres container within the AKS cluster.

Thank you for reading!

This step-by-step guide will help you grasp the fundamentals of working with databases, data manipulation, and email communication, all while automating these processes with a Python script.

Business Context

Imagine you're a part of an organization where your managers expect a weekly report filled with valuable insights. But creating this report is far from a straightforward task.

To get the information you need, you have to manually run ten different database queries, gather the results, and then meticulously compile them into an Excel spreadsheet. It's a time-consuming and error-prone process that can leave you exhausted.

In this scenario, wouldn't it be a game-changer if Python could take the reins and handle this entire process for you?

Picture this: Every week, without any manual intervention, Python seamlessly extracts the required data, compiles it into a neat Excel sheet, and even sends it off to your managers like clockwork.

This tutorial will help you learn how to do this. I'll walk you through the steps to automate this process, making your weekly or monthly reporting a breeze, and freeing you up to focus on more critical tasks.

Table of Contents

1. Prerequisites
2. How to Set Up Your Virtual Environment
3. How to Set Up Your Sample Database
4. How to Set Up Logging and Environment Variables
5. How to Extract the Data From the Database
6. How to Structure the Booking Data with the BookingInfo Class
7. How to Convert the Data into an Excel Sheet
8. How to Combine the Functionalities
9. How to Send an Email with the Bookings Data Report
10. How to Test the Flow
11. How to Schedule the Application
12. Wrapping Up

Prerequisites

Before you get started, make sure you have the following:

1. Python installed on your computer. You can download Python from Python.org.
2. Basic knowledge of the Python programming language
3. Familiarity with sending emails in Python
4. PostgreSQL installed on your computer. You can download PostgreSQL from here.

How to Set Up Your Virtual Environment

Before you start coding, you'll need to make sure you have all the necessary tools and libraries installed.

To ensure that you have a clean and isolated environment, you'll create a virtual environment using venv.

Create a project directory and navigate to it in the terminal:

mkdir report-automation
cd report-automation

Create a virtual environment named env using the following command:

python -m venv env

Python now ships with the pre-installed venv library to create virtual environments.

Activate the virtual environment like this:

source env/bin/activate

Note: if you're on Windows, you'll need to use source env/Scripts/activate to activate the environment.

You should see (env) in your terminal prompt, indicating that the virtual environment has been activated.

How to Install the Required Libraries

Now that you've created the virtual environment, you can install the following libraries:

• psycopg2: Python adapter for PostgreSQL, enabling Python applications to interact with PostgreSQL databases.
• pandas: A versatile data manipulation and analysis library for Python, ideal for working with structured data.
• xlsxwriter: Python module for creating and formatting Excel (XLSX) files, useful for generating reports and spreadsheets.

To install the libraries, run the following command:

pip install psycopg2 pandas xlsxwriter

How to Set Up Your Sample Database

In this section, I will guide you through setting up a demo database named "airlines" that we'll use throughout this tutorial. The database includes three tables: bookings, flights, and airports_data.

I will provide you with an SQL script file named airlines_db.sql that creates the database and populates it with sample data. To set up the database, you will need PostgreSQL installed on your system.

Download and Install the Database

1. Download the SQL script file "airlines_db.sql" from here.
2. Open your terminal or command prompt.
3. Use the following command to install the database. Make sure you have the PostgreSQL command-line tools installed and that you can access the psql command. Replace postgres with your PostgreSQL username if it's different.

psql -f airlines_db.sql -U postgres

This command will execute the SQL script and create the "airlines" database with the bookings, flights, and airports_data tables.

Schema Description

The main schema in the database is bookings. Let's take a closer look at the tables in the "airlines" database:

Schema Diagram

Table bookings.bookings

The "bookings" table is designed to store crucial information about bookings made for flights. Each booking is uniquely identified by the book_ref, which is a character(6) field. The total_amount field is a numeric(10,2) type and represents the total cost of the booking.

To track the booking date and time, the table includes a book_date field of type bigint. This table serves as the central repository for booking data and is essential for tracking passenger reservations, costs, and booking dates.

Table bookings.flights

The "flights" table is dedicated to capturing comprehensive details about flights, including information about their statuses, scheduled and actual times of departure and arrival, and other important flight-related data.

The primary key for this table is the flight_id, an integer identifier. Each flight is associated with a specific flight number denoted by the flight_no field, a character(6) type.

To understand the flight's origin and destination, the departure_airport and arrival_airport fields store the departure and arrival airport codes as character(3) types, respectively.

The status field is a character varying(20) that records the flight's status, which must be one of 'On Time,' 'Delayed,' 'Departed,' 'Arrived,' 'Scheduled,' or 'Cancelled.' The table also includes fields for scheduled departure and arrival times (scheduled_departure and scheduled_arrival) and actual departure and arrival times (actual_departure and actual_arrival).

Furthermore, this table establishes two essential foreign keys: flights_arrival_airport_fkey and flights_departure_airport_fkey, which link to the airport_code in the "airports_data" table. This establishes connections between flights and their respective departure and arrival airports.

Table bookings.airports_data

The "airports_data" table serves as a repository for data related to airports and their geographic locations. Each airport is identified by a unique character(3) code stored in the airport_code field, which also serves as the primary key.

The timezone field, of type text, records the specific timezone of the airport, providing essential information for scheduling and operational purposes. The airport_name field is a character varying type that holds the name of the airport. Additionally, the table includes the city field as a character varying type, indicating the city in which the airport is situated.

These details enable the "airports_data" table to provide a comprehensive overview of airport locations and information. This serves as a reference for the "flights" table through the flights_arrival_airport_fkey and flights_departure_airport_fkey foreign keys, facilitating the association between flights and their respective departure and arrival airports.

How to Set Up Logging and Environment Variables

In this section, we'll configure logging to provide informative messages and handle errors throughout the code. We'll also set up environment variables to securely store sensitive information and configuration parameters. These practices enhance code readability, maintainability, and security.

Logging Configuration

We will utilize Python's built-in logging module to configure a logging system. Logging is essential for tracking the execution flow of the code and capturing important information or errors.

The logging.basicConfig method is called to define the format of log messages and set the logging level to INFO.

import logging

logging.basicConfig(
    format="%(asctime)s | %(levelname)s : %(message)s", level=logging.INFO
)

• Format: The format parameter specifies the format of log messages. In this case, each log entry includes a timestamp, log level (for example, INFO, ERROR), and the actual log message.
• Log Levels: We set the logging level to INFO, which means the logger will record informational messages. You can also use higher severity levels, such as WARNING or ERROR, for more critical issues.

You can learn more about logging in Python in this tutorial.

How to Manage Environment Variables

We will create a .env file to manage environment variables. Environment variables are used to store sensitive information and configuration settings, allowing us to keep such data separate from the code.

In this case, we set environment variables for email credentials and database connection details.

export EMAIL=
export PASSWORD=
export EMAIL_PORT=587
export SMTP_SERVER=smtp.gmail.com
export DB_HOSTNAME=localhost
export DB_NAME=airlines
export DB_PORT=5432
export DB_USERNAME=postgres
export DB_PASSWORD=postgres

Here's a breakdown of the variables:

• EMAIL: The email address to be used for sending emails.
• PASSWORD: The password associated with the email account.
• EMAIL_PORT: The port for the email server (for example, SMTP server). The default is 587 for secure email transmission (TLS/SSL).
• SMTP_SERVER: The SMTP server address, often specific to the email service provider.
• DB_HOSTNAME: The hostname or IP address of the PostgreSQL database server.
• DB_NAME: The name of the PostgreSQL database.
• DB_PORT: The port number for connecting to the database (default is 5432 for PostgreSQL).
• DB_USERNAME: The username for authenticating with the database.
• DB_PASSWORD: The password for the database user.

Make sure you run source .env to load the environment variables.

By using environment variables, sensitive data like passwords and email credentials can be kept separate from the code, reducing the risk of accidental exposure or unauthorized access. The code can access these variables at runtime, ensuring security and flexibility in configuration.

How to Extract the Data From the Database

Let's start by setting the database configurations.

import logging
import os

logging.basicConfig(
    format="%(asctime)s | %(levelname)s : %(message)s", level=logging.INFO
)

DB_CONFIG = {
    "host": os.environ.get("DB_HOSTNAME"),
    "database": os.environ.get("DB_NAME"),
    "user": os.environ.get("DB_USERNAME"),
    "password": os.environ.get("DB_PASSWORD"),
}

The DB_CONFIG dictionary is used to store the configuration parameters for connecting to the PostgreSQL database. These parameters include the host, database name, username, and password. These values can be set through environment variables.

How to Connect to the Database

Before we extract the data from the database, we need to connect to our database. We will use the psycopg2 library to connect to the PostgreSQL database.

We will start by defining a DataExporter class that will contain methods to extract the database and generate the Excel sheet.

class DataExporter:
    def __init__(self):
        """Initialize the DataExporter with the database configuration."""
        self.db_config = DB_CONFIG

The class constructor initializes the DataExporter with the database configuration stored in the DB_CONFIG dictionary.

Next, let's define a method that connects to the database.

...
import psycopg2

...

class DataExporter:
    def __init__(self):
        """Initialize the DataExporter with the database configuration."""
        self.db_config = DB_CONFIG

    def __connect_to_database(self) -> None:
        """
        Establish a connection to the PostgreSQL database.

        Raises:
            Exception: If a connection to the database cannot be established.
        """
        try:
            self.conn = psycopg2.connect(**self.db_config)
            self.cursor = self.conn.cursor()
            logging.info("Connected to the database")
        except Exception as e:
            logging.error(
                "Failed to connect to the database with error: %s", e)
            raise

The __connect_to_database private method is responsible for establishing a connection to the PostgreSQL database. It uses the psycopg2 library to create a connection and a cursor for executing SQL queries. If the connection fails, it logs an error and raises an exception.

You can learn more about exception handling in Python here.

How to Fetch Data from the Database

Now we'll define another private method that connects to the database and fetches the total number of bookings and the total amount from the database.

from datetime import datetime

class DataExporter:
    ...

    def __fetch_from_database(self, start_timestamp, end_timestamp) -> list | None:
        """
        Fetch booking data from the database for a given time range.

        Args:
            start_timestamp (datetime): The start of the time range.
            end_timestamp (datetime): The end of the time range.

        Returns:
            list: A list containing booking data (num_bookings, total_amount) or None if an error occurs.
        """
        self.__connect_to_database()
        query = f"""
        SELECT COUNT(*) AS num_bookings, SUM(total_amount) AS total_amount
        FROM bookings
        WHERE book_date >= {int(start_timestamp.timestamp()) * 1000} AND book_date <= {int(end_timestamp.timestamp()) * 1000}
        """
        logging.info(
            "Exracting bookings data from database for start timestamp=%s and end_timestamp=%s",
            start_timestamp,
            end_timestamp,
        )
        result = None
        try:
            self.cursor.execute(query)
            result = list(self.cursor.fetchone())
            result.append(
                f'{start_timestamp.strftime("%d %b, %Y")} - {end_timestamp.strftime("%d %b, %Y")}'
            )
            logging.info(
                "Successfully exracted bookings data from database for start timestamp=%s and end_timestamp=%s",
                start_timestamp,
                end_timestamp,
            )
        except Exception as e:
            logging.error(
                "Error occurred while extracting bookings data from database: %s", e
            )
        return result

This private method retrieves booking data from the database for a specified time range.

It takes two datetime objects as arguments, start_timestamp and end_timestamp. It also constructs a SQL query to retrieve the count of bookings and the total booking amount for that time range.

The query is executed, and if it's successful, the method returns the data as a tuple. We convert the tuple into a list and append the timeframe for which data was extracted to the list. If an error occurs during the database interaction, it logs an error and returns None.

Using the above method, you can extract booking data for various timeframes, whether it's for a week, a month, a year, or any custom time range of your choice.

How to Structure the Booking Data with the BookingInfo Class

In this section, we will define a BookingInfo class in booking_info.py, which serves as a structured container for booking data retrieved from the database. The class encapsulates booking-related information, making it easier to work with and present the data.

from decimal import Decimal


class BookingInfo:
    def __init__(self, data_list: list):
        """
        Initialize BookingInfo with data from the database.

        Args:
            data_list (list): A list containing booking data (total_bookings, total_amount, timestamp).

        Note:
            The total_amount is converted to a Decimal type.

        """
        self.__total_bookings, self.__total_amount, self.__timestamp = data_list
        self.__total_amount = Decimal(self.__total_amount) if self.__total_amount else Decimal(0)

    def __str__(self) -> str:
        """
        Return a string representation of BookingInfo.

        Returns:
            str: A string in the format "Total Bookings: X, Total Amount: $Y".

        """
        return f"Total Bookings: {self.__total_bookings}, Total Amount: ${self.__total_amount}"

    def get_total_bookings(self) -> int:
        """
        Get the total number of bookings.

        Returns:
            int: The total number of bookings.

        """
        return self.__total_bookings

    def get_total_amount(self) -> Decimal:
        """
        Get the total booking amount as a Decimal.

        Returns:
            Decimal: The total booking amount.

        """
        return self.__total_amount

    def get_timestamp(self) -> str:
        """
        Get the timestamp associated with the booking data.

        Returns:
            str: The timestamp as a string.

        """
        return self.__timestamp

The BookingInfo class is designed to organize and represent booking data returned from the database. It receives a list of values containing total bookings, total booking amount, and a timestamp as input and converts the total amount to a Decimal type. The class offers methods for accessing and presenting this data in a structured manner.

The constructor of the BookingInfo class takes a data_list as input, which is expected to be a list containing the following elements:

• total_bookings: An integer representing the total number of bookings.
• total_amount: A floating-point value representing the total booking amount.
• timestamp: A timestamp associated with the booking data.

The __init__ method initializes private instance variables (__total_bookings, __total_amount, and __timestamp) with the values from the data_list. It also converts the __total_amount to a decimal type for precise handling of monetary values.

The __str__ method is implemented to provide a string representation of the BookingInfo object. It returns a string in the format "Total Bookings: X, Total Amount: $Y", where X is the total number of bookings and Y is the total booking amount formatted as dollars.

Getter Methods

The class provides three getter methods to access the encapsulated data:

• get_total_bookings(): Returns the total number of bookings as an integer.
• get_total_amount(): Returns the total booking amount as a Decimal type.
• get_timestamp(): Returns the timestamp associated with the booking data as a string.

By encapsulating the booking data within the BookingInfo class, the code is more organized, readable, and reusable. This structured approach simplifies the handling of booking information throughout the application, making it more intuitive to work with and present the data.

How to Convert the Data into an Excel Sheet

Now that you can retrieve data from the database for a specific time range, you can also generate an Excel sheet based on the extracted data.

To do this, let's define another private method to create the Excel sheet.

...
import pandas as pd

from booking_info import BookingInfo


...

class DataExporter:

    ...

    def __convert_to_excelsheet(self, data: list, sheet_name: str):
        """
        Convert the fetched data into an Excel sheet.

        Args:
            data (list): A list containing booking data.
            sheet_name (str): Name of the Excel sheet to be created.

        Raises:
            ValueError: If there is an error in converting data to an Excel sheet.
        """
        try:
            booking_info = BookingInfo(data)
            data = {
                "": ["Total Bookings", "Total Amount ($)"],
                booking_info.get_timestamp(): [
                    booking_info.get_total_bookings(),
                    booking_info.get_total_amount(),
                ],
            }
            logging.info("Converting the data into pandas dataframe")
            df = pd.DataFrame(data)
            logging.info("Inserting the data into the excelsheet")
            with pd.ExcelWriter(sheet_name, engine="xlsxwriter") as writer:
                df.to_excel(writer, sheet_name="Sheet1", index=False)
            logging.info("Successfully inserted data into the excelsheet")
        except ValueError as e:
            logging.error("Error converting data into excel: %s", e)

The __convert_to_excelsheet method within the DataExporter class is responsible for structuring and converting extracted booking data into an Excel sheet.

It accepts two input parameters. The first parameter, data, is expected to be a list containing specific booking data. This data includes the total number of bookings, the total booking amount, and a timestamp for which data was extracted. The second parameter, sheet_name, represents the desired name for the Excel sheet that will contain the formatted data.

A key aspect of the method is the structuring of the data. To achieve this, the method initiates the creation of a BookingInfo object, referred to as booking_info. The BookingInfo object provides a structured representation of the booking data, which simplifies the subsequent formatting and presentation.

Following the creation of the booking_info object, a new dictionary called data is generated. This dictionary is designed to structure the data in a format suitable for conversion into an Excel sheet.

The dictionary consists of two key-value pairs:

• The first pair uses an empty string as the key and contains a list with two header values, "Total Bookings" and "Total Amount ($)".
• The second pair uses the timestamp obtained from booking_info.get_timestamp() as the key and includes a list with two elements: the total number of bookings (booking_info.get_total_bookings()) and the total booking amount (booking_info.get_total_amount()).

This dictionary allows the data to be inserted in the excel sheet as below:

Sample Excel Sheet

Then, the structured data dictionary is converted into a pandas DataFrame, referred to as df. Dataframes are a commonly used data structures for handling tabular data in Python. This step streamlines the manipulation and export of the data for further processing or visualization.

To create the Excel sheet, the code uses the pd.ExcelWriter context manager with the "xlsxwriter" engine. This context manager ensures that the Excel file is appropriately prepared for data insertion. The sheet_name parameter is supplied to specify the name of the sheet within the Excel file.

The data within the DataFrame, df, is then written to the Excel sheet. The to_excel method is used in conjunction with the writer object, and the index parameter is set to False. This specific configuration excludes the default row numbers that are typically included in Excel sheets.

How to Combine the Functionalities

Now let's write a public method that the users can use to extract the data from the database and convert the extracted data into the Excel sheet file.

...


class DataExporter:

    ...

    def generate_excelsheet(
        self,
        start_timestamp: datetime,
        end_timestamp: datetime,
        sheet_name: str = "Bookings Data.xlsx",
    ) -> bool:
        """
        Generate an Excel sheet with booking data for a specified time range.

        Args:
            start_timestamp (datetime): The start of the time range.
            end_timestamp (datetime): The end of the time range.
            sheet_name (str, optional): Name of the Excel sheet to be created. Defaults to "Bookings Data.xlsx".

        Returns:
            bool: True if excelsheet was generated successfully else False

        Note:
            This method logs errors but does not raise exceptions to avoid breaking the workflow.
        """
        data = self.__fetch_from_database(start_timestamp, end_timestamp)
        if data is not None:
            self.__convert_to_excelsheet(data, sheet_name)
            return True
        else:
            logging.error("No data to convert generate excelsheet")
            return False

This method accepts several parameters, including start_timestamp and end_timestamp, which define the beginning and end of the time period for data extraction. There's also an optional sheet_name parameter that allows the user to specify the name of the Excel sheet. By default, the sheet is named "Bookings Data.xlsx" to provide a convenient default option.

Upon execution, the method initiates the data retrieval process by calling the __fetch_from_database method, an internal private method of the class, with the specified time range.

If the data retrieval is successful and data is available, the method proceeds to call the __convert_to_excelsheet method. This structures and formats the data for insertion into the Excel sheet.

If, on the other hand, no data is available for the provided time range, the method logs an error message and returns "False" to indicate that the Excel sheet generation was unsuccessful.

How to Send an Email with the Bookings Data Report

In this section, you will learn how you can use Python to send an email with a bookings data report as an attachment.

Create a mailer.py file and add the following content:

import logging
import os
import smtplib
import ssl

from email import encoders
from email.mime.base import MIMEBase
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText

logging.basicConfig(
    format="%(asctime)s | %(levelname)s : %(message)s", level=logging.INFO
)

SMTP_SERVER = os.environ.get("SMTP_SERVER")
PORT = os.environ.get("EMAIL_PORT")
EMAIL = os.environ.get("EMAIL")
PASSWORD = os.environ.get("PASSWORD")


def send_email(to_email: str, subject: str, attachment_name: str):
    """
    Send an email with an attachment to the specified recipient.

    Args:
        to_email (str): The recipient's email address.
        subject (str): The subject of the email.
        attachment_name (str): The filename of the attachment.

    Note:
        This function assumes that the SMTP server requires TLS encryption.

    Raises:
        smtplib.SMTPException: If there is an issue with sending the email.

    """
    message = MIMEMultipart()
    message["From"] = EMAIL
    message["To"] = to_email
    message["Subject"] = subject
    body = "Hi there\n\nPlease find attached your report.\n\nThanks"

    message.attach(MIMEText(body, "plain"))

    with open(attachment_name, "rb") as file:
        part = MIMEBase(
            "application", "vnd.openxmlformats-officedocument.spreadsheetml.sheet"
        )
        part.set_payload(file.read())

    encoders.encode_base64(part)

    part.add_header(
        "Content-Disposition",
        f"attachment; filename= {attachment_name}",
    )

    logging.info(f"Attaching {attachment_name} to the email")
    message.attach(part)
    text = message.as_string()

    context = ssl.create_default_context()
    with smtplib.SMTP(SMTP_SERVER, PORT) as server:
        logging.info(f"Sending email to {to_email}")
        server.starttls(context=context)
        server.login(EMAIL, PASSWORD)
        server.sendmail(EMAIL, to_email, text)
        logging.info(f"Successfully sent the email to {to_email}")

As usual, we have configured the logger and environment variables in our script.

The core functionality is encapsulated within the send_email function. This function takes three parameters:

1. to_email: The recipient's email address.
2. subject: The subject of the email.
3. attachment_name: The filename of the attachment, which should be the bookings data report in this context.

Within the function, we construct an email message using the MIMEMultipart class. This message includes the sender's email address, recipient's email address, subject, and a plain text body with a simple message.

The script allows attaching the bookings data report as an attachment. It reads the attachment file, encodes it, and adds it to the email message. This ensures that the recipient can easily access and download the data report from the email.

You can learn how you can add attachments while sending emails using Python here.

The create_default_context function from the ssl library creates a secure SSL context for email communication. Finally, the script connects to the SMTP server, logs in using the sender's email address and password, sends the email, and logs a success message upon successful transmission.

How to Test the Flow

Let's finally test the flow of the application.

In this section, we will automate the monthly reports. Create a main.py file and add the following content:

from exporter import DataExporter
from datetime import datetime
from mailer import send_email

start_timestamp = datetime(2023, 5, 28, 00, 00, 00)  # May 28 2023 00:00:00
end_timestamp = datetime(2023, 8, 20, 23, 59, 59)  # Aug 20 2023 23:59:59

exporter = DataExporter()
if exporter.generate_excelsheet(
        start_timestamp, end_timestamp, sheet_name="Bookings Data.xlsx"):
    send_email("myemail@gmail.com", "Your Report", "Bookings Data.xlsx")

In the above code, we create two timestamp objects, start_timestamp and end_timestamp, to specify a time range. We have the start date set to May 28, 2023 at midnight and the end date set to August 20, 2023 just before midnight.

Next, we create an instance of the DataExporter class, which handles the data export and Excel sheet generation. The generate_excelsheet method of this instance is called with the previously defined timestamps to create a report related to bookings.

Finally, the code sends an email with the generated Excel sheet as an attachment using the send_email function.

How to Schedule the Application

Next, our goal is to automate the report scheduling process. We aim to schedule report deliveries for two distinct scenarios: on every Monday for the previous week's data, and on the 1st day of every month for the previous month's information.

To schedule the execution, you will need to install the schedule library:

pip install schedule

Once the library is installed, here's how you can do automate the monthly and weekly reports:

import schedule
from exporter import DataExporter
from datetime import datetime, timedelta
from mailer import send_email


def main():
    today = datetime.now()
    sheet_name = "Bookings Data.xlsx"

    if today.weekday() == 0:  # Check if it's Monday (0 means Monday)
        # It's Monday, fetch data for the previous week (Monday to Sunday)
        start_timestamp = (today - timedelta(days=7)
                           ).replace(hour=0, minute=0, second=0, microsecond=0)
        end_timestamp = (today - timedelta(days=1)
                         ).replace(hour=23, minute=59, second=59, microsecond=0)
        sheet_name = "Weekly Report.xlsx"
    elif today.day == 29:
        # It's the 1st day of the month, fetch data for the last month
        start_timestamp = (today.replace(day=1) - timedelta(days=1)
                           ).replace(day=1, hour=0, minute=0, second=0, microsecond=0)
        end_timestamp = (today.replace(day=1) - timedelta(days=1)
                         ).replace(hour=23, minute=59, second=59, microsecond=0)
        sheet_name = "Monthly Report.xlsx"

    exporter = DataExporter()
    exporter.generate_excelsheet(
        start_timestamp, end_timestamp, sheet_name)

    send_email("youremail@gmail.com",
               "Your Report", sheet_name)


schedule.every().day.at("00:00").do(main)

while True:
    schedule.run_pending()

The above script uses the schedule library to run the main function daily at midnight. The main function calculates the timestamps for data extraction and Excel sheet generation. After generating the Excel sheet, the script sends it via email to a specified recipient.

If the script runs on a Monday, it sets up to generate a weekly report. It calculates the start_timestamp and end_timestamp for the previous week. The start_timestamp is set to the previous Monday at midnight (00:00:00), and the end_timestamp is set to the previous Sunday just before midnight (23:59:59). The Excel sheet is named "Weekly Report.xlsx."

On the 1st day of the month, the script shifts its focus to generating a monthly report. It calculates the start_timestamp and end_timestamp to encompass the entire previous month. The start_timestamp is set to the first day of the previous month at midnight (00:00:00), while the end_timestamp is set to the last day of the previous month just before midnight (23:59:59). The Excel sheet is named "Monthly Report.xlsx."

Wrapping Up

In this tutorial, you learned how you can leverage Python to automate generating a report and sending it to email recipients. I hope you found the tutorial helpful!

Future Scope

• You can add the email recipients in a database and fetch their list from there instead of hardcoding them in the code itself. This will make the application more configurable.
• You can also use Cron Jobs to automate the execution of the script every day at midnight. In that case, you won't need the schedule library.

Here's a link to the Github Code Repository.

• Building an API server in Node
• Integrating securely with a 3rd party API
• Interacting with a Postgres database using Prisma
• Making API requests from React
• Creating reusable components
• Working with pagination
• Working with UI elements such as tabs, image grids, modals and styling

Let's dive in.

Table of Contents

• Prerequisites
• GitHub Repositories
• Video Tutorial
• Project Architecture
• How to Setup the Backend
• How to Setup the Database and Prisma
• How to Get and Secure a Spoonacular API Key
• How to Create the Search Endpoint
• How to Setup the Frontend
• How to Call the Search API and Display Results on the Frontend
• How to Create the Search Input and Recipe Card Component
• How to Build the Pagination and View More Functionality
• How to Build the Recipe Summary Modal Component
• How to Create Endpoints to Get/Create/Delete Favorite Recipes
• How to Add Favorites Functionality to the Frontend
• How to Adding CSS/Styling
• Conclusion

Prerequisites

Since we will be focusing on how to build a project, there are a few prerequisites that will be needed to get the most out of this tutorial:

• Some knowledge about web development concepts (frontend, backend, databases, API's, REST).
• Some knowledge of JavaScript (variables, functions, objects, arrays, and so on).
• Basic understanding on React (how to create components, add styles, work with state).
• Basic understanding on Node.js/Express (working with APIs).

GitHub Repositories

Completed Code

You can find the completed code on GitHub by clicking here, or clone the repo:

git clone git@github.com:chrisblakely01/react-node-recipe-app.git

Starter Code

If you want to save some time and skip the initial setup, you can find the starter code here. This has a skeleton frontend/backend project already setup, as well as the basic layout and CSS. Or clone the repo:

git clone git@github.com:chrisblakely01/react-node-recipe-app-starter.git

Video Tutorial

If you'd like to learn from the video version as well, here it is:

Project Architecture

Here's a diagram that illustrates how the various components of our app will interact with each other:

Diagram illustrating the various components of the app

We will have a React front end and a Node backend. These two will communicate through specific endpoints. We will establish five endpoints, all housed within our backend.

We can categorize these endpoints into two distinct groups. The first group will cater to recipe searches. It will invoke the recipe API and return the results based on a given search query. Our front end will initiate a call to our backend, which will then relay the search request to the recipe API.

We choose not to call the recipe API directly from our front end because it requires an API key—a form of authentication similar to a password. Exposing this key in our front end code could lead to unauthorized access if someone delves into the code through their browser to retrieve the API key.

It's a more secure practice to house the API key on our backend within environment variables. From there, we can call the recipe API and then transmit the response back to our front end.

This approach aligns with common practices in production environments. It also offers the flexibility to modify the data on the backend, if necessary, before sending it back to the front end. And it enhances performance, as the UI will not have to manage multiple API requests to and from the recipe API.

That's the essence of how our search functionality will operate. We'll also have several endpoints to add, create, and delete favorites. These favorites will be stored in our own database, providing a clear picture of what we aim to build.

How to Setup the Backend

In this tutorial, we will walk through the process of building a full-stack recipe application. We'll set up the backend, create the frontend, and link it to a database. We will also connect to a recipe API using an API key.

If you prefer to skip the setup, a starter code is available on CodeCoyotes, which includes some basic setup and CSS. But you'll still need to create a database and obtain an API key.

Let’s start by setting up our workspace:

Step 1: Setup Your Workspace

Start by opening Visual Studio Code (or your preferred code editor). Create a new folder named recipe-app on your desktop or another location. Then drag this folder into the Visual Studio Code window to open it.

Your folder structure should now look like this:

recipe-app

Step 2: Setup the Backend

In the recipe-app folder, create another folder named backend.

Navigate to View -> Terminal in Visual Studio Code to open a terminal. Change your directory to the backend folder using the command cd backend.

Type npm init to initialize a new npm package, then hit Enter to move through the prompts. For the entry point, type ./src/index.ts and hit Enter.

Your folder structure should now look like this:

recipe-app
|-- backend
    |-- package.json

Step 3: Install the Dependencies

First, install the necessary dependencies using the following command:

npm install express prisma @prisma/client cors

Now, install the development dependencies:

npm install --save-dev ts-node typescript nodemon @types/cors @types/express @types/node

Your folder structure should now look like this:

recipe-app
|-- backend
    |-- node_modules
    |-- package.json
    |-- package-lock.json

Step 4: Setup Your Backend Code

In the backend folder, create a new folder named src. Inside src, create a file named index.ts.

Add the following code to index.ts:

import express from "express";
import cors from "cors";

const app = express();

app.use(express.json());
app.use(cors());

app.get("/api/recipe/search", async (req, res) => {
  res.json({ message: "success" });
});

app.listen(5000, () => {
  console.log("Server running on localhost:5000");
});

Step 5: Add the Start Script

First, open package.json in the backend folder. In the scripts section, replace the test script with a start script as follows:

"scripts": {
    "start": "npx nodemon ./src/index.ts"
}

Step 6: Run Your Backend

In the terminal, ensure you are in the backend folder, then type npm start to run your backend server.

Then open a browser and go to http://localhost:5000/api/recipe/search. You should see a response with the message success.

Congratulations! You have successfully set up and run your backend server. In the next part of this tutorial, we will focus on setting up the frontend and connecting to a database.

How to Setup the Database and Prisma

In this section, we'll focus on setting up a Postgres database using ElephantSQL and integrating Prisma to interact with our database effortlessly. Let's jump right in!

Step 1: Set Up the ElephantSQL Database

Start by navigating to ElephantSQL. Click on "Get a managed database today," followed by selecting the "Tiny Turtle" plan for a free instance.

Sign in or create an account to proceed to the "Create new instance" page.

Then enter a name for your database (for example, recipe-app-db), keep the plan on the free tier, and choose a region closest to you.

Click on "Review," verify the details, and then click on "Create instance."

Step 2: Retrieve the Database Credentials

Once your instance is created, click on it to view the details.

Locate and copy the URL under the "Details" section. This URL contains the credentials needed to connect to your database.

Step 3: Create an Environment File

Now, return to your code editor and open the backend folder.

Create a new file named .env. Inside the .env file, add the following line:

DATABASE_URL=<Your-Copied-Database-URL>

Replace <Your-Copied-Database-URL> with the URL you copied from ElephantSQL.

Step 4: Integrate Prisma

Stop your server if it's running by pressing Ctrl + C (or Cmd + C on Mac) in the terminal.

In the terminal, ensure you are in the backend directory, and type the following command to initialize Prisma:

npx prisma init

This command will create a new folder named prisma with a file named schema.prisma.

Step 5: Verify Prisma Integration

Now, open prisma/schema.prisma to ensure your DATABASE_URL has been detected correctly.

Start your server again with the command npm start. Then navigate to http://localhost:5000/api/recipe/search in your browser to ensure your API still works and returns the success message.

Your folder structure should now include the Prisma folder and look like this:

recipe-app
|-- backend
    |-- prisma
        |-- schema.prisma
    |-- .env
    |-- ...

How to Get and Secure a Spoonacular API Key

Step 1: Obtain an API Key from Spoonacular

To do this, navigate to Spoonacular and click on "Start Now." Sign up for an account and proceed to the dashboard.

Within the dashboard, click on “Profile” on the left-hand side, and find the section related to API keys. Generate a new API key, and copy it to your clipboard.

Step 2: Store the API Key Securely

Now, return to your code editor and open the .env file in the backend folder.

Add a new environment variable to store your API key as follows:

API_KEY=<Your-Copied-API-Key>

Replace <Your-Copied-API-Key> with the API key you copied from Spoonacular.

Step 3: Install and Setup Thunder Client

In Visual Studio Code, navigate to the extensions tab (square icon on the sidebar), and search for Thunder Client.

Install Thunder Client by clicking on the Install button. Once installed, you will see a new icon (a purple thunderbolt) on the sidebar.

Step 4: Test the API Key with Thunder Client

Now click on the Thunder Client icon, then click on “New Request.”

Copy the endpoint URL for searching recipes from Spoonacular's documentation. It should look something like this: https://api.spoonacular.com/recipes/complexSearch.

Paste this URL into the URL bar in Thunder Client.

Under the query tab, add two new parameters:

• apiKey with the value of your API key.
• query with a value of burgers or any other term you wish to search for.

Then hit "Send" to issue the request and observe the response. You should see a list of recipes related to the term you searched for, indicating that your API key and endpoint are working correctly.

How to Create the Search Endpoint

Step 1: Setup a New File for Recipe API Logic

First, navigate to your backend/src folder and create a new file named recipe-api.ts.

In recipe-api.ts, initiate a constant to store your API key from the environment variables:

const API_KEY = process.env.API_KEY;

Step 2: Create the searchRecipes Function

In recipe-api.ts, define a new asynchronous function searchRecipes that takes in a searchTerm and a page as parameters:

export const searchRecipes = async (searchTerm: string, page: number) => {
  if (!API_KEY) {
    throw new Error("API key not found");
  }

  const baseURL = "https://api.spoonacular.com/recipes/complexSearch";
  const url = new URL(baseURL);

  const queryParams = {
    apiKey: API_KEY,
    query: searchTerm,
    number: 10,
    offset: (page - 1) * 10,
  };

  url.search = new URLSearchParams(queryParams).toString();

  try {
    const searchResponse = await fetch(url.toString());
    const resultsJson = await searchResponse.json();
    return resultsJson;
  } catch (error) {
    console.error(error);
  }
};

Step 3: Import and Use searchRecipes in index.ts

Now, navigate back to index.ts in the backend/src folder.

Import the searchRecipes function from recipe-api.ts at the top of your file:

import * as RecipeAPI from "./recipe-api";

Locate the endpoint where you wish to utilize the searchRecipes function, and modify it to call searchRecipes with the appropriate arguments, then return the results:

app.get("/api/recipe/search", async (req, res) => {
  const searchTerm = req.query.searchTerm as string;
  const page = parseInt(req.query.page as string);

  const results = await recipeAPI.searchRecipes(searchTerm, page);
  return res.json(results);
});

Step 4: Test Your Endpoint

Now you can restart your server by stopping it (Ctrl + C or Cmd + C on Mac) and then running npm start.

Test your endpoint by sending a GET request with the appropriate query parameters. For example, navigate to http://localhost:5000/api/recipe/search?searchTerm=burgers&page=1 in your browser or use a REST client like Postman or Thunder Client.

You should now see a list of recipes returned in response to your request, indicating that your backend logic for calling the Recipe API and returning the results is functioning as expected.

How to Setup the Frontend

Before you get started on this section, make sure you're in the top-level directory of your project, which in this case is named recipe-app.

Step 1: Create a React App with Vite

Start by opening a terminal and ensuring you're in the top-level folder (recipe-app).

Then run the following command to install the latest version of Vite:

npm install vite@latest --save-dev

Now, initiate a new React project with Vite by running:

npx create-vite frontend --template react-ts

This command creates a new folder named frontend, sets it up as a React project, and specifies TypeScript as the language.

Step 2: Navigate to Your New React App

Change your directory to the frontend folder:

cd frontend

Then install any necessary dependencies:

npm install

Step 3: Start the Development Server

You can start the development server with the following command:

npm run dev

Your default web browser should now open displaying the initial setup of your React app, which includes a counter example.

Step 4: Clean Up and Personalize Your React App

Now you can head back to your code editor and navigate to frontend/src. Delete the index.css file as it won't be needed.

In main.tsx, remove the import statement for index.css.

Now, open App.tsx. Here, you'll see code for a counter. Delete all the content inside App.tsx.

Let’s start fresh by adding the following code to App.tsx:

// src/App.tsx
const App = () => {
  return <div>Hello from Recipe App</div>;
};

export default App;

Save App.tsx and check your browser to see the updated text: "Hello from Recipe App".

Step 5: Setup Your Stylesheet

Go to src and open App.css. Delete all the pre-filled styles but keep the .root class definition empty for now.

Add a font-family property to the .root class:

/* src/App.css */
.root {
  font-family: Helvetica, Arial, sans-serif;
}

Then head back to App.tsx and import your stylesheet:

// src/App.tsx
import "./App.css";

const App = () => {
  // ...
};

Then save App.tsx and check your browser to see the updated font.

Now you have a clean slate to start building out the frontend of your recipe app with React and TypeScript, using Vite as your build tool. As you progress, you can now start adding components, routing, and state management to build out your application's UI and functionality.

How to Call the Search API and Display Results on the Frontend

Now we are going to fetch data from an API and display the results on the UI. The API response is structured as follows:

{
  "results": [
    {
      "id": 650235,
      "title": "Loaded Turkey Burgers",
      "image": "https://spoonacular.com/recipeImages/650235-312x231.jpg",
      "imageType": "jpg"
    }
    // ... other recipes
  ],
  "offset": 10,
  "number": 10,
  "totalResults": 50
}

Each recipe object contains an id, title, image, and imageType. We will map through the results array and display each recipe on our UI.

Step 1: Setup State

In your App.tsx, setup state to store the searchTerm and recipes.

import React, { useState } from "react";

const App = () => {
  const [searchTerm, setSearchTerm] = useState("");
  const [recipes, setRecipes] = useState([]);

  // ... rest of your component
};

Step 2: Fetch Data from the API

Create a new file API.ts and set up a function to make the API call.

// src/API.ts
const searchRecipes = async (searchTerm: string, page: number) => {
  const baseURL = new URL("http://localhost:5000/api/recipes/search");
  baseURL.searchParams.append("searchTerm", searchTerm);
  baseURL.searchParams.append("page", page.toString());

  const response = await fetch(baseURL.toString());

  if (!response.ok) {
    throw new Error(`HTTP Error: ${response.status}`);
  }

  return response.json();
};

export { searchRecipes };

Step 3: Create a Handler Function

Back in App.tsx, import the searchRecipes function and create a handler function to be called when the form is submitted.

import React, { useState, FormEvent } from "react";
import { searchRecipes } from "./API";

const App = () => {
  // ... previous state setup

  const handleSearchSubmit = async (event: FormEvent) => {
    event.preventDefault();

    try {
      const { results } = await searchRecipes(searchTerm, 1);
      setRecipes(results);
    } catch (error) {
      console.error(error);
    }
  };

  // ... rest of your component
};

Step 4: Display the Data

Display the recipes data in your component's return statement.

const App = () => {
  // ... previous code

  return (
    <div>
      <form onSubmit={handleSearchSubmit}>
        <button type="submit">Submit</button>
      </form>
      {recipes.map((recipe) => (
        <div key={recipe.id}>
          Recipe Image Location: {recipe.image}
          <br />
          Recipe Title: {recipe.title}
        </div>
      ))}
    </div>
  );
};

export default App;

Step 5: Define the Recipe Type

Define a Recipe interface in a new file named types.ts.

// src/types.ts
export interface Recipe {
  id: number;
  title: string;
  image: string;
  imageType: string;
}

Back in App.tsx, import the Recipe interface and use it to type your state and map function.

import React, { useState, FormEvent } from 'react';
import { searchRecipes } from './API';
import { Recipe } from './types';

const App = () => {
  const [searchTerm, setSearchTerm] = useState('');
  const [recipes, setRecipes] = useState<Recipe[]>([]);

  // ... rest of your code

  return (
    <div>
      {/* ... rest of your return statement */}
      {recipes.map((recipe: Recipe) => (
        {/* ... rest of your map function */}
      ))}
    </div>
  );
};

export default App;

Step 6: Test Your Setup

Now, start both your frontend and backend servers. Open your browser, navigate to your app, and try submitting a search. You should see the recipes data displayed on the screen.

# In one terminal
cd frontend
npm run dev

# In another terminal
cd backend
npm start

This setup should now fetch and display recipe data based on the hardcoded searchTerm of "burgers". In a real-world scenario, you'd replace the hardcoded searchTerm with a dynamic value from a user input field.

How to Create the Search Input and Recipe Card Component

Step 1: Setup Your Project

Start by setting up a new React project or navigate to your existing project directory.

npx create-react-app recipe-search-ui
cd recipe-search-ui

Step 2: Create State Hooks for User Input and Data

In your src folder, create a new file named App.tsx and import the necessary dependencies:

import React, { useState, FormEvent } from "react";

function App() {
  const [searchTerm, setSearchTerm] = useState < string > "";
  const [recipes, setRecipes] = useState < Array < any >> [];

  // ... rest of the code
}

export default App;

Here, we have set up two state hooks: one for capturing the user's search term and another for holding the recipe data returned from the backend.

Step 3: Build a Form to Capture User Input

Within the App component, build a form with an input field and a submit button. We'll also create a function to handle the form submission, which will trigger the API call.

function App() {
  // ... rest of the code

  const handleSearchSubmit = async (event: FormEvent) => {
    event.preventDefault();
    // ... API call logic
  };

  return (
    <div>
      <form onSubmit={handleSearchSubmit}>
        <input
          type="text"
          required
          placeholder="Enter a search term"
          value={searchTerm}
          onChange={(event) => setSearchTerm(event.target.value)}
        />
        <button type="submit">Submit</button>
      </form>
      {/* ... rest of the code */}
    </div>
  );
}

Now, users can enter their search term, and upon form submission, handleSearchSubmit will be triggered.

Step 4: Fetch Recipe Data from the Backend

In the handleSearchSubmit function, use the fetch API to send a request to your backend, capture the returned data, and update the recipes state.

const handleSearchSubmit = async (event: FormEvent) => {
  event.preventDefault();
  try {
    const response = await fetch(
      `http://localhost:5000/api/recipes/search?searchTerm=${searchTerm}`
    );
    if (!response.ok) {
      throw new Error(`HTTP error! status: ${response.status}`);
    }
    const data = await response.json();
    setRecipes(data.results);
  } catch (error) {
    console.error(error);
  }
};

Step 5: Display Recipe Data

Create a new folder named components in your src directory. Inside this folder, create a file named RecipeCard.tsx. This component will display individual recipe data.

import { Recipe } from "../types";

interface Props {
  recipe: Recipe;
}

const RecipeCard = ({ recipe }: Props) => {
  return (
    <div className="recipe-card">
      <img src={recipe.image}></img>
      <div className="recipe-card-title">
        <h3>{recipe.title}</h3>
      </div>
    </div>
  );
};

export default RecipeCard;

Now, go back to App.tsx and import RecipeCard. Map over the recipes state to display each recipe using the RecipeCard component.

import RecipeCard from "./components/RecipeCard";

// ... rest of the code

return (
  <div>
    {/* ... rest of the code */}
    {recipes.map((recipe) => (
      <RecipeCard key={recipe.id} recipe={recipe} />
    ))}
  </div>
);

Now, when you submit a search term, the UI will display a list of recipe cards containing the images and titles of the recipes returned from the backend.

Step 6: Test Your UI

Run your React app, enter a search term such as "pasta" or "burgers", and submit the form. You should see a list of recipe cards displaying the relevant recipes from the backend.

npm start

Navigate to http://localhost:3000 in your browser and try out your new recipe search UI!

How to Build the Pagination and View More Functionality

Step 1: Backend Pagination

We added a page query parameter in the search endpoint. The page value is used to calculate the offset for the recipe data fetched from the database or external API.

Step 2: Add "View More" Button to the UI

Navigate to your App.tsx file. Scroll down to the JSX code where you map through the recipes array and add a "View More" button below it.

// ... other code
{
  recipes.map((recipe) => <RecipeCard key={recipe.id} recipe={recipe} />);
}
<button className="view-more" onClick={handleViewMoreClick}>
  View More
</button>;
// ... other code

Step 3: Create a useRef Hook to Store the Current Page Number

Above your component's return statement, create a useRef hook to keep track of the current page number without causing re-renders.

// ... other code
const pageNumber = useRef(1);
// ... other code

Step 4: Implement the handleViewMoreClick Function

Define a function called handleViewMoreClick to handle the logic for loading more recipes.

// ... other code
const handleViewMoreClick = async () => {
  try {
    const nextPage = pageNumber.current + 1;
    const nextRecipes = await api.searchRecipes(searchTerm, nextPage);
    setRecipes((prevRecipes) => [...prevRecipes, ...nextRecipes.results]);
    pageNumber.current = nextPage;
  } catch (error) {
    console.error(error);
  }
};
// ... other code

Step 5: Reset Page Number on New Search

Modify your handleSearchSubmit function to reset the page number back to 1 whenever a new search term is entered.

// ... other code
const handleSearchSubmit = async (event: FormEvent) => {
  // ... other code
  setRecipes(recipes.results);
  pageNumber.current = 1;
};
// ... other code

Step 6: Test Your Implementation

Run your app and perform a search. Click the "View More" button to load more results. Change the search term and ensure that the page number resets, and you get a fresh list of recipes.

npm start

Now, as you search for recipes and click "View More," you should see additional recipes being loaded and displayed in the UI.

How to Build the Recipe Summary Modal Component

I'll walk you through this process step-by-step. We'll create a model that displays a recipe summary using a specific endpoint from the provided API.

Step 1: Understanding the Recipe Summary Endpoint

You can understand where the summary data comes from by looking at your API documentation. The endpoint you need is called Summarize Recipe. This endpoint requires a recipe ID to generate a summary.

Step 2: Setup Backend Endpoint

Create a backend endpoint that interfaces with the Summarize Recipe endpoint.

// In your backend, create an endpoint to fetch recipe summary
// File: recipe-api.ts
export const getRecipeSummary = async (recipeId: string) => {
  if (!apiKey) {
    throw new Error("API key not found");
  }

  const url = new URL(
    `https://api.spoonacular.com/recipes/${recipeId}/summary`
  );
  const params = { apiKey: apiKey };
  url.search = new URLSearchParams(params).toString();

  const response = await fetch(url.toString());
  const json = await response.json();
  return json;
};

Step 3: Create a Backend Route

Create a route in your backend to handle requests to your new endpoint.

// File: index.ts
app.get("/api/recipe/:recipeId/summary", async (req, res) => {
  const recipeId = req.params.recipeId;
  const result = await recipeSummary(recipeId);
  res.json(result);
});

Step 4: Create the Recipe Modal Component

Create a React component for the recipe modal. We will use the useEffect hook to call the backend endpoint we just created, and store the Recipe Summary data in state.

First add a type for RecipeSummary to types.ts

export interface RecipeSummary {
  id: number;
  title: string;
  summary: string;
}

// File: RecipeModal.tsx
import React, { useState, useEffect } from "react";
import { RecipeSummary } from "../types";

interface Props {
  recipeId: string;
  onClose: () => void;
}

const RecipeModal: React.FC<Props> = ({ recipeId, onClose }) => {
  const [recipeSummary, setRecipeSummary] =
    (useState < RecipeSummary) | (null > null);

  useEffect(() => {
    const fetchRecipeSummary = async () => {
      try {
        const summary = await getRecipeSummary(recipeId);
        setRecipeSummary(summary);
      } catch (error) {
        console.error(error);
      }
    };
    fetchRecipeSummary();
  }, [recipeId]);

  return (
    <div className="overlay">
      <div className="modal">
        <div className="modal-content">
          <div className="modal-header">
            <h2>{recipeSummary?.title}</h2>
            <span className="close-button" onClick={onClose}>
              &times;
            </span>
          </div>
          <p dangerouslySetInnerHTML={{ __html: recipeSummary?.summary }} />
        </div>
      </div>
    </div>
  );
};

export default RecipeModal;

Step 5: Style the Modal

Add the following CSS to style the modal:

/* File: app.css */
.overlay {
  position: fixed;
  top: 0;
  left: 0;
  width: 100%;
  height: 100%;
  background-color: rgba(0, 0, 0, 0.7);
  z-index: 1;
}

.modal {
  position: fixed;
  top: 50%;
  left: 50%;
  transform: translate(-50%, -50%);
  z-index: 2;
  background-color: white;
  padding: 2em;
  border-radius: 4px;
  max-width: 500px;
}

.modal-header {
  display: flex;
  flex-direction: row;
  align-items: center;
  justify-content: space-between;
}

Step 6: Render and Handle Modal Interactions

Modify your main component to handle rendering and interactions with the modal.

// File: App.tsx
const App: React.FC = () => {
  const [selectedRecipe, setSelectedRecipe] =
    (useState < Recipe) | (undefined > undefined);

  return (
    <div className="App">
      {/* Other components and logic */}
      {selectedRecipe && (
        <RecipeModal
          recipeId={selectedRecipe.id.toString()}
          onClose={() => setSelectedRecipe(undefined)}
        />
      )}
    </div>
  );
};

export default App;

Now, when a user clicks on a recipe, the modal will appear displaying the summary of the selected recipe. The modal can be closed by clicking the close button, which will set the selectedRecipe back to undefined, hiding the modal.

How to Create Endpoints to Get/Create/Delete Favorite Recipes

Step 1: Setup the Database

First, we need to extend our database schema to include a table for storing favorite recipes by their IDs.

First, navigate to the Prisma folder within the backend directory of your project. Then open the schema.prisma file.

Define a new model for favorite recipes as follows:

model FavoriteRecipe {
  id        Int    @id @default(autoincrement())
  recipeId  Int    @unique
}

Step 2: Synchronize the Database Schema

Now, let's synchronize the updated schema with our database.

cd backend
npx prisma db push

Step 3: Setup the Endpoints

We need to set up endpoints in our Node backend to handle creating, viewing, and deleting favorites. We'll use the prismaClient to help us perform crud operations on the database.

First, we'll create a new post endpoint like this:

// In backend/index.ts
import { PrismaClient } from "@prisma/client";

const prismaClient = new PrismaClient();

app.post("/api/recipes/favorite", async (req, res) => {
  const { recipeId } = req.body;
  try {
    const favoriteRecipe = await prismaClient.favoriteRecipe.create({
      data: { recipeId },
    });
    res.status(201).json(favoriteRecipe);
  } catch (error) {
    console.error(error);
    res.status(500).json({ error: "Oops, something went wrong." });
  }
});

Next, we'll create the View endpoint. To do that, create a utility function to fetch recipe details by IDs:

// In backend/src/recipe-api.ts
export const getFavoriteRecipesByIds = async (ids: string[]) => {
  if (!apiKey) {
    throw new Error("API Key not found");
  }
  const url = new URL("https://api.spoonacular.com/recipes/informationBulk");
  url.search = new URLSearchParams({
    apiKey: apiKey,
    ids: ids.join(","),
  }).toString();

  const response = await fetch(url);
  const json = await response.json();
  return { results: json };
};

Now, create the endpoint to fetch all favorite recipes:

// In backend/index.ts
import { getFavoriteRecipesByIds } from "./src/recipe-api";

app.get("/api/recipes/favorite", async (req, res) => {
  try {
    const favoriteRecipes = await prismaClient.favoriteRecipe.findMany();
    const recipeIds = favoriteRecipes.map((recipe) =>
      recipe.recipeId.toString()
    );
    const favorites = await getFavoriteRecipesByIds(recipeIds);
    res.json(favorites);
  } catch (error) {
    console.error(error);
    res.status(500).json({ error: "Oops, something went wrong." });
  }
});

Next up is the Delete endpoint:

// In backend/index.ts
app.delete("/api/recipes/favorite", async (req, res) => {
  const { recipeId } = req.body;
  try {
    await prismaClient.favoriteRecipe.delete({
      where: { recipeId },
    });
    res.status(204).send();
  } catch (error) {
    console.error(error);
    res.status(500).json({ error: "Oops, something went wrong." });
  }
});

Step 4: Test the Endpoints

You can use tools like Postman or Thunder Client to test the endpoints. Make sure to adjust the request method and URL accordingly, and provide the necessary request body or parameters.

• Creating a Favorite: POST request to /api/recipes/favorite with recipeId in the body.
• Viewing Favorites: GET request to /api/recipes/favorite.
• Deleting a Favorite: DELETE request to /api/recipes/favorite with recipeId in the body.

Step 5: Verify the Database

Check the favoriteRecipes table in your ElephantSQL database to verify the actions performed through the endpoints.

How to Add Favorites Functionality to the Frontend

Step 1: Setup the Tab Functionality

Next, we'll look at how to integrate these endpoints on the frontend. We'll start by setting up tabs for 'Search' and 'Favorites' in our app.

First, define a new state to keep track of the selected tab.

import React, { useState } from "react";

type Tabs = "search" | "favorites";

function App() {
  const [selectedTab, setSelectedTab] = useState < Tabs > "search";

  // Rest of your code...
}

Step 2: Render Tabs

Now you'll render tabs in your JSX, and handle tab switching with the onClick event. This makes each <h1> element is clickable, and saves the tab the user clicked on in state. This helps conditionally render different UI elements depending on their selection.

// Inside your JSX...
<div className="tabs">
  <h1 onClick={() => setSelectedTab("search")}>Recipe Search</h1>
  <h1 onClick={() => setSelectedTab("favorites")}>Favorites</h1>
</div>

Step 3: Conditional Rendering

Based on the selected tab, you want to conditionally render either the search component or the favorites component. This will show/hide either the "search section" or the "favourites" section depending on the selectedTab state variable.

{selectedTab === 'search' && (
  // search component code...
)}
{selectedTab === 'favorites' && (
  // favorites component code...
)}

Step 4: Fetch Favorite Recipes

Next we need to populate the Favorite recipes tab with our favourite recipes. We want to do this when the App loads, for a quick user experience.

To do this, fetch the favorite recipes from the backend when the app loads using the useEffect hook, and store the fetched favorite recipes in a new state.

// api.ts
export const getFavouriteRecipes = async () => {
  const url = new URL("http://localhost:5000/api/recipes/favourite");
  const response = await fetch(url);

  if (!response.ok) {
    throw new Error(`HTTP error! Status: ${response.status}`);
  }
  return response.json();
};

//App.tsx
import React, { useEffect, useState } from "react";

// ... Rest of your imports and code

function App() {
  // ... Rest of your state declarations

  const [favoriteRecipes, setFavoriteRecipes] = useState([]);

  useEffect(() => {
    const fetchFavoriteRecipes = async () => {
      try {
        const favouriteRecipes = await api.getFavouriteRecipes();
        setFavouriteRecipes(favouriteRecipes.results);
      } catch (error) {
        console.error(error);
      }
    };

    fetchFavoriteRecipes();
  }, []);

  // ... Rest of your code
}

Step 5: Render Favorite Recipes

Now you need to render the favorite recipes in the 'Favorites' tab.

{selectedTab === 'favorites' && (
  <div>
    {favoriteRecipes.map(recipe => (
      // Render each favorite recipe card...
    ))}
  </div>
)}

Step 6: Add a Heart Icon

Next we'll add a way for the user to add and remove favorites. We'll do this by adding a "heart" icon to each card.

Before diving into the code, ensure you are in the correct directory by navigating to your project's front-end directory in your terminal. Install the necessary package for icons by running:

npm install react-icons

Step 7: Import the Icon

Open the RecipeCard component, and import the heart icon at the top of your file:

import { AiOutlineHeart } from "react-icons/ai";

Step 8: Insert the icon

In the RecipeCard component, add the heart icon within a span element just above the h3 tag:

<span onClick={handleFavoriteClick}>
  <AiOutlineHeart size={25} />
</span>

Step 9: Add CSS Styling

In your App.css file, add the following CSS to style the icon and ensure it appears on the same line as the title. Using flex and align-items means the icon and title will be aligned nicely beneath the image:

.recipe-card-title {
  display: flex;
  align-items: center;
  gap: 0.5rem;
}

Step 10: Create an Add Favourite Event Handler

In App.tsx, create an event handler for favoriting a recipe. This is what will get called when the user clicks the heart icon on an recipe that hasn't yet been favorited:

const addfavoriteRecipe = async (recipe) => {
  try {
    await API.addFavoriteRecipe(recipe);
    setFavoriteRecipes([...favoriteRecipes, recipe]);
  } catch (error) {
    console.log(error);
  }
};

Step 11: API Logic

In a new file called API.ts, create a function to handle the API call to save a favorite recipe. This will call our endpoint which we created earlier in the backend:

export const addFavoriteRecipe = async (recipe) => {
  const body = {
    recipeId: recipe.id,
  };
  const response = await fetch("http://localhost:5000/api/recipes/favourite", {
    method: "POST",
    headers: {
      "Content-Type": "application/json",
    },
    body: JSON.stringify(body),
  });
  if (!response.ok) {
    throw new Error("Failed to save favorite");
  }
};

Step 12: Hook Up the Event Handler

Pass the event handler to the RecipeCard component:

<RecipeCard
  //.. other props
  onFavoriteButtonClick={favoriteRecipe}
/>

Step 13: Create the Remove Favorite Event Handler

Similarly, create an event handler for un-favoriting a recipe in App.tsx:

const removeFavoriteRecipe = async (recipe) => {
  try {
    await API.removeFavoriteRecipe(recipe);
    const updatedRecipes = favoriteRecipes.filter(
      (favRecipe) => favRecipe.id !== recipe.id
    );
    setFavoriteRecipes(updatedRecipes);
  } catch (error) {
    console.log(error);
  }
};

Step 14: API Logic

In API.ts, create a function to handle the API call to remove a favorite recipe. Again, this will call the backend API to remove a recipe which we created earlier:

export const removeFavoriteRecipe = async (recipe) => {
  const body = {
    recipeID: recipe.id,
  };
  const response = await fetch("http://localhost:5000/api/recipes/favourite", {
    method: "DELETE",
    headers: {
      "Content-Type": "application/json",
    },
    body: JSON.stringify(body),
  });
  if (!response.ok) {
    throw new Error("Failed to remove favorite");
  }
};

Step 15: Conditional Event Handler

Depending on if the user is "favoriting" or "unfavoriting" a recipe, we want to conditionally call either addFavoriteRecipe or removeFavoriteRecipe based on the favorited state:

<RecipeCard
  //.. other props
  onFavoriteButtonClick={isFavorite ? removeFavoriteRecipe : favoriteRecipe}
/>

Step 16: Determine the Favorited State

Before we can display the heart icon a favorited/non-favorited state, we need to know if the recipe is already a or not.

To do this, we determine whether a recipe is favorited by checking if it exists in the favoriteRecipes state array. Pass this information to RecipeCard:

const isFavorite = favoriteRecipes.some(
  (favRecipe) => favRecipe.id === recipe.id
);
<RecipeCard
  // ...other props
  isFavorite={isFavorite}
/>;

Step 17: Display the Favorited State

In RecipeCard, conditionally render a filled or outlined heart icon based on the isFavorite prop:

{
  isFavorite ? (
    <AiFillHeart size={25} color="red" />
  ) : (
    <AiOutlineHeart size={25} />
  );
}

How to Add CSS/Styling

Step 1: Prepare the Hero Image

We've added some basic styling so far, so lets complete the CSS so that our app looks polished!

Firstly obtain an image from a source like Pexels or any other image repository. This will be used in the Hero section of our app at the top, and will have our title overlaid on it. Ensure the image has a horizontal orientation for better handling of aspect ratios.

Place the image in the public folder of your project.

project-folder
│
└───public
    │   hero-image.jpeg

Step 2: Structure the Header

Open app.tsx and locate the JSX markup. Add a className of app-container to the top div element.

Inside the app-container div, add a new div with a className of header. Within the header div, add an img element with a src attribute pointing to your image, and a div element with a className of title containing the app's title.

<div className="app-container">
  <div className="header">
    <img src="/hero-image.jpeg" alt="Hero" />
    <div className="title">My Recipe App</div>
  </div>
  {/* ...rest of your code */}
</div>

Step 3: Style the Header

Open app.css and scroll to the top. Add the following CSS to style the app-container, header, img, and title elements. This makes the title appear on top of the image, with a translucent background:

.app-container {
  display: flex;
  flex-direction: column;
  gap: 2em;
}

.header {
  position: relative;
}

.header img {
  width: 100%;
  height: 500px;
  object-fit: cover;
  object-position: center;
  opacity: 0.5;
  border-radius: 1em;
}

.title {
  position: absolute;
  top: 50%;
  left: 50%;
  transform: translate(-50%, -50%);
  color: white;
  font-size: 2em;
  text-align: center;
  background-color: black;
  opacity: 0.8;
  padding: 0.5em 1.5em 0.5em 1.5em;
}

Step 4: Adjust the Layout

Add padding to the body element and use a media query to add margins on larger screens. We do this so our app doesn't appear to narrow on mobile devices. When the screen size reaches 768px, the media query will kick in and add margin to the left and right of our app, so that the app doesn't appear too wide.

body {
  padding: 5em 0;
  height: 100vh;
  background-color: #f0f0f0; /* or any color you prefer */
}

@media (min-width: 768px) {
  body {
    margin-left: 10em;
    margin-right: 10em;
  }
}

Step 5: Style Tabs Underline

Currently its not clear which tab the user has selected. What we want to do is add an orange underline to the selected tab. To do this, we can use a combination of CSS classes and conditional rendering.

Within app.tsx, locate your h1 elements representing tabs, and dynamically apply a className of tab-active based on the selected tab.

<h1 className={selectedTab === 'search' ? 'tab-active' : ''}>Search</h1>
<h1 className={selectedTab === 'favorites' ? 'tab-active' : ''}>Favorites</h1>

In app.css, define the tab-active class:

.tab-active {
  border-bottom: 4px solid orange; /* or any color you prefer */
  padding-bottom: 0.5em;
}

Step 6: Style the Search Bar

We want our search bar to take up the width of the container, and we want to add an icon instead of the search button, which makes our UI more interesting.

In app.tsx, locate the form element within the Search tab. Replace the text "Submit" in the button element with an icon from a library like React Icons.

<button>
  <AiOutlineSearch size={40} />
</button>

In app.css, style the form, input, and button elements:

form {
  display: flex;
  background-color: white;
  align-items: center;
}

input {
  padding: 0.5em;
  font-size: 2em;
  flex: 1;
  border: none;
}

input:focus {
  outline: none;
}

button {
  background-color: white;
  border: none;
  cursor: pointer;
}

Step 7: Implement a Responsive Recipe Card Grid

Currently our Recipe Cards are stacked horizontally. We'll use CSS grid to make the recipe cards appear in a grid layout, which will also make things more responsive.

Within app.tsx, create a new div with a className of recipe-grid just above where you map over your recipes, and place the logic to rendering the recipes inside this div.

<div className="recipe-grid">
  {recipes.map((recipe) => {
    const isFavourite = favouriteRecipes.some((favRecipe) => favRecipe.id === recipe.id);

    return (
      <RecipeCard
        key={recipe.id}
        recipe={recipe}
        onFavouriteButtonClick={isFavourite ? removeFavouriteRecipe : addFavouriteRecipe}
        onClick={() => setSelectedRecipe(recipe)}
        isFavourite={isFavourite}
      />
    );
  })}
</div>

In app.css, style the recipe-grid and recipe-card elements:

.recipe-grid {
  display: grid;
  grid-template-columns: repeat(auto-fill, minmax(400px, 1fr));
  gap: 2em;
}

.recipe-card {
  display: flex;
  flex-direction: column;
  justify-content: space-evenly;
  background-color: white;
  padding: 1em;
  box-shadow: 0 4px 12px rgba(0, 0, 0, 0.1);
  position: relative;
  cursor: pointer;
  gap: 1.5em;
}

.recipe-card h3 {
  font-size: 1.5em;
  margin: 0;
  white-space: nowrap;
  overflow: hidden;
  text-overflow: ellipsis;
}

Step 8: Final Touches

Style the "View More" button to make sure it matches the style of our app, and is centered beneath our recipe grid:

.view-more-button {
  font-size: 1.5em;
  padding: 1em;
  font-weight: bold;
  margin: auto;
}

Conclusion

Congrats on making it to the end! Hopefully you've learned a few things about full stack development using React and Node.

If you enjoyed this project, you can find more at CodeCoyotes.com, where you can also send me a message if you need to get in contact.

Thanks for reading, see you in the next one!

We just posted a full PostgreSQL course on the freeCodeCamp.org YouTube channel. It covers PostgreSQL from the ground up. Alexandru Cristian created this course. He has created courses on Udemy and the freeCodeCamp channel.

Here is what is covered in this course:

PostgreSQL Introduction

• A deep dive into what PostgreSQL is and its distinct advantages.

Windows Installation - PostgreSQL and PgAdmin with Database Setup

• Step-by-step installation guide.
• Setting up your first database with PgAdmin.

Diving into the SELECT statement

• Fundamentals and use-cases of the SELECT statement.

SELECT Challenge

• Put your knowledge to the test with hands-on challenges.

Exploring SELECT DISTINCT and Challenges

• Understanding the role of DISTINCT in queries.
• Engaging challenges to solidify your knowledge.

Delving into WHERE Clauses

• SELECT WHERE fundamentals and real-world examples.
• Engaging challenges to ensure you've grasped the concept.

Diving Deeper: Advanced Query Structures

• Introduction to COUNT, ORDER BY, LIMIT, and the BETWEEN Statement.
• Exploring the IN Statement and understanding the LIKE and ILIKE clauses.

General Challenge

• A comprehensive challenge covering all the topics so far.

Aggregate Functions and Beyond

• Introduction to functions that allow data analysis.
• Deep dive into GROUP BY - from basic introduction to practical examples and challenges.
• Unveiling the HAVING command and the AS Statement.

This course is ideally suited for a diverse range of people. Developers interested in enhancing their database skills will find it particularly beneficial, as will aspiring Database Administrators looking to specialize in PostgreSQL. Also, computer science students eager for hands-on experience and tech enthusiasts curious about the intricacies of relational databases will also greatly benefit from the content provided.

Watch the full course on the freeCodeCamp.org YouTube channel (3-hour watch).

We'll start from scratch and end up with a fully functioning app where you can create, edit, and delete notes. Plus, we're adding validation on both the UI and the backend to keep things in check!

This guide is all about giving you the real-deal experience of building a web app. You'll get to learn how each piece of the puzzle fits together – from the front-end magic with React, to the server-side wonders with Node.js, and storing all the good stuff in a PostgreSQL database. And hey, we're making sure it looks good and works well on mobile screens too!

By the end of this, you’ll have a good grip on full stack development with React and Node, which you can carry with you into future projects. It’s all about learning by doing, and getting the skills to make your ideas come to life. So, grab a cup of coffee, roll up your sleeves, and let’s get coding!

Prerequisites

Since we will be focusing on how to build a project, there are a few prerequisites that will be needed to get the most out of this tutorial:

• Some knowledge about web development concepts (frontend, backend, databases, API's, REST).
• Some knowledge of JavaScript (variables, functions, objects, arrays, and so on).
• Basic understanding on React (how to create components, add styles, work with state).
• Basic understanding on Node.js/Express (working with APIs).

Table of Contents

• What We'll Build
• Challenge: Try it Yourself First!
• Video Tutorial
• PART 1 – Create the UI
• PART 2 - Create the Backend
• PART 3 - Connect UI to Backend
• The End - Why not try the bonus challenges?

What We'll Build

In this tutorial, we'll build a full stack notes app from scratch, using React, Node.js and PostgreSQL, with the following features:

• Create/Edit/Delete Notes
• Validation on the UI and Backend
• Responsive on mobile screens

Challenge: Try it Yourself First!

If you would like to attempt this project yourself first without looking at the tutorial, here's a few hints:

• Tackle one little piece at a time. For example, you would focus on getting the UI working on the UI first, and tackle the APIs later.
• Think about your data – What do you need to store? What data structure (for example, arrays) will you use to return the data via the API? How will you render this data on the UI?
• Don't forget about validation and error handling. What will happen if the user tries to save a note without a title field? How will you prevent this? (Hint: Forms and the required field will be your friend here)
• Remember there is no perfect way to complete a project. The tutorial below is one way to tackle the problem. You can choose to go a different way, putting your own unique style on things. The main thing is that you start!

If you need more help on getting started yourself, you can find more hints and tips, starter code, and completed code you can reference over at codecoyotes.com.

Video Tutorial

PART 1 – Create the UI

We'll start this tutorial by creating the UI using some mock data. This lets us focus on our styling, and how things to look, without having to worry about creating a backend right away.

Create a New React App

Okay, first things first: let's set up our project structure. Open your terminal and navigate to your desktop. Our next step is to create a new folder that will hold both our UI and backend code. Let's name it notes-app:

mkdir notes-app

Once that's done, navigate into the newly created notes-app directory:

cd notes-app

Next, we'll create a new React app using TypeScript as our template. We'll use the npx create-react-app command for this, specifying TypeScript as the template:

npx create-react-app notes-app --template typescript

After you hit Enter, the process may take a few minutes to install all necessary packages. Once it's completed, open the notes-app folder in Visual Studio Code or your preferred IDE.

In Visual Studio Code, you should see that the notes-app is at the top level of your directory. As the course progresses, we will add a notes-app-server directory as well to keep all the code together in one place.

Now open a new terminal within your IDE and navigate to your React app's directory (let's assume you named it notes-app):

cd notes-app

Then, run the following command to start the front-end development server:

npm start

If all goes well, your browser will automatically open and display your new React app. You should see a spinning React logo, among other default assets.

Finally, let's clear out the boilerplate code to have a clean starting point for our app. Open src/App.tsx in your IDE and delete its content. This will be our new starting point for building the app.

Add UI Elements

Okay, the first thing we'll do is put some of our UI components in place. This will consist of the general markup and CSS, without involving any JavaScript. This gives us a glimpse of how we envision the layout, without having to concern ourselves with API calls or database interactions at this stage.

We'll navigate to .App.tsx and create a new component. Make sure to import our stylesheet from App.css. The first thing to add is a div with a class name of AppContainer. This will help position our form and the CSS grid for our notes.

Within this div, we'll include our form tags. Here, we'll add an input field for the title — this is where the user can enter the note title. We'll also include a textarea for the note content. Both of these fields will be set to required, enabling browser-native validation messages if the user tries to submit an incomplete form.

At the bottom of the form, we'll include a button of type submit, which will handle form submissions when clicked.

Off to the right, we'll add a div to contain our notes. This will be structured as a CSS grid. Initially, we'll populate this grid with a single note to see how it looks.

For our note, we'll have a header containing a delete button situated on the right-hand side. We'll display the user-entered title – for demonstration purposes, we're using a dummy title. We'll also include the content that the user entered.

Finally, we'll export our component at the very bottom.

Completed code for this section

import "./App.css";

const App = () => {
  return (
    <div className="app-container">
      <form className="note-form">
        <input placeholder="Title" required />
        <textarea placeholder="Content" rows={10} required />

        <button type="submit">Add Note</button>
      </form>
      <div className="notes-grid">
        <div className="note-item">
          <div className="notes-header">
            <button>x</button>
          </div>
          <h2>Note Title</h2>
          <p>Note content</p>
        </div>
      </div>
    </div>
  );
};

export default App;

Add CSS

Start the App

First, let's open a terminal and type npm start. This will launch the app in the browser. As you can see on the right-hand side, the appearance isn't great yet. This is because we haven't applied any styles. To fix this, we'll navigate to App.css and style the classes we added earlier.

You can download the styles from the link in the description if you prefer to copy and paste. Alternatively, feel free to follow along with the video and pause as needed. Remember, these styles are just examples for learning – they don't have to be perfect.

Style the Body and App Container

In App.css, the first thing we'll do is add some styles to the body. We'll give it a gray background and a margin to prevent the app from touching the browser window edges. Next, we'll style our App Container.

We're designing this mobile-first, meaning the default styles will target mobile screens. We'll use media queries for larger displays. This approach is optional, but often it's easier to start with mobile designs.

For mobile screens, we want our App Container to default to a single column layout, stacking our form and notes grid on top of each other.

Use Media Queries

We'll add a media query specifying that for screens larger than 600 pixels, we'll use a two-column layout. We'll define this using grid-template-columns.

The first column will be 200 pixels wide, accommodating the form. The second column will use 1fr, filling the remaining space. A 20-pixel gap will separate the two columns.

Style the Notes Grid

Next, let's style our notes grid. We'll use CSS grid and define grid-template-columns.

Each grid item will have a minimum width of 250 pixels and can expand to fill available space. Don't worry if this sounds confusing – it will become clear soon.

We'll also set grid-auto-rows to ensure each row is a minimum of 250 pixels tall, accommodating notes of different sizes while maintaining a consistent row height.

Style Individual Notes

For each note, we'll use Flexbox and set flex-direction to column, stacking the header, title, and content vertically. We'll also add some basic styles like border, padding, and background color. A box shadow will provide a finishing touch.

Style the Header and Delete Button

The header will also use Flexbox, and we'll set justify-content to flex-end to align the delete button to the right. The button will receive custom styles for a polished look.

Style the Form

Lastly, we'll style the form on the left column. Again, we'll use Flexbox with a column layout and a 20-pixel gap between elements. The text area and input fields will get borders, padding, and resized fonts. We'll also style the submit button and add hover effects.

Completed code for this section

body {
  margin: 20px;
  background-color: lightgrey;
}

.app-container {
  grid-template-columns: 1fr;
}

@media (min-width: 600px) {
  .app-container {
    display: grid;
    grid-template-columns: 200px 1fr;
    gap: 20px;
  }
}

.notes-grid {
  display: grid;
  grid-template-columns: repeat(auto-fill, minmax(250px, 1fr));
  grid-auto-rows: minmax(250px, auto);
  gap: 20px;
}

.note-item {
  display: flex;
  flex-direction: column;
  border: 1px solid #ccc;
  padding: 10px;
  border-radius: 5px;
  background-color: #f9f9f9;
  box-shadow: 0px 0px 10px rgba(0, 0, 0, 0.1);
  cursor: pointer;
}

.notes-header {
  display: flex;
  justify-content: flex-end;
}

.notes-header button {
  font-size: 16px;
  background: transparent;
  border: none;
  cursor: pointer;
  max-width: fit-content;
}

h2 {
  margin: 0;
}

.note-form {
  display: flex;
  flex-direction: column;
  gap: 20px;
}

textarea,
input {
  border-radius: 5px;
  border: 1px solid black;
  padding: 10px;
  font-size: 16px;
}

.note-form button {
  border-radius: 5px;
  background-color: rgb(64, 154, 184);
  border: none;
  padding: 10px;
  font-size: 16px;
  color: white;
}

.note-form button:hover {
  background-color: rgb(106, 175, 198);
  cursor: pointer;
}

.edit-buttons {
  display: flex;
  justify-content: space-evenly;
  gap: 5px;
}

.edit-buttons button {
  flex: 1;
}

.edit-buttons button:last-of-type {
  background-color: rgb(220, 89, 89);
  color: white;
}

Add Dummy Notes

Add Dummy Notes for CSS Grid Testing

Now that we have our CSS in place, the next step is to add dummy notes to our App component to test the responsiveness of our CSS grid. To achieve this, we'll navigate to App.tsx and import the useState hook from React.

Inside our App component, we'll store the notes within the useState hook. Whenever you have UI elements that can change, it's a good idea to manage them in the state. We'll initialize this with a dummy array of notes, where each note has an id, a title, and content:

const [notes, setNotes] = useState<Note[]>([
{
  id: 1,
  title: "test note 1",
  content: "bla bla note1",
},
{
  id: 2,
  title: "test note 2 ",
  content: "bla bla note2",
},
{
  id: 3,
  title: "test note 3",
  content: "bla bla note3",
},
{
  id: 4,
  title: "test note 4 ",
  content: "bla bla note4",
},
{
  id: 5,
  title: "test note 5",
  content: "bla bla note5",
},
{
  id: 6,
  title: "test note 6",
  content: "bla bla note6",
},
]);

Think of this as simulating an API call and storing the returned data in the state. The structure of this data will be similar to what we'll receive from our API requests when we eventually build out our backend.

Map Notes to Components

With our array of notes in state, we can now use the map function within our notes grid to display the markup for each note. The map function will run as many times as there are notes in the array. Instead of hardcoding the title and content, we'll pull these values from each note object:

<div className="notes-grid">
  {notes.map((note) => (
    <div className="note-item">
      <div className="notes-header">
        <button>x</button>
      </div>
      <h2>{note.title}</h2>
      <p>{note.content}</p>
    </div>
  ))}
</div>

Verify Responsiveness

After these steps, you should see four notes displayed in the browser, populated with the values from the objects in the array.

To verify that our layout is responsive, you can change the window size. You'll see that the notes adjust based on the window size. When the window is at its smallest—simulating a mobile screen—the form will stack vertically above the notes grid.

Save Note Form

Now that we have our UI set up, let's focus on adding functionality to the form that allows us to create a new note. Initially, we'll implement this for the UI. Later, we'll make the data persistent by linking it to the backend, which we'll build separately.

Use State for Form Inputs in React

In React, when working with forms, it's a best practice to maintain a state variable for each form input. This enables React to control those inputs, making it easier to capture their values and use them programmatically.

In our code, we have two form inputs: one for the title and another for the content. For these, we'll set up two state variables called title and content:

const [title, setTitle] = useState("");
const [content, setContent] = useState("");

For the title input, we bind its value to the title state variable and update this state whenever the user types into the field:

<input
  value={title}
  onChange={(event) => setTitle(event.target.value)}
  placeholder="Title"
  required
></input>

Similarly, we'll handle the textarea for content:

<textarea
  value={content}
  onChange={(event) => setContent(event.target.value)}
  placeholder="Content"
  rows={10}
  required
></textarea>

Handle Form Submission

After binding our form inputs to state variables, the next step is to add a function that handles the form submission. We'll name this function handleAddNote:

const handleAddNote = (event: React.FormEvent) => {
  event.preventDefault();
  console.log("title: ", title);
  console.log("content: ", content);
};

In this function, we specify the parameter type as React.FormEvent to satisfy TypeScript's typing requirement. We also call event.preventDefault() to prevent the form from submitting and refreshing the page, which is its default behavior. Following that, we log the title and content state variables to the console.

Finally, we'll connect this function to the onSubmit event in our form:

<form onSubmit={handleAddNote}>{/* ...form inputs here... */}</form>

Test the Form

To test this setup, open the browser console, input a title and some content, and click the "Add Note" button. You should see the title and content values logged in the console, confirming that our form is capturing input as expected.

Handle the "Add Note" Functionality

Now that we've set up our state variables for the title and content, we can proceed to implement the function that handles adding a new note. This function will create a new note object and add it to our notes array, thereby updating the UI.

Create a New Note Object

First, let's create a new note object and specify its type as Note, taking advantage of TypeScript's type system:

const newNote: Note = {
  id: notes.length + 1,
  title: title,
  content: content,
};

Because we've explicitly typed our object, TypeScript's IntelliSense will assist us in populating the object, ensuring that we don't miss any required properties. For now, we'll set the id to the length of the current notes array plus one, although this id will eventually be generated by our backend database.

Update State with New Note

Once we have our new note object, we need to update our notes state array. We'll use the setNotes function for this purpose:

setNotes([newNote, ...notes]);

The new note object will be the first item in the new notes array, followed by the existing notes, which we'll spread into the new array using the spread operator. This effectively makes a copy of the old notes array and inserts it into the new one.

Clear the Form Inputs

Lastly, let's reset the title and content state variables to empty strings, improving the user experience by clearing the form once a note is added:

setTitle("");
setContent("");

Test the Functionality

And that's it! If you now go to the browser, input a title and some content, and then click "Add Note," you'll see your new note appear at the top of the list, and the form fields will be cleared, ready for a new entry.

Handle the "Update Note" Functionality

In this section, we'll focus on implementing the feature that allows users to update an existing note. When a user clicks on a note, we want to populate the title and content fields in our form with the note's existing values. We'll also add a "Save" and "Cancel" button.

Cleanup and Initial Setup

First, let's clean up our code by removing any console.log statements – they are no longer needed:

const [selectedNote, setSelectedNote] = useState<Note | null>(null);

Track the Selected Note

To track which note the user has clicked on, we'll create a new state variable called selectedNote. This state variable will have a type of Note or null to account for the possibility that no note is selected. We'll initialize this state to null.

Create the Click Handler

Next, let's create a function named handleNoteClick to handle the user's click event on a note. This function will take a note object as its argument:

const handleNoteClick = (note: Note) => {
  setSelectedNote(note);
  setTitle(note.title);
  setContent(note.content);
};

Within this function, we'll use setSelectedNote to save the clicked note to our selectedNote state. Additionally, we'll populate the title and content state variables with the values from the clicked note.

Update the UI

In the JSX for rendering each note, add an onClick event to the top-level div element for each note. Call the handleNoteClick function and pass the note object to it:

<div key={note.id} className="note-item" onClick={() => handleNoteClick(note)}>
  <div className="notes-header">
    <button>x</button>
  </div>
  <h2>{note.title}</h2>
  <p>{note.content}</p>
</div>

Since we are iterating over notes using the map function, this onClick handler will be added to each note automatically.

Save user changes

Now that we have the capability for the user to edit a note, we'll implement the functionality to save the changes they make to both the title and content of a note into our state.

The handleUpdateNote Function

Let's create a new function called handleUpdateNote:

const handleUpdateNote = (event: React.FormEvent) => {
  event.preventDefault();

  if (!selectedNote) {
    return;
  }

  const updatedNote: Note = {
    id: selectedNote.id,
    title: title,
    content: content,
  };

  const updatedNotesList = notes.map((note) => (note.id === selectedNote.id ? updatedNote : note));

  setNotes(updatedNotesList);
  setTitle("");
  setContent("");
  setSelectedNote(null);
};

Within this function, we use event.preventDefault() to prevent the form from automatically submitting when the "Save" button is clicked. We also validate if a note is selected. If not, we exit the function early to prevent potential errors.

Next, we form an updated note object based on the selected note's id and the updated title and content. After that, we utilize the map function to generate a new array of notes, replacing the selected note with our updated note where the id matches. The updated array is then set to our state using the setNotes function. Finally, we reset our title, content, and selectedNote state values to their initial states.

The handleCancel Function

We'll also implement a simple handleCancel function to reset our form and selected note when the user decides not to proceed with an update:

const handleCancel = () => {
  setTitle("");
  setContent("");
  setSelectedNote(null);
};

Update the JSX

Let's introduce conditional rendering in our JSX to display the appropriate buttons based on whether a note is selected for editing or not:

<form
  className="note-form"
  onSubmit={(event) => (selectedNote ? handleUpdateNote(event) : handleAddNote(event))}
>
  {/* ... other form elements ... */}
  {selectedNote ? (
    <div className="edit-buttons">
      <button type="submit">Save</button>
      <button onClick={handleCancel}>Cancel</button>
    </div>
  ) : (
    <button type="submit">Add Note</button>
  )}
</form>

Within our form's onSubmit event, we've added a conditional. If a note is selected, we'll trigger the handleUpdateNote function. Otherwise, the handleAddNote function will be executed.

Test the Implementation

After incorporating these changes, run your application. When you select a note, make modifications, and click on "Save", you'll observe the note gets updated.

Delete Notes from the UI

The last piece of functionality we need on the frontend before moving to the backend development is the ability to delete notes. You'll recall that we added a small "X" button to each note for this purpose. Clicking this button should remove the note from the UI. Let's jump back into the App.tsx file and implement this.

The deleteNote Function

First, create a function named deleteNote as follows:

const deleteNote = (event: React.MouseEvent, noteId: number) => {
  event.stopPropagation();

  const updatedNotes = notes.filter((note) => note.id !== noteId);

  setNotes(updatedNotes);
};

This function takes in two parameters: the event object and the noteId. The event.stopPropagation() line is crucial here because the delete button is nested within a clickable note. It prevents the deleteNote event from interfering with the click event on the note itself. This is especially important when dealing with nested onClick events.

The Filtering Logic

The core of the delete functionality lies in the filter method applied to the notes array. This method loops through the array and applies a function to each element, much like the map method. It will only return the notes whose IDs do not match the noteId provided, effectively removing the selected note.

We save this newly filtered array into a variable called updatedNotes and then update our state with it by calling setNotes(updatedNotes).

Add the onClick Event

After defining the deleteNote function, attach it to the delete button within the note. Pass in the event and the note ID, like so:

<button onClick={(event) => deleteNote(event, note.id)}>x</button>

Test the Functionality

Now, if you run your app and click the delete button on a given note, you will observe that the note disappears from the UI.

PART 2 - Create the Backend

After implementing UI functionalities, it's time to set up a backend that allows us to persist notes when the user adds, edits, or deletes them. For this, create a new folder in your project at the top level and name it notes-app-server. Even though it might seem like the server code is in the same directory as the UI, they are entirely separate and will run independently.

Initial Setup

1. Open your terminal and navigate to the notes-app-server folder you just created.
2. Run the following commands:

npm init
npm i ts-node typescript nodemon @types/cors @types/express @types/node --save-dev
npm i @prisma/client cors express prisma
npx tsc --init

• npm init: Initializes a new npm module and gives you access to npm packages.
• npm i ... --save-dev: Installs development dependencies like TypeScript and type definitions.
• npm i ...: Installs production dependencies like Express and Prisma.

Modify package.json

After running the above commands, navigate to your package.json and update the scripts section with:

"start": "npx nodemon"

This script uses nodemon for hot-reloading.

Implement the Server

Now, within the notes-app-server directory, create a src folder and within it, an index.ts file. Insert the following code:

import express from "express";
import cors from "cors";

const app = express();

app.use(express.json());
app.use(cors());

app.get("/api/notes", async (req, res) => {
  res.json({ message: "success!" });
});

app.listen(5000, () => {
  console.log("server running on localhost:5000");
});

1. import express and cors: We import the required libraries for our server.
2. const app = express();: Initializes a new Express application.
3. app.use(express.json());: Parses the JSON body from incoming API requests.
4. app.use(cors());: Adds CORS support.
5. app.listen(5000, ...): This starts the server listening on port 5000.

Test

Finally, you can test the server by navigating to the notes-app-server directory in your terminal and running:

npm start

You should see the console log: server running on localhost:5000. To further test, you can use a curl command to hit the /api/notes endpoint. If everything is set up correctly, you'll get a JSON object back.

Create a Postgres Database

ElephantSQL is a PostgreSQL database hosting service that makes it easy to set up, maintain, and scale your PostgreSQL database. Here's how to get started with creating a database using ElephantSQL.

Step 1: Sign Up / Log In

1. Navigate to the ElephantSQL website.
2. If you don't have an account, you can sign up for free. If you already have one, go ahead and log in.

Step 2: Create a New Instance

1. Once logged in, you'll find yourself on the "Dashboard" page.
2. Click on the "Create New Instance" button.
3. You'll be taken to a page where you can set the details for your new PostgreSQL database instance.

Step 3: Choose a Plan

1. You can start with a free "Tiny Turtle" plan, which is perfect for small projects and testing.
2. Select the plan that best suits your needs and click "Select".

Step 4: Configure Your Instance

1. You'll be asked to name your instance. Choose a name that you'll remember and that describes the purpose of the database.
2. You can also select the data center that is geographically closest to you or your users for better performance.
3. Click on "Review" and then "Create instance" to finalize the creation.

Step 5: Access Your Database

1. Once the instance is created, click on it in the Dashboard.
2. Here, you'll see the "Details" tab which includes all the information you need to connect to your database: URL, User & Default database, Password, and more.

Populate the DB

Step 1: Login to ElephantSQL

Open your web browser and navigate to the ElephantSQL website. Log
in to your account.

Step 2: Open your Instance

Once logged in, click on the name of the database instance you've set up.

Step 3: Navigate to the SQL Browser

In the left sidebar, find and click on "SQL Browser" or something similar (it might say "Browser").

Step 4: Run SQL Query

In the SQL Query editor that appears, you can type or paste in your SQL command:

INSERT INTO "public"."Note" (title, content)
VALUES ('test title', 'test content bla bla');

After entering the SQL, click on the "Execute" or "Run" button.

That should insert a new row into your Note table with the title 'test title' and content 'test content bla bla'.

Optional: Verify the Insert

You may also want to verify if the data has been inserted correctly. For that, you could use:

SELECT * FROM "public"."Note";

Run this SQL query in the same SQL browser, and it should return all rows from the Note table, including the one you've just inserted.

And that's it! You've inserted a new row into your table through the ElephantSQL web console.

Connect to DB from Node.js backend using Prisma

Step 1: Copy the ElephantSQL Connection URL

Once you've set up your ElephantSQL database, make sure to copy the connection URL that appears on your dashboard. This URL includes your username and password to the database, so keep it secure.

Step 2: Create an .env File

Navigate to your notes-app-server directory and create a new .env file:

touch .env

Open this file and add the following line to specify the database connection URL:

DATABASE_URL="your_connection_url_here"

Make sure not to commit this .env file to your Git repository to keep your credentials secure.

Step 3: Initialize Prisma

If you haven't installed Prisma yet, install it first:

npm install prisma --save-dev

Now, initialize Prisma in the notes-app-server directory:

npx prisma init

This command will create a new prisma folder containing a schema.prisma file.

Step 4: Configure schema.prisma

Open schema.prisma in your text editor. You'll see that Prisma has already generated some configurations for you. Update the datasource block to use the environment variable:

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

Step 5: Create the Note Model

Below the datasource block, add a new model block to represent a Note:

model Note {
  id      Int     @id @default(autoincrement())
  title   String
  content String
}

Step 6: Generate Prisma Client and Database Table

Run the following command to generate your Prisma client and create the database tables:

npx prisma db push

Step 7: Add Prisma to Your Application

First, import Prisma at the top of your index.ts:

import { PrismaClient } from "@prisma/client";

Then, initialize the Prisma client:

const prisma = new PrismaClient();

Step 8: Query Your Database

Now you can use Prisma in your application to query the database. For example, in a GET endpoint:

app.get("/notes", async (req, res) => {
  const notes = await prisma.note.findMany();
  res.json(notes);
});

Optional: Install Thunder Client in VS Code

(Feel free to skip this step if you already have a preferred API client)

Using curl is useful for quickly testing APIs, but it becomes cumbersome when you need to build out more complex requests. For instance, handling POST requests with custom bodies and headers can be more complicated.

To make API requests more straightforward, we'll install a client designed for this purpose.

While there are several options like Postman, we're going to focus on installing Thunder Client within VS Code, which makes it simple to execute requests right from your IDE.

To install Thunder Client, navigate to the Extensions section in VS Code and type "Thunder Client" in the search bar. You'll find it in the list of available extensions, identifiable by its purple logo. Click "Install," and upon completion, you'll see a Thunder Client option appear on the left-hand taskbar of your IDE.

Once you've clicked on Thunder Client, a list of your past requests will display. To initiate a new request, click the "New Request" button at the top. This action opens a new tab within Visual Studio Code.

Before proceeding, ensure that your server is running. Open the terminal and verify this. We will use Thunder Client to test our GET endpoint and get familiar with the request-making process. In the URL bar, enter the address of your 'notes' endpoint and specify that it's a GET request.

Click "Send," and you'll see a small window displaying the response. If the status code is 200 and you see an array containing your note, you've successfully made a GET request. Thunder Client will be our tool of choice for testing subsequent create, update, and delete requests. Of course, feel free to use any other tool you're comfortable with for this purpose.

Create POST Endpoint

In this section, we'll add an endpoint to our Express application that allows us to create a new note. Locate the index.ts file and insert the following code below your existing GET endpoint:

app.post("/api/notes", async (req, res) => {
  const { title, content } = req.body;

  if (!title || !content) {
    return res.status(400).send("title and content fields required");
  }

  try {
    const note = await prisma.note.create({
      data: { title, content },
    });
    res.json(note);
  } catch (error) {
    res.status(500).send("Oops, something went wrong");
  }
});

The structure is similar to the GET endpoint, but we're using app.post this time. We specify the URL for this POST endpoint and then define our function.

Inside the function, the first task is to extract title and content from the req.body. This is what the UI will send when a user submits the "Add Note" form.

After obtaining title and content, we utilize the Prisma client that we set up earlier to create a new note. We pass the title and content to the prisma.note.create() method, which returns a new note object complete with an ID. This object is then sent back as a JSON response.

To test the endpoint, go to the Thunder Client tab in VS Code. Switch the HTTP method from GET to POST while keeping the URL the same. Click the "Body" tab, which should default to JSON, and input some test values for title and content. After hitting "Send," you should receive a 200 OK status along with the created note, containing an ID, title, and content.

For robustness, we've added validation and error-handling. If either title or content is missing, the server returns a 400 Bad Request status with an appropriate error message. To test this, remove either title or content from the request body and resend it. You should now see a 400 status code along with your error message.

Additionally, we use a try-catch block to handle any errors thrown by the Prisma client. This helps in cases of database connection issues or other unforeseen errors, preventing the backend from crashing.

Finally, you can test the GET endpoint again. It should now return two notes: the first one manually added to the database, and the second one just created through Thunder Client. Switch the method back to GET in Thunder Client and hit "Send"; you should see two notes in the response.

Create PUT Endpoint

In this segment of the tutorial, we'll focus on adding the ability to update a note. Add the following code snippet below the code for your previous POST endpoint:

app.put("/api/notes/:id", async (req, res) => {
  const { title, content } = req.body;
  const id = parseInt(req.params.id);

  if (!title || !content) {
    return res.status(400).send("title and content fields required");
  }

  if (!id || isNaN(id)) {
    return res.status(400).send("ID must be a valid number");
  }

  try {
    const updatedNote = await prisma.note.update({
      where: { id },
      data: { title, content },
    });
    res.json(updatedNote);
  } catch (error) {
    res.status(500).send("Oops, something went wrong");
  }
});

The structure of this app.put function is similar to the GET and POST endpoints you've already created. The major difference is the :id parameter in the URL. This acts as a placeholder, allowing you to specify the ID of the note you wish to update.

Inside the function, you'll notice we extract title and content from req.body, just like before. Additionally, we retrieve the ID from req.params and convert it to an integer using parseInt(), as our database stores IDs as integers.

We've added validation checks to ensure that the id exists and is a valid number. If either id, title, or content is missing or invalid, the API returns a 400 status code along with an error message.

Next, we use a try-catch block to attempt the update operation. Within the try section, we call the prisma.note.update() function. We specify the id in a where object and provide the new title and content via a data object. If the operation succeeds, the updated note is sent back in the response. In case of an error, the catch block will return a 500 status and an error message.

To test this, switch to your Thunder Client tab in VS Code. Update the method to PUT and set the URL to include the ID of the note you want to update, for example, /api/notes/3. In the request body, send JSON data with the new title and content. Upon hitting "Send", a 200 status should confirm the update. The returned note should reflect your changes.

To double-check, perform a GET request on the /api/notes endpoint. You should see the updated note in the list.

Lastly, test the validation by supplying an invalid ID, like a random string. The API should return an error message stating that the ID must be a valid number.

Create DELETE Endpoint

In addition to our existing endpoints, it's crucial to add validation for empty title or content fields in our app.put function, since these fields are required by our database. Revisit your app.put function in index.ts and add similar validation to what we added for the POST request. Specifically, if either title or content is empty, return a 400 status code along with an error message.

With that in place, let's move on to the DELETE endpoint. Add the following code just after your PUT endpoint:

app.delete("/api/notes/:id", async (req, res) => {
  const id = parseInt(req.params.id);

  if (!id || isNaN(id)) {
    return res.status(400).send("ID field required");
  }

  try {
    await prisma.note.delete({
      where: { id },
    });
    res.status(204).send();
  } catch (error) {
    res.status(500).send("Oops, something went wrong");
  }
});

This app.delete function works similarly to the update (app.put) endpoint. It also accepts an ID as part of the URL parameters (query params should be URL parameters or route parameters).

First, we validate that the provided ID is a valid number. If it isn't, we return a 400 status code and an accompanying error message.

Once the ID is validated, we proceed to delete the note using Prisma's delete method. In the try block, we specify which note to delete by its ID in the where object. Upon successful deletion, we return a 204 status code, which indicates 'No Content.' This is a standard way to signal to the frontend or API consumers that the deletion was successful.

If an error occurs during the deletion, the catch block returns a 500 status code along with a generic error message.

To test the new DELETE endpoint, switch your HTTP method to DELETE in your testing tool (like Thunder Client or Postman). Use the ID of the note you wish to delete, such as /api/notes/3, and hit 'Send'. You should receive a 204 status code, indicating the operation was successful. To confirm, perform a GET request on your /api/notes endpoint and observe that the note with the specified ID has indeed been removed.

PART 3 - Connect UI to Backend

Now that we have our backend and UI ready, it's time to connect them. We will do this by using the built in fetch function to call our backend from our UI.

Get and Display Notes

Let's dive back into our frontend code. Just below our state declarations at the top of our component, we're going to introduce a useEffect hook:

useEffect(() => {
  // ...
}, []);

Inside this useEffect, we'll define an asynchronous function named fetchNotes. We need to put this in a separate function because React does not support making the useEffect hook asynchronous directly:

const fetchNotes = async () => {
  // ...
};

To handle any potential errors from the API, we'll wrap our API logic inside a try-catch block:

try {
  // ...
} catch (e) {
  console.log(e);
}

Inside the try block, we use the native fetch function to make an API call. Our API is running at http://localhost:5000/api/notes. By default, fetch performs a GET request, which is what we need:

const response = await fetch("http://localhost:5000/api/notes");

After making the request, we'll process the response and convert it to JSON. The API returns an array of notes, which we'll capture in a variable named notes of type Note[]:

const notes: Note[] = await response.json();

If everything goes smoothly, the next step is to update our state with the notes fetched from the API:

setNotes(notes);

In the catch block, we'll log any errors that may occur:

console.log(e);

We've defined fetchNotes, but haven't called it yet. To invoke this function, add a call to fetchNotes() at the end of the useEffect block:

fetchNotes();

Lastly, add an empty dependency array to ensure that this code only runs once when the component is first mounted:

}, []);

After saving your changes, you should see the notes from your database displayed in the browser. If you've added or deleted notes directly through the database, those changes should be reflected here.

To wrap things up, you can remove any hardcoded array that you initially added to your notes state variable. Instead, populate it with the data fetched from the API:

const [notes, setNotes] = useState<Note[]>([]);

This ensures that the notes state is initially empty, then populated by the useEffect through the fetchNotes function.

Completed Code for this Section

  const [notes, setNotes] = useState<Note[]>([]);


  useEffect(() => {
    const fetchNotes = async () => {
      try {
        const response = await fetch(
          "http://localhost:5000/api/notes"
        );

        const notes: Note[] =
          await response.json();

        setNotes(notes);
      } catch (e) {
        console.log(e);
      }
    };

    fetchNotes();
  }, []);

Save New Note

Next, let's explore how to save a note to our backend. We already have a function called handleAddNote that deals with adding a note to the UI:

const handleAddNote = async (
  event: React.FormEvent
) => {
  // ...
};

To start, remove any code that manually creates a new note object on the frontend. This is because our backend will return this object with all its properties once the note has been saved to the database.

As in our previous example, we'll use a try-catch block to handle the API logic and error handling:

try {
  // API logic here
} catch (e) {
  console.log(e);
}

Place your existing state-changing function calls (setNotes, setTitle, and setContent) inside the try block. These will be executed after the API successfully saves the note:

setNotes([newNote, ...notes]);
setTitle("");
setContent("");

To call the API, we'll use the fetch function, similar to how we fetched notes. The difference is that this time, we need to pass a second argument to fetch to specify the HTTP method and payload:

const response = await fetch(
  "http://localhost:5000/api/notes",
  {
    method: "POST",
    headers: {
      "Content-Type": "application/json",
    },
    body: JSON.stringify({
      title,
      content,
    }),
  }
);

Don't forget to add the async keyword to the handleAddNote function signature if you haven't already, as we are using the await keyword inside the function.

The server will respond with the newly created note object, which we can then add to our UI. Convert the response to JSON and store it in a variable named newNote:

const newNote = await response.json();

Finally, in the catch block, we log any errors that might occur:

console.log(e);

Also, make sure to add headers to specify the content type of the data we are sending:

headers: {
  "Content-Type": "application/json",
}

Save your changes and test the functionality in the browser. Use the form to add a new note and click "Add Note." If everything is set up correctly, your new note should appear in the list.

Completed Code for this Section

  const handleAddNote = async (
    event: React.FormEvent
  ) => {
    event.preventDefault();
    try {
      const response = await fetch(
        "http://localhost:5000/api/notes",
        {
          method: "POST",
          headers: {
            "Content-Type": "application/json",
          },
          body: JSON.stringify({
            title,
            content,
          }),
        }
      );

      const newNote = await response.json();

      setNotes([newNote, ...notes]);
      setTitle("");
      setContent("");
    } catch (e) {
      console.log(e);
    }
  };

Save Updated Note

Next, let's explore how to save a note to our backend. We already have a function called handleAddNote that deals with adding a note to the UI:

const handleAddNote = async (
  event: React.FormEvent
) => {
  // ...
};

To start, remove any code that manually creates a new note object on the frontend. This is because our backend will return this object with all its properties once the note has been saved to the database.

As in our previous example, we'll use a try-catch block to handle the API logic and error handling:

try {
  // API logic here
} catch (e) {
  console.log(e);
}

Place your existing state-changing function calls (setNotes, setTitle, and setContent) inside the try block. These will be executed after the API successfully saves the note:

setNotes([newNote, ...notes]);
setTitle("");
setContent("");

To call the API, we'll use the fetch function, similar to how we fetched notes. The difference is that this time, we need to pass a second argument to fetch to specify the HTTP method and payload:

const response = await fetch(
  "http://localhost:5000/api/notes",
  {
    method: "POST",
    headers: {
      "Content-Type": "application/json",
    },
    body: JSON.stringify({
      title,
      content,
    }),
  }
);

Don't forget to add the async keyword to the handleAddNote function signature if you haven't already, as we are using the await keyword inside the function.

The server will respond with the newly created note object, which we can then add to our UI. Convert the response to JSON and store it in a variable named newNote:

const newNote = await response.json();

Finally, in the catch block, we log any errors that might occur:

console.log(e);

Also, make sure to add headers to specify the content type of the data we are sending:

headers: {
  "Content-Type": "application/json",
}

Save your changes and test the functionality in the browser. Use the form to add a new note and click "Add Note." If everything is set up correctly, your new note should appear in the list.

Completed Code for this Section

  const handleAddNote = async (
    event: React.FormEvent
  ) => {
    event.preventDefault();
    try {
      const response = await fetch(
        "http://localhost:5000/api/notes",
        {
          method: "POST",
          headers: {
            "Content-Type": "application/json",
          },
          body: JSON.stringify({
            title,
            content,
          }),
        }
      );

      const newNote = await response.json();

      setNotes([newNote, ...notes]);
      setTitle("");
      setContent("");
    } catch (e) {
      console.log(e);
    }
  };

Save Deleted Note

In this section, we'll discuss how to delete a note by invoking an API endpoint. We'll focus on the deleteNote function for this functionality:

const deleteNote = async (
  event: React.MouseEvent,
  noteId: number
) => {
  // ...
};

First, we need to make our function asynchronous to handle API calls. So, add the async keyword to the function declaration like this:

const deleteNote = async (
  event: React.MouseEvent,
  noteId: number
) => {
  // ...
};

Next, let's add a try-catch block to manage the API call. The catch block is essential for logging errors, which prevents the application from crashing unexpectedly:

try {
  // API logic here
} catch (e) {
  console.log(e);
}

Copy the existing UI-update logic you have and paste it into the try block, right after the API call. This ensures that the UI only updates if the API call is successful.

Now, let's get to the main part—making the API call to delete the note. To do so, we'll use the fetch API:

await fetch(
  `http://localhost:5000/api/notes/${noteId}`,
  {
    method: "DELETE",
  }
);

Note that the URL is a template string. It allows us to inject the ID of the note (noteId) that we want to delete. This noteId is passed into our deleteNote function when the user clicks the delete button corresponding to a specific note.

We specify the HTTP method as "DELETE" to indicate that we're requesting to delete a note:

method: "DELETE",

Unlike the 'add' or 'update' operations, there's no need to assign the API response to a variable, as we're not expecting any data to be returned:

await fetch(
  `http://localhost:5000/api/notes/${noteId}`,
  {
    method: "DELETE",
  }
);

After successfully deleting the note, we filter out the deleted note from our local notes state:

const updatedNotes = notes.filter(
  (note) => note.id !== noteId
);
setNotes(updatedNotes);

Finally, if everything goes smoothly and you save your changes, try running the application in the browser. Click the delete button for a specific note, and then refresh the page. You'll see that the note has been removed successfully.

Completed Code for this Section

  const deleteNote = async (
    event: React.MouseEvent,
    noteId: number
  ) => {
    event.stopPropagation();

    try {
      await fetch(
        `http://localhost:5000/api/notes/${noteId}`,
        {
          method: "DELETE",
        }
      );
      const updatedNotes = notes.filter(
        (note) => note.id !== noteId
      );

      setNotes(updatedNotes);
    } catch (e) {
      console.log(e);
    }
  };

The End - Why not try the bonus challenges?

Congratulations on making it to the end! If you enjoyed this project, I have created a list of additional challenges to try over at codecoyotes.com.

If you have any questions or suggestions feel free to drop me a message here. See you in the next one!

Indexing in PostgreSQL is a process that involves creating data structures that are optimized to efficiently search and retrieve data from tables.

An index is a copy of a portion of a table, arranged in a way that enables PostgreSQL to quickly locate and retrieve rows that match a particular query condition.

When a query is executed, PostgreSQL looks at the indexes available to determine if any of them can be used in satisfying the query condition. If it finds a relevant index, PostgreSQL employs it to quickly identify the corresponding rows in the table. This results in significantly speedy queries, especially in situations where tables are large or the conditions are complex.

PostgreSQL provides support for several index types, including B-tree, hash, GiST, SP-GiST, and BRIN. Each index type is tailored to cater to distinct query types and data access patterns.

Apart from the standard index types, PostgreSQL permits users to define custom indexes utilizing user-defined functions.

It's important to note that creating an index requires additional disk space and can impact the performance of write operations, such as INSERT, UPDATE, and DELETE. Because of this, it's essential to consider the trade-offs and carefully choose which columns to index based on the queries you frequently execute and the access patterns of your data.

B-tree Index

B-tree index is the most commonly used type of index to efficiently store and retrieve data in PostgreSQL. It's the default index type. Whenever we use the CREATE INDEX command without specifying the type of index we want, PostgreSQL will create a B-tree index for the table or column.

A B-tree index is organized in a tree-like structure. The index starts with a root node, with pointers to child nodes. Each node in the tree typically contains multiple key-value pairs, where the keys are used for indexing, and the values point to the corresponding data in the table.

To create a B-tree index in PostgreSQL, use the CREATE INDEX statement. Here’s the syntax:

CREATE INDEX index_name ON table_name;

Single-column indexing

To create a single B-tree index based on one table column instead of creating an index on the entire table, the syntax is as follows.

CREATE INDEX index_name ON table_name (column_name);

index_name is the name you want to give to the index.

table_name is the name of the table on which you want to create the index.

column_name is the name of the column(s) on which you want to create the index.

Example:

Let’s create a table called “sales_info” and insert some dummy data.

CREATE TABLE sales_info (
  sales_id integer NOT NULL, email VARCHAR, 
  location VARCHAR, item_purchased VARCHAR, 
  price VARCHAR
);

Insert values into the table using the INSERT statement:

INSERT INTO sales_info (
  sales_id, email, location, item_purchased, 
  price
) 
VALUES 
  (
    1, 'halie46@gmail.com', 'London', 
    'Headphone', '$50'
  ), 
  (
    2, 'romaine21@gmail.com', 'Australia', 
    'Webcam', '$50'
  ), 
  (
    3, 'frederique19@gmail.com', 'Canada', 
    'iPhone 14 pro', '$1259'
  ), 
  (
    4, 'kenton_macejkovic80@hotmail.com', 
    'London', 'Wireless Mouse', '$20'
  ), 
  (
    5, 'alexis62@hotmail.com', 'Switzerland', 
    'Dell Charger', '$15'
  ), 
  (
    6, 'concepcion_kiehn@hotmail.com', 
    'Canada', 'Longitech Keyboard', 
    '$499'
  );

If we create a B-tree index on the sales_id column by running this statement:

CREATE INDEX idx_sales_id ON sales_info (sales_id);

When we run the SELECT statement, we get the total query runtime below.

The time displayed might look insignificant, as the table we are working with is small. But when working with a large amount of data, this will significantly improve the performance of your queries.

To learn more about the B-Tree index and the behaviors of B-Tree operator classes, see the official documentation here.

Hash Indexes

Hash indexes are designed for fast key-value lookups. When a query condition requires equality checks on indexed columns, hash indexes can provide extremely fast retrieval, as the hash function directly determines the location of the desired data. Hash indexes are most suitable for equality comparisons, such as = or IN operations.

Like other index types, hash indexes need to be maintained during data modifications (inserts, updates, and deletes) to ensure data consistency. But hash index maintenance can be more expensive than B-tree indexes due to the need to resolve collisions and rehash data.

To create a hash index in PostgreSQL, you can use the CREATE INDEX statement with the USING HASH clause. For example:

CREATE INDEX hash_name ON table_name USING HASH (column_name);

This statement creates a hash index named "hash_name" on the specified column of the table.

Point to note here: while hash indexes are available in PostgreSQL, they are not suitable for range queries or sorting. B-tree indexes are typically preferred for such scenarios. Again, B-tree indexes are the default and commonly used index type.

Hash indexes have specific use cases and limitations, and it's essential to assess your requirements and query patterns before deciding on the appropriate index type for your PostgreSQL database.

Example:

Create a Hash index on the table sales_info using HASH for the column sales_id:

CREATE INDEX idx_sales_id ON sales_info USING HASH(sales_id);

Select and filter the data using the WHERE clause:

EXPLAIN (ANALYZE) 
Select 
  * 
from 
  sales_info 
WHERE 
  sales_id = 5;

Check out the official documentation if you want to dive deeper into Hash Indexes.

GiST and SP-GiST Indexes

GiST (Generalized Search Tree) and SP-GiST (Space-Partitioned Generalized Search Tree) indexes are advanced index types in PostgreSQL that provide support for a wide range of data types and search operations.

They are particularly useful for handling complex data structures and spatial data, GiST indexes are what you use if you want to speed up full-text searches.

Creating GiST and SP-GiST Indexes:

To create a GiST or SP-GiST index in PostgreSQL, you can use the CREATE INDEX statement with the USING GIST or USING SPGIST clause, respectively.

Here's an example of creating a GiST index on a geometry column:

CREATE INDEX index_geometry ON table_name USING GIST (geometry_column);

And here's an example of creating an SP-GiST index on a tsvector column:

CREATE INDEX index_text_search ON table_name USING SPGIST (tsvector_column);

Here's an overview of GiST and SP-GiST indexes in PostgreSQL:

GiST Index:

• Generalized Search Tree (GiST) indexes are versatile index structures that support various data types beyond simple scalar values.
• GiST indexes enable efficient searching and retrieval for complex data structures such as geometric objects, text documents, arrays, and more.
• They are based on the concept of multidimensional trees, allowing for flexible search operations.
• GiST indexes can handle different search predicates, including equality, range, and spatial operations like overlaps, containment, and distance-based searches.

SP-GiST Index:

• Space-Partitioned Generalized Search Tree (SP-GiST) indexes are an extension of GiST indexes that further enhance indexing capabilities.
• SP-GiST indexes are designed for data types with space-filling characteristics, such as multi-dimensional data, time-series data, and network data.
• They partition the index space into non-overlapping regions, optimizing search performance for specific access patterns.
• SP-GiST indexes provide support for various data types, including geometric objects, text search, and more.
• They are particularly efficient for spatial indexing and can handle complex spatial queries, including intersection, nearest-neighbor, and clustering operations.

See the official documentation GiST and SP-GiST indexes for more information.

BRIN Indexes

BRIN, or Block Range Index, is an index type in PostgreSQL designed to provide efficient indexing for large tables with sorted data. BRIN index contains the minimum and maximum in a group of database pages.

BRIN index makes is the easiest way to optimize for speed. It is particularly useful for data that exhibits sequential or sorted characteristics, such as time series data or data with a natural ordering.

Here’s an overview of the BRIN Index:

• BRIN indexes divide the table into logical blocks and store summary information about each block.
• Each block contains a range of values, and the index stores the minimum and maximum values within each block.
• Instead of storing individual index entries for each row, BRIN indexes store block-level summaries, making them smaller in size compared to other index types.
• BRIN indexes work well when the data is sorted or when sequential scans are more efficient than index scans.

To create a BRIN index in PostgreSQL, you use the CREATE INDEX statement with the USING BRIN clause.

Here's an example of creating a BRIN index on a timestamp column:

CREATE INDEX timestamp ON table_name USING BRIN (column);

The above statement creates a BRIN index on the specified timestamp column of the table.

Here are some things to consider when creating a BRIN Index:

• BRIN indexes are most effective when the data is sorted or exhibit natural ordering.
• They may not be suitable for tables with highly unsorted or non-sequential data.
• BRIN indexes are generally used for read-intensive workloads where sequential scans are prevalent.
• Regular maintenance and periodic reindexing may be necessary to ensure optimal performance.

To read more on BRIN Indexes, you can check out the official documentation.

Conclusion

In this quick guide, we have seen other types of indexes supported by PostgreSQL other than the B-Tree index.

It is not recommended to create an index on the fly just before running a one-off query. Creating a well-designed index requires careful planning and testing.

It's important to consider that indexes consume disk space. Also, whenever new data rows are inserted or existing rows are updated, the database automatically updates the corresponding index entries.

PostgreSQL is a powerful open-source relational database management system (RDBMS). It was initially created as a successor to the Ingres database system and was later named "PostgreSQL" (short for "Post-Ingres SQL").

PostgreSQL is known for its robustness, reliability, and scalability, making it a popular choice for large and complex database applications. It offers advanced features such as support for JSON and other non-relational data types as well as support for spatial data.

JSON file support was first introduced in PostgreSQL v9.2, and with every new release, steady improvements are being made.

In this comprehensive guide, you will learn about JSON functions and operators in PostgreSQL. We’ll also go into the basics of storing JSON data in PostgreSQL, how to query JSON data in PostgreSQL to make it readily accessible, and finally, you’ll learn about working with JSON arrays.

What is JSON?

JSON stands for JavaScript Object Notation. It is a common way to store data, especially in web applications. It is pretty similar to HTML or XML and was made for applications to easily read JSON files.

Key-Value Pairs: JSON data is written in key-value pairs surrounded by quotes. Here’s an example of a key-value pair “email”: “jsonlearning@gmail.com”. “Email” here is the key, while “jsonlearning@gmail.com” represents the value. The two are separated by a colon “:”.

Objects: An object is a key-value pair or pairs enclosed in curly brackets. Whenever a key-value pair is enclosed in curly brackets it becomes an object and can be treated as a single unit. Multiple key-value pairs can be added in an object, separated with a comma.

Example of a JSON object:

{“email” : “jsonlearning@gmail.com”, 
“country” : “United Kingdom”}

Arrays: Arrays in JSON are a way to store a collection of values within a single JSON object. An array in JSON is represented by square brackets [ ] containing a comma-separated list of values.

Here’s an example of an array in JSON: [ "apple", "banana", "cherry"].

Arrays in JSON can also be nested, meaning that an array can contain other arrays or objects as values. Here's an example of a nested array:

{ "firstname" : "Claire", 
"location" : "United Kingdom", 
"blog" : [{ "id" : "1", 
"title" : "Welcome to my blog" }, 
{ "id" : "2", 
"title" : "My first programming language" }]}

In this example of nested Arrays, you can see, “blog” is contained in an array, and the array also contains several objects.

JSONB in PostgreSQL

What is the JSONB data type? And how is it different from JSON?

JSONB (JSON Binary) is a data type in PostgreSQL that allows you to store and manipulate JSON data in a more effective and efficient way than the regular JSON data type.

JSONB stores JSON data in a binary format, which enables faster indexing and query performance compared to the regular JSON data type. This is because the binary format allows for more efficient storage and retrieval of JSON data, particularly when dealing with large or complex JSON objects.

In addition, JSONB supports additional indexing options, such as the ability to index specific keys within a JSON object, which allows for even faster queries.

The regular JSON data type in PostgreSQL stores JSON data as plain text, without any binary encoding or special indexing support. This makes it simpler to use but can result in slower query performance when dealing with large or complex JSON objects.

How to create a table with the JSONB data type

You can create a table and give a column a data type of JSON or JSONB, just like you give a column the data type of Int, VARCHAR, or Double. You can just simply give the column a data type of either JSON or JSONB.

Here’s an example of creating a Table Journal and giving the column “diary_information” the data type JSONB.

CREATE TABLE journal (
  id Int NOT NULL PRIMARY KEY, day VARCHAR, 
  diary_information JSONB
);

Because we specified the data type to be of type JSONB, any data held in that column must be a valid JSON.

How to insert JSON data into tables

After creating a table and giving our column the data type JSONB, how do we insert values into the column? Remember the data must be in a valid JSON format.

To insert data to our table, we use this statement:

INSERT INTO journal (id, day, diary_information) 
VALUES 
  (
    1, “Tuesday”, '{"title": "My first day at work", "Feeling": "Mixed                   feeling"}'
  );

If we try to retrieve the information using a select statement SELECT * FROM journal we get the following output, meaning the records have been inserted.

In the next section, we’ll take a look at some Functions and Operators.

Overview of JSON Functions and Operators

Functions and operators allow you to store, manipulate, and query data in JSON format in PostgreSQL.

Here are some commonly used PostgreSQL Functions and operators used in working with JSON files:

• ->: This operator allows you to extract a specific value from a JSON object, you specify the key as a “child” to the “parent”.

For example:

To retrieve a specific value from a JSON object using the -> operator, use it in a SELECT statement as seen below:

SELECT 
  Id, 
  day, 
  diary_information -> 'Feeling' AS Feeling 
FROM 
  journal;

Something to note here is that this operator extracts the field name, with the quote around it.

• ->>: This operator allows you to extract a JSON object field as text without the quotes around it from a JSON object.

For example:

SELECT 
  id, 
  day, 
  dairy_information ->> 'Feeling' as Feeling 
FROM 
  products;

This will extract the value of the "material" key as text from the "features" column in the "products" table.

• json_agg: This function aggregates JSON values into a JSON array.

For example, SELECT json_agg(my_column) FROM my_table; will return a JSON array containing the values in the "my_column" column of the "my_table" table.

• jsonb_set: This function updates a JSON object field with a new value. For example:

UPDATE 
  my_table 
SET 
  json_column = jsonb_set(
    json_column, '{field_name}', '"new_value"'
  ) 
WHERE 
  id = 1;

To update an existing JSON record, we use the function jsonb_set() () in an update statement.

For example, to update the record in the table we created earlier, you can run the following code:

UPDATE 
  journal 
SET 
  diary_information = jsonb_set(
    diary_information, '{Feeling}', '"Excited"'
  ) 
WHERE 
  id = 1;

This will update the "Feeling" key in the "diary_information" column of the "journal" table with the new value "Excited".

• JSONB_BUILD_OBJECT: Manually inserting JSON values can lead to errors, especially if it’s your first time working with JSON data. But with this function, you can input values without having to worry about curly brackets, colons, and the rest of them.

You can use a JSONB_BUILD_OBJECT function to insert a plain text record and this will convert it to JSON data. For example if you run the code:

JSONB_BUILD_OBJECT('Morning', 'Everybody is annoying today', 'Evening', 'Cannot wait to go home’)

This will create a value that looks like this:

{“Morning”: “Everybody is annoying today”, “Evening”: “Cannot wait to go home”}

Using this function in an insert statement:

INSERT INTO journal (id, day, feeling) 
VALUES 
  (
    2, 
    'Wednesday', 
    JSONB_BUILD_OBJECT(
      'Tired', 
      'Everybody is annoying today', 
      'Hungry', 
      'Cannot wait to go home’));

The new record will be added to the table and because we used the JSONB_BUILD__OBJECT function, the values that follow will be in JSON format.

These are the few functions and operators we can cover in this article. You can read more on JSON functions and Operators in PostgreSQL in the official documentation here.

How to Work with JSON Arrays in PostgreSQL

In PostgreSQL, you can store JSON data as a column value in a table, and you can use JSON arrays to store a collection of JSON objects in a single column.

Working with JSON arrays in PostgreSQL involves various operations, such as inserting, querying, and manipulating JSON data. Let's see how those work.

How to insert JSON arrays into tables

To insert JSON arrays into a table in PostgreSQL, you can use the INSERT INTO statement along with the VALUES clause to specify the JSON array as a string value.

Here's an example:

Suppose you have a table called employees with columns id, name, and skills. The skills column stores an array of JSON objects representing the skills of each employee.

To insert a new employee record with the following details:

• id: 1
• name: John
• skills: [{"name": "Python", "level": "Intermediate"}, {"name": "JavaScript", "level": "Expert"}]

You can use the following SQL statement:

INSERT INTO employees (id, name, skills) 
VALUES 
  (
    1, 'John', '[{"name": "Python", "level": "Intermediate"}, {"name":   "JavaScript", "level": "Expert"}]'
  );

How to query JSON arrays using JSON operators

To query JSON arrays in PostgreSQL, you can use the various JSON functions and operators provided by PostgreSQL. These functions allow you to extract specific values or elements from the JSON array and perform various operations on them. Let's look at an example.

How to extract values from a JSON array

Suppose you have a table called employees with a skills column that stores an array of JSON objects representing the skills of each employee.

To extract the names of all employees who have "Python" as one of their skills, you can use the ->> operator to extract the "name" property of each skill object, and the @> operator to check if the resulting array contains the value "Python":

SELECT 
  name 
FROM 
  employees 
WHERE 
  skills @ > '[{"name": "Python"}]' :: jsonb

This is just an example of the many ways in which you can query and manipulate JSON arrays using the JSON operators provided by PostgreSQL.

Conclusion

In conclusion, PostgreSQL support for JSON provides developers with the ability to simplify data models, enhance application performance, and so much more. This also provides a seamless relationship between relational and non-relational data structures.

You have learned about the JSON and JSONB data types, and what key-value pairs, objects, and arrays are in JSON. You also learned about some operators and functions in PostgreSQL to query data in JSON format.

If you learned a thing or two from this article, please share it with others.

There are many different types of databases in use today. We have centralized databases, commercial databases, cloud databases, distributed databases, end-user databases, NoSQL databases, relational databases, and lots more.

This article will focus on an example of a relational database (PostgreSQL) and how to query data from it. Other examples of relational databases include MySQL, MariaDB, and SQLite.

In this tutorial, you will learn how to install, connect, and finally query a PostgreSQL database with Python.

To get started, let's ease into it by learning a bit more about PostgreSQL.

What is PostgreSQL?

One of the most well-known open-source relational databases is PostgreSQL. It is used by developers and businesses of all sizes worldwide.

In terms of global popularity, PostgreSQL is ranked fourth by DB-Engines, and its popularity is growing. This shouldn't come as a surprise, given that many web and mobile applications, as well as analytical tools, use PostgreSQL databases.

PostgreSQL also has a robust ecosystem with a huge selection of add-ons and extensions that work well with the main database. For these reasons, PostgreSQL is a fantastic option whether you want to create your own custom database solution or need a transactional or analytical database.

Now that you know what PostgreSQL is, let's discuss how to connect to the database using Python.

Getting Started

We must use a database connector library to connect to a PostgreSQL database instance from our Python script. We can pick from a variety of alternatives in Python, but Psycopg2 is the most well-known and widely-used one.

There are alternative libraries built entirely in Python, such as pg8000 and py-postgresql, but we'll use Psycopg2 here.

What is Psycopg2?

The Psycopg2 library uses the C programming language as a wrapper around the libpq PostgreSQL library to support the Python DB API 2.0 standards. The C implementation of Psycopg2 makes it incredibly quick and effective.

Using a SQL query, we can utilize Psycopg2 to get one or more rows from the database. With this library, we can also insert data into the database using a variety of single or batch inserting methods.

The library is like SQL (Structured Query Language) and it performs all the tasks and operations a query language can do. It is both Unicode and Python 3 friendly, and it also has thread safety (the same connection is shared by multiple threads).

It is made to run highly multi-threaded programs, which frequently produce and delete a lot of cursors and do a lot of simultaneous INSERTS or UPDATES. Psycopg2's features include client-side and server-side cursors, asynchronous communication, and notifications.

How to Install Psycopg2

We must first install Psycopg2 in order to use it. We can install it via the terminal or command prompt using pip.

#installation

pip install psycopg2
pip3 install psycopg2

If we also decide to install the connector library in a virtual environment, you can do so using this code:

virtualenv env && source env/bin/activate
pip install psycopg2-binary

The Psycopg2 library and all of its dependencies will be installed into our Python virtual environment with this code snippet.

We have installed our connector, so let's start typing some queries.

How to Query PostgreSQL using Python

First, you'll need to create a new file and name it whatever you want. Then open it up in your IDE and start writing the code.

The first thing to do is to import the library (this is very important). We will make use of two Psycogp2 objects:

• Conection object: The connection to a PostgreSQL database instance is managed by the connection object. It encapsulates a database session, created using the function connect().
• Cursor object: The cursor object makes it possible for Python scripts to run PostgreSQL commands within a database session. The connection generates cursors, then the cursor() method ties them permanently to the connection. All commands are carried out within the framework of the connection-enclosed database session.

import psycopg2

conn = psycopg2.connect(database="db_name",
                        host="db_host",
                        user="db_user",
                        password="db_pass",
                        port="db_port")

We have to specify those arguments in order to be able to connect to the database. Let's have a quick look into there arguments.

• database: the name of the database we wish to access or connect to. Note that we can only connect to one database with one connection object.
• host: this most likely refers to the database server's IP address or URL.
• user: as the name implies, this refers to the name of the PostgreSQL user.
• password: this is the password that matches the PostgreSQL user.
• port: the PostgreSQL server's port number on localhost – it is usually 5432.

If our database credentials were entered correctly, we will receive a live database connection object that we can use to build a cursor object. We can go ahead and run any database queries and retrieve data with the aid of a cursor object.

cursor = conn.cursor()

Let's write a simple query:

cursor.execute("SELECT * FROM DB_table WHERE id = 1")

We apply the execute() function and supply a query string as its parameter. Then the database will be queried using the query that we entered.

After we have successfully achieved this, in order to be able to retrieve data from the database using Pyscopg2, we have to use any of these functions: fetchone() fetchall(), or fetchmany().

How to use fetchone():

After running the SQL query, this function will only return the first row. It is the simplest method of getting data out of a database.

#code
print(cursor.fetchone())

#output
(1, 'A-CLASS', '2018', 'Subcompact executive hatchback')

The fetchone() function returns a single row in the form of a tuple, with the information arranged in the order specified by the query's supplied columns.

When constructing the query string, it's crucial to provide the column orders precisely in order to distinguish which data in the tuple belongs to which.

How to use fetchall():

The fetchall() function works the same way as fetchone() except that it returns not just one row but all the rows. So in case we want 20-200 rows or more, we make use of Psycopg2 fetchall().

#code
print(cursor.fetchall())

#output
[(1, 'A-CLASS', '2018', 'Subcompact executive hatchback'),
 (2, 'C-CLASS', '2021', 'D-segment/compact executive sedan'),
 (3, 'CLA', '2019', 'Subcompact executive fastback sedan'),
 (4, 'CLS', '2018', 'E-segment/executive fastback sedan'),
 (5, 'E-CLASS', '2017', 'E-segment/executive sedan'),
 (6, 'EQE', '2022', 'All-electric E-segment fastback'),
 (7, 'EQS', '2021', 'All-electric full-size luxury liftback'),
 (8, 'S-CLASS', '2020', 'F-segment/full-size luxury sedan.'),
 (9, 'G-CLASS', '2018', 'Mid-size luxury SUV, known as the G-Wagen'),
 (10, 'GLE', '2019', 'Mid-size luxury crossover SUV')]
[...]

How to use fetchmany():

The fetchmany() function allows us to get a number of records out of the database and gives us additional control over the precise number of rows we get.

#code
print(cursor.fetchmany(size=3))

#output
[(1, 'A-CLASS', '2018', 'Subcompact executive hatchback'),
 (2, 'C-CLASS', '2021', 'D-segment/compact executive sedan'),
 (3, 'CLA', '2019', 'Subcompact executive fastback sedan')]

Because we set the argument to 3, we only received three rows.

When we are done querying our database we need to close the connection with conn.close().

Conclusion

That was pretty easy, right? We were able to perform all these tasks from a single Python script and it worked really well.

I hope this article was helpful and you can now work with PostgreSQL using Python.

For more information, do check out the Psycopg2 documentation to learn more.

Those aggregate functions are AVG(), COUNT(), SUM(), MIN(), and MAX().

While making queries with the aggregate functions, you can also use them in combination with the GROUP BY clause and HAVING statement in any relational database – MySQL PostgreSQL, and others.

In this article, you will learn how to use aggregate functions on their own and with the GROUP BY clause and HAVING statement.

What We'll Cover

• How to Use Aggregate Functions
• Syntax of Aggregate Functions
• How to Use the AVG() Aggregate Function
  • How to use the AVG() Function with GROUP BY and HAVING
• How to Use the COUNT() Aggregate Function
  • How to Use COUNT() with GROUP BY and HAVING
• How to Use the MAX() Aggregate Function
  • How to Use MAX() with GROUP BY and HAVING
• How to Use the MIN() Aggregate Function
  • How to Use MIN() with GROUP BY and HAVING
• How to Use the SUM() Aggregate Function
  • How to Use SUM() with GROUP BY and HAVING
• Conclusion

How to Use Aggregate Functions

To show you how the aggregate functions work, I’ll be working with an employees table in an employees_data database.

Running SELECT * FROM employees got me the following:

Syntax of Aggregate Functions

The syntax for working with aggregate functions looks like this:

aggregate_function(MODIFIER | expression)

• the aggregate function could be AVG, COUNT, MAX, MIN, or SUM
• the modifier could be all the values or the values in a particular column

This syntax would make more sense in practice, so let’s get to use it with the aggregate functions.

How to Use the AVG() Aggregate Function

The AVG() aggregate function gets the total number of data and calculates their average.

I was able to get the average wage paid to the employees this way:

SELECT AVG(wage) 
FROM employees

The query below gets the average wage of junior developers:

SELECT AVG(wage) 
FROM employees
WHERE role = "Junior dev"

How to use the AVG() Function with GROUP BY and HAVING

You can get the average number of entries (rows) in a particular column with the GROUP BY clause and HAVING statement. This means you have to combine those two with AVG().

For instance, I was able to get the average wage paid to employees in each row with this query:

SELECT role, AVG(wage) 
FROM employees
GROUP BY role

I was also able to get the average wage of senior developers by using the HAVING statement:

SELECT role, AVG(wage) 
FROM employees
GROUP BY role
HAVING role = "Senior dev"

How to Use the COUNT() Aggregate Function

COUNT() returns the number of rows in a table based on the condition (or filter) you apply.

For example, to get the total number of rows, I ran the query below:

SELECT COUNT(*) 
FROM employees

And I got 20:

To get the total number of employees from the USA, I ran the query below:

SELECT COUNT(*) 
FROM employees
WHERE country = "USA"

And to get the employees who are technical writers, I did this:

SELECT COUNT(*) 
FROM employees
WHERE role = "Tech Writer"

How to Use COUNT() with GROUP BY and HAVING

In a large database, you can use the GROUP BY clause and HAVING statement in combination with COUNT() to get the total number of entries (rows) in a particular column.

In the database I’m using in this article, I was able to get the total number of employees in each row with the GROUP BY clause:

SELECT role, COUNT(*) 
FROM employees
GROUP BY role

To get the number of only the employees that are senior developers, I attached HAVING role = "Senior dev" to the query:

SELECT role, COUNT(*) 
FROM employees
GROUP BY role
HAVING role = "Senior dev"

How to Use the MAX() Aggregate Function

The MAX() function returns the maximum value within non-NULL values. This means it would ignore fields that are empty and return the highest value within those that are not empty.

For example, to get the highest wage in the employees table, I used the MAX() function like this:

SELECT MAX(wage) 
FROM employees

To get the maximum wage for mid-level developers, I used the WHERE statement:

SELECT MAX(wage) 
FROM employees
WHERE role = "Mid level dev"

How to Use MAX() with GROUP BY and HAVING

To get the maximum wage in each role, the GROUP BY clause comes in handy:

SELECT role, MAX(wage) 
FROM employees
GROUP BY role

And to get the maximum wage in a particular role, combining the HAVING statement with the GROUP BY clause gets it done:

SELECT role, MAX(wage) 
FROM employees
GROUP BY role
HAVING role = "Tech writer"

How to Use the MIN() Aggregate Function

The MIN() function is the opposite of the MAX() function – it returns the minimum value within non-NULL values.

For example, I got the lowest wage on the employees table this way:

SELECT MIN(wage) 
FROM employees

How to Use MIN() with GROUP BY and HAVING

Again, to get the minimum wage in each role, the GROUP BY clause can get it done:

SELECT role, MIN(wage) 
FROM employees
GROUP BY role

And to get the minimum wage of a particular role, the HAVING statement and GROUP BY clause are what to use:

SELECT role, MIN(wage) 
FROM employees
GROUP BY role
HAVING role = "Junior dev"

How to Use the SUM() Aggregate Function

The SUM() aggregate function adds the number of entries in a column based on the filter applied.

The query below gets the total number of wages paid to employees:

SELECT SUM(wage) 
FROM employees

How to Use SUM() with GROUP BY and HAVING

To get the sum of the total wages paid for employees in each role, I selected the role, used SUM() on the wages, and grouped them by the role:

SELECT role, SUM(wage) 
FROM employees
GROUP BY role

To get the total wages paid to technical writers only, I used the HAVING statement:

SELECT role, SUM(wage) 
FROM employees
GROUP BY role
HAVING role = "Tech Writer"

Conclusion

This article took you through what aggregate functions are in SQL, their syntax, and how to use them.

In addition, you also learned how to use aggregate functions with the GROUP BY clause, HAVING, and WHERE statements.

If you want to learn how the HAVING statement works, you should read this article I wrote on it.

Thank you for reading.

It's impossible to create zero-downtime Postgres upgrades across major versions – right? Please, correct me if I’m wrong :)

But at least we’ve found a way to get close to zero downtime.

• PostgreSQL Upgrades are hard!
• How Retool upgraded our 4 TB main application PostgreSQL database

At Listen Notes, we've performed Postgres major version upgrades twice since 2017, the year Listen Notes was founded. During these upgrades, we experienced zero downtime for “read” operations, and less than 1 minute downtime for “write” operations.

Let’s walk through the process we went through of upgrading Postgres at Listen Notes.

TL;DR

1. Provision a new replica DB (DB_A) with the old version of Postgres.
2. Change DB hosts’ IP addresses in “/etc/hosts” of online servers to use read-only replica DB (not DB_A). By this moment, all write operations will fail.
3. Run pg_upgrade (with “--link”) on DB_A to upgrade to the new version of Postgres, and promote DB_A to be a primary.
4. Replace all DB hosts’ IP addresses in “/etc/hosts” of online servers to use DB_A. By this moment, write operations would resume.
5. Re-provision new replica nodes with the new version of Postgres.

How We Use Postgres in Listen Notes

Listen Notes is a popular podcast search engine. We provide a website (ListenNotes.com) with millions of monthly pageviews, and a solid Podcast API used by thousands of 3rd-party apps and websites.

We use Postgres as our main database, which stores all podcasts, episode metadata, and user data.

We run a self-hosted Postgres cluster on AWS EC2, consisting of one primary (db1) for write and read operations, and two replicas (db2 & db3) for read-only operations. The database size is a bit smaller than 1TB.

Postgres Progress

When Listen Notes was started in 2017, we ran Postgres 9.6.

Then we upgraded to Postgres 11.0 in 2019.

By 2021, we were running Postgres 13.0.

Generally speaking, we are not comfortable using the latest version of any infrastructure software, be it Postgres, Django, Redis, or others.

We trust the quality of Postgres, but there may be fewer documents and online discussions for the latest version, which might make troubleshooting difficult when version-specific issues occur.

Listen Notes servers that talk to Postgres DB are using hostnames like db1.internal.ln, db2.internal.ln, db3.internal.ln, and so on.

These hostnames are in “/etc/hosts,” so it’s easy to change the actual IP address while keeping the hostnames unchanged.

There are both online and offline workloads for our Postgres cluster. The online workload is to serve our website (ListenNotes.com) and API endpoints (PodcastAPI.com), which can’t have long downtime (for example, over 5 minutes).

The offline workload runs Celery tasks and other scripts that can be stopped for a relatively long time (for example, 2 hours). You can read our past blog posts to learn details of the Listen Notes architecture:

• The boring technology behind a one-person Internet company
• Good enough engineering to start an Internet company
• How I accidentally built a Podcast API business

For upgrading Postgres across major versions, our goal is to achieve zero downtime for read operations and minimal downtime (less than 5 minutes) for write operations. This will ensure most of our users won’t be affected during the time of upgrading.

How to Prepare for Postgres Upgrades**

The actual upgrade may take only 30 minutes, but we typically spend a few workdays preparing, which increases the odds of success during the upgrade.

Prep Step 1:

We must make sure the new major version of Postgres works well with our code base. So we test the new version of Postgres on dev and staging.

In addition to automatic unit tests, we have to manually test all major product features.

Prep Step 2:

Online services (ListenNotes.com and PodcastAPI.com) should work for most users even when Postgres write operations are disabled.

For ListenNotes.com, a majority of users are conducting “read-only” tasks, such as searching podcasts, browsing podcast details, and similar harmless actions. This means that “write” failures should affect only a tiny fraction of users.

For PodcastAPI.com, all API endpoints are read-only or offloading writes to async offline tasks, so write operations can be temporarily disabled.

We would spend some time testing on staging to make sure online services can still be functional when database writes are disabled.

Prep Step 3:

We spend the most time rehearsing the process of upgrading Postgres. Basically, we provision the entire fleet of Listen Notes and practice all necessary steps to upgrade Postgres.

We try to codify some steps in Ansible or Bash scripts to automate a bit. We document and time each step. By the time we perform it in the production environment, we know how many minutes (or even seconds) each step will take.

Prep Step 4:

We practice how to quickly rollback to the old version of Postgres, just in case the upgrade fails and we are forced to restore a stable environment ASAP.

How to Upgrade Postgres**

We typically perform the actual upgrade on a Friday night, when website and API traffic is low. Plus, we must have a good rest during the daytime, to preserve enough energy to perform such dangerous and stressful operations in production later that same evening :)

Since we’ve created a detailed TODO list in Notion during the previous few days of preparation, we carefully follow the TODO list to upgrade Postgres:

Upgrade Step 1:

We provision a new read-only replica DB with the old version of Postgres. Let’s call it DB_A. It’ll sync data from the primary DB in real-time, and will be upgraded to the new version of Postgres first then be promoted to be primary.

If the upgrade on DB_A fails later, we still have the option to quickly rollback and use the old primary DB instead.

Upgrade Step 2:

We stop all offline tasks, except for one Celery worker to handle some time sensitive async tasks, such as sending login emails. We’ll stop this Celery worker right before Step 4.

We also take most web/API servers out of load balancer, leaving only a minimal fleet of online servers.

Upgrade Step 3:

We change all DB hosts’ IP addresses in “/etc/hosts” on the minimal fleet of online servers (for example, web, API…) to use an old read-only DB. Let’s call it DB_B.

From this point on, all write ops should fail. This step is to make sure the future new primary DB won’t have outdated data.

Upgrade Step 4:

We run pg_upgrade (with “--link”) on DB_A to upgrade to the new version of Postgres, and promote it to be a primary DB. From this point on, DB_A is the primary, running the new version of Postgres.

Upgrade Step 5:

We replace all occurrences of DB_B’s IP with DB_A’s in “/etc/hosts” of the minimal fleet of online servers (for example, web, API…). By this point, DB_A is used as both primary and replica. And write ops should be good now.

Upgrade Step 6:

We change “/etc/hosts” to use DB_A for all DB hosts (primary + replica) on all other servers and bring back offline tasks.

From the users’ point of view, all Listen Notes services should be normal now . In fact, all API users should not experience any outage during the entire upgrade process, while a tiny portion of website users may experience errors when performing “write operations,” such as creating a podcast playlist or clip.

The most important step in upgrading to a new version of Postgres

Among them all, Step 4 is the most critical. If it fails or runs too long (for example, more than 10 minutes), then we must rollback by changing “/etc/hosts” on those online servers.

From our experience, it took less than 1 minute to run Step 4. Your mileage may vary if you’ve got a bigger (or smaller) database.

After we make sure things are back to normal after Step 6, we could re-provision replica DB instances with the new version of Postgres. And eventually, we terminate old DB instances.

Final thoughts

Sounds complex? Yeah, kind of…

Database operations in production are inherently complex and dangerous. Can’t rush the process :)

FAQs

Why don’t you use managed Postgres, for example Amazon RDS?

We want to have full control of key infrastructure software (for example, Postgres, Elasticsearch…), because…

1. We don’t want platform lock-in
2. We want to understand what’s going on inside the server, avoiding helplessly waiting for 3rd-party customer support teams (for example, AWS) to help solve urgent production issues inside blackboxes
3. It’s more cost-effective for us to run Postgres instances on our own — if money is not an issue (for example, raising big VC funding) or if we had less Postgres operational experience, then Amazon RDS might have been a good option to start with. Just like many things in life, we need to do things with constraints (like money, time, expertise…).

As far as I know, using a managed Postgres (like Amazon RDS) won’t remove the pain of upgrading across major versions: Google “Amazon RDS upgrade Postgres versions with zero downtime”.

Why don’t you use 3rd-party tools to automate the process a bit (like one-button push to automate the whole thing)?

We don’t know if there are any reliable 3rd-party tools out there that are easy to use, easy to understand, safe to use… But we are open to recommendations – wenbin@listennotes.com.

We oftentimes need to evaluate if it is worth the time and risk to learn and to use new blackbox tools in production, especially for serious DevOps tasks.

Why don’t you use MySQL, MongoDB, or other non-Postgres databases?

When I started Listen Notes, I knew Postgres way better than MySQL and other databases, because my previous employer Nextdoor.com uses Postgres. And I know Instagram and other large scale online services also use Postgres as their main data store (at least for the first few years).

If Postgres works well for huge online services, then it should also work for Listen Notes :) Sometimes we spend time learning new technologies to start a project, but more often we simply use technologies that we already know in order to jump-start a project faster and more efficiently.

Again, upgrading non-Postgres databases across major versions is also not easy…but here’s hoping all the above steps helped make whatever Postgres upgrade you performed a success!

This blog post was originally published at ListenNotes.com.

Scheduling allows you to automate things so you don't have to do them in real time.

In this article we will see how to schedule a job in PostgreSQL. We'll use pgAgent, a job scheduling agent for PostgreSQL.

How to Install PostgreSQL and Stack Builder

You can install pgAgent with Stack Builder.

Install PostreSQL from the official website. This will download Stack Builder along with the installer.

If you have PostgreSQL already installed, you could download the installer and run Stack Builder if you don't have it already.

Stack Builder runs once PostgreSQL installation is complete. I am using PostgreSQL14 and pgAdmin4.

How to Install pgAgent

When you run Stack Builder it will first open a welcome wizard.

If you have multiple PostgreSQL versions installed you will pick one to use to install pgAgent.

Under Adds-ons, tools and utilities, you will find pgAgent. Check the checkbox to install it.

Next, it will ask you to choose a directory where you want to install pgAgent.

Stack Builder will then open a pgAgent SetUp Wizard.

Here you will pick whether you want to install it in an upgrade mode. If you do not want to automatically change scripts while upgrading you can check the box.

In the PostgreSQL installation details wizard, provide the username and password that you entered when you installed PostgreSQL.

If you enter incorrect details it will throw a connection error. So make sure you remember those details.

NOTE: Login to PostgreSQL with the username and password you provided at this stage to view pgAgent jobs.

After adding those details, the setup begins:

It takes a couple of seconds to finish.

Click the finish button at the end.

Stack Builder will also display an installation completed wizard. It has instructions to install and uninstall utilites.

Once Stack Builder is installed you simply run it to install other utilites. To uninstall them you need to use the Control Panel.

pgAgent jobs will be visible to you in the browser tree on the left side of the dashboard.

Above you can see a close up view of the browser tree.

How to Create a Job in pgAgent

To create a new job, right click on the pgAgent Jobs button and click on create.

You will see a menu, and there just click create > pgAgent Job.

The create pgAgent dialog box has four tabs.

The first one is General tab. Here you enter the name of the job and select a category.

Category is just for internal categorization purposes – this does not affect how your job runs. You can select one based on the function of the job. Since I want to export the data to a CSV, I will pick the Data Export category.

Next we click on the Steps tab in the create pgAgent dialog box. In the top right corner of the box you will see a + sign. Click on it to add a new row.

The Steps tab has two sections: General and Code.

In the General tab:

1) Add the name of the step. 2) Next, you Enable or Disable the step. Your job will run only if the step is enabled. 3) Depending on whether your job is local or remote, you can pick the Connection type. I will choose a remote connection. 4) A remote connection allows you to manually add the Connection String. The syntax should be like in libq connection string. I will add my connection details in the same format: host=localhost port=5432 dbname=postgres 5) In the On Error select box, you can pick what should be happen in case an error occurs. I have selected for the job to fail. 6) Finally, you can comment on the step. Then save the changes.

Next comes the code section in Steps tab.

Since I want to export the data from a view, I will call the view and ask it to export the file. The code will be: COPY (select * from acc_view) TO E'C:\\test-data\\try.csv';

I will save the changes after adding the code.

We are now ready to schedule a job. In the Schedules tab we add the start date time and the end date time for the job to start and end.

SQL is the last tab. It shows the code generated by the GUI. If you want to schedule a job dynamically you will have to execute the procedure code displayed here.

How to View Created Jobs in pgAgent

Once a new job is created, it will be displayed under pgAgent jobs in the browser tree.

Its schedules and steps will be displayed when you extend the job.

To see whether the job was executed (whether it failed or succeeded), you select the job by its name and click on the Statistics tab in the dashboard. Here you can view the number of times the job was executed, start and end time, its status and id. s means success and f means failed in the Status column.

To debug why a job failed, you can simply click on the name of the step under Steps in the browser tree and click Statistics on the dashboard. In the output column you can see why the job failed.

In my case it wasn't able to access the directory I was trying to copy the data to. Once I changed the path, my job was successfully executed (note the first row).

How to Edit Jobs in pgAgent

To edit a job in pgAgent you select the job and click on the Properties tab on the dashboard.

Click on the pencil icon in the top left corner, it will open a wizard where you can edit all the details.

Conclusion

It is not always feasible to create schedulers in your code, but when it's an option, it can be really helpful.

Job Scheduling coupled with exporting data in CSV format is a powerful feature of PostgreSQL. I will try to explain how to create a job dynamically in the next tutorial. Happy learning.

In the Stack Overflow 2021 Survey, 4 out of the top 5 database technologies used by professional developers were relational database management systems.

PostgreSQL is an excellent choice as a first relational database management system to learn.

1. It’s widely used in industry, including at Uber, Netflix, Instagram, Spotify, and Twitch.

2. It’s open source, so you won’t be locked into a particular vendor.

3. It's more than 25 years old, and in that time it has earned a reputation for stability and reliability.

Whether you’re learning from the freeCodeCamp Relational Database Certification or trying out PostgreSQL on your own computer, you need a way to create and manage databases, insert data into them, and query data from them.

While there are several graphical applications for interacting with PostgreSQL, using psql and the command line is probably the most direct way to communicate with your database.

What is psql?

psql is a tool that lets you interact with PostgreSQL databases through a terminal interface. When you install PostgreSQL on a machine, psql is automatically included.

psql lets you write SQL queries, send them to PostgreSQL, and view the results. It also lets you use meta-commands (which start with a backslash) for administering the databases. You can even write scripts and automate tasks relating to your databases.

Now, running a database on your local computer and using the command line can seem intimidating at first. I’m here to tell you it’s really not so bad. This guide will teach you the basics of managing PostgreSQL databases from the command line, including how to create, manage, back up, and restore databases.

Prerequisite – Install PostgreSQL

If you haven’t already installed PostgreSQL on your computer, follow the instructions for your operating system on the official PostgreSQL documentation.

When you install PostgreSQL, you will be asked for a password. Keep this in a safe place as you’ll need it to connect to any databases you create.

How to Connect to a Database

You have two options when using psql to connect to a database: you can connect via the command line or by using the psql application. Both provide pretty much the same experience.

Option 1 – Connect to a database with the command line

Open a terminal. You can make sure psql is installed by typing psql --version. You should see psql (PostgreSQL) version_number, where version_number is the version of PostgreSQL that’s installed on your machine. In my case, it's 14.1.

Checking psql version via the command line

The pattern for connecting to a database is:

psql -d database_name -U username

The -d flag is shorter alternative for --dbname while -U is an alternative for --username.

When you installed PostgreSQL, a default database and user were created, both called postgres. So enter psql -d postgres -U postgres to connect to the postgres database as the postgres superuser.

psql -d postgres -U postgres

You will be prompted for a password. Enter the password you chose when you installed PostgreSQL on your computer. Your terminal prompt will change to show that you’re now connected to the postgres database.

Connecting to a database from the command line with psql

If you want to directly connect to a database as yourself (rather than as the postgres superuser), enter your system username as the username value.

Option 2 – Connect to a database with the psql application

Launch the psql application – it'll be called "SQL Shell (psql)". You will be prompted for a server, a database, a port and a username. You can just press enter to select the default values, which are localhost, postgres, 5432, and postgres.

Next, you’ll be prompted for the password you chose when you installed PostgreSQL. Once you enter this, your terminal prompt will change to show that you’re connected to the postgres database.

Connecting to a database with the psql application

Note: If you’re on Windows you might see a warning like “Console code page (850) differs from Windows code page (1252) 8-bit characters might not work correctly. See psql reference page 'Notes for Windows users' for details.” You don’t need to worry about this at this stage. If you want to read more about it, see the psql documentation.

How to Get Help in psql

To see a list of all psql meta-commands, and a brief summary of what they do, use the \? command.

\?

psql's help command

If you want help with a PostgreSQL command, use \h or \help and the command.

\h COMMAND

This will give you a description of the command, its syntax (with optional parts in square brackets), and a URL for the relevant part of the PostgreSQL documentation.

psql describing the DROP TABLE statement

How to Quit a Command in psql

If you’ve run a command that’s taking a long time or printing too much information to the console, you can quit it by typing q.

q

How to Create a Database

Before you can manage any databases, you’ll need to create one.

Note: SQL commands should end with a semicolon, while meta-commands (which start with a backslash) don’t need to.

The SQL command to create a database is:

CREATE DATABASE database_name;

For this guide, we’re going to be working with book data, so let’s create a database called books_db.

CREATE DATABASE books_db;

How to List Databases

You can view a list of all available databases with the list command.

\l

Listing all databases

You should see books_db, as well as postgres, template0, and template1. (The CREATE DATABASE command actually works by copying the standard database, called template1. You can read more about this in the PostgreSQL documentation.)

Using \l+ will display additional information, such as the size of the databases and their tablespaces (the location in the filesystem where the files representing the database will be stored).

\l+

Listing all databases with additional information

How to Switch Databases

You’re currently still connected to the default postgres database. To connect to a database or to switch between databases, use the \c command.

\c database_name

So \c books_db will connect you to the books_db database. Note that your terminal prompt changes to reflect the database you’re currently connected to.

Switching databases

How to Delete a Database

If you want to delete a database, use the DROP DATABASE command.

DROP DATABASE database_name;

You will only be allowed to delete a database if you are a superuser, such as postgres, or if you are the database’s owner.

If you try to delete a database that doesn’t exist, you will get an error. Use IF EXISTS to get a notice instead.

DROP DATABASE IF EXISTS database_name;

Deleting a database

You can’t delete a database that has active connections. So if you want to delete the database you are currently connected to, you’ll need to switch to another database.

How to Create Tables

Before we can manage tables, we need to create a few and populate them with some sample data.

The command to create a table is:

CREATE TABLE table_name();

This will create an empty table. You can also pass column values into the parentheses to create a table with columns. At the very least, a basic table should have a Primary Key (a unique identifier to tell each row apart) and a column with some data in it.

For our books_db, we’ll create a table for authors and another for books. For authors, we’ll record their first name and last name. For books, we’ll record the title and the year they were published.

We’ll make sure that the authors’ first_name and last_name and the books’ title aren’t null, since this is pretty vital information to know about them. To do this we include the NOT NULL constraint.

CREATE TABLE authors(
    author_id SERIAL PRIMARY KEY, 
    first_name VARCHAR(100) NOT NULL, 
    last_name VARCHAR(100) NOT NULL
);

CREATE TABLE books(
    book_id SERIAL PRIMARY KEY, 
    title VARCHAR(100) NOT NULL, 
    published_year INT
);

You will see CREATE TABLE printed to the terminal if the table was created successfully.

Now let's connect the two tables by adding a Foreign Key to books. Foreign Keys are unique identifiers that reference the Primary Key of another table. Books can, of course, have multiple authors but we’re not going to get into the complexities of many to many relationships right now.

Add a Foreign Key to books with the following command:

ALTER TABLE books ADD COLUMN author_id INT REFERENCES authors(author_id);

Next, let’s insert some sample data into the tables. We’ll start with authors.

INSERT INTO authors (first_name, last_name) 
VALUES (‘Tamsyn’, ‘Muir’), (‘Ann’, ‘Leckie’), (‘Zen’, ‘Cho’);

Select everything from authors to make sure the insert command worked.

SELECT * FROM authors;

Querying all data from the authors table

Next, we’ll insert some books data into books.

INSERT INTO books(title, published_year, author_id) 
VALUES (‘Gideon the Ninth’, 2019, 1), (‘Ancillary Justice’, 2013, 2), (‘Black Water Sister’, 2021, 3);

If you run SELECT * FROM books; you’ll see the book data.

Querying all data from the books table

How to List All Tables

You can use the \dt command to list all the tables in a database.

\dt

For books_db you will see books and authors. You'll also see books_book_id_seq and authors_author_id_seq. These keep track of the sequence of integers used as ids by the tables because we used SERIAL to generate their Primary Keys.

Listing all tables in a database

How to Describe a Table

To see more information about a particular table, you can use the describe table command: \d table_name. This will list the columns, indexes, and any references to other tables.

\d table_name

Describing the authors table

Using \dt+ table_name will provide more information, such as about storage and compression.

How to Rename a Table

If you ever need to change the name of a table, you can rename it with the ALTER TABLE command.

ALTER TABLE table_name RENAME TO new_table_name;

How to Delete a Table

If you want to delete a table, you can use the DROP TABLE command.

DROP TABLE table_name;

If you try to delete a table that doesn’t exist, you will get an error. You can avoid this by including the IF EXISTS option in the statement. This way you’ll get a notice instead.

DROP TABLE IF EXISTS table_name;

How to Manage Longer Commands and Queries

If you’re writing longer SQL queries, the command line isn’t the most ergonomic way to do it. It's probably better to write your SQL in a file and then have psql execute it.

If you are working with psql and think your next query will be long, you can open a text editor from psql and write it there. If you have an existing query, or maybe want to run several queries to load sample data, you can execute commands from a file that is already written.

Option 1 – Open a text editor from psql

If you enter the \e command, psql will open a text editor. When you save and close the editor, psql will run the command you just wrote.

\e

Writing commands in a text editor

On Windows, the default text editor for psql is Notepad, while on MacOs and Linux it's vi. You can change this to another editor by setting the EDITOR value in your computer’s environment variables.

Option 2 – Execute commands and queries from a file

If you have particularly long commands or multiple commands that you want to run, it would be better to write the SQL in a file ahead of time and have psql execute that file once you’re ready.

The \i command lets you read input from a file as if you had typed it into the terminal.

\i path_to_file/file_name.sql

Note: If you're executing this command on Windows, you still need to use forward slashes in the file path.

If you don’t specify a path, psql will look for the file in the last directory that you were in before you connected to PostgreSQL.

Executing SQL commands from a file

How to Time Queries

If you want to see how long your queries are taking, you can turn on query execution timing.

\timing

This will display in milliseconds the time that the query took to complete.

If you run the \timing command again, it will turn off query execution timing.

Using query execution timing

How to Import Data from a CSV File

If you have a CSV file with data and you want to load this into a PostgreSQL database, you can do this from the command line with psql.

First, create a CSV file called films.csv with the following structure (It doesn’t matter if you use Excel, Google Sheets, Numbers, or any other program).

A spreadsheet with Pixar film data

Open psql and create a films_db database, connect to it, and create a films table.

CREATE DATABASE films_db;

\c films_db

CREATE TABLE films(
    id SERIAL PRIMARY KEY,
    title VARCHAR(100),
    year INT,
    running_time INT
);

You can then use the \copy command to import the CSV file into films. You need to provide an absolute path to where the CSV file is on your computer.

\copy films(title, year, running_time) FROM 'path_to_file' DELIMITER ‘,’ CSV HEADER;

The DELIMITER option specifies the character that separates the columns in each row of the file being imported, CSV specifies that it is a CSV file, and HEADER specifies that the file contains a header line with the names of the columns.

Note: The column names of the films table don't need to match the column names of films.csv but they do need to be in the same order.

Use SELECT * FROM films; to see if the process was successful.

Importing data from a .csv file

How to Back Up a Database with pg_dump

If you need to backup a database, pg_dump is a utility that lets you extract a database into a SQL script file or other type of archive file.

First, on the command line (not in psql), navigate to the PostgreSQL bin folder.

cd "C:\Program Files\PostgreSQL\14\bin"

Then run the following command, using postgres as the username, and filling in the database and output file that you want to use.

pg_dump -U username database_name > path_to_file/filename.sql

Use postgres for the username and you will be prompted for the postgres superuser's password. pg_dump will then create a .sql file containing the SQL commands needed to recreate the database.

Backing up a database to a .sql file

If you don’t specify a path for the output file, pg_dump will save the file in the last directory that you were in before you connected to PostgreSQL.

Contents of films.sql backup file

You can pass the -v or --verbose flag to pg_dump to see what pg_dump is doing at each step.

Running pg_dump in verbose mode

You can also backup a database to other file formats, such as .tar (an archive format).

pg_dump -U username -F t database_name > path_to_file/filename.tar

Here the -F flag tells pg_dump that you're going to specify an output format, while t tells it it's going to be in the .tar format.

How to Restore a Database

You can restore a database from a backup file using either psql or the pg_restore utility. Which one you choose depends on the type of file you are restoring the database from.

1. If you backed up the database to a plaintext format, such as .sql, use psql.

2. If you backed up the database to an archive format, such as .tar, use pg_restore.

Option 1 – Restore a database using psql

To restore a database from a .sql file, on the command line (so not in psql), use psql -U username -d database_name -f filename.sql.

You can use the films_db database and films.sql file you used earlier, or create a new backup file.

Create an empty database for the file to restore the data into. If you're using films.sql to restore films_db, the easiest thing might be to delete films_db and recreate it.

DROP DATABASE films_db;

CREATE DATABASE films_db;

In a separate terminal (not in psql), run the following command, passing in postgres as the username, and the names of the database and backup file you are using.

psql -U username -d database_name -f path_to_file/filename.sql

The -d flag points psql to a specific database, while the -f flag tells psql to read from the specified file.

If you don’t specify a path for the backup file, psql will look for the file in the last directory that you were in before you connected to PostgreSQL.

You will be prompted for the postgres superuser's password and then will see a series of commands get printed to the command line while psql recreates the database.

Restoring a database using psql

This command ignores any errors that occur during the restore. If you want to stop restoring the database if an error occurs, pass in --set ON_ERROR_STOP=on.

psql -U username -d database_name --set ON_ERROR_STOP=on -f filename.sql

Option 2 – Restore a database using pg_restore

To restore a database using pg_restore, use pg_restore -U username -d database_name path_to_file/filename.tar.

Create an empty database for the file to restore the data into. If you're restoring films_db from a films.tar file, the easiest thing might be to delete films_db and recreate it.

DROP DATABASE films_db;

CREATE DATABASE films_db;

On the command line (not in psql), run the following command, passing in postgres as the username, and the names of the database and backup file you are using.

pg_restore -U username -d database_name path_to_file/filename.tar

Restoring a database using pg_restore

You can also pass in the -v or --verbose flag to see what pg_restore is doing at each step.

Using pg_restore in verbose mode

How to Quit psql

If you’ve finished with psql and want to exit from it, enter quit or \q.

\q

This will close the psql application if you were using it, or return you to your regular command prompt if you were using psql from the command line.

Where to Take it from Here

There are lots more things you can do with psql, such as managing schemas, roles, and tablespaces. But this guide should be enough to get you started with managing PostgreSQL databases from the command line.

If you want to learn more about PostgreSQL and psql, you could try out freeCodeCamp’s Relational Database Certificate . The official PostgreSQL documentation is comprehensive, and PostgreSQL Tutorial offers several in-depth tutorials.

I hope you find this guide helpful as you continue to learn about PostgreSQL and relational databases.

When you're working on a project that consists of a lot of microservices, it'll likely also include multiple databases.

For example, you might have a MySQL database and a PostgreSQL database, both running on separate servers.

Usually, to join the data from the two databases, you would have to introduce a new microservice that would join the data together. But this would increase the complexity of the system.

In this tutorial, we will be using Materialize to join MySQL and Postgres in a live materialized view. We'll then be able to query that directly and get results back from both databases in real-time using standard SQL.

Materialize is a source-available streaming database written in Rust that maintains the results of a SQL query (a materialized view) in memory as the data changes.

The tutorial includes a demo project which you can start using docker-compose.

The demo project that we are going to use will monitor the orders on our mock website. It'll generate events that could, later on, be used to send notifications when a cart has been abandoned for a long time.

The architecture of the demo project is as follows:

Prerequisites

All of the services that we will be using in the demo will run inside Docker containers, that way you will not have to install any additional services on your laptop or server rather than Docker and Docker Compose.

In case that you don't have Docker and Docker Compose already installed, you can follow the official instructions on how to do that here:

• Install Docker
• Install Docker Compose

Overview

As shown in the diagram above, we will have the following components:

• A mock service to continually generate orders.
• The orders will be stored in a MySQL database.
• As the database writes occur, Debezium streams the changes out of MySQL to a Redpanda topic.
• We'll also have a Postgres database where we can get our users.
• We'll then ingest this Redpanda topic into Materialize directly along with the users from the Postgres database.
• In Materialize we'll join our orders and users together, do some filtering, and create a materialized view that shows the abandoned cart information.
• We will then create a sink to send the abandoned cart data out to a new Redpanda topic.
• At the end we will use Metabase to visualize the data.
• You could, later on, use the information from that new topic to send out notifications to your users and remind them that they have an abandoned cart.

As a side note here, you would be perfectly fine using Kafka instead of Redpanda. I just like the simplicity that Redpanda brings to the table, as you can run a single Redpanda instance instead of all of the Kafka components.

How to Run the Demo

First, start by cloning the repository:

git clone https://github.com/bobbyiliev/materialize-tutorials.git

After that you can access the directory:

cd materialize-tutorials/mz-join-mysql-and-postgresql

Let's start by first running the Redpanda container:

docker-compose up -d redpanda

Build the images:

docker-compose build

Finally, start all of the services:

docker-compose up -d

In order to Launch the Materialize CLI, you can run the following command:

docker-compose run mzcli

This is just a shortcut to a Docker container with postgres-client pre-installed. If you already have psql you could run psql -U materialize -h localhost -p 6875 materialize instead.

How to Create a Materialize Kafka Source

Now that you're in the Materialize CLI, let's define the orders tables in the mysql.shop database as Redpanda sources:

CREATE SOURCE orders
FROM KAFKA BROKER 'redpanda:9092' TOPIC 'mysql.shop.orders'
FORMAT AVRO USING CONFLUENT SCHEMA REGISTRY 'http://redpanda:8081'
ENVELOPE DEBEZIUM;

If you were to check the available columns from the orders source by running the following statement:

SHOW COLUMNS FROM orders;

You would be able to see that, as Materialize is pulling the message schema data from the Redpanda registry, it knows the column types to use for each attribute:

    name      | nullable |   type
--------------+----------+-----------
 id           | f        | bigint
 user_id      | t        | bigint
 order_status | t        | integer
 price        | t        | numeric
 created_at   | f        | text
 updated_at   | t        | timestamp

How to Create Materialized Views

Next, we will create our first Materialized View, to get all of the data from the orders Redpanda source:

CREATE MATERIALIZED VIEW orders_view AS
SELECT * FROM orders;

CREATE MATERIALIZED VIEW abandoned_orders AS
    SELECT
        user_id,
        order_status,
        SUM(price) as revenue,
        COUNT(id) AS total
    FROM orders_view
    WHERE order_status=0
    GROUP BY 1,2;

You can now use SELECT * FROM abandoned_orders; to see the results:

SELECT * FROM abandoned_orders;

For more information on creating materialized views, check out the Materialized Views section of the Materialize documentation.

How to Create a Postgres Source

There are two ways to create a Postgres source in Materialize:

• Using Debezium just like we did with the MySQL source.
• Using the Postgres Materialize Source, which allows you to connect Materialize direct to Postgres so you don't have to use Debezium.

For this demo, we will use the Postgres Materialize Source just as a demonstration on how to use it, but feel free to use Debezium instead.

To create a Postgres Materialize Source run the following statement:

CREATE MATERIALIZED SOURCE "mz_source" FROM POSTGRES
CONNECTION 'user=postgres port=5432 host=postgres dbname=postgres password=postgres'
PUBLICATION 'mz_source';

A quick rundown of the above statement:

• MATERIALIZED: Materializes the PostgreSQL source’s data. All of the data is retained in memory and makes sources directly selectable.
• mz_source: The name for the PostgreSQL source.
• CONNECTION: The PostgreSQL connection parameters.
• PUBLICATION: The PostgreSQL publication, containing the tables to be streamed to Materialize.

Once we've created the PostgreSQL source, in order to be able to query the PostgreSQL tables, we would need to create views that represent the upstream publication’s original tables.

In our case, we only have one table called users so the statement that we would need to run is:

CREATE VIEWS FROM SOURCE mz_source (users);

To see the available views execute the following statement:

SHOW FULL VIEWS;

Once that is done, you can query the new views directly:

SELECT * FROM users;

Next, let's go ahead and create a few more views.

How to Create a Kafka Sink

Sinks let you send data from Materialize to an external source.

For this Demo, we will be using Redpanda.

Redpanda is Kafka API-compatible and Materialize can process data from it just as it would process data from a Kafka source.

Let's create a materialized view, that will hold all of the high volume unpaid orders:

 CREATE MATERIALIZED VIEW high_value_orders AS
      SELECT
        users.id,
        users.email,
        abandoned_orders.revenue,
        abandoned_orders.total
      FROM users
      JOIN abandoned_orders ON abandoned_orders.user_id = users.id
      GROUP BY 1,2,3,4
      HAVING revenue > 2000;

As you can see, here we are actually joining the users view which is ingesting the data directly from our Postgres source, and the abandond_orders view which is ingesting the data from the Redpanda topic, together.

Let's create a Sink where we will send the data of the above materialized view:

CREATE SINK high_value_orders_sink
    FROM high_value_orders
    INTO KAFKA BROKER 'redpanda:9092' TOPIC 'high-value-orders-sink'
    FORMAT AVRO USING
    CONFLUENT SCHEMA REGISTRY 'http://redpanda:8081';

Now if you were to connect to the Redpanda container and use the rpk topic consume command, you will be able to read the records from the topic.

However, as of the time being, we won’t be able to preview the results with rpk because it’s AVRO formatted. Redpanda would most likely implement this in the future, but for the moment, we can actually stream the topic back into Materialize to confirm the format.

First, get the name of the topic that has been automatically generated:

SELECT topic FROM mz_kafka_sinks;

Output:

                              topic
-----------------------------------------------------------------
 high-volume-orders-sink-u12-1637586945-13670686352905873426

For more information on how the topic names are generated check out the documentation here.

Then create a new Materialized Source from this Redpanda topic:

CREATE MATERIALIZED SOURCE high_volume_orders_test
FROM KAFKA BROKER 'redpanda:9092' TOPIC ' high-volume-orders-sink-u12-1637586945-13670686352905873426'
FORMAT AVRO USING CONFLUENT SCHEMA REGISTRY 'http://redpanda:8081';

Make sure to change the topic name accordingly!

Finally, query this new materialized view:

SELECT * FROM high_volume_orders_test LIMIT 2;

Now that you have the data in the topic, you can have other services connect to it and consume it and then trigger emails or alerts for example.

How to Connect Metabase

In order to access the Metabase instance visit http://localhost:3030 if you are running the demo locally or http://your_server_ip:3030 if you are running the demo on a server. Then follow the steps to complete the Metabase setup.

Make sure to select Materialize as the source of the data.

Once ready you will be able to visualize your data just as you would with a standard PostgreSQL database.

How to Stop the Demo

To stop all of the services, run the following command:

docker-compose down

Conclusion

As you can see, this is a very simple example of how to use Materialize. You can use Materialize to ingest data from a variety of sources and then stream it to a variety of destinations.

Helpful resources:

• CREATE SOURCE: PostgreSQL
• CREATE SOURCE
• CREATE VIEWS
• SELECT