DEV Community: Rizqy Eka Putra

Easily Create an XLSX Output by Using Only HTML Table Component

Rizqy Eka Putra — Sat, 30 Aug 2025 17:02:21 +0000

TLDR; The Origin Story

So, recently while i work on my weekend projects, as well in some projects with my team at Nodewave, there are a significant amount of time (and pains...) consumed when we need to generate a structured XLSX with complex tables that has merged cells (row, columns). Before, we are always writing a custom excel builder with xlsx-js-style for EVERY single XLSX file output, which you can already tell must be really time consuming, nevermind the back and forth to check the result while testing it.

Then, in a discussion, we came with an idea, why not write it with a table components in HTML, which natively already has colspan, rowspan attribute, and also has inline-css for the styling.

As a bunch of curious engineers we started to get going by discussing it with an expert every developer's reach out whenever they have a new idea (TLDR; AI) 🤖.

Initially, all we wanted to do is just to parse an html table that contains rowspan and colspan attributes, process it with cheerio feed it into a custom function that builds excel rows with xlsx-js-style and call it a day.

But then again, we came to think, why not also take care of the styling, xlsx-js-style has the capability to set various things like background color , text color and many more.

So in a span of just several hours, we already came with 4 Minor VERSIONS of the library.

v0.1.0 -> Basic HTML table processor, which have a separate title config (we removed this immediately later on)
v0.1.1 -> Some small refactors
v0.1.2 -> Yet again another refactors, to handle title within the conversion process
v0.2.0 -> Handles inline CSS

So, without further ado, we presents you @nodewave/table-to-xlsx , the idea is simple, it takes an html table in a string, then returns an excel corresponding of that tables, we support 2 output mode :

Buffer Mode
File Output Mode.

Let's See It In Action! 🚀

Now that we've covered the origin story, let me showcase the incredible capabilities of our @nodewave/table-to-xlsx package with some real-world examples.

🎯 Basic Usage - Simple Table Conversion

Let's start with the basics. Here's how simple it is to convert a basic HTML table:

import { convert } from '@nodewave/table-to-xlsx'

const simpleTable = `
<table>
    <tr>
        <th>Name</th>
        <th>Age</th>
        <th>City</th>
    </tr>
    <tr>
        <td>John Doe</td>
        <td>30</td>
        <td>New York</td>
    </tr>
    <tr>
        <td>Jane Smith</td>
        <td>25</td>
        <td>Los Angeles</td>
    </tr>
</table>
`

// Convert to buffer (in-memory)
const buffer = await convert(simpleTable)

// Or save directly to file
await convert(simpleTable, './employees.xlsx')

🔥 Advanced Features - Merged Cells & Complex Layouts

This is where our package truly shines! Let's create a complex table with merged cells that would be a nightmare to build manually with Excel APIs:

