Outerbase is a cloud-based database interface that modernizes how we interact with databases. Typical Database interface supports only basic CRUD operations in a spreadsheet form. Outerbase sets itself apart by offering the ability to query databases using EZQL (a natural language to SQL for data queries), data visualization through plugins, and workflow automation using commands. With Outerbase, you can save time and hassle because everything you need is in one location.

One of the features offered in Outerbase is the ability to create and install plugins. Plugins enable us to perceive and interpret our data in custom visually engaging experiences. In this article, I will describe how I created a plugin for Outerbase to visualize GPS information.

About the Plugin

I would like to create a plugin that allows users to visualize the addresses in the database table on a map view (Google Maps in this case). Instead of just looking at GPS coordinates in a number form, why not visualize them directly on a map? It is more appealing and useful.

For the demo, I am using a dummy database generated using UIBakery (https://uibakery.io/sql-playground). They can generate a Car dealer database which contains a table that stores the location of all cars they have. The plugin uses the longitude and latitude information to show the location on a map.

It is a simple plugin but I imagine this plugin can also be used in different scenarios or domains, for example, Asset Management where you can show the location of your assets visually on a map.

Creating the Plugin

In this section, we will see how the configuration model is defined. After the configuration model is ready, we can start building the configuration view and the table plugin view.

Configuration Model

Here we define a class model that captures the types of information for the plugin to render. For the Map View plugin, we want the users to specify which column(s) to reference the following fields:

1. Longitude and Latitude.

2. Image.

3. Title.

4. Subtitle.

5. Description.

The longitude and latitude are used to create the markers on the map; the remaining items are used to render the "detail view" when the user clicks on the marker.

Two extra fields are added: longitudeKey and latitudeKey.

class OuterbasePluginConfig_$PLUGIN_ID {
    // Inputs from Outerbase for us to retain
    tableValue = undefined
    count = 0
    page = 1
    offset = 50
    theme = "light"

    // Inputs from the configuration screen
    imageKey = undefined
    optionalImagePrefix = undefined
    titleKey = undefined
    descriptionKey = undefined
    subtitleKey = undefined
    longitudeKey = undefined
    latitudeKey = undefined

    // Variables for us to hold state of user actions
    deletedRows = []

    constructor(object) {
        this.imageKey = object?.imageKey
        this.optionalImagePrefix = object?.optionalImagePrefix
        this.titleKey = object?.titleKey
        this.descriptionKey = object?.descriptionKey
        this.subtitleKey = object?.subtitleKey
        this.latitudeKey = object?.latitudeKey
        this.longitudeKey = object?.longitudeKey
    }

    toJSON() {
        return {
            "imageKey": this.imageKey,
            "imagePrefix": this.optionalImagePrefix,
            "titleKey": this.titleKey,
            "descriptionKey": this.descriptionKey,
            "subtitleKey": this.subtitleKey,
            "latitudeKey": this.latitudeKey,
            "longitudeKey": this.longitudeKey
        }
    }
}

Configuration View

After defining the configuration model, we can start defining how we present the user interface to the users so that they can fill in the configuration model. For this plugin, I would like to ask the users to specify which column each field should link to. In addition, the detail view will be previewed when the user starts filling in the fields.

Thankfully Outerbase team has done an awesome job providing an example plugin code. I used the example code and made some small modifications to build the configuration view for my plugin.

What I did was add two extra fields for the longitude and latitude. The preview is still displayed on the right side of the view.

class OuterbasePluginConfiguration_$PLUGIN_ID extends HTMLElement {

    // ...

    render() {
        let sample = this.config.tableValue.length ? this.config.tableValue[0] : {}
        let keys = Object.keys(sample)

        if (!keys || keys.length === 0 || !this.shadow.querySelector('#configuration-container')) return

        this.shadow.querySelector('#configuration-container').innerHTML = `
        <div style="flex: 1;">

            // ... Truncated ...

            <p class="field-title">Subtitle Key</p>
            <select id="subtitleKeySelect">
                ` + keys.map((key) => `<option value="${key}" ${key === this.config.subtitleKey ? 'selected' : ''}>${key}</option>`).join("") + `
            </select>

            <p class="field-title">Longitude Key</p>
            <select id="longitudeKeySelect">
                ` + keys.map((key) => `<option value="${key}" ${key === this.config.longitudeKey ? 'selected' : ''}>${key}</option>`).join("") + `
            </select>

            <p class="field-title">Latitude Key</p>
            <select id="latitudeKeySelect">
                ` + keys.map((key) => `<option value="${key}" ${key === this.config.latitudeKey ? 'selected' : ''}>${key}</option>`).join("") + `
            </select>

            <div style="margin-top: 8px;">
                <button id="saveButton">Save View</button>
            </div>
        </div>

        <div style="position: relative;">
            <div class="preview-card">
                <img src="${sample[this.config.imageKey]}" width="100" height="100">

                <div>
                    <p style="margin-bottom: 8px; font-weight: bold; font-size: 16px; line-height: 24px; font-family: 'Inter', sans-serif;">${sample[this.config.titleKey]}</p>
                    <p style="margin-bottom: 8px; font-size: 14px; line-height: 21px; font-weight: 400; font-family: 'Inter', sans-serif;">${sample[this.config.descriptionKey]}</p>
                    <p style="margin-top: 12px; font-size: 12px; line-height: 16px; font-family: 'Inter', sans-serif; color: gray; font-weight: 300;">${sample[this.config.subtitleKey]}</p>
                </div>
            </div>
        </div>
        `

        // ....
        // Whenever the latitudeKey and longitudeKey are changed, re-render the view.

        var latitudeKeySelect = this.shadow.getElementById("latitudeKeySelect");
        latitudeKeySelect.addEventListener("change", () => {
            this.config.latitudeKey = latitudeKeySelect.value
            this.render()
        });

        var longitudeKeySelect = this.shadow.getElementById("longitudeKeySelect");
        longitudeKeySelect.addEventListener("change", () => {
            this.config.longitudeKey = longitudeKeySelect.value
            this.render()
        });
    }
}

Here is the final result.

Table Plugin View

This is the most important part of the plugin. The way it works is as follows: It receives all of the rows from the table and displays the results to the users. Using this plugin, we want to display the results to the users on a Google Maps view.

Given the longitude and latitude information, we will create a marker for each entry in the table. When the user clicks on one for the marker, they will see more detailed information about the marker.

The first challenge in using embedded Google Maps API in this plugin is importing the Google Maps API. Google provides a guide on how to use Google Maps API in a Web component. The document can be found here. It is still in preview mode, so I guess things might change in the future.

To import Google Maps API, I created a new DOM element for script. Here we can specify the src of the script. After that, we need to append this element to the shadowDOM. The script will be evaluated when it is being appended.

// ...
var script = document.createElement('script')
script.type = 'text/javascript'
script.async = true
script.src = "//maps.googleapis.com/maps/api/js?key=<YOUR_API_KEY>&libraries=maps,marker&v=beta&callback=initMap";

// ...

class OuterbasePluginTable_$PLUGIN_ID extends HTMLElement {
    // ...

    constructor() {
        super()

        this.shadow = this.attachShadow({ mode: "open" })    
        this.shadow.appendChild(script)
        this.shadow.appendChild(templateTable.content.cloneNode(true))
    }

After the Google Maps Javascript API has been included, we can start using the map by using the <gmp-map> custom element in the render function of the plugin table class. To add a marker, we can nest an <gmp-advanced-marker> element inside <gmp-map>. So in the code snippet, you can see that the <gmp-advanced-marker> element is added for every table row in the database (this.config.tableValue).

In addition, I would like to show a small info window showing more detailed information when a marker is being clicked on. To achieve this, we need to add an event listener to every <gmp-advanced-marker> element and the callback whenever the event occurs. The info window is just an HTML element. Since every map marker is unique, we need to pass some information from the TableValue. This can be easily done by adding data-<attribute> elements into the <gmp-advanced-marker> element. We can access this information in the callback by accessing the dataset variable.

Here is what the complete render() function looks like.

render() {

        console.log(this.config.page)

        this.shadow.querySelector("#container").innerHTML = `
        <div class="grid-container">
            <h1>Welcome to the Outerbase Car Dealership!</h1>
            <div class="grid-item">
                <gmp-map id="marker-click-event-example" center="43.4142989,-124.2301242" zoom="4" map-id="DEMO_MAP_ID">
                    ${this.config?.tableValue?.length && this.config?.tableValue?.map((row) => `
                        <gmp-advanced-marker position="${row[this.config.latitudeKey]},${row[this.config.longitudeKey]}" title=${row[this.config.titleKey]} 
                            data=title='${row[this.config.titleKey]}'
                            data-image='${row[this.config.imageKey]}'
                            data-description='${row[this.config.descriptionKey]}'
                            data-subtitle='${row[this.config.subtitleKey]}'
                        >
                        </gmp-advanced-marker>
                    `).join("")}
                </gmp-map>
            </div>

            <div style="display: flex; flex-direction: column; gap: 12px;">
                <h1>What Next?</h1>
                <button id="previousPageButton">Previous Page</button>
                <button id="nextPageButton"}>Next Page</button>
            </div>
        </div>
        `

        const advancedMarkers = this.shadow.querySelectorAll("#marker-click-event-example gmp-advanced-marker");

        for (const advancedMarker of advancedMarkers) {
            customElements.whenDefined(advancedMarker.localName).then(async () => {
                advancedMarker.addEventListener('gmp-click', async () => {

                    if (this.infoWindow) {
                        this.infoWindow.close()
                    }

                    const {InfoWindow} = await google.maps.importLibrary("maps");

                    const content = document.createElement('div');
                    content.classList.add("property")
                    content.innerHTML = `
                        <style>
                            #theme-container {
                                height: 100%;
                            }

                            #container {
                                display: flex;
                                flex-direction: column;
                                height: 100%;
                                overflow-y: hidden;
                                width: 450px;
                            }

                            .grid-container {
                                flex: 1;
                                display: grid;
                                // grid-template-columns: repeat(2, minmax(0, 1fr));
                                gap: 12px;
                                padding: 12px;
                            }

                            .grid-item {
                                position: relative;
                                display: flex;
                                flex-direction: column;
                                background-color: transparent;
                                border: 1px solid rgb(238, 238, 238);
                                border-radius: 4px;
                                box-shadow: 0 4px 4px 0 rgba(0, 0, 0, 0.05);
                                overflow: clip;
                            }

                            img {
                                vertical-align: top;
                                height: 300px;
                                object-fit: cover;
                            }

                            .contents {
                                padding: 12px;
                            }

                            .title {
                                font-weight: bold;
                                font-size: 16px;
                                line-height: 24px;
                                font-family: "Inter", sans-serif;
                                line-clamp: 2;
                                margin-bottom: 8px;
                            }

                            .description {
                                flex: 1;
                                overflow: hidden;
                                text-overflow: ellipsis;
                                font-size: 14px;
                                line-height: 20px;
                                font-family: "Inter", sans-serif;

                                display: -webkit-box;
                                -webkit-line-clamp: 3;
                                -webkit-box-orient: vertical;  
                                overflow: hidden;
                            }

                            .subtitle {
                                font-size: 12px;
                                line-height: 16px;
                                font-family: "Inter", sans-serif;
                                color: gray;
                                font-weight: 300;
                                margin-top: 8px;
                            }

                            p {
                                margin: 0;
                            }

                            .dark {
                                #container {
                                    background-color: black;
                                    color: white;
                                }
                            }
                        </style>

                        <div id="theme-container">
                            <div id="container">
                                 <div class="grid-item">
                                    ${ advancedMarker.dataset.image ? `<img src="${advancedMarker.dataset.image}">` : `` }

                                    <div class="contents">
                                        ${ advancedMarker.dataset.title ? `<p class="title">${advancedMarker.dataset.title}</p>` : `` }
                                        ${ advancedMarker.dataset.subtitle ? `<p class="subtitle">${advancedMarker.dataset.subtitle}</p>` : `` }
                                        ${ advancedMarker.dataset.description ? `<p class="description">${advancedMarker.dataset.description}</p>` : `` }
                                    </div>
                                </div>
                            </div>
                        </div>

                    `

                    this.infoWindow = new InfoWindow({
                        content: content
                    });
                    this.infoWindow.open({
                        anchor: advancedMarker
                    });
                });
            });
        }

        var previousPageButton = this.shadow.getElementById("previousPageButton");
        previousPageButton.addEventListener("click", () => {
            triggerEvent(this, {
                action: OuterbaseTableEvent.getPreviousPage,
                value: {}
            })
        });

        var nextPageButton = this.shadow.getElementById("nextPageButton");
        nextPageButton.addEventListener("click", () => {
            triggerEvent(this, {
                action: OuterbaseTableEvent.getNextPage,
                value: {}
            })
        });
    }

Demo Time

You can find the code in my Github repository.

Final Thoughts

I have had lots of fun building this plugin for the Outerbase platform. Web component is a new thing for me and I have got lots of issues in the beginning. But I am happy that I can make it work in the end. Overall, I think the ability to create a plugin and visualize the database tables directly on the platform is a cool feature. As more and more plugins are available, it can be a powerful tool to easily interact with our databases.

I hope this article helps you create a new plugin for Outerbase. If you have any questions or comments, feel free to do so.