Introduction
As part of my data analytics learning journey, I completed an exploratory data analysis project using Tableau. For this project, I worked with Airbnb listing data and built an interactive dashboard to explore how location, property size, pricing, and time relate to Airbnb listings.

The goal was not simply to create charts. I wanted to turn a large dataset into visuals that could answer practical questions about pricing and listing distribution.

Project Objective
The project explored questions such as:
- How does the average listing price vary by number of bedrooms?
- Which zipcodes have higher average listing prices?
- How are listings distributed across bedroom categories?
- How does the total calendar price change over time?

Dataset
The workbook contains three related datasets:
- Listings: 3,818 records and 92 fields describing Airbnb properties, hosts, prices, bedrooms, locations, reviews, and availability.
- Reviews: 84,849 review records.
- Calendar: daily availability and price records used for time-based analysis.

Tools Used
- Tableau
- Microsoft Excel
- Tableau Cloud

Data Preparation and Exploration
I first explored the available fields and identified variables that were useful for answering the business questions. These included price, zipcode, bedrooms, listing ID, date, and calendar price.

I then created separate worksheets before combining them into a dashboard.

Analysis and Visualizations

1. Average Price by Number of Bedrooms
The first visualization compares average listing price across bedroom categories. Property size is an important factor when analysing Airbnb pricing, and this chart makes the relationship easier to understand.

2. Number of Listings by Bedrooms
Next, I analysed the distribution of listings by bedroom count. This helps show which property sizes are most common in the dataset.

3. Average Price by Zipcode
Location can strongly influence accommodation prices. I compared average prices across zipcodes to identify areas with relatively higher-priced listings.

4. Calendar Price Over Time
Finally, I used the calendar data to create a time-based view of total listed prices.

Key Insights
From the analysis, several patterns became clear:
- Pricing varies across different property sizes.
- The number of bedrooms provides useful context when comparing listings.
- Location matters: average prices differ significantly across zipcodes.
- The dataset contains far more listings in some bedroom categories than others.
- Time-based data can reveal how the overall value of listed inventory changes across the available period.

The listings dataset contained 3,818 properties. The overall average listed price was approximately $128, while the median price was $100. The difference between the average and median also suggests that higher-priced properties influence the overall average.

Dashboard
After creating the individual worksheets, I combined them into a Tableau dashboard so that the insights could be viewed together. The dashboard brings pricing, location, property size, listing distribution, and time-based information into one analytical view.

What I Learned
This project helped me practice several important data analytics skills:
- Understanding a new dataset
- Selecting useful variables for analysis
- Creating clear visualizations in Tableau
- Comparing categorical and numerical data
- Analysing geographic and time-based patterns
- Communicating findings through a dashboard

Challenges
One challenge was working with a large workbook containing multiple related tables and many fields. Rather than trying to analyse everything, I focused on variables that directly supported the questions I wanted to answer.

Another challenge was choosing visualizations that communicated the findings clearly. This reinforced an important lesson: a good dashboard is not about using as many charts as possible. It is about selecting visuals that help answer specific questions.

Conclusion
This Airbnb Listings project was another important step in my data analytics journey. It gave me hands-on experience working with real-world data and using Tableau to transform raw records into meaningful insights.

I am continuing to build my portfolio through practical projects using Excel, SQL, Tableau, and other data analytics tools.

If you are also learning data analytics, I would love to connect and learn together.

When I decided to start my journey into Data Analytics, I knew I needed more than watching tutorials. I needed to build projects.

So instead of just learning Excel formulas, I challenged myself to complete a small data analysis project from start to finish.

For this project, I worked with a dataset containing information about 1,000 customers and their bike purchasing behavior.

My goal was to use Excel to clean the data, explore it, identify patterns, and turn those findings into an interactive dashboard.

This gave me the opportunity to experience what a real data analysis workflow looks like.

Step 1: Understanding the Dataset

Before doing any analysis, I wanted to understand what I was working with.

The dataset contained customer information such as:

• Marital Status
• Gender
• Income
• Number of Children
• Education
• Occupation
• Home Ownership
• Number of Cars
• Commute Distance
• Region
• Age
• Bike Purchase Status

At first, it was simply a spreadsheet containing rows and rows of customer information.

But I quickly realized that the data could answer interesting questions about customer behavior.

Before jumping into charts, however, I needed to clean the data.

Step 2: Cleaning the Data

Raw data is rarely ready for analysis.

Some of the values in the dataset were abbreviated, which made them less readable.

For example:

• M became Married or Male, depending on the column
• S became Single
• F became Female

I also created a new feature called Age Bracket using nested IF functions.

Instead of analyzing hundreds of individual ages, I grouped customers into three categories:

• Adolescence: Less than 31
• Middle Age: 31–54
• Old: 55+

This simple transformation made the data much easier to analyze.

It also taught me one of my first important lessons in data analytics:

Good analysis often starts with good data preparation.

If the data isn’t clean and structured properly, the analysis that follows can easily become confusing or misleading.

Step 3: Exploring the Data with Pivot Tables

After cleaning the dataset, I started using Pivot Tables to answer some business questions.

I focused on three main areas:

1. Does income influence bike purchases?

I compared the average income of customers based on gender and whether they purchased a bike.

One interesting observation was that customers who purchased bikes generally had a higher average income than those who did not.

For the overall dataset:

• Customers who did not purchase a bike had an average income of approximately $54,875
• Customers who purchased a bike had an average income of approximately $57,963

This showed me a noticeable relationship between income and bike purchasing behavior in the dataset.

However, I also learned something important about interpreting data:

A relationship does not necessarily mean that one factor causes another.

So rather than saying that higher income causes customers to buy bikes, I would describe it as an association observed in this dataset.

2. Does commute distance affect purchasing decisions?

Next, I analyzed bike purchases based on commute distance.

This was particularly interesting.

Customers with shorter commute distances appeared more likely to purchase bikes.

For example, customers commuting 0–1 miles had:

• 200 bike purchases
• 166 non-purchases

That’s approximately a 55% purchase rate.

Customers commuting more than 10 miles, on the other hand, had:

• 33 bike purchases
• 78 non-purchases

That’s approximately a 30% purchase rate.

The pattern made me think about how commuting distance and transportation choices might be connected.

Again, this doesn’t prove that commute distance causes someone to purchase or not purchase a bike, but it is an interesting pattern worth exploring further.

3. Which age group buys the most bikes?

The Age Bracket column I created earlier became particularly useful here.

The dataset contained:

• 110 customers in the Adolescence group
• 701 customers in the Middle Age group
• 189 customers in the Old group

The Middle Age group stood out.

Out of 701 customers between the ages of 31 and 54:

383 purchased a bike.

That means approximately 55% of customers in this group purchased a bike.

Compared with:

• Adolescence: approximately 35%
• Middle Age: approximately 55%
• Old: approximately 31%

This was one of my favorite findings from the project.

It showed me how a simple transformation of the data can make a pattern much easier to identify and communicate.

Step 4: Building the Dashboard

Once I completed the Pivot Tables, I wanted to bring everything together.

So I created an Excel dashboard to present the results visually.

Instead of scrolling through hundreds of rows or looking at separate tables, the dashboard provided a more convenient way to explore the findings.

This was probably my favorite part of the project.

It was the moment I could see the entire process coming together:

Raw Data → Cleaned Data → Analysis → Visualization → Insights

It also helped me understand that data analysis isn’t only about finding numbers.

It’s also about presenting those numbers in a way that makes sense to other people.

What I Learned

This project taught me much more than Excel formulas.

I learned a simple but powerful data analysis workflow:

1. Understand the data
2. Clean the data
3. Create meaningful features
4. Analyze the data
5. Communicate the insights

I also learned that asking the right questions is just as important as knowing the right Excel functions.

A Pivot Table is useful, but it becomes much more useful when you know what question you’re trying to answer with it.

My Biggest Takeaway

Before starting this project, I thought learning Excel for Data Analytics would mostly involve learning formulas and functions.

Now I see it differently.

Excel is a tool.

The real skill is learning how to use that tool to investigate questions, identify patterns, and communicate findings.

This project started with a spreadsheet full of raw customer data.

I cleaned it.

I transformed it.

I explored it.

I found patterns.

And finally, I built a dashboard to communicate what I found.

That experience made me realize that I wasn’t simply learning Excel anymore.

I was learning how to think like a data analyst.

What’s Next?

Excel is only the beginning of my Data Analytics journey.

I’m continuing to learn:

• Power BI
• Python
• Statistics
• Data Visualization

My goal is to keep building projects that strengthen my analytical thinking and help me solve real-world business problems.

I’m also documenting my journey publicly through GitHub and Medium.

Each project is another opportunity to learn something new.

Final Thoughts

If you’re also starting your journey into Data Analytics, here’s something I’m learning along the way:

Don’t wait until you feel ready before building projects.

Take a dataset.

Ask questions.

Try to answer them.

Make mistakes.

Clean the data.

Build something.

Then document what you learned.

You don’t need to know everything before you start.

You just need to start.

This is my first Excel data analysis project, and I’m excited to see where the next project takes me.

One dataset at a time.

If you’re also learning Data Analytics, I’d love to connect and learn alongside you.

Happy learning!

A few weeks ago, I started building toward something new. I picked data analysis, and I picked SQL as my starting point.

This is a walkthrough of my first real project, cleaning a dataset of global tech layoffs, built while following Alex The Analyst’s Data Analyst bootcamp series on YouTube. It’s not a complex project. But it taught me more about how real data behaves than any amount of reading tutorials passively ever could.

Why start with data cleaning?

It’s tempting, when you’re new, to want to jump straight to the “exciting” part like dashboards, charts, insights. But almost every data analyst will tell you the same thing: the unglamorous work of cleaning data is where most of the actual time goes, and where most of the actual skill shows up. A chart built on a messy table is just a good-looking wrong answer.

So I decided to sit with the boring part first.

The dataset

The dataset tracked layoffs at tech companies, company name, location, industry, number of people laid off, percentage of the workforce affected, date, funding stage, country, and funds raised. On the surface, it looked usable. It was not.

Here’s what I found once I actually looked closely:

• Duplicate rows: This can be in the form of the same layoff event recorded more than once.
• Inconsistent category labels — several different spellings/variants of “Crypto” as an industry
• A stray character — “United States” and “United States.” (yes, with a trailing period) treated as two different countries
• Dates stored as text, not as actual date values
• Blank and NULL values scattered across several columns, some of which were recoverable, some of which weren’t

None of these is exotic problems. They’re the kind of thing you’ll hit in almost any real dataset, which is exactly why this was a good place to start.

Step 1: Never touch the raw data

Before writing a single cleaning query, I made a staging copy of the table:

CREATE TABLE layoffs_staging
LIKE layoffs;

INSERT layoffs_staging
SELECT * FROM layoffs;

This felt like an obvious step once I understood why it mattered: if a cleaning step goes wrong, you want to be able to start over from the original data, not from an already-damaged copy of it.