const complexTable = `
<table>
    <tr>
        <th style="background-color: #2E86AB; color: white; font-size: 18px; text-align: center;" colspan="6">
            🏢 Company Financial Report Q4 2024
        </th>
    </tr>
    <tr>
        <td style="border: none;" colspan="6"></td>
    </tr>
    <tr>
        <th style="background-color: #F8F9FA; font-weight: bold;" rowspan="2">Department</th>
        <th style="background-color: #F8F9FA; font-weight: bold;" rowspan="2">Team</th>
        <th style="background-color: #F8F9FA; font-weight: bold; text-align: center;" colspan="2">Q3 Results</th>
        <th style="background-color: #F8F9FA; font-weight: bold; text-align: center;" colspan="2">Q4 Results</th>
    </tr>
    <tr>
        <th style="background-color: #E9ECEF; text-align: center;">Revenue</th>
        <th style="background-color: #E9ECEF; text-align: center;">Growth</th>
        <th style="background-color: #E9ECEF; text-align: center;">Revenue</th>
        <th style="background-color: #E9ECEF; text-align: center;">Growth</th>
    </tr>
    <tr>
        <td style="font-weight: bold; color: #2E86AB;">Engineering</td>
        <td>Backend Team</td>
        <td style="text-align: right; color: #28A745;">$2.5M</td>
        <td style="text-align: center; color: #28A745;">+15%</td>
        <td style="text-align: right; color: #28A745;">$2.9M</td>
        <td style="text-align: center; color: #28A745;">+16%</td>
    </tr>
    <tr>
        <td style="font-weight: bold; color: #2E86AB;">Engineering</td>
        <td>Frontend Team</td>
        <td style="text-align: right; color: #28A745;">$1.8M</td>
        <td style="text-align: center; color: #28A745;">+12%</td>
        <td style="text-align: right; color: #28A745;">$2.1M</td>
        <td style="text-align: center; color: #28A745;">+17%</td>
    </tr>
    <tr>
        <td style="font-weight: bold; color: #2E86AB;">Sales</td>
        <td>Enterprise</td>
        <td style="text-align: right; color: #28A745;">$5.2M</td>
        <td style="text-align: center; color: #28A745;">+8%</td>
        <td style="text-align: right; color: #28A745;">$5.8M</td>
        <td style="text-align: center; color: #28A745;">+12%</td>
    </tr>
    <tr>
        <td style="font-weight: bold; color: #2E86AB;">Sales</td>
        <td>SMB</td>
        <td style="text-align: right; color: #FFC107;">$1.2M</td>
        <td style="text-align: center; color: #FFC107;">-3%</td>
        <td style="text-align: right; color: #28A745;">$1.4M</td>
        <td style="text-align: center; color: #28A745;">+17%</td>
    </tr>
    <tr>
        <td style="border: none;" colspan="6"></td>
    </tr>
    <tr>
        <th style="background-color: #D4EDDA; color: #155724; font-size: 16px; text-align: center;" colspan="6">
            📈 Total Q4 Revenue: $12.2M (+15% YoY)
        </th>
    </tr>
</table>
`

// Convert this complex table with merged cells and styling
await convert(complexTable, './financial-report.xlsx')

🎨 Styling Capabilities - CSS Classes & Inline Styles

Our package supports both inline CSS and CSS classes, making it incredibly flexible:

const styledTable = `
<table>
    <tr>
        <th class="header-cell text-center font-bold" style="background-color: #6C757D; color: white; font-size: 20px;" colspan="4">
            🎯 Product Performance Dashboard
        </th>
    </tr>
    <tr>
        <td style="border: none;" colspan="4"></td>
    </tr>
    <tr>
        <th class="subheader text-left font-bold" style="background-color: #F8F9FA;">Product Name</th>
        <th class="subheader text-center font-bold" style="background-color: #F8F9FA;">Category</th>
        <th class="subheader text-center font-bold" style="background-color: #F8F9FA;">Sales</th>
        <th class="subheader text-center font-bold" style="background-color: #F8F9FA;">Rating</th>
    </tr>
    <tr>
        <td style="color: #2E86AB; font-weight: bold;">🚀 Product A</td>
        <td class="text-center">Software</td>
        <td class="text-right font-bold" style="color: #28A745;">$125K</td>
        <td class="text-center font-bold" style="background-color: #D4EDDA; color: #155724;">⭐⭐⭐⭐⭐</td>
    </tr>
    <tr>
        <td style="color: #2E86AB; font-weight: bold;">💡 Product B</td>
        <td class="text-center">Hardware</td>
        <td class="text-right font-bold" style="color: #28A745;">$89K</td>
        <td class="text-center font-bold" style="background-color: #D4EDDA; color: #155724;">⭐⭐⭐⭐</td>
    </tr>
    <tr>
        <td style="color: #2E86AB; font-weight: bold;">🔧 Product C</td>
        <td class="text-center">Service</td>
        <td class="text-right font-bold" style="color: #FFC107;">$67K</td>
        <td class="text-center font-bold" style="background-color: #FFF3CD; color: #856404;">⭐⭐⭐</td>
    </tr>
</table>
`

await convert(styledTable, './product-dashboard.xlsx')

📊 Real-World Use Case - Invoice Table

