Building a Robust Backend in Just 30 Minutes with Outerbase
Tags: outerbase, backend, api, database
Published: 2023-10-11 15:32:59

## Introduction

In the ever-evolving landscape of software development, new tools and technologies consistently emerge. However, every once in a while, something truly groundbreaking makes its mark. Today, we're looking into one such potential innovation “[Outerbase](https://outerbase.com)”, and I promise to be candid, discussing both its strengths and weaknesses.

When the idea for RateMyCraft germinated in my mind, I was both eager and hesitant. My eagerness stemmed from the potential of the project, but my reluctance arose from the anticipated time-consuming backend setup — setting up databases, crafting APIs, and the works. This initial phase usually demands significant time, and frustratingly, often offers little to showcase for all the effort.

Enter Outerbase. A tool that, I believe, has the promise to usher in a transformative shift in how many of us approach backend development. But like all tools, it has its highs and lows, which we'll explore in-depth.

![Rate My Craft Logo](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/owv2sjll0lnjkw1p5l7o.jpg)

{% embed https://www.youtube.com/watch?v=m7LMcFRsmxY %}

## What is RateMyCraft?

RateMyCraft offers users a platform to explore, rate, and review various local services, from plumbers and electricians to bakeries. Think of it as a close-knit community where people share their experiences, ensuring others can make informed choices about local services.

## **Unraveling Outerbase: The Future of Backend Development**

Before diving into the intricacies of RateMyCraft's functionalities, it's imperative to lay the foundation by understanding Outerbase – the backbone of our backend.

### **Setting Up a Database with Outerbase**

The beauty of Outerbase is that it streamlines the process of initializing and managing databases. Here’s a brief walkthrough:

1. **Database Creation**: Within the Outerbase environment, you have the flexibility to connect to an existing MySQL, Postgres, or any other supported database. Alternatively, you can create a new SQLite database from scratch with just a few clicks
    
![DB Table 1](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/47yqugagn2swroc1p6yw.png)
    
2. **Table Creation and Structure Definition**: Once the database is in place, the next step is to define your tables. With Outerbase, this step is straightforward. Use a simple command like from Outerbase web console:
    
    ```sql
    -- Table Creation for 'service_providers'
    CREATE TABLE service_providers (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        services TEXT NOT NULL,
        contact_info TEXT NOT NULL,
        description TEXT,
        city TEXT NOT NULL
    );
    
    -- Table Creation for 'reviews'
    CREATE TABLE reviews (
        id INTEGER PRIMARY KEY,
        provider_id INTEGER,
        nickname TEXT NOT NULL,
        content TEXT,
        rating INTEGER CHECK (rating >= 1 AND rating <= 5),
        FOREIGN KEY (provider_id) REFERENCES service_providers(id)
    );
    ```
    

![DB Table 2](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/m59o15qq61g7lxnv5eqw.png)
    

**Populating the Tables**: Now that your tables are set up, you can populate them with data either by manual entry or by importing data sets. The latter is especially handy if you’re migrating from another system or have bulk data ready.

```sql
-- SQL queries for generating test data can be grabbed from github repo of this project
INSERT INTO service_providers (name, services, contact_info, description, city) 
VALUES 
('Baker Delight', 'Bakery', '+11234567915 | baker@delight.com', 'Experience the finest baked delicacies in Chicago.', 'Chicago'),
('Chicago Car Care', 'Car Mechanic', '+11234567896 | care@chicagocar.com', 'Premium car maintenance services ensuring smooth drives every time.', 'Chicago'),
('CleanSpace NY', 'Cleaning', '+11234567899 | clean@space.com', 'Revitalize your home with professional cleaning services.', 'New York')
-- Add as many services as you want

INSERT INTO reviews (provider_id, nickname, content, rating) VALUES
(1, 'BakingLover', 'The pastries are to die for!', 5),
(2, 'CarFan123', 'Got my car repaired here. Top-notch service!', 4),
(2, 'Jess', 'Pretty quick service. The price was reasonable.', 4),
-- Add as many reviews as you want
```

This whole process will not take more than 5 minutes, but we only have a database with test data, how about the API endpoints let's dive into that and see if we can do the rest in remaining 25 minutes.

## The Outerbase commands magic and API endpoints

Ever felt that building a backend is like trying to solve a jigsaw puzzle with pieces from different sets? I've been there! But are Outerbase Commands any better or more helpful in this regard? Well, my journey led me to some interesting answers. Let's explore.

Think of it as that friend who always has the right tools for the job. Now, let’s dive into how this nifty tool simplified the often intricate process of creating API endpoints for me. For this project, we need multiple endpoints so we can present and update ratings for different service providers.

### 1\. New Command

Let's start with creating the commands for `/providers` endpoint. This endpoint will list all the service providers registered with **RateMyCraft**. To create a command go to  
`+ New` -&gt; `Commands`

This will show a popup where you specify the **Name**, **Path** and **Type (Get/Post/Put/Delete)** for the endpoint. The following screenshot demonstrates this process.

![New Command](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xoc7a1ete416wwsnvkvr.png)

### 2\. Javascript Command Node

The `/providers` endpoint can be invoked with or without `city` `GET` parameters. If the city param is not provided then we will list all the service providers, if the `city` param is provided for example `/providers?city=Miami`, this will return all the services from that city, in this case `Miami`.  
The following javascript code will return the `city name` or `%`, depending on city parameters. Let's write a code to read query parameters into a node and call it a "**City Node**".

```javascript
function userCode() {
    const city = {{request.query.city}};
    if (typeof city !== 'string' || city.trim() === '') {
        return "%";
    } 
    return city;
}
```

### 3\. SQL Query Command Node

Now we will use the city name returned from the javascript node in the SQL query node. The interesting thing here is you can pass on data from one node to the other here we will pass the `city` from **City Node** to **Query Node**, you can see in the following code we are using `{{city-node}}`, which is the placeholder for the return value from the previous node.

```sql
SELECT 
    s.*, 
    ROUND(AVG(r.rating), 1) AS average_rating, 
    COUNT(r.rating) AS total_ratings 
FROM service_providers AS s 
LEFT JOIN reviews AS r ON s.id = r.provider_id 
WHERE s.city LIKE '{{city-node}}' 
GROUP BY s.id;
```

The command will eventually look like the following screenshot and you know what that it `/provider` endpoint is finished, you can access that by simply sending a request to `https://thoughtless-amber.cmd.outerbase.io/providers`. `thoughtless-amber` is the unique id and will change for each project.

![Providers command](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/kv3jus9j3naob1vyq7vv.png)

### Other endpoints

Since providers was our first node it took us a little longer and was done in 10 minutes, but now we have the basic understanding of Outerbase commands, different options and where to find what. Now all we need to do is focus on the logic and write in Outerbase.

Now we will implement the remaining endpoints

#### Retrieving Reviews

This will share the same 2-node structure, the first node will format the data and the second will run the query. Since Outerbase is still in beta the functionality to return in between is yet not available but it has been documented to work like the following where in case of `provider_id` is not given we will return `400`. But currently we can not return in between a node. After this feature is released it will give the developers a lot more control and can do a lot of different things.

* **Endpoint**: `/reviews`
    
* **Nodes**:
    
    ```javascript
    function userCode() {
        const provider_id = {{request.query.provider_id}};
        if (typeof provider_id !== 'string' || provider_id.trim() === '') {
            return {
                status: 400,
                error: "missing first name from request body"
            }
        } 
        return provider_id;
    }
    ```
    
    ```sql
    SELECT * FROM reviews WHERE provider_id = '{{provider-node}}';
    ```
    
* **Testing**: After this is done your `/reviews` endpoint is ready and can be tested using the following example
    

```bash
curl -X GET "https://thoughtless-amber.cmd.outerbase.io/reviews?provider_id=2"
```

#### Searching Service Providers

To demonstrate the simplicity and power of Outerbase commands we will only be using a single node with SQL query for search.

Or we can say that "**SQL Query ≈ API Endpoint**" let that sink in. 🤯

* **Endpoint**: `/search`
    
* **Nodes:**
    
    ```sql
    SELECT * FROM service_providers 
    WHERE 
      name LIKE '%{{request.query.query}}%' 
      OR services LIKE '%{{request.query.query}}%';
    ```
    
* **Testing**: After this is done your `/search` endpoint is ready and can be tested using the following example
    
* ```bash
      curl -X GET "https://thoughtless-amber.cmd.outerbase.io/search?query=Beauty"
    ```
    

#### Fetching Provider Details

For some functions we need to fetch the provider details, for that, we will create the following endpoint.

* **Endpoint**: `/provider`
    
* **Nodes:**
    
    ```sql
    SELECT * FROM service_providers WHERE id = '{{request.query.id}}'
    ```
    
* **Testing**: After this, your `/provider` endpoint is ready and can be tested using the following example\*\*:\*\*
    
    ```bash
    curl -X GET "https://thoughtless-amber.cmd.outerbase.io/provider?id=1"
    ```
    

#### Adding a Review

The rating system without the functionality for adding a review is incomplete. Let's create an endpoint for that as well. For this endpoint, we will select `POST` as command type.

* **Endpoint:** `/review/add`
    
* **Nodes:**
    
    ```sql
    INSERT INTO reviews(provider_id, nickname, content, rating) VALUES({{request.body.provider_id}}, {{request.body.nickname}}, {{request.body.content}}, {{request.body.rating}})
    ```
    
* **Testing**: Now `/review/add` endpoint is ready and can be tested using the following example
    

```bash
curl -X POST "https://thoughtless-amber.cmd.outerbase.io/review/add" \
-H "Content-Type: application/json" \
-d '{"nickname":"Mirza","rating":"5","content":"The best cupcakes in town","provider_id":"1"}'
```

#### Adding a Service Provider

New service providers can also register themselves or users can simply add the last service provider they used themselves in RateMyCraft.

* **Endpoint**: `/provider/add`
    
* **Nodes:**
    
    ```sql
    INSERT INTO service_providers (name, services, contact_info, description, city) VALUES ({{request.body.name}}, {{request.body.services}},{{request.body.contact_info}},{{request.body.description}}, {{request.body.city}});
    ```
    
* **Testing**: Now `/provider/add` endpoint is ready and you can add the provider using the following template.
    
    ```bash
    curl -X POST "https://thoughtless-amber.cmd.outerbase.io/provider/add" \
    -d '{"name":"Mirza",
    "contact_info":"mirza@example.com | +1234232232",
    "city":"New York",
    "description": "We provide all kind of Electronics repairs",
    "services":"Electronics Repair"
    }'
    ```
    

#### Update Service Provider

Users or admins also need the functionality to update any field of service provider for that we will create our first `PUT` request. Due to the current limitation with Outerbase, we will first fetch the current values for the provider and then replace only the ones that the client has requested to update and use the rest as it is.

* **Endpoint**: `/provider/update`
    

**Command:**

```sql
SELECT * FROM service_providers WHERE id = {{request.body.id}};
```

```javascript
function userCode() {
    const requestBody = {
        id: {{request.body.id}},
        name: {{request.body.name}},
        services: {{request.body.services}},
        city: {{request.body.city}},
        contact_info: {{request.body.contact_info}},
        description: {{request.body.description}}
    };

    const current_values = JSON.parse({{data-node}});
    const items = current_values?.response?.items;

    if (!items || !Array.isArray(items) || items.length === 0) {
        return "No items available";
    }
    let item = items[0];
    // Update item properties if the corresponding request body values are valid
    for (let key in requestBody) {
        if (typeof requestBody[key] === 'string' && requestBody[key].trim() !== '') {
            item[key] = requestBody[key];
        }
    }
    return item;
}
```

```sql
UPDATE service_providers 
SET 
    name = {{params-node.name}},
    services = {{params-node.services}},
    contact_info = {{params-node.contact_info}},
    description = {{params-node.description}},
    city = {{params-node.city}}
WHERE 
    id = {{params-node.id}};
```

#### Deleting a Service Provider

To remove a service provider we will use `DELETE` request.

* **Endpoint**: `/provider/delete`
    
* **Command:**
    

```sql
DELETE FROM service_providers WHERE id = {{request.body.id}};
```

Did you see how fast creating these Outerbase commands was? Oh, rather these API Endpoints 😊, and these last 7 endpoints hardly took 15 mins. The only thing that could hinder your progress or could consume more time is the current beta state, the Outerbase team has a lot of ground to make, but the idea is worth waiting for.

APIs which are the backbone for **RateMyCraft** are ready and deployed in barely 30 minutes, The disclaimer here is, that I have spent quite some time with Outerbase and knew what would work out of the box and, what would require workarounds. It might take you longer for the first time but once you are familiar with it, and you understand your problem you can do it even for less.

## Where is the Dashboard?

In applications like RateMyCraft, a dashboard isn't just an accessory—it's the nerve center. It's where you'd typically view data summaries, gain insights, and make decisions. Now, traditionally, creating an intuitive dashboard involves layers of design, development, and countless hours of tweaking. But what if there was a shortcut? Another functionality of Outerbase is the **integration of EZQL**.

With Outerbase's EZQL, the very platform can serve as your dashboard! This isn't about cutting corners; it's about optimizing processes. Instead of toggling between multiple tools, you can query directly and get real-time insights, all within Outerbase.

For instance, need to know the top-rated car mechanics in Chicago? All you need to do is ask the Outerbase magician

> Get the best mechanic from Chicago based on average ratings

![EZQL](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/favnf5f7ix8kb8lvwk8h.png)

![EZQL Results](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zwjeevzai3qdxb9sxxk4.png)

Isn't it magic? 🪄 You can do so many things with Outerbase, not only you, but your non-technical co-founder can access everything without any technical know-how or without waiting for developers to finish the dashboard and add the specific data he need. It's that simple! With EZQL, you're not just building an application; you're crafting an experience—both for your users and yourself as a developer.

## The Final Product

After harnessing the powerful capabilities of Outerbase and weaving them into the backend of RateMyCraft, the outcome is nothing short of spectacular. But as they say, a picture is worth a thousand words. So, let me show you!

![Homepage](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/j1el93ip9a54sdeb9h0s.png)

![View Craft Ratings](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/msry934hqiodwf4w86z7.png)

![Rate Craft](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/221s4umsspan1vs7h3l9.png)

As for the aesthetic and user-friendly frontend, I utilized Vuetify—a fantastic framework for Vue.js. It played a pivotal role in giving life to the visuals and interactivity of RateMyCraft. However, getting deep into the intricacies of Vuetify is beyond the scope of this article. Today, our spotlight remains firmly on the magic and efficiency that Outerbase brought to the table. But if you are interested you can check the code in [GitHub repo](https://github.com/bilalmughal/rate-my-craft) for this project.

The fusion of Outerbase's backend prowess with a polished frontend showcases what modern tools can achieve when used innovatively.

## **The Pros and Cons of Outerbase**

When navigating the seas of software development, it's essential to have a balanced view of the tools we use. Outerbase, despite its groundbreaking capabilities, is no exception. It's worth noting that Outerbase is currently in beta, which means some bumps in the road are to be expected. That said, let's weigh the advantages against the challenges.

### Pros:

1. **Responsive Team that Listens**: There's immense value in having a team behind a product that's not just technically adept but is also receptive to feedback. This means issues get addressed, and user suggestions often shape the tool's evolution.
    
2. **Growing Community on Discord**: A robust community is a sign of a product's potential. With discussions, troubleshooting, and shared experiences, new users find a supportive ecosystem ready to assist.
    
3. **Intuitive UI**: Outerbase's user interface stands out for its clean design and user-centric approach. Even those without a solid technical background find it easy to navigate and understand.
    
4. **Flexibility of Commands**: The ability to craft commands using multiple programming languages provides developers with a versatile toolkit, ensuring they can tackle diverse challenges head-on.
    

### Cons:

1. **Incomplete Documentation**: As with many beta products, the documentation isn't exhaustive. While foundational topics are covered, some nuanced or advanced features lack comprehensive guides or no documentation at all.
    
2. **Broken Features**: Being in beta means not everything is polished to perfection. Users might stumble upon features that don't work as expected. However, with the responsive team behind Outerbase, these issues are often addressed swiftly.
    
3. **Absence of an Inbuilt Authentication System**: Security is paramount, especially in backend operations. The lack of a built-in authentication system means developers might need to integrate third-party solutions or build custom ones, which can be time-consuming.
    

## The Future?

Outerbase has nailed down many things exceptionally well, but as with any evolving product, there's always room for enhancements. As a staunch fan of **OpenAPI specifications**, I'd relish the option to import them, translating directly into Outerbase commands. Each endpoint from the specification could manifest as an Outerbase command, with distinct steps or nodes defined through specialized '**vendor-specific extensions**'. For comparison, AWS employs `x-amazon-apigateway-integration`. In a similar vein, Outerbase could introduce its proprietary extension. I believe this concept harmonizes perfectly with Outerbase's vision of rapid backend development. Implementing such a feature would mark a significant leap towards realizing the effortless backend platform that Outerbase aspires to become.

## Conclusion

This is surely the future for backend development, and some product is going to rule this space, will it be Outerbase? Everything depends on the Outerbase team if they can deliver on their promises.

Like any tool, Outerbase has its strengths and areas for improvement. However, its potential shines through, and as it matures out of beta, many of the current challenges are likely to be ironed out.

Outerbase, with its revolutionary approach to backend operations, enabled the creation of a robust backend in just 30 minutes. For fellow developers out there, this is a testament to the capabilities of modern-day tools. Embrace them, and the sky's the limit!

## Resources

* **Blog Home:** [https://mirzabilal.com](https://mirzabilal.com)
        
* **Source Code:** [github.com/bilalmughal/rate-my-craft](https://github.com/bilalmughal/rate-my-craft)
    
* **Live Demo:** [ratemycraft.mirzabilal.com](https://ratemycraft.mirzabilal.com)