Step 2: Finding duplicates with ROW_NUMBER()

SQL doesn’t have a simple “highlight the duplicates” button. Instead, I used a window function, ROW_NUMBER(), partitioned across every column:

SELECT *,
    ROW_NUMBER() OVER (
        PARTITION BY company, location, industry, total_laid_off,
                      percentage_laid_off, `date`, stage, country,
                      funds_raised_millions
    ) AS row_num
FROM layoffs_staging;

Any row where row_num came back as 2 or higher meant it was an exact repeat of a row that appeared earlier. I wrapped this in a CTE to isolate just the duplicates, spot-checked a few (Casper, a mattress company, was a good example), confirmed they were genuine repeats, and deleted them.

This was the first moment the project clicked for me understanding that ROW_NUMBER() isn't just a way to number rows, it's a way to detect something.

Step 3: Standardizing messy text

A few examples of what “standardizing” actually meant in practice:

• Trimming stray whitespace from company names with TRIM()
• Collapsing multiple “Crypto” variants into a single consistent label
• Catching that “United States” had a hidden trailing period on some rows, using TRIM(TRAILING '.' FROM country)
• Converting the date column from text into an actual DATE type with STR_TO_DATE(), so it could eventually be sorted and filtered properly

None of this is difficult SQL. What it required was noticing running SELECT DISTINCT on every column and actually reading the output, rather than assuming the data was clean because it looked clean at a glance.

Step 4: Backfilling what could be recovered

Some rows were missing an industry value, but the same company appeared elsewhere in the dataset with an industry filled in. Rather than deleting or guessing, I recovered it with a self-join:

UPDATE layoffs_staging2 t1
JOIN layoffs_staging2 t2
    ON t1.company = t2.company
    AND t1.location = t2.location
SET t1.industry = t2.industry
WHERE t1.industry IS NULL
AND t2.industry IS NOT NULL;

I matched on company and location, not just company, since a company could plausibly operate in different industries across different branches or offices. Getting this join condition right mattered more than I expected going in, it was the step I revisited and corrected after a first pass.

Rows that had no usable data at all, no total laid off, no percentage laid off, I removed. There was nothing left to analyze in them.

What I’d do differently next time

• I’d validate the date format across the entire dataset before applying STR_TO_DATE(), rather than assuming a sample was representative. That function fails silently on formats it doesn't expect.
• I’d write my join conditions more carefully the first time. I caught the location-matching issue on this project, but it’s the kind of thing that’s easy to miss.

Why this mattered to me

This wasn’t a complicated project. But it was the first time I’d taken raw, real, flawed data and made a series of deliberate, defensible decisions about how to fix it, rather than just following a tutorial by rote. Every step needed a “why,” and being able to explain that why is, I think, the actual skill being built here.

The full project, including the cleaning script and both the raw and cleaned datasets, is on my GitHub

Next up: exploratory analysis of the same cleaned dataset, looking for trends by industry, company stage, and time.

If you’re also making a career pivot into data analysis, I’d love to hear how you’re approaching it — feel free to connect.

Introduction: Why Automate Form Testing?

Forms are one of the most common features on the web — whether you’re signing up for a service, placing an order, or submitting feedback. But here’s the challenge: how do we make sure these forms work exactly as intended, every single time?

That’s where Cypress comes in. It’s a powerful testing tool that lets you simulate real user interactions in a browser, so you can be confident your forms are working perfectly.

In this beginner-friendly tutorial, you’ll:

• Build a simple React form with fields for Name, Address, Age, and Continent.
• Style it to look clean and user-friendly.
• Install Cypress and write your first end-to-end test.
• Understand each Cypress command so you know exactly what’s happening at every step.

By the end, you’ll have a fully functional form and an automated test that verifies it works — even if you’ve never touched Cypress before.

Tools and Technologies Used

• Cypress — for end-to-end testing with real browser interactions.
• React.js — to build our form and handle state.

Step-by-Step Guide to Building the Full Form Test

Step 1 — Set Up the Project

First, let’s create a new React app.
Open your terminal and run:

npx create-react-app my-form-project
cd my-form-project
npm start

If everything works, you should see the default React page at
http://localhost:3000.

Step 2 — Folder Structure

Before adding anything, here’s the basic folder structure we’ll end up with:

my-form-project/
├── cypress/
│   ├── e2e/
│   │   └── form.cy.js       # Cypress test file
│   ├── fixtures/
│   ├── support/
├── src/
│   ├── components/
│   │   └── Form.js          # Our form
│   │   └── Form.css         # Form styles
│   ├── App.js
│   ├── index.js
└── package.json

This makes it easier to stay organised.

Step 3 — Create the Form Component

Inside src/components/Form.js:

Step 4 — Add Styling

Inside src/components/Form.css:

Step 5 — Display the Form in App.js

Open src/App.js:

Let us view the React app in the browser
Open the terminal and Run:

npm start

View in the browser:

Step 6 — Install Cypress

In your project folder, run:

npm install --save-dev cypress

Then open Cypress for the first time:

npx cypress open

This opens a Cypress window and creates a new cypress/ folder automatically.

Select the E2E testing option and create cypress/e2e/form.cy.js file.

This may run the default test template. We can edit the form.cy.js file in our code editor.

Step 7 — Write the Cypress Test

Inside cypress/e2e/form.cy.js Edit the template test and write this:

• cy.visit(url) – Opens your app in Cypress.
• cy.get(selector) – Selects an element on the page.
• .type(text) – Types text into an input field.
• .select(option) – Selects a dropdown option.
• .click() – Clicks a button or link.
• .on('window:alert') – Listens for alert pop-ups.