Here's a practical example of an invoice table that demonstrates complex merging and professional styling:

const invoiceTable = `
<table>
    <tr>
        <td style="border: none; font-size: 24px; font-weight: bold; color: #2E86AB;" colspan="6">
            �� NODEWAVE TECHNOLOGIES
        </td>
    </tr>
    <tr>
        <td style="border: none; font-size: 14px; color: #6C757D;" colspan="6">
            123 Innovation Street, Tech City, TC 12345 | Phone: (555) 123-4567
        </td>
    </tr>
    <tr>
        <td style="border: none;" colspan="6"></td>
    </tr>
    <tr>
        <td style="border: none; font-weight: bold;" colspan="3">Bill To:</td>
        <td style="border: none; font-weight: bold; text-align: right;" colspan="3">Invoice Details:</td>
    </tr>
    <tr>
        <td style="border: none;" colspan="3">
            John Smith<br>
            ABC Company<br>
            456 Business Ave<br>
            Corporate City, CC 67890
        </td>
        <td style="border: none; text-align: right;" colspan="3">
            Invoice #: INV-2024-001<br>
            Date: ${new Date().toLocaleDateString()}<br>
            Due Date: ${new Date(Date.now() + 30 * 24 * 60 * 60 * 1000).toLocaleDateString()}<br>
            Terms: Net 30
        </td>
    </tr>
    <tr>
        <td style="border: none;" colspan="6"></td>
    </tr>
    <tr>
        <th style="background-color: #2E86AB; color: white; text-align: center;">Item</th>
        <th style="background-color: #2E86AB; color: white; text-align: center;">Description</th>
        <th style="background-color: #2E86AB; color: white; text-align: center;">Qty</th>
        <th style="background-color: #2E86AB; color: white; text-align: center;">Rate</th>
        <th style="background-color: #2E86AB; color: white; text-align: center;">Amount</th>
        <th style="background-color: #2E86AB; color: white; text-align: center;">Tax</th>
    </tr>
    <tr>
        <td style="font-weight: bold;">Web Development</td>
        <td>Custom e-commerce platform with admin dashboard</td>
        <td style="text-align: center;">1</td>
        <td style="text-align: right;">$15,000</td>
        <td style="text-align: right; font-weight: bold;">$15,000</td>
        <td style="text-align: right;">$1,500</td>
    </tr>
    <tr>
        <td style="font-weight: bold;">UI/UX Design</td>
        <td>User interface and experience design</td>
        <td style="text-align: center;">1</td>
        <td style="text-align: right;">$5,000</td>
        <td style="text-align: right; font-weight: bold;">$5,000</td>
        <td style="text-align: right;">$500</td>
    </tr>
    <tr>
        <td style="font-weight: bold;">Testing</td>
        <td>Quality assurance and testing services</td>
        <td style="text-align: center;">1</td>
        <td style="text-align: right;">$3,000</td>
        <td style="text-align: right; font-weight: bold;">$3,000</td>
        <td style="text-align: right;">$300</td>
    </tr>
    <tr>
        <td style="border: none;" colspan="6"></td>
    </tr>
    <tr>
        <td style="border: none;" colspan="4"></td>
        <td style="background-color: #F8F9FA; font-weight: bold; text-align: right;">Subtotal:</td>
        <td style="background-color: #F8F9FA; font-weight: bold; text-align: right;">$23,000</td>
    </tr>
    <tr>
        <td style="border: none;" colspan="4"></td>
        <td style="background-color: #F8F9FA; font-weight: bold; text-align: right;">Tax (10%):</td>
        <td style="background-color: #F8F9FA; font-weight: bold; text-align: right;">$2,300</td>
    </tr>
    <tr>
        <td style="border: none;" colspan="4"></td>
        <td style="background-color: #2E86AB; color: white; font-weight: bold; font-size: 16px; text-align: right;">TOTAL:</td>
        <td style="background-color: #2E86AB; color: white; font-weight: bold; font-size: 16px; text-align: right;">$25,300</td>
    </tr>
</table>
`

await convert(invoiceTable, './invoice.xlsx')