Step 8 — Run the Test

To run interactively (with a visual Cypress browser):

npx cypress open

To run in the terminal (headless mode):

npx cypress run

Now the Cypress window opened in the browser should look like this:

Step 9 — Conclusion

Congratulations! You just:

• Built a simple React form
• Styled it with CSS
• Installed Cypress and wrote your first automated test
• Learned the meaning of key Cypress commands

From here, you can:

• Add more fields and validations
• Test for invalid input
• Test error messages and form resets

In software development, terms like API, endpoint, gateway, and load balancer are frequently used. However, these terms can be confusing, especially for those new to the field. This article will demystify these concepts with clear definitions and practical examples.

API (Application Programming Interface)

An API is a set of rules and protocols that enable software applications to communicate with each other. It defines the methods and data formats that applications can use to request and exchange information. APIs are crucial for integrating different systems and allowing them to work together seamlessly.

Example:
When you use a weather application on your smartphone to check the weather, the app connects to a remote server using an API to get the most up-to-date weather information. The API specifies how the app can ask for weather data and in what format the server will provide the information..

Endpoint

An endpoint is a specific URL that provides access to a particular function or resource within an API. It serves as one end of a communication channel through which an API interacts with another system. Endpoints are crucial for retrieving or sending data via the API..

Example:
In a RESTful API, an endpoint could be a URL that enables you to retrieve user information. For example:

https://api.example.com/users

Here, “https://api.example.com” is the base URL, and “/users” is the endpoint that returns information about users.

Gateway (API Gateway)

An API Gateway is a server that acts as an intermediary for requests from clients who are looking for resources from other servers. It handles all the tasks involved in accepting and processing numerous API calls, including traffic management, authorization and access control, monitoring, and API version management. The gateway routes different requests, transforms protocols, and manages authentication and rate limiting.

Example:
Just to recap, in this scenario, we have an e-commerce platform that consists of different microservices to manage user accounts, product catalogues, and orders. The API Gateway acts as an intermediary between the client applications (like web and mobile apps) and these microservices. When a user makes a purchase, the request is transmitted through the API Gateway, which then directs it to the relevant microservice.

Load Balancer

A load balancer is a device or software that evenly distributes incoming network or application traffic across multiple servers. This helps prevent any single server from being overloaded with too much traffic, thus improving the responsiveness and availability of applications. Load balancers can distribute traffic using different algorithms, such as round-robin, the least connections, or IP hash.

Example:
Consider a popular streaming service that needs to serve millions of users simultaneously. To ensure a smooth viewing experience, a load balancer distributes incoming video stream requests across multiple servers. If one server becomes too busy, the load balancer routes new requests to less busy servers, maintaining optimal performance.

Conclusion

Understanding the roles of APIs, endpoints, gateways, and load balancers is essential for anyone involved in software development or IT. APIs enable communication between different software systems, endpoints define specific access points within APIs, gateways manage and route API requests and load balancers ensure even distribution of traffic across servers.

Setting up Vagrant on Ubuntu within a VirtualBox environment is a powerful way to manage and create development environments.

Vagrant simplifies the process of configuring and provisioning virtual machines, allowing for consistent development setups across teams.

In this tutorial, we will be taking the easy steps to setting up Ubuntu 20 using Vagrant on a Virtualbox:

Prerequisites:

1. VirtualBox:

VirtualBox is a virtualization product that is suitable for both home and enterprise use. It is freely available as Open Source Software under the GNU General Public License (GPL) version 3.

Ensure it’s installed on your machine. It can easily be downloaded and installed from the official website. There is a link to the installation packages.

2. Vagrant:

Vagrant is a command line utility that manages virtual machine lifecycles, enabling the creation and configuration of lightweight, reproducible, and portable development environments.

It can be installed by downloading the package from the official website or via the terminal in Linux using the command below:

 sudo apt install vagrant
 

Setting up a Vagrant environment

1. Create a directory:
Open your terminal and create a new directory where you want to store your Vagrant configuration and related files.

 mkdir my_vagrant_project
 cd my_vagrant_project
 

2. Initialize Vagrant:

Inside the directory, initialize Vagrant to create a Vagrantfile, which defines the configuration of your virtual environment.

vagrant init ubuntu/focal64

3. Start the Virtual Machine:

Run the following command within your project directory to start the VM based on the Vagrantfile configuration.

 vagrant up

That should set up the Ubuntu 20 LTS automatically in the Virtualbox.

5. Access and Interact with the Virtual Machine:

Open your virtual box and see that the "Ubuntu 20.04 LTS" is already running. It may require a login and password. For those, as explained in the official guide, please use:

user: vagrant
password: vagrant

Now you’re inside the VM, and you can use it as a separate Ubuntu environment. Make changes, install software, or set up your development environment within this VM.

Customizing the Vagrantfile enables complex setups, with specific configurations for provisioning, syncing folders, and more.

Step-by-Step Beginner’s Guide: Setting Up Ubuntu 20 LTS in VirtualBox with Vagrant was originally published in Towards Dev on Medium, where people are continuing the conversation by highlighting and responding to this story.

Using MySQL databases, we’ll create a Golang-based CRUD API to manage club members’ details. Explore modern web development with Golang as we demystify crafting a Beginner-Friendly CRUD API. This guide unveils Go programming and API design, empowering you to create a robust CRUD API from scratch.

Golang:

Go or Golang is a programming language created by Google. It’s known for its simplicity, efficiency, and concurrent programming capabilities. It has garbage collection and concurrency, perfecting system programming and web development.