🚀 API Flexibility - Multiple Import Styles

Our package supports multiple import styles for maximum flexibility:

// Style 1: Default import (class-based)
import TableToXlsx from '@nodewave/table-to-xlsx'
const buffer1 = await TableToXlsx.convert(html)

// Style 2: Named imports (functional)
import { convert, convertToFile, convertToBuffer } from '@nodewave/table-to-xlsx'
const buffer2 = await convert(html)
const filePath = await convertToFile(html, './output.xlsx')

// Style 3: Namespace import
import * as TableToXlsx from '@nodewave/table-to-xlsx'
const buffer3 = await TableToXlsx.convert(html)

🎯 Supported CSS Properties

Our package has the ability to parse and converts these CSS properties:

Colors: background-color, color, border-color
Typography: font-size, font-weight, text-align
Borders: border, border-style, border-color
CSS Classes: text-center, text-left, text-right, font-bold, font-normal

🚀 Get Started Today

npm install @nodewave/table-to-xlsx

No more painstaking XLSX producer API calls. No more complex cell merging logic. Just write your table in HTML (which you probably already know how to do, or ask AI to do it for you 😜) and let our package handle the rest!

🌱 Contributing to this Package

At Nodewave, we believe in collaboration to boost developer productivity and experience. So if you have any idea for this package to extends it functionality any further, please feel free to open an issue and create your PR at our repository. And don't forget to drop some ⭐️ to it also 👋.

Dynamic Multi Column and Relational Filtering With Prisma ORM

Rizqy Eka Putra — Mon, 24 Jul 2023 21:36:03 +0000

If you already use Prisma, then you already know all about the awesomeness, except maybe the migration or schema part when you are working in a big team.

But anyway, my blog today will be about how we can use Prisma Query to achieve dynamic filtering!

To give you a little bit more context, what I am talking about is building one function that can apply to all your sql table, with fully customizeable :

Aplly Multi-Filtering a table by any column and it's relation too!
Search by any column and it's relation too !
Ordering by any column

How is this possible?

Maybe some of you guys have already kinda guess it right, it's because, prisma use JS / TS most precious thing (Object) in order to execute any query, especially the findMany query.


const blogs = await prisma.blogs.findMany({
    where:{
       title: "A blog"
    },
    orderBy:{
        createdAt:'desc'
    }
})

See how the WHEREclause and ORDER BY is basically formed by an object?

That's what we'll experiment with as we go.
In this part 1, we'll go with the WHERE clause for data filtering first, other things will hopefully covered in next parts of this series ! :D

Enough Talk, Lets Get Started

Setting Up

First things first, let's set up our mini server with just some several dependencies

npm init -y

npm install express prisma @prisma/client

Don't forget to initialize the prisma folders too !

npx prisma init

Alright, now we basically have something like this in our project

node_modules
prisma
- schema.prisma

If you don't want to go the hassle of setting-it-up , you can clone the repository here

Database Config and Prisma Schema

If you're kinda new, prisma schema also contains the database url, which actually can be overrided later on the code when you initialize prisma client instance.

All you have to do is just fill in your Database Connection URI in the .env file (generated from npx prisma init) and choose your provider, i'll be using mysql for this tutorial. But I think there'll be no difference in postgres too !

For this tutorial, we'll keep it simple, we'll just do it with two entities

Blogs
User

and i'll just use enum for the blog category.

The schema will look like this :

// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "mysql"
  url      = env("DATABASE_URL")
}

enum BlogCategory {
  BACKEND
  FRONTEND
  MOBILE
}

model Blogs {
  id        String       @id @default(uuid())
  title     String
  content   String
  category  BlogCategory
  writer    User         @relation(fields: [writerId], references: [id])
  writerId  String
  createdAt DateTime     @default(now())
  updatedAt DateTime     @updatedAt
}

model User {
  id       String  @id @default(uuid())
  username String  @unique
  fullName String
  blogs    Blogs[]
}

And we're done with schema, now we just have to migrate it with this command :

npx prisma migrate dev --name migrationName

Implementation

Folder Structure

Basically, i will just split the code into some parts

server.js (Our Express based webserver to do request and response)
services/blogService.js ( we'll do logics here)
services/buildFilterQuery.js (we'll apply the filtering before passing it to the query here)
prisma-instance.js (prisma instance will be stored here)

It'll look basically like this :

Initalizing Server and Prisma Instance

On this part, we'll create one route, and then inside it, we'll check whatever filters that is provided.

server.js


const express = require("express");
const { getAll } = require("./blogs/blogService");


const app = express();



const checkFilteringQuery = (req) => {
    const enabledFilters = {};

    if (req.query.searchKey) {
        enabledFilters.searchKey = req.query.searchKey;
    }

    if (req.query.searchValue) {
        enabledFilters.searchValue = req.query.searchValue;
    }

    if (req.query.filters) {
        // This needs to be parsed into object first, because we are retrieving this from a request query, which is basically just a string.
        enabledFilters.filters = JSON.parse(req.query.filters);
    }


    if (req.query.orderKey) {
        enabledFilters.orderKey = req.query.orderKey;
    }

    if (req.query.orderRule) {
        enabledFilters.orderRule = req.query.orderRule;
    }


    if (req.query.page) {
        enabledFilters.page = req.query.page;
    }

    if (req.query.rows) {
        enabledFilters.rows = req.query.rows;
    }

    return enabledFilters
}



app.get("/blogs", async (req, res) => {
    const enabledFilters = checkFilteringQuery(req);

    const result = await getAll(enabledFilters);


    return res.status(200).json({
        data: result
    })
})

app.listen(3000, () => {
    console.log("[INFO] READY!!!")
})

If you are a bit confused, i'll explain it shortly, basically in this server.js we already did :

Create a simple express app
Create one GET routes to retrieve blogs data
And we'll only use combination of filters that client / user choose to pass via request. We are doing this by simply checking it inside the checkFilteringQuery function and then pass it to the blogService's getAll function (will be explained later).

And next up, it's just a simple task, we'll create a prisma instance in a separate file (just to get close into real-world scenario here :p)

prisma-instance.js

const { PrismaClient } = require("@prisma/client")

const prisma = new PrismaClient();


module.exports = {
    prisma
}

Building The Query and Service

Okay, so now, this is where the magic happens , we'll try to manipulate prisma's findMany query, to be more flexible, matching our end-user requirements of dynamic filtering.

services/buildQuery.js

const buildFilterQuery = (enabledFilters) => {
    const usedFilter = {
        where: {
            AND: []
        }
    };


    if (enabledFilters.filters) {

        /*
                Filters would look like this : 

                {
                    "filter1":["a","b"],
                    "filter2":["a"]
                }
            */


        // First, we'll loop through the key inside enabledFilters . filters

        // IT'S IMPORTANT THAT key of these filter object, reflect to the column in our database !
        for (const key in enabledFilters.filters) {

            // We'll store the filter values (array of value that we want to be inside where clause, to make the code more readable)
            const filterValues = enabledFilters.filters[key];



            /* In this part we'll include relational filter as well, such as user.fullName or user.username
                It's because, prisma support this, the where clause will looks like : 

                where:{
                    relation:{
                        column : "value"
                    }
                }

            */
            if (key.includes(".")) {
                let [relation, column] = key.split(".");

                /*If values array length is just 1 , we'll just filter for exact match 

                 Pretty much like : 

                    prisma.blog.findMany({
                        where:{
                            relation:{
                                column: "value"
                            }
                        }
                    })

                */

                if (filterValues.length === 1) {
                    usedFilter.where.AND.push({
                        [`${relation}`]: {
                            [`${column}`]: filterValues,
                        },
                    });
                }

                /*But if values array length is more than 1 , we'll filter with `in` operator

                 Pretty much like : 

                    prisma.blog.findMany({
                        where:{
                            relation:{
                                column: {
                                    in : ["value1", "value2"]
                                }
                            }
                        }
                    })

                */
                else {
                    usedFilter.where.AND.push({
                        [`${relation}`]: {
                            [`${column}`]: { in: filterValues,
                            },
                        },
                    });
                }
            }
            /* This next part, is for filtering column that is available in our table 

                where:{
                    column:"value"
                }

            */
            else {
                if (filterValues.length === 1) {
                    usedFilter.where.AND.push({
                        [`${key}`]: filterValues[0]
                    })
                } else {
                    usedFilter.where.AND.push({
                        [`${key}`]: { in: filterValues,
                        },
                    });
                }
            }
        }
    }



    if (enabledFilters.searchKey && enabledFilters.searchValue) {
        /*
            Same logic as filter applied here, if the searchKey include ".", then it means we are searching based on relation
        */
        if (enabledFilters.searchKey.includes(".")) {
            let [relation, column] = enabledFilters.searchKey.split(".");
            usedFilter.where.AND.push({
                [`${relation}`]: {
                    [`${column}`]: {
                        contains: `${enabledFilters.searchValue}`,
                    },
                },
            });
        } else {
            usedFilter.where.AND.push({
                [`${enabledFilters.searchKey}`]: {
                    contains: `${enabledFilters.searchValue}`,
                },
            });
        }
    }



    return usedFilter;
}

Right now, the code may looks a bit crazy, but for now let's just try to understand the code based on comments that I already put there, and later on, during the testing part, I'll break it down about how the filter is applied in various cases.

Now we're done building the query, we'll call prisma's find many and from our blogService ,

services/blogService.js


const { prisma } = require("../prisma-instance");
const { buildFilterQuery } = require("./buildQuery");

const getAll = async(filters) => {

    const usedFilter = buildFilterQuery(filters);

    const blogs = await prisma.blogs.findMany({
        ...usedFilter,
        include: {
            writer: true
        }
    })

    return blogs
}


module.exports = {
    getAll
}

Tests and Explanation

Okay, i've promised you that the crazy looking code from some minutes ago, is going to be explained here as we test the functionality right

Data shown below is inserted by seeding, you can find it on repository I have listed for you back on setup section !

Test 1 : Filter by Blog's Category

Filtering by One Category

Okay, so what is happening here is:

We provide a request query of :

{"category":["BACKEND"]

which our buildFilterQuery function translates into :

where:{
    AND:[
       {category:"BACKEND"}
    ]
}

combine this with the full prisma query in blogService.js, it will actually looks like this :

prisma.blogs.findMany({
   where:{
    AND:[
       {category:"BACKEND"}
    ]
   },
   include:{
     writer:true
   }
})

What if we want to filter blog with category of BACKEND and FRONTEND?

Easy! we'll just pass this into the request query :

{"category":["BACKEND","FRONTEND"]

and it'll translate to this :

where:{
    AND:[
       {category: {in:["BACKEND","FRONTEND"]}
    ]
}

combine this with the full prisma query in blogService.js, it will actually looks like this :

prisma.blogs.findMany({
   where:{
    AND:[
        {category: {in:["BACKEND","FRONTEND"]}
    ]
   },
   include:{
     writer:true
   }
})

Notice that if we give multiple argument to it, it will need to use the 'in' operator.

Test 2 : Filtering by Category + Searching

The Request Will Look Like this :

And then, our buildFilterQuery function, will simply translate this into :

where:{
    AND:[
       {category: "BACKEND"},
       {title:{
           contains : "Part 2"
         }
       }
    ]
}

And the full query will be :

prisma.blogs.findMany({
   where:{
     AND:[
       {category: "BACKEND"},
       {title:{
           contains : "Part 2"
         }
       }
    ]
   },
   include:{
     writer:true
   }
})

Test 3 (Bonus) Filter by Category and Search By Writer's Name

Oh yes, we can do that !
If you look at filter query, for filtering and searching, we have if condition that will check if the filter key or the searchKey contains "." inside of it,

to use it we'll just simply do :

and our buildFilterQuery translate this into :

where:{
    AND:[
       {category: "BACKEND"},
       {
         writer:{
           fullName:{
              contains : "Writer 2"
           }
         }
       }
    ]
}

And the full query will be :

prisma.blogs.findMany({
  where:{
    AND:[
       {category: "BACKEND"},
       {
         writer:{
           fullName:{
              contains : "Writer 2"
           }
         }
       }
     ]
   },
   include:{
     writer:true
   }
})

So at last, we got an Object Relation Mapper that works really-well with Object Manipulaton Right !

With this, you can even have many more endpoints and will just have to apply this one filtering function for all different entities in your application.

Stay tuned for the next parts where we will go through more with things like ordering, paginating (with cursor and limit-offset).

Cheers !

Github Repository: here

Connect With Me :
LinkedIn

Simple CRUD REST API With Lumen + MySQL + Eloquent ORM

Rizqy Eka Putra — Wed, 21 Oct 2020 18:12:41 +0000

Introduction

Hello there ! I am Rep , Software Engineer in the making and currently a 3rd year Computer Engineering student ! In this post i am going to share a quick guide on creating a CRUD ( Create , Read , Update , Delete ) Rest API With Lumen , MySQL Database and also Eloquent ORM .

Why Lumen ?

When you are only intended to Create an API , you wont need lots of Laravel default Libraries / Dependencies , because the goals is only to output a plain data formatted with most probably JSON , therefore we'll use Lumen , a micro-framework which utilize laravel's best part , with smaller size and faster execution time too!

What should i know or have to follow this post?

Tools

Make sure you already installed :

PHP 7.2 or Higher
Composer
MySQL Server ( You can install it via XAMPP on Windows , or via your favorite Packet Manager on UN*X like system)
PHPMyAdmin ( You get this automatically via XAMPP , you can install it on linux via Packet Manager also)

Knowledge

Several things you need to know before following this post :

Basic Request / Response on Web
PHP ( Very , very , very mandatory )
A bit of Laravel Knowledge
SQL Knowledge (would be really helpful)

Configuring the Project

In this tutorial , we'll create a simple CRUD for a Product Entity , we'll name our project "product-rest-api" , please kindly remember that by the time this tutorial is being written , the latest release of Lumen and Laravel is 8.0 .

Creating The Project

Firstly , we'll have to create the project first , by executing this command :

Once the project has successfuly built , you can change your directory to the project directory (product-rest-api) and run this command to start the server :

You can test the endpoint via Postman or Your web browser by sending a GET request to the "/" route

Result :

If you get the same response as shown on image above , it means that the project has built sucessfully and you're ready to move on to the next step.

Configuring The Database

We need a database to later save / hold the migrated table from lumen , therefore we'll need to create one in MySQL , i'll give the database name "product-rest" , and i'll do it via the terminal to speed up the process , you can also create it via PHPMyAdmin.

*Note : if you are using Windows and XAMPP , dont forget to turn on the MySQL Service and Apache (if you are going to use PHPMyAdmin) via the XAMPP Control Panel.
If you are using linux , start the mysql / mariadb service via your terminal
*

Creating The Database Via Terminal / CMD :

Once you're done with the database , now open your favorite Code Editor and open your project folder . Once your project folder are loaded , open the .env file , and do the configuration for your project database (don't forget to stop the server running first)

The Important part of the .env file configuration as shown above is to make sure you filled your Database Configuration correctly , configuration related to Database are those started with **DB_**.

One more step is done , the next step is to create the database migration or defining the database schema , while working with laravel , you could easily utilize the artisan's make:model with -m option to automatically generate a model and a migration , unfortunately this is not available in Lumen , therefore we'd have to create the migration first and later on , create the Model .

Create the product table migration with this command :

Command above will create you a migration file which will be located on database/migrations , locate the create_products_table file and open it up.

Once you already opened it , we'll start on Creating the product schema , it'll just have some basics data like :

Name
Price
Category

Lets jump right into the code , inside the CreateProductsTable class , you will find a public function up() , we'll define our schema there , or to put simply , the function will looked like this :

Once we already done with defining the schema , we would have to create a Model class named Product.php , but before we do that , keep in mind that Lumen dont have Facades nor Eloquent enabled by default , therefore we'll need to first turn it on by uncommenting these following lines located on bootstrap/app.php on line 26 and 28 respectively

Line to Uncomment

$app->withFacades();
$app->withEloquent();

Next thing is to create the Product.php file inside the app/Models directory , and fill it with these code :

The protected $guarded = [] means that we'll allow the web app to fill datas to any column on the table.

The last configuration we'll have to do is to run the migration , it can be done by simply running this command below

php artisan migrate

Check your database , and see you'll get the product table with 4 columns containing id , name , price and category .

Building The Logic

Defining the routes

Our API will have 5 Endpoints , those are :

GET /product to get all products data
GET /product/{id} to get a product by its ID
POST /product to create new product
PUT /product/{id} to update a product base on its id
DELETE /product/{id} to delete a product base on its id

Before we create the routes , it'll be better to create the controller first and then create functions that will represents each action. In order to do so , we need to create a ProductController.php file first on the app/Http/Controllers directory

First , we'll need to use the Product Model inside this class and we'll also need to parse incoming request , therefore we'll add these lines of code:

use App\Models\Product;
use Illuminate\Http\Request;

And after that , we'll need to create a function that would return all Products data , to do so , we'll create a function named index , the controller will look pretty much like this :

Next up, below the index function , we'll create a function called show , which will take up a parameter of id , and return a product based on it id :

We're done showing products , now its time to create a new one with the create function , which will accept a parameter of $request with type Request :

Fancy an update to your product? we'll create a function which will takes two parameter consist of $request with type Request and the product's id we're going to update :

What will happen if the product is no longer produced ? it will get deleted from the product list , therefore the last function we are going to create is a delete function which will takes up a parameter of product id :

The Full ProductController.php Code would look like this :

Creating The Routes

We're done with all the logics , but how could we access all these logics , the answer is by binding the route to the controller's function .

Navigate to routes directory , and open up the web.php , we'll changed it , so it'll match the routes we have defined above before :

That's it for the codes and logic stuff , next up we'll test the Routes , starting from creating the product first , you might realize that we dont have any product yet .

** Note : you could also use Seeder to Seed data into your database first **

Testing the EndPoints

Create Product

Create a POST request to localhost:8000/product via Postman , and fill the body section with raw option , and toggle the JSON Option , more or less like this :

Create 2 or 3 Products so that we could see the difference between index and show function afterwards.

Show Products

Now open a new tab in postman, we'll send a GET request to localhost:8000/product which is binded to index() function and will return all products

And turns out , it works , it gives us all of the products data.

Show Product by ID

In my products data , i have products with ID of 2 , 3 and 4 , you could check it from the previous response , and try to access only 1 product by sending a GET Request to localhost:8000/{id} in this case i'll try to get a product with id of 2 , so the request will be a GET to localhost:8000/2 :

Yes ! it responded correctly by showing us only a data which id is 2 .

Update Product

To update a product , we'll have to send a PUT request, which will require request parameters , just like we did with Create Product request , and the id of product we're going to update , therefore it will be a PUT request to : localhost:8080/product/{id} this time i will update the previous data of ID 2 , and change the product price to $3000 , notice that you'll need to re-write the name and category data as well.

And once again , it works perfectly , it send us back a json response with the product's updated data.

Delete a Product

Because i changed the price just recently , turns out people are started to grab the Laptops very quickly and it is now out of stock until a new version released .

So now we'd have to delete the product , by sending a DELETE request to localhost:8000/product/2

We'll also later validate this request by showing all products, because it should have been gone afterwards.

And if we checked it by sending a GET request to /product , it returned :

It has been verified that all of the routes are working properly , because it only responded with 2 items with ID of 3 and 4.

That's a Wrap

Thanks for you all who finally made it to the bottom and successfully created a REST API with Lumen , there's a lot of things you could improve from the current API , such as request validation etc.

And if you want to see the source code , feel free to fork it from my repository at : https://github.com/rizqyep/product-rest-api

Happy Coding !