Go can be installed easily using the guidelines in the official documentation.

MySQL:

MySQL is an open-source relational database system that efficiently stores and manages structured data for various applications, from small projects to enterprise systems. It can be installed using the official guidelines.

STEPS:

1. Initialize Go:

Begin by creating your project folder or directory. Within this directory, initiate a Go server. Personally, I recommend initializing it through a GitHub repository but feel free to choose any preferred module.

//Create a work directory and move into the folder
mkdir clubTeam && cd clubTeam

//Initialize Go modules in the folder
go mod init github.com/<username>/repo.com

//Also can be
go mod init <yourmodule.com>

2. Import the packages:

In this guide, we will require the GORM and Gorilla Mux packages:

a. GORM is a widely used Go package for Object-Relational Mapping (ORM), streamlining database interactions by utilizing Go structs and methods to manage relational databases. It improves data manipulation, retrieval, and querying while enhancing code organization and maintainability. More details on GORM are available in the official documentation.

We must also import the MySQL dialect from GORM since we are using the MySQL database.

go get "github.com/jinzhu/gorm"

go get "github.com/jinzhu/gorm/dialects/mysql"

b. Gorilla Mux is a powerful Go package for HTTP routing, simplifying the mapping of incoming requests to handlers with a flexible syntax. It’s popular for building web apps, offering clean routes, URL parameter management, and effective HTTP method handling.

go get "github.com/gorrilla/mux"

With our packages imported, we’re ready to establish the project structure.

3. Organizing the Folders:
We will set up a main folder and a pkg folder. The main folder hosts the server’s main.go file.
Inside the pkg folder, we’ll arrange five sub-folders to manage distinct concerns, enhancing code organization and readability.

Within the pkg folder, we’ll have:

a. config > config.go
b. controllers > member-controller.go
c. models > member-model.go
d. utils > utils.go
e. routes > member-routes.go

4. Streamlining the Process: Let’s Begin with Routes:

We will kick off by defining our routes, and keeping logic minimal. In the member-routes.go file within the routes folder, we’ll utilize the Gorilla Mux package. As previously mentioned, this package plays a crucial role in handling API routing.

The above sets up routes using Gorilla Mux for managing CRUD operations on a “member” resource within a web app.

Key points:

A. Imports: In the “routes” package, it imports external packages:

• <yourmodule.com>pkg/controllers: Imports "controllers" from "workers" project.
• github.com/gorilla/mux: Imports Gorilla Mux router for HTTP routing.

B. MembersRoutes Function: Named “MembersRoutes,” it configures routes to handle various HTTP methods for “member” actions:

• Route Definitions:
• router.HandleFunc("/member", controllers.CreateMember).Methods("POST"): POST route for creating members.
• router.HandleFunc("/member", controllers.GetMember).Methods("GET"): GET route for retrieving all members.
• router.HandleFunc("/member/{memberId}", controllers.GetMemberById).Methods("GET"): GET route for fetching a member by ID.
• router.HandleFunc("/member/{memberId}", controllers.UpdateMember).Methods("PUT"): PUT route for updating a member by ID.
• router.HandleFunc("/member/{memberId}", controllers.DeleteMember).Methods("DELETE"): DELETE route for deleting a member by ID.

5. Connect with Database:

With our routes defined, it’s time to establish a connection to the database.
It’s recommended to have MySQL running locally, and the corresponding database set up. In the config.go file, ensure to input your MySQL username, password, and the database name.

In the config > config.go file:

The config package manages MySQL database configuration and connection through GORM. It involves:

A. Imports: The package imports essential libraries:

• github.com/jinzhu/gorm: GORM, an ORM framework for Go.
• _ "github.com/jinzhu/gorm/dialects/mysql": Imports MySQL dialect for GORM with a side effect.

B. Database Setup: It establishes and maintains a MySQL connection:

• Connect() uses gorm.Open for connection, including user info, host, etc.
• Errors trigger program panic.
• Database Access: The db variable (type *gorm.DB) holds the connected instance post Connect().

C. GetDB Function: GetDB() offers access to db, facilitating database operations:

In short, config manages MySQL connectivity via GORM. Connect() initializes, GetDB() grants access, enabling efficient database management.

6. Creating models:

We will create the models for our data in the model > member-model.go

The models package facilitates database interactions for a Member entity. Here’s a concise overview:

• Database Setup: The package uses GORM and custom configuration for database operations. It initializes the GORM database and sets up the Member struct to define attributes.
• Member Entity: Member struct represents a member with name, email, country, and color. GORM tags enable JSON serialization and link to database properties.
• Initialization: The init() function connects to the database using custom settings and auto-migrates to create the Member table.
• CreateMember Method: Adds a new member to the database and returns the created member’s pointer.
• GetAllMembers Function: Retrieves all members as a slice of Member objects from the database.
• GetMemberById Function: Fetches a specific member by ID and provides details along with the database query reference.
• DeleteMember Function: Removes a member by ID from the database and returns the deleted member.

In short, the models package manages database actions for Member data, enabling CRUD operations through GORM

7. We craft the Utils:

Considering our data is stored as JSON in the model, we will require utilities to handle both “unmarshaling” JSON requests from users and the database.

The ParseBody function efficiently extracts JSON data from an HTTP request body and fills a flexible interface x. The process involves:

• Importing necessary packages for managing JSON data and handling HTTP requests (encoding/json and io/ioutil).
• Defining the ParseBody function, which takes an HTTP request (r *http.Request) and an interface (x interface{}) as parameters.
• Reading the request body using ioutil.ReadAll, storing it as a byte slice named body, and proceeding if no reading errors occur.
• Unmarshaling (converting) the JSON data in the body byte slice into the provided interface x using json.Unmarshal.
• Handling successful unmarshaling by storing the parsed JSON data within interface x.
• Handling errors during body reading or JSON unmarshaling without changing the value of x, thus preserving its original state.

In essence, the ParseBody function streamlines the extraction and conversion of JSON data from an HTTP request body to a versatile interface x. It simplifies the process while handling potential errors, ensuring that the JSON data structure matches the expected interface x structure.

8. Now, it’s a fitting time to generate the Controller responsible for managing API function calls.

In the controller > member-controller.go:

The above code in Go defines a collection of HTTP handler functions responsible for managing various CRUD (Create, Read, Update, Delete) operations for a “Member” resource.
The handlers use the Gorilla Mux router for routing HTTP requests to the appropriate functions. Here’s an explanation of each function:

1. CreateMember:
    — HTTP Method: POST
    — Path: “/members”
    — Description: Creates a new member by parsing the request body JSON into a model structure — Member struct.
   It then calls the CreateMember method on the struct to create and store the new member. Serializes the created member into JSON format and sends it as the response.
2. GetAllMembers:
    — HTTP Method: GET
    — Path: “/members”
    — Description: Retrieves a list of all members by calling the GetAllMembers function from the models’ package. Serializes the list into JSON format and sends it as a response.
3. GetMemberById:
    — HTTP Method: GET
    — Path: “/members/{memberId}”
    — Description: Retrieves details of a specific member by their ID.
   It extracts the memberId from the request’s URL parameters using Gorilla Mux. It then converts the ID to an integer and uses it to call the GetMemberById function from the models’ package. Serializes the member details into JSON format and sends it as a response.
4. DeleteMember:
    — HTTP Method: DELETE
    — Path: “/members/{memberId}”
    — Description: Deletes a member by their ID. It extracts the memberId from the request’s URL parameters using Gorilla Mux. It then converts the ID to an integer and uses it to call the DeleteMember function from the models package. Serializes the deleted member into JSON format and sends it as the response.
5. UpdateMember:
    — HTTP Method: PUT
    — Path: “/members/{memberId}”
    — Description: Updates an existing member’s details.
   It extracts the memberId from the request’s URL parameters using Gorilla Mux. It then converts the ID to an integer and uses it to call the GetMemberById function from the `model` package to retrieve the current member details.
   It parses the request body JSON into a models.Member struct for the updated data.
   It then updates the member’s attributes with the new values if provided (name, email, country, color), saves the changes to the database, and serializes the updated member details into JSON format to send as the response.

In addition to the CRUD operations, each handler function sets the HTTP response status code and content type header appropriately before writing the response.

9. Establish a Connection with the Main Server:

It is time to connect with the main server file:

10. Compile and Run Your Go Project:

With the entire package configured, it’s time to perform a test. Head to the main folder and execute the following command:

//Move to the Man folder
cd main


//Compile the Go server binary file
go build

This action generates a main binary file.
Additionally, within the main folder, execute the package:

go run main.go

At this point, we can proceed to Postman for API testing.

11. Testing API using Postman:

a. CreateMember:

b. GetAllMembers:

c. GetMemberById:

d. UpdateMember:

Congratulations on the successful execution of all the requests on Postman!
The outcomes are in line with our expectations. Keep up the great work and embrace the learning process!

Stackademic

Thank you for reading until the end. Before you go:

• Please consider clapping and following the writer! 👏
• Follow us on Twitter(X), LinkedIn, and YouTube.
• Visit Stackademic.com to find out more about how we are democratizing free programming education around the world.

Building a Beginner-Friendly CRUD API with Golang: Step-by-Step Guide was originally published in Stackademic on Medium, where people are continuing the conversation by highlighting and responding to this story.

We can create a remarkable CRUD API using MySQL, Node.js, and Express that empowers users to build their own food menus effortlessly. In this article, we can easily manage and update food information, such as names, categories, prices, and descriptions.

We will also implement the search for food in the menu using the name string.

Node.js is an open-source, cross-platform JavaScript runtime environment. It allows developers to execute JavaScript code on the server, enabling them to build scalable and efficient network applications. There are more details on the official documentation.

Express is a fast and minimalistic web application framework for Node.js. It simplifies the creation of web apps and APIs by offering a wide range of features and tools. With Express developers can easily manage routing, middleware, and HTTP requests in a structured and organized manner. Everything about the installation and documentation can be found on the link.

MySQL is an open-source relational database management system (RDBMS) that offers a structured and efficient way to store, manage, and retrieve data. You can find the comprehensive installation guide in the documentation.

Pre-requisite:

1. MySQL installed

2. Basic knowledge of Node.js and Express

3. Essential SQL Command Knowledge

Steps:

1. Initializing:
   Create a directory, move into the directory and install the node package manager.

mkdir mysql-food-menu

cd mysql-food-menu

npm init -y

This should create a package.json file.

2. Install Dependencies:

To establish a connection to the database, we will install MySQL, and for the server connection, we will use Express.

npm install mysql express --save

3. Folder arrangement:

Let us create the needed folders for handling the database connection, API controllers and routes separately.

i. connection folder which should contain database.sql and dbconnection.js- This will handle all the connections to the database and table.

ii. controller folder which contains the itemController.js file. This handles the function calls to API.

iii. router folder which contains the itemroutes.js file. This handles the routes for interacting with the server.

iv index.js is the server file.

4. Setting up the server file:

Open the index.js file and add the express server. Set it to listen to a port. We will be using port 2090.

In the directory, open a terminal, and run the command: node index.js.

node index.js

You should see the Message logged Server is listening to port 2090

5. Connecting with the database:

We need to ensure that the MySQL server is active. Then log in and create the database and the corresponding table.

Open the database.sql file and insert commands for creating a database called kitchen and inside the database, create a table called menu.

The menu table has columns for a unique identifier menuId, name, category, price and description.
Run these commands in the MySQL terminal after successfully logging in with your MySQL details.

Now is a good time to set up the connection with the MySQL server. Open the dbconnection.js file, we create a connection with the MySQL server.

This code establishes a connection to a MySQL database using the mysql Node.js module.
It sets up the connection details including the host, user, password, and database name.
The mysqlConnection.connect function is used to attempt a connection, and if successful, it logs a message confirming the connection. If the connection fails, it logs an error message with the details of the error.

6. Set up the controller:

Open the itemcontroller.js file in the controller folder. We create the addItem, getAllItems, getOneItemById, getAllItemsByName, updateOneItem and deleteOneItem functions.

Here’s a summary of each function:

A. addItem: This function adds a new menu item to the database. It extracts information like name, category, price, and description from the request body and inserts them into the “menu” table. It responds with a success message and inserted values, or an error message if there’s an issue.

B. getAllItems: This function retrieves all menu items from the database. It executes a SQL query to select all records from the “menu” table and responds with the retrieved data or an error message.

C. getOneItemById: This function retrieves a single menu item based on its ID. It retrieves the item’s ID from the request parameters, queries the database for all matching items, and responds with the item’s details or appropriate error messages if the item is not found.

D. getAllItemsByName: This function retrieves all menu items that have a name matching a specified string. It takes the name as a parameter, constructs a SQL query with a LIKE condition, queries the database, and responds with the matching items or error messages.

E. updateOneItem: This function updates the details of a menu item. It takes the item’s ID and updated information from the request parameters and body. It first checks if the item exists, and if it does, it performs an update operation on the item’s details. It responds with a success message or error message.

F. deleteOneItem: This function deletes a menu item from the database. It takes the item’s ID from the request parameters, checks if the item exists, and if it does, performs a delete operation. It responds with a success message or error message.

Each function includes error handling to catch and respond to potential issues, such as database errors or missing items. These functions serve as an API for managing menu items in the restaurant’s database.

7. Set up the Routes:

In the itemroutes.js file; we create the routes for the API call.

The routes are designed to correspond with the appropriate HTTP methods. Now would be a good time to update the server file with the routes.

8. Update the Server file:

To enable the server to utilize the routes, all we need to do is import the itemroutes.js file and then harness the power of the app.use() method. Our index.js server file should look like:

Open the terminal and run the command:

node index.js

You should see the logs in the terminal:
Server is listening to port 2090 and Connection Established Successfully.

9: Testing in Postman:

a. Add Item:

b. getAllItems:

c. getOneItemById:

d. getAllItemsByName:

e. updateOneItem:

f. deleteOneItem:

Thank you for reading until the end. Please consider following the writer and this publication. Visit Stackademic to find out more about how we are democratizing free programming education around the world.

Creating a beginner-friendly CRUD API using Node.js, Express, and MySQL was originally published in Stackademic on Medium, where people are continuing the conversation by highlighting and responding to this story.

We will build a CRUD API of Notes with Node.js, Express, and PostgreSQL.

Node.js serves as an outstanding open-source runtime environment, empowering the execution of JavaScript code on the server side. This facilitates the seamless and scalable development of network applications and web services. More details can be found in the official documentation.

Express.js is a minimalist and flexible web application framework for Node.js, designed to simplify the creation of robust and efficient web APIs and applications. It can be installed using the Node Package Manager. There is more about it in the documentation.

PostgreSQL is a powerful open-source RDBMS valued for extensibility, reliability, and advanced SQL support, popular for data storage in diverse applications.

Pre-requisite:
The only prerequisite is a basic understanding of Node.js and PostgreSQL. You might also need to install PostgreSQL on your local machine.

This tutorial will guide you through building a simple Note app. This app enables users to CREATE, READ, UPDATE, and DELETE notes stored in a PostgreSQL database.

Folder Structure:
We require the following components:

a. A controller folder containing noteController.js.
b. A database.sql file for the database and table creation commands.
c. db.js for PostgreSQL pool initialization.
d. The main server file named index.js

STEPS:
1. Initialize the Node server

npm init -y

This command generates a package.json file.

2. Install Express and node-postgres

npm install express pg

This generates the node_modules directory and potentially creates package-lock.json.

3. Initialize the server file by navigating to index.js

Open the terminal, move to the project folder and run

node index.js

Upon execution, it will log “Server is running on port 8989” to the terminal.

4. Let’s generate the database and project table. I suggest including the command in the file before running it in the terminal, reducing the likelihood of errors. Navigate to the Database.sql file.

a. Open the terminal, and log in to the postgres using your details.

Then type psql in the terminal to be able to run the commands below:

sudo -i -u postgres

psql

b. Create a database named notestutorial;

CREATE DATABASE notestutorial;

c. Move into that database and then create a database table with these two commands:

//Move into the database//


\c notestutorial;


//Create a table//


CREATE TABLE note(
    note_id SERIAL PRIMARY KEY,
    description VARCHAR(255)
);

5. Establish a node-postgres connection for database interaction.
Navigate to the db.js file:

The above code sets up a PostgreSQL connection using the “pg” library’s Pool object. The pool instance is configured and exported for broader use. Here's a brief overview:

a. Import Pool Object: Import Pool from "pg" to manage PostgreSQL connections.

b. Configure Connection: Configure pool with user, password, host, port, and database.

c. Create pool Instance: Instantiate Pool using provided configuration.

d. Export pool: Export pool instance for easy access in other app parts.

It configures and exports pool for widespread use, optimizing concurrent database operations.

6. Set up the Controller:

We will create the controller to manage the API function calls.
Open the notecontroller.js file within the controller directory:

The above code features asynchronous functions for CRUD operations on a “note” entity through a database. The pool object from the "db" module handles database queries. Here's a concise explanation:

createNote Function: Creates a new note.

• Parameters: req (request object), res (response object).
• Extracts description from the request body.
• Executes an SQL INSERT query via pool.query to add a note, responding with the new data.

getAllNotes Function: Retrieves all notes.

• Parameters: req, res.
• Executes an SQL SELECT query via pool.query to fetch all records, responding with an array of notes.

getOneNote Function: Retrieves a single note by note_id.

• Parameters: req, res.
• Uses the note_id from request parameters.
• Executes an SQL SELECT query via pool.query with a WHERE clause, responding with the requested note.

updateOneNote Function: Updates an existing note.

• Parameters: req, res.
• Extracts note_id and description from request parameters and body.
• Executes an SQL UPDATE query via pool.query to modify the note description, responding with an update message.

deleteOneNote Function: Deletes a note.

• Parameters: req, res.
• Extracts note_id from request parameters.
• Executes an SQL DELETE query via pool.query to remove a note, responding with a deletion message.

Each function is enclosed in a try...catch block for error handling. Errors trigger console log messages. This code establishes a foundation for CRUD operations on a "note" entity via a PostgreSQL database connection pool.

7. Complete the server file:

Now the final step is the write the API requests in the server file.

app.post("/notes", noteController.createNote)

app.get("/notes", noteController.getAllNotes)

app.get("/notes/:id", noteController.getOneNote)

app.put("/notes/:id", noteController.updateOneNote);

app.delete("/notes/:id", noteController.deleteOneNote)

As a result, the index.js server file will resemble the following:

Now, we have completed the CRUD API, we can run our server and test the API on Postman.

Testing the API
Open the Postman application and enter the Routes:
a. Create a Note

b. Get All Notes:

c. Get a Note by ID:

d. Update a Note:

e. Delete One Note:

Conclusion:
With our combination of Node.js, Express, and PostgreSQL, we have created a powerful CRUD API for managing notes. This showcases the incredible possibilities of modern web development and provides effortless control over your data. As we explore the evolving landscape of web app creation, the knowledge gained here will serve as a solid foundation for future progress.

Happy Hacking =)

Create a CRUD API using Node.js, Express and PostgreSQL was originally published in Dev Genius on Medium, where people are continuing the conversation by highlighting and responding to this story.

Introduction:

PostgreSQL, also known as Postgres, is a powerful and open-source object-relational database management system (ORDBMS). It offers advanced features, high scalability, and robust data integrity, making it a popular choice for various applications.

In this article, we will walk you through installing PostgreSQL on a Linux Ubuntu system. Whether you are a developer, a system administrator, or an enthusiast, this guide will help you get PostgreSQL up and running smoothly.

First, we make sure that the system is updated to the latest packages by running the following commands in the terminal:

sudo apt update
sudo apt upgrade

Step 1: Installing PostgreSQL:

To install PostgreSQL, we will use the apt package manager, which is the default package manager for Ubuntu. Open the terminal and execute the following command:

sudo apt install postgresql postgresql-contrib

This command will download and install the PostgreSQL packages along with the necessary dependencies.

It will also add a default PostgreSQL user — postgres.

Step 2: Verifying the Installation:

Once the installation is complete, PostgreSQL will automatically start running as a background service.
In the Ubuntu terminal, log in as a Postgres user with the following command:

sudo -i -u postgres

Running the command psql when logged in as a Postgres user will show the version of Postgres installed. This command will enable you to log into the PostgreSQL prompt, which allows you to interact with the database management system.

psql

To get more help from the command, you can use the --help command.

psql --help

Step 3: Creating a New PostgreSQL user role (Optional)

This is an optional step. The default Postgres user created when PostgreSQL was installed can be used without issues. You also have the flexibility of creating an additional user.

3a. In the terminal, log in as a Postgres user. Then run the command below:

//Login as a Postgres user

sudo -i -u postgres


//Create a new user role
createuser --interactive

Remember the name of the role added. We will need to add the database with the same name. Add the name of the role and accept the user to be a superuser. For the tutorial purpose, we will use the name o the user as poweruser.

We will create a database for it and also a new user using the same name — poweruser.

3b. Create a database for the user

createdb poweruser

3c. Create a new Linux user that matches that of the database created in 3b above.

sudo adduser poweruser

3d. Open a new terminal and log in using the newly created user

sudo -i -u poweruser

3e. Test the installation on the Postgres user. Type psql after logging in as a user to connect to the database.

psql

Remember that step 3 is optional. One can easily log in using the default Postgres user.

Congratulations! You have successfully installed PostgreSQL on your Ubuntu Linux system.

To expand your knowledge of commands for creating and modifying databases and tables, you can explore the article.

You can now utilize the power and flexibility of PostgreSQL to build robust and scalable database-driven applications.

Happy Coding =)

How to install PostgreSQL using Terminal on Ubuntu Linux was originally published in Dev Genius on Medium, where people are continuing the conversation by highlighting and responding to this story.