DEV Community: Ronan McQuillan

REST API Authentication | 4 Methods

Ronan McQuillan — Fri, 11 Aug 2023 10:04:00 +0000

Understanding the different methods of API authentication is a core competence for any would-be developer, data professional, or anyone else who wants to connect to external tools, data sources, or other resources.

Nowadays, APIs are fundamental to the way we build, integrate, and use all kinds of digital tools - whether this is building custom software from scratch - or simply connecting existing SaaS platforms to create streamlined workflows.

So, you need to know how to use the different authentication methods that vendors could implement for their APIs.

Today, we’re going to cover everything you need to know about working with the most common REST API authentication methods.

By the end of this guide, you’ll be fully familiar with how to access data and resources from just about any API. We’ll also wrap up with a few extra considerations for managing API authentication with extra levels of security.

Here goes.

What is REST API authentication?

API authentication is the process of verifying the identity of a user or other actor - in order to confirm that they have the necessary permissions for whatever they’re trying to do via an API.

Specifically, authentication allows API owners to do three things:

Verify the identity of a client or user.
Enable authorized clients and users can access the API.
Prevent unauthorized access.

This is very tightly linked to another idea - authorization. Following on from authentication, this is how vendors govern which elements of their API clients and users can access.

Authorization is used for:

Granting access and exposure to particular resources or data for different users.
Governing what actions different users and clients can take with our API.
Otherwise enforce defined access control policies.

From the perspective of someone accessing an API, we’re not too worried about how vendors implement authorization after authentication. But, it’s worth being aware that they might use our authentication data in order to determine what resources and actions we can access.

How do I know what authentication method to use?

We can actually attack this question from two slightly different angles. On the one hand, there’s how each particular API is built. On the other hand, there’s the kind of solution that we’re trying to build.

Let’s think about the first point first, because it’s a relatively straightforward one. Individual vendors are going to require you to use different authentication methods.

Some might give you the flexibility to choose from multiple methods, while others will be more opinionated, and insist on using a specific method. Ultimately, there’s not a lot we can do here.

If a vendor only offers access through a particular authentication method, then we’ll need to play ball if we want to use their API.

The other side of the equation requires us to think about the actual solution we’re trying to create with whatever API we’re accessing.

In the most generic sense, this comes down to two different decisions. The first is whether we want our API client to authenticate statically or dynamically.

The difference is as follows:

Static authentication means that the same authentication data is used, whoever is using our solution.
Dynamic authentication means that our solution will store and leverage different authentication information, depending on who the end user is - or even using some other contextual conditions.

The other thing we want to think about is whether we need to authenticate globally or on a per-request basis from our API client.

In other words, choosing between:

All requests from our client using the same authentication credentials.
Using different credentials for individual API requests.

There’s no better or worse here - you just need to be aware of these distinctions in order to make an informed choice about how you configure your API connections.

4 methods for REST API authentication

With that bit of theory out of the way, let’s dive into the four most common REST API authentication methods.

Just a quick note on our examples here. We’re sending a GET request that sends and instruction to an API to retrieve data bout our users. To do this, we’ve created a Budibase app, with REST API selected as its data source.

We’re going to look at what each authentication method looks like within Budibase, but the basic principles are the same whichever API client you’re using.

We’ll also check out what they would look like in a hard-coded solution for the sake of completion.

1. API keys as headers

First, we have API authentication via HTTP headers. The headers section of an API request is a series of key/value pairs that are used to provide extra information for the server about whatever we’re trying to do.

The key is the name of the attribute we’re providing and the value is the specific piece of data that corresponds to this.

Authentication is actually one of the most common use cases for headers. Most of the time, this means using a key called x-api-key and giving it a value of whatever our unique API key is.

Naming conventions can vary here, so make sure you’re using whatever attribute the vendor specifies in their documentation.

So, if our API key was super-secret-key, for example, our hard-coded headers might look like this:

{

“x-api-key”: “super-secret-key”

}

But, we aren’t necessarily going to want to hard code solutions, so let’s look at how we can use API headers for authentication in Budibase.

We’ve actually got two options for adding headers to queries. First, we can create a new query, and add key/value pairs under the headers tab, like so:

This allows us to stipulate authentication credentials on query-by-query basis.

Alternatively, we can also add global headers. To do this, head to the configuration options for your REST data source, and hit the headers tab again:

With both options, if we simply enter our API key in the text box provided, it will be applied statically. We can also use the lightening bolt icon to open Budibase’s binding menu to create dynamic values.

For example, if we wanted to use environment variables, user accounts, or even form inputs as our API headers. Here’s what this would look like with a secure environment variable:

Check out our in-depth guide to API headers for more information on what these can be used for.

2. API keys as query parameters

Alternatively, some vendors will ask us to provide authentication details as API parameters. Rather than within the headers sections, these are keys and values that are provided in our request URL or body.

If we were using a URL parameter, our GET Users request would look something like this:

{

https://example-api-url.com/v1/users**?apiKey=super-secret-key**

}

In other words, to add a URL parameter to a query we append the provided endpoint with:

{

?attributeName=value

}

Again, we’ve got a couple of different options for how we want to do this in Budibase.

We could add this manually to our request URL or under the parameters tab for any given query. When we populate a parameter in one location, it will appear in the other - since these are actually the same thing:

We also have the option of creating bindable values with a default value - and assigning these as parameters.

For example, we could create a binding called apiKey with a default value of super-secret-key, but use front-end logic to assign different contextual values to this - giving us more flexibility over our queries.

Here’s what this would look like in-situ:

You might also like our guide titled What are the Components of an API?

3. Basic auth

Some vendors might want us to use what’s known as basic authentication. This means that instead of having a unique API key, clients and users must provide their a username and password.

To configure this, first head to the Authentication tab under your REST data source’s global settings and hit add authentication:

Then, we’ll see a modal where we can select basic auth, and give our method a name. We can also enter our credentials here:

Once we’ve saved this, we can access it in individual queries using the Auth dropdown:

4. Bearer tokens

Under the global authentication tab, we can also provide a bearer token, if this is what the vendor requires - for instance, if they perform authentication via OAuth tokens.

We have the option of simply adding our access token in manually as a string. Or, if we’re using the same SSO system as the API we’re trying to access, we can use the following binding to provide this dynamically, based on the current users:

{{ Current User.OAuthToken }}

And we can use this in queries in exactly the same way as we did a second ago with basic auth.

That’s it! Those are the four most common REST API authentication methods, along with how you can leverage them to connect to data sources in Budibase.

Other security practices for API authentication

Authentication and authorization are crucial aspects of your security strategy. However, they’re only going to get you so far in the real world.

Say for example some nefarious actor gets a hold of your API authentication credentials.

You’d potentially be facing a very big problem, wouldn’t you?

Here are some of the other strategies we can use to safeguard our security when using APIs.

Access control

Access control is all about how we determine which users can do what with particular resources. It’s important to use an access control policy within our client applications that reflects and compliments the API resources we’re leveraging.

For example, granting permissions based on different data sources, queries, automation rules, or front-end elements.

Take a look at our guide to role-based access control to learn more.

Secure authentication protocols

Secure authentication protocols such as OpenID, OAuth, and other proprietary SSO tools are an effective way to authenticate different users, applications, and other API clients.

These help to provide an additional layer of security, while also giving IT administrators more control over who can access tools - and how.

Enforcing strong passwords

Where possible, it’s vital to enforce strong passwords when dealing with user-generated authentication credentials. Of course, we’re constrained here in terms of what is provided for by vendors.

Strong passwords are those that mix different character types, in long or even arbitrary strings.

The best passwords are typically those that have been automatically generated by dedicated tools, within API clients, or other similar platforms.

Leveraging encryption

Encryption is used to keep database credentials, REST API authentication details, or other sensitive data secure.

This helps us to eliminate several risk vectors, including hacks, data breaches, or malicious access to our business-critical data.

For example, Budibase offers configurable encryption for in-application environment variables.

Access monitoring

Finally, monitoring who is accessing our technical resources, alongside how and when, is critical for maintaining the security of all kinds of solutions - including those that rely on external API requests.

Regular auditing of our automation logs and user behavior ensures that we can identify new threats, unauthorized data access, or other critical security risks before they escalate and result in more serious outcomes.

For more information on how to put what you’ve learned today into practice, check out our tutorial on how to create a REST API GUI with Budibase.

What are the Components of a Web-Based REST API?

Ronan McQuillan — Mon, 17 Jul 2023 12:20:47 +0000

Knowing the components of an API is obviously important for setting them up to do what you want.

Otherwise, how can you ever expect to use them correctly?

Today, we’re covering everything you need to know. We’re going to take each of the constituent parts of a web-based REST APIs in turn and explain exactly what they are, what they do, and how you can use them.

Along the way, we’ll think about real-world examples of why each one is important, and the specific scenarios where they’re useful.

But, before we dive in, let’s take a zoomed-out view of what we’re dealing with.

So…

How do APIs work?

Let’s start with a high-level overview of APIs. API stands for application programming interface. Basically, this is a piece of code that allows two or more applications, data sets, or other nodes to communicate with each other.

To ensure interoperability, APIs have to adhere to prescribed standards. There are a couple of specific protocols out there, but the basic idea is that - whichever tools we’re using - setting up an API should work the same way.

But what do we mean by communicate? Obviously, software tools don’t chat about their feelings.

APIs allow clients to make requests to servers - which then send back responses. We’ll dive into each of these in a second.

For now, the important thing is understanding that APIs are a way to request, retrieve, and share data between applications, within a defined framework.

(NordisCapis)

To understand this in more depth, we need to circle back to our original question - what are the components of an API?

Here goes!

API clients

An API client is the application that makes the initial request for data. This could be a web browser, a SaaS platform, an API management tool, a mobile app, or any other piece of software.

The client is also responsible for bringing together all of the data that we need to make the request - which we’ll explore in a second.

When a request is made successfully, the response is sent back to the client.

We also need to consider the logic that determines when a client sends a request. This could be based on a whole range of different triggers, depending on the specific workflow we’re dealing with.

So, this could be user actions, like someone pressing a button, refreshing a screen, or filling in a form.

Or, it could be based on automated logic - like a database query, time-based trigger, or some other condition being met in the back end.

API requests

Next, let’s examine how requests are made up.

Remember, part of the idea behind APIs is that - whichever protocol you’re using - any platform that supports this should be able to interact with requests and responses in the same way.

This is the basis of interoperability.

Therefore, it follows logically that requests are going to need to follow a consistent format so that they can be handled the same way, regardless of the client and server involved.

API requests are made up of five basic components. These are:

Endpoints

The endpoint is the URL that an API request is directed at. This can be made up of a few distinct parts, depending on how our client and server are configured.

The first is the actual destination of the resource that we’re trying to access. This includes the domain of the server, along with any resources that the vendor has chosen to expose to us and the version of their API that we’re calling.

So, say we were sending a call to an API for our local zoo. It lives at api.the-zoo.com and we want to access data about lions. The vendor has created an API endpoint for us to request this called /lions.

We’re using the second version of their API.

Our endpoint might be:

https://api.example.com/v2/lions

You’ll need to check specific vendors’ documented API specifications to see what format you need to use and what endpoints are available.

The endpoint can also be used to provide extra information - called query parameters within our request. These need to be attributes that the server can accept within a particular endpoint so again, check the docs carefully.

Say we only wanted to pull data about a particular lion - whose name is Franky. The zoo’s API allows us to do this using the lionName parameter. We can add this to our endpoint like so:

https://api.example.com/v2/lions?lionName=Franky

But, this isn’t the only way to pass parameters in an API request. We’ll check this issue out more fully in a couple of minutes.

Methods

Before we get to that, we need to talk about methods. A method is an instruction that tells the server what we expect it to do with the information that we’ve sent in our request.

Specifically, what sort of operation we want it to perform on the resource we’re dealing with. API methods are essentially analogous to database operations.

REST, the most dominant API protocol, uses standard HTTP methods. The most common of these include:

GET - for retrieving a data object.
POST - for creating a new data object.
PUT - for creating a new data object or altering an existing one.
PATCH - for modifying an existing object.
DELETE - for deleting existing objects.

For example, we created an endpoint earlier that targets any lions with the name Franky. If we sent a GET request to this endpoint, we might get back a JSON formatted data object that looks like this:

{

“lions”:{

 “lionName”: “Franky”,

 “age”: 16,

 “adoptionDate”: 2016-02-02,

 “favoriteFood”: “Steak”

 }

}

If we didn’t have any kind of filtering, the API request would return the same information for all of the zoo’s lions.

Say we wanted to change Franky’s favorite food. We’d probably use a PUT request - although some APIs might be set up to prefer for you to use PATCH - always read the docs.

But, so far, we only have the components to select a row and say we want to update it. We don’t have anywhere to add new values just yet.

Parameters

That’s where parameters come in. These are arguments that we can pass to our API request. We’ve actually seen this already when we placed attributes in our URL in order to filter a GET request.

Besides filtering, parameters can also be used to specify which attributes we want to retrieve, for authentication and user management, to provide new data in our request, or for any other data transfer purposes.

There are a few different types of parameters that we need to be aware of. These include:

Path parameters - Extensions to the URL to select specific attributes that we want to retrieve.
Query parameters - For performing sort, filter, and pagination functions within our request URL.
Header parameters - Data that we store in our request headers. More on this in a second.
Request body parameters - Attributes that we place in our request body in order to add or modify data objects.

So, in order to successfully perform a PUT request on our lions data, we’ll need to provide the new data in our request body.

In our example, this would be Franky’s new favorite food.

Headers

Before we look at the request body though, we need to think about headers. This is a special part of our API request that allows us to provide additional context to the data we’re sending.

This is useful for a number of reasons, including:

Adding authentication details to prove that we are permitted to take whatever action we’re requesting.
Storing cookies.
Configuring whether and how we want our request and response to be cached.
Giving information about how our request is formatted and the type of response we expect.

(NordisCapis)

We’ve created a dedicated guide to API headers which explains this in more depth.

Request bodies

Finally, we have the body of our request. This is where we store any data objects and attributes that we want to send in our request - mostly within PUT and PATCH, and POST requests.

PUT requests are intended to replace the entire entity. So, we’d need to provide our new values, as well as all of the existing ones. In this case, the body for Franky’s new favorite meal might be:

{

“lions”:{

 “lionName”: “Franky”,

 “age”: 16,

 “adoptionDate”: 2016-02-02,

 “favoriteFood”: “Chicken”

 }

}

PATCH requests can be used to update only specified fields. Basically, that means that we don’t need to fill in all of the existing data. If we specify an existing attribute, its value is modified.

To update Franky’s favorite meal, we could simply use:

{

“lions”:{

 “favoriteFood”: “Chicken”

 }

}

If we specify an attribute that doesn’t exist, then this will be added to the current schema, along with whatever value we give it - assuming we have permissions to edit the schema.

Whether we’re using PUT or PATCH, we’ll still need the same query parameters to filter the data set and only update Franky’s row.

Note that different servers will have rules in the backend that constrain how we take different actions, so read the vendor’s documentation for guidance on how you’re supposed to modify values.

API server

Next, we can move on to the server. This is the platform, tool, data set, or other node that we send our request to.

The first role that this plays is providing an API in the first place.

So, the vendor specifies what the endpoint is, which objects are exposed to the API, what actions developers can take, how they can do this, and what the application will do in response.

They should also provide detailed documentation to outline all of this information for developers.

The server then determines how to handle requests. This includes authentication, data validation, retrieving data, creating responses, caching and cookies, and taking any other actions that are provided for.

To understand this, we need to examine two distinct layers of whichever applications we’re dealing with:

The data layer.
The business process layer.

(CloudZero)

Let’s take each in turn.

Data layer

As the name suggests, the data layer is where data is stored. At the most basic level, this is the data source that we’re querying using our API requests - whichever operation we’re using. So, in the simplest possible example, this would be the target application’s database.

Therefore, the data layer comprises not just the actual attributes and values stored, but also the underlying data structures and schema of this.

Check out our post - what is a database schema?

However, real-world data layers are often a bit more complex than this.

There are plenty of ways that the database can enforce rules on the data that we can use in API requests - including validation rules, access control policies, stored procedures, query permissions, and any constraints that are applied to individual attributes.

For example, some attributes might be read-only, while others might be updatable only by certain users. Or, they might have rules applied to only permit certain values - like if a certain attribute must be unique.

In the context of sending API requests, how these rules are implemented isn’t necessarily a big concern. We only really care about whether something is possible or not, which should be outlined in the vendor’s docs.

Process layer

The process layer is where the target application carries out its own actions on the data layer. We might be able to implement these processes without substantially altering the underlying data.

For example, if we had a database that stored all of our sales data - a basic process might be generating and sending an invoice.

This is important because many of these actions will be triggerable via API requests.

For instance, we might have a customer portal that allows users to generate invoices based on their previous orders.

Depending on how the vendor sets up this process, we might need to use a POST or PUT method in order to make this request successfully.

We’d also need to provide the right data in our client to select the appropriate data - that is, getting the right data for the current user and whichever time frame they need the invoice for.

It’s harder to generalize here than it is with basic data management actions. The important thing is to fully understand the vendor’s requirements around how to trigger different actions in the process layer.

API response

The next component of an API that we need to get our heads around is the response. This is what’s sent from the server back to the client after a request is made.

This provides three key roles:

Outlining whether or not the request action was successful - and if not, why.
Providing extra information around authentication, cookies, and caching.
Passing data back to the client.

Each of these has its own distinct section.

Let’s take a look.

Status code

First, we have the status code. This is a three-digit code that indicates what the server has done with our request. The first digit indicates the category of the status, and the second two indicate the specific status.

Codes in the 200s indicate that the request is successful. 300s relate to redirects. 400s indicate an issue with the request. 500s indicate an issue with the server which prevented the request from being actioned.

In theory, we could see the full suite of HTTP status codes in response to REST requests.

In practice though, there are a few key ones that we need to be aware of. The most common status codes include:

200 - The request was successful.
201 - The request succeeded and a new data object was created.
303 - The response can be found at a different location.
400 - The request was bad and the server couldn’t understand it - likely due to a syntax issue.
401 - The response was not authorized.
404 - The requested response wasn’t found.
408 - Request timeout.
500 - Internal server error.
501 - The server doesn’t support the requested method.
504 - The server is acting as a gateway and timed out trying to retrieve the requested resources.

Response headers

The response can also include header parameters. These work in exactly the same way as they do within an API request - to provide context to the information included in the response itself.

Most of the time, response headers are used to do some combination of three things:

Sending cookies to the client, to be stored for user management purposes.
Stipulating which additional authentication information is required.
Giving information about the format of the response body.

Body

The final component of an API we need to be aware of is the response body. Just like the request body, this is used to hold the data that we want to pass to the client. However, this is only required in responses to certain request methods.

So, if we’re sending a DELETE, PUT, PATCH, or POST method, we don’t necessarily need the server to send any data back. We’d simply get a 200 or 201 status code to indicate that the request was successfully actioned.

But, we always need a response body in a GET request, in order to pass the data that was retrieved back to the client.

Just like earlier, this comes in the form of a data object - a series of key/value pairs representing the information that was requested.

And that’s it - all of the components of an API that we need to be aware of.

4 types of APIs

For a bit of extra context, it’s important to know that APIs aren’t all created equal. In fact, there are a couple of different types of APIs that vendors might provide for different kinds of developers and projects.

These are:

Private APIs - Internal APIs that are created to be used by a company’s own developers.
Public APIs - APIs that are publicly available and usable by anyone - although these might require payment.
Partner APIs - APIs that are made to satisfy particular business relationships. For example native integrations with other platforms.
Composite APIs - A combination of multiple APIs that are used to action related or interdependent tasks. Usually, the idea is to improve performance compared to using multiple individual API requests.

Check out our ultimate guide to IT transformation.

Manage internal processes with Budibase

Budibsae is the ideal solution for building sleek, integrated applications that leverage all kinds of existing data assets. We’ve even got our own public API, alongside full support for REST data sources and automation actions.

Here’s what makes Budibase tick.

Our open-source, low-code platform

Budibase is the fast, cost-effective way to build professional applications. We offer extensive data support, market-leading extensibility, autogenerated screens, optional self-hosting, intuitive design tools, flexible automations, and much more.

Check out our features overview to learn more.

Connect your data

We’re proud to lead the pack for external data support. Budibase offers dedicated data connectors for MSSQL, MySQL, Postgres, Oracle, S3, Redis, Mongo, Couch, Arango, Google Sheets, REST API, and more.

We’ve also got our own built-in database, alongside full support for custom data sources.

Optional self-hosting

Security-first organizations love Budibase for the power to deploy custom solutions to their own infrastructure. We offer self-hosting through Kubernetes, Docker, Docker Compose, Digital Ocean, Linode, Portainer, and many more.

Or, choose Budibase Cloud and let us handle everything. Check out our pricing page to learn more about both options.

Public API

Budibase has its own public API for all kinds of application management, data manipulation, integration, and customization tasks.

Check out our API docs to learn more.

Automations and integrations

Build custom automation rules with minimal custom code. Combine, nest, and configure our built-in triggers and actions to build, test, and publish powerful flows in minutes.

We also offer extensive integration with third-party tools, using Zapier, REST API, and WebHooks.

Role-based access control

Budibase offers configurable role-based access control. Assign users to defined roles and grant permissions based on data sources, queries, automation rules, screen UIs, or individual components.

We also provide free SSO through OAuth, OpenID, Microsoft, and more - alongside secure environment variables for storing database credentials.

Custom plug-ins

Budibase won’t be beaten for extensibility. Use our dedicated CLI to build custom data sources, automation actions, and components. Or, import community contributions from GitHub, in just a couple of clicks.

Take a look at our plug-ins docs to learn more.

50+ free app templates

Companies around the world - in all kinds of industries - choose Budibase to build secure, performant apps in a fraction of the time. To show off what’s possible, we’ve built more than fifty free, totally customizable app templates.

Build an Inventory Management App for Free [VIDEO]

Ronan McQuillan — Wed, 28 Jun 2023 10:04:24 +0000

Keeping track of stock is one of the most fundamental processes in any customer-facing business. Today, we’re checking out how to build a custom inventory management app for free - in just 20 minutes.

Even better, since we’re going to use Budibase to build it, you’ll barely need any coding skills - and we’ll have a fully functional application before your coffee gets cold.

All we need to do is connect our data, add a couple of custom queries, and start using Budibase’s built-in components to create a professional UI for managing stock levels.

Let’s jump right in.

What are we building?

Our inventory management tool is going to be a one-screen app, but don’t let this fool you. It’s actually going to pack quite a punch.

In terms of requirements, we basically want to enable our end users to do five things:

Browse products based on their category.
Edit product information.
Search for items based on their name.
Check which items are low on stock.
Reorder stock where necessary.

Under the hood, we’re querying three tables from an existing MySQL database.

Along with Budibase’s native read functionality, we’ll also need three custom queries.

But don’t worry if you’re not a SQL whizz - we’ll provide all of the code you need below and show you exactly how to use it.

Without further ado…

How to build a free inventory management app in 7 steps

If you haven’t already, you’ll need to sign up for a Budibase account. Our pricing structure is the most generous in the low-code space, and you can do everything you need to build and deploy a fully-functioning inventory management tool with our free tier.

1. Create a new app

We’ll start by hitting create new app on the Budibase portal. Then press start from scratch. You’ll be prompted to choose a name and URL for your new app project:

Then, we’ll be asked to select our data source. Budibase offers a market-leading range of external data connectors, as well as our own built-in database.

We’re going to choose MySQL, but you can go with whatever option matches up with where your inventory data lives.

2. Connect your data

Now, we need to add our database credentials, including the host, port, database, and our login details.

Once we’ve done this, we can use Budibase’s fetch tables functionality to access our tables from the data tab of the builder.

Once we’ve done this, the tables will look like this in the back end:

Already, we can edit entries without writing queries, using our spreadsheet-like interface.

However, we’ll need some slightly more advanced logic in our data layer to achieve our desired functionality.

3. Add custom queries

This means adding some custom queries. If you’re not familiar with SQL, basically a query is an instruction to retrieve information from our database in a particular format - perhaps by applying some transformations or filters.

Remember, our three tables are products, inventories, and reorders.

We also need three custom queries to:

Return a list of our product categories, without any duplicates.
Return our product information from the products table, along with their respective stock levels from the inventories table.
Return the total number of items that have been reordered for each product entry.

From our MySQL configuration page, we’ll start by hitting add query:

This presents us with the following options:

Query name - what we’ll call our query within Budibase.
Function - the type of action we’re performing.
Access level - which user roles can use our query.
Fields - where we’ll input our SQL query itself.
Transformer - any JavaScript we want to apply to the data returned by our query before we pass it to Budibase’s front-end or automation builder.

All of our queries use the read function. Other than that, all we need to worry about is giving our query a name and pasting the SQL that we’ll provide in a second.

So first, we want to return a list of our product categories. We’ll use a SELECT statement, with a GROUP BY clause to achieve this. Specifically:

SELECT category FROM Products GROUP BY category;

This returns all of the category values from our Products table, but groups all the duplicates together, returning:

Next, we have a query that returns the details of each row from the products table, along with the stock level from their respective entries in the inventories table.

We’ll use the following query:

`SELECT SUM(Quantity) as TotalQuantity,

Inventory.ProductID as ProductID,

Products.ProductName as ProductName,

Products.Description as Description,

Products.Image as Image,

Products.Category as Category

FROM Inventory

JOIN Products ON Inventory.ProductID = Products.ProductID

GROUP BY Inventory.ProductID`

This looks a little bit more complex, but really our query does two things:

Tells SQL to retrieve the product details from our products table and the quantity from the inventories table, and gives the returned values slightly more concise names.
States that entries in the products and inventories tables with the same ProductID attribute are the same item.

Giving us:

Finally, we want to query our reorders table, aggregating all of the quantities for each product. So - if we two users had reordered 10 Welding Masks each, the total quantity returned would be 20.

We’ll use this query:

`SELECT ProductID, SUM(Quantity) as TotalQuantity

FROM Reorders

WHERE Status = 'Requested'

GROUP BY ProductID
`

This returns:

And that’s all of our custom queries in place. We’ll return to these later when it comes time to actually use them.

For now, we can start building our free inventory management app’s UI.

4. Build a category list

The bulk of our UI will be based around clickable cards for each individual product - sorted into different categories.

We’ll start by adding a repeater block which we’ll set to our categoryList custom query. Inside this, we’ll place a cards block named ProductBlock with the following settings:

Title - Category List.CategoryList.category
Data - Products
Cards Titles - ProductBlock.Products.ProductName
Subtitle - ProductBlock.Products.Category
Description - ProductBlock.Products.Description
Image - ProductBlock.Products.Image

We’ve also added a filter expression so that the Category attribute for each card needs to match the current category from our repeater block.

And the whole thing should look like this:

5. Create a side panel

Now, we want users to be able to interact with the data we’re displaying here. Specifically, they need to be able to update entries in our products database. We want to stick to a one-screen design though.

So, the best option is adding a side panel form that pops up when we click on one of our cards.

We’ll start by adding a side panel component - which we’ll name ProductDetails. This is basically a design container that only shows up when we tell it to.

So next, we need to set up a trigger for our side panel to appear. This is going to be when someone clicks on a card. So, we’ll return to our cards block and hit define actions.

And we can add the Open Side Panel action.

While we’re here, we need to add another action to set a state. Essentially, we’re going to place an update form in our side panel, so we need to tell Budibase to load the data for the row that we’ve clicked on.

To do this, we’ll first add another on-click action before our side panel opens. This time we’re choosing update state. This lets us save a variable in the background. We’ll call it current-product and set it equal to ProductBlock.Products.ProductID.

Now when we click on a card, Budibase saves the relevant ProductID and then opens our side panel.

We’ll start by adding a form block nested within our side panel component. We need to set the Type to Update and then set the Row ID to State.current-product. We’ll also give it a name so that it’s easy to identify in our component tree.

Already, we have a fully functional update form. But, it could look a little bit snazzier for sure.

Let’s start by adding a new data provider to pull the information for our product. Inside, we’ll add a headline component and an image. The headline has its Text field set to:

ProductDetailProvider.Rows.0.ProductName ( ProductDetailProvider.Rows.0.Category)

And the image has its URL as ProductDetailProvider.Rows.0.Image. The whole thing looks like this:

We’ll make one more small change to this later, but for now, that’s our side panel UI complete.

6. Add a search bar

The next thing we need to build is our product search functionality. The way this works is pretty simple. We’ll provide users with a text field where they can enter the name of the product they’re looking for.

If they do this, we’ll filter our cards block to show any products that match this.

So, we’ll start by adding the following new components:

A container - to hold everything in a neat fashion.
A headline - purely for UX.
A form - all form components need to be nested in a form.
A text field - where users can enter their desired product.

Altogether, this will look like this:

We’ll also set an action on our text field to save the value that users enter. So, we’ll use the Update State action to record whatever they type in with the key search-value:

But, nothing will happen when users type in a product name. For this, we’ll need to update our filtering expression on our cards block from earlier.

Under define filters we’ll add a second expression. This time, we want to display any products where the ProductName attribute is like our search-value state:

Now we have fully working search functionality.

7. Low-stock notifications

The last thing we want to build is our low-stock notifications. Remember, we wrote a custom query earlier that’s going to do most of the legwork for us here. We’ll add another container to keep things tidy.

Inside this, we’ll place:

A data provider set to our LowStockQuery.
A headline.
A repeater to access the values from our LowStockQuery provider.
A form, so that we can update our reorders data with user actions.
A second data provider, set to our custom Reorder query.
A cards block.

The finished product will look like this:

The card component also has a button, with two actions. First, it adds a new row to the reorders table, with the productID set to the relevant data from the card, and the quantity set to 10. We then set the button text to order 10 more.

Now, users can directly order new stock, but they can also see if someone else has already done so.

And that’s our free inventory management app completed. We could tweak the design a little bit more if we wanted to, but otherwise, we’re ready to publish and send our tool to users.

You can also check out our guide to building a SQL GUI.

Turn data into action with Budibase

At Budbase, we’re on a mission to help teams turn data into action. Businesses around the world choose our open-source, low-code platform to build all kinds of custom web apps, at pace.

Here’s what makes Budibase tick.

Our open-source, low-code platform

Our design ethos is simplicity by default; extensibility when you need it. Our platform offers intuitive design tools, extensive customization, market-leading data support, autogenerated UIs, and much more.

Check out our features overview to learn more.

External data support

Budibase leads the pack for external data support. We offer dedicated connectors for MySQL, SQL Server, Postgres, Airtable, S3, Mongo, Arango, Oracle, Couch, Redis, REST API, Google Sheets, and more.

We’ve even got our own built-in database to help get you up and running in seconds.

Optional self-hosting

Security-conscious organizations love Budibase for the power to deploy their tools however they choose. We offer optional self-hosting through Kubernetes, Docker, Digital Ocean, Portainer, and more.

Or, use Budibase Cloud and let us handle everything. Check out our pricing page to learn more.

Automations & integrations

Budibase makes it a breeze to automate all kinds of workflows and processes. Use our flow-based editor to combine built-in triggers and actions to create your perfect automations, with minimal coding skills.

We also offer extensive third-party app integrations, using webhooks, Zapier, REST API, and more.

Role-based access control

Use our native role-based access control to tailor users’ data exposure without compromising on experiences. Add users to defined roles and grant permissions based on data sources, queries, automations, app screens, or individual components.

We also offer free SSO through OpenID, OAuth, Microsoft, and more.

Custom plug-ins

Budibase is the clear leader for extensibility in the low-code space. Build your own components, data sources, and automation blocks with our dedicated CLI tools - or import community contributions from GitHub at the click of a button.

Check out our custom plug-ins documentation to learn more.

50+ free application templates

Our users choose Budibase to build all kinds of internal tools, web apps, and utilities, in minutes, not months. We’ve even built more than fifty free, customizable app templates to help get you started.

How to Build MySQL Admin Tools in 6 Steps [VIDEO]

Ronan McQuillan — Tue, 27 Jun 2023 12:41:07 +0000

MySQL admin tools are some of the simplest applications around - but they can play a vital role in all kinds of daily workflows.

The unfortunate reality is that the kinds of colleagues who are responsible for most admin tasks don’t have the technical expertise or time to sit around writing custom queries all day. Therefore, we often need custom solutions to bridge this gap.

IT teams are tasked with outputting huge numbers of internal tools for managing data sets. These aren’t the most exciting solutions in the world, but they do take up a massive amount of internal development resources.

Today, we’re going to check out how we can use Budibase to produce admin tools for MySQL in a matter of minutes - not days.

What are we building?

We want to build a tool that’s an accurate representation of the kinds of tasks your average admin colleague will need to perform on a daily basis. So, we’re going for the most basic solution of them all - a simple CRUD app.

We’re going to base this around some dummy data for inventory management - just so we’re dealing with a use case that most of you will be familiar with.

Essentially, we have a MySQL database with five tables - and we’re going to create a simple tool for users to create, read, update, and delete entries - including managing relationships between different entities.

You might also like our tutorial on how to build a MySQL GUI.

Let’s jump right in.

How to build MySQL admin tools

We’re going to use Budibase to build our MySQL admin tools without writing a single line of code.

The whole thing should take us about fifteen to twenty minutes - but there’s also plenty of scope for you to put your own spin on the build.

Here’s how.

1. Create a new application

First, we want to create a new application project. From your Budibase portal, hit create new app and choose start from scratch.

We’re then prompted to give our app a name. Budibase uses this to generate a URL extension based on your tenant address - but you can override this and specify whatever slug you like.

Then, we’re asked to choose a database. As you might guess, we’re picking MySQL:

2. Connecting our data

On the next screen, we’re prompted to enter our database credentials. Once we’ve done this, we can hit connect.

Once we fetch our tables, we can view and manipulate our MySQL database tables within Budibase’s Data section:

We’ll make a couple of changes here a little bit later, but for now, that’s our data layer pretty much completed.

3. Autogenerate CRUD screens

Next, we can start building our MySQL admin tool’s interface. Remember, the requirements here are that we want to give non-technical users an easy way to carry out CRUD actions on our five MySQL tables.

Luckily, in Budibase, we can basically create these with a couple of clicks.

From the design tab, we’re going to hit add screen and then select list view on the modal that pops up.

Then we’re prompted to choose which tables we want to add CRUD screens for. We’re going to select all five of the tables in our MySQL database, but we could opt for any combination of these if we wanted to.

Now we’ve got five admin screens where users can read, update, or create entries on each of our tables.

To finish these off, we’ll open the component tree on each page, select the relevant table block, and scroll down to check show delete - giving users the option to delete entries when they click through to edit a row.

And that’s our basic CRUD screens completed. We’ll return to these in a bit to tidy up the UI slightly, but already, we have fully functioning MySQL admin tools.

4. Define relationships

Now, the problem with our current app is that we haven’t accounted for how our five tables relate to each other. That’s a pretty big oversight - since manipulating relational data is basically the main selling point of SQL.

Let’s head back to the data section of the Budibase builder.

On each table, we have the option to define relationships - effectively telling Budibase how each of our tables links up with the others.

For example, each row in our products table might relate to many rows in our inventories table. We can hit define relationships and configure this information, including which variables to use as our primary and foreign keys.

So, for the relationship between products and inventories, the configuration would look like this:

Budibase creates a new relationship column on each table to reflect this.

And we can repeat this process for all of the other relationships that we need to create between our tables.

5. Customize our CRUD screens

Now, if we return to our CRUD screens, they’re gotten a bit messier - since we’ve added extra columns to our tables.

Even worse, from an end-user perspective, some of these even appear to be effectively duplicated:

We can go back to our table block component and configure which columns we actually want to display to users. Since we have our new relationship variables, we no longer strictly need ProductID or WarehouseID, so let’s remove them.

First, we’ll hit configure columns and then choose add all columns in the drawer that opens - then we can use the delete icon to remove any columns we don’t want to display.

This leaves us with a much neater interface, free from any clutter:

Again, we can repeat this step for each of the tables we want to manage with our MySQL admin tools.

6. Navigation and design

Finally, we can play around with our app’s overall design to make it a little bit more appealing. By default, every Budibase app has a blank home screen. But we’re not using this, so we can simply remove it.

Now we need to assign one of our remaining screens as our MySQL admin tool’s homepage. We’ll go with inventory, so select that page and check the set as home screen box in the top right.

We can also head over to the theme panel and select one of Budibase’s built-in color palettes, and alter the design of interactive elements.

The last thing we want to do is remove Home from our navigation menu - since we don’t have a dedicated home screen anymore. Under navigation hit configure links and the drawer will allow you to edit your menu.

Once again, we’ll use the delete icon to remove the Home option. We can also change the copy our the order of our remaining menu items.

And that’s our MySQL admin tools totally finished.

All that’s left is to hit Publish and start sending our app to users.

If you liked this tutorial, check out our guide on how to build a SQL GUI.

Turn data into action with Budibase

Of course, this is just a basic example of what you could build with Budibase. To get a flavor of what else is possible, let’s take a look at what Budibase brings to the table for busy IT teams.

Here’s why Budibase is the ideal solution for all sorts of apps - not just MySQL admin tools.

Our open-source, low-code platform

Our design philosophy is simplicity by default; extensibility when you need it. Budibase offers a generous free tier, market-leading data support, intuitive design tools, autogenerated UIs, custom automations, and much more.

Check out our features overview to learn more.

External data support

No other low-code tool comes close for data support. We offer dedicated connectors for MySQL, MSSQL, Postgres, Oracle, S3, Airtable, Mongo, Couch, Arango, Google Sheets, REST API, and more.

We’ve also got our own built-in database, with full support for CSV uploads.

Optional self-hosting

At Budibase, we empower our users to deploy their solutions, where they want, how they want. We offer self-hosting with Docker, Digital Ocean, Kubernetes, Portainer, Linode, and more.

We’ve also got our own proprietary cloud hosting option. Check out our pricing page to learn more about both.

Automations and integrations

Budibase makes building custom automations a breeze. Define triggers and combine, nest, and configure pre-built automation blocks, with minimal custom code.

We also offer a huge array of integrations with Zapier, Webhooks, REST, and more.

Role-based access control

Use Budibase’s built-in RBAC to safeguard security without compromising on UX. Add users to defined roles and grant permissions based on data sources, queries, automation rules, screens, or individual components.

We even offer free SSO using OpenID, OAuth, Microsoft, and more.

Custom plug-ins

Budibase leads the pack for extensibility. Build your own data sources, automation actions, or components, and leverage them across your app projects with our dedicated CLI tools. Or, import community contributions from GitHub at the click of a button.

Check out our custom plug-ins documentation to learn more.

50+ free application templates

Budibase is the ideal solution for building all kinds of web apps, utilities, internal tools, and more. To prove it, we’ve created over fifty free, fully-customizable app templates to help get you started.

Whether you need MySQL admin tools or a totally bespoke solution, sign up to Budibase today to start building professional apps the fast, easy way.

How to Build a Google Sheets GUI in 5 Steps

Ronan McQuillan — Fri, 28 Apr 2023 14:22:17 +0000

A Google Sheets GUI can help you turn your spreadsheets into real apps.

There are many useful free online tools for businesses. And Google Sheets is certainly one of the best of them. It allows you to get all the power of spreadsheets, databases, and scripting all at once.

But at the end of the day, it’s still a spreadsheet tool.

So you are limited to the grid design and unfiltered data entry. Users can add data in the wrong format. If you have multiple users, editing files can get confusing. There’s no user access control, advanced filtering, or search options.

In general, it feels like you need to adjust all your workflows to work around the limitations, instead of having an app that adjusts to your needs.

That’s where creating a Google Sheets GUI can come in handy.

With a Graphical User Interface (GUI) you can use Google Sheets as a flexible data source. It includes options to manipulate data, collect data from other sites, or even run custom functions with Apps Script.

At the same time, end users interact with your app using your Google Sheets GUI. Thus, you can control data entry, create custom views, add charts and metrics, and create different user levels.

This allows you to get the best of both worlds. A flexible data source, along with a powerful user-facing app.

Today, we’ll show how you can use a free open-source no-code builder to create your Google Sheets GUI in just 5 steps. By the end of the day, you’ll know how you can create beautiful Google Sheets apps, get data, edit it, search and much more.

Let’s get started!

How do I create a dashboard in Google Sheets?

You can use Google Sheets as a dashboard, by using custom styles, charts, and App Script to run functions. But it’s probably better and easier to use a free no-code builder such as Budibase to create a custom Dashboard for Google sheets.

You can follow the tips from this tutorial to build a dashboard in addition to a Google Sheets GUI.

Can Google Sheets be used as a database?

Google Sheets is a great option for a free and simple database. With it, you can add data to your tables and use other apps to access it using the API.

How do I make Google Sheets more attractive?

You can use the Google Sheets tools and create better designs. You can use options such as:

Better font choices.
A nice color palette.
A modern design style.
Custom icons.
Fixed elements for improved readability.

But at the end of the day, the best way to make Google Sheets look nice is not to use it for your design.

At the end of the day, it’s your data source, and its design options are quite limited. Today, we are going to explore how you can make your custom design with a Google Sheets GUI.

Our demo app is a simple CRM. It has two main menu items, and each of them has a few options:

Contacts
- Add / edit
Messages
- Reply

The contacts screen looks like this:

In it, you can quickly see some KPIs at the top of your Google Sheets GUI. Then you have filtering options and a table with your clients list.

These are the possible statuses for your clients:

Lead - You have their contact (maybe via a lead magnet) but they aren’t in your sales funnel.
Funnel - They are in your sales funnel, so you are getting to know them, checking their problems, analyzing options, and negotiating.
Active - These customers have bought already and you need to deliver or nurture them with pos-sales actions.
Lost - These are customers who have received a proposal and rejected it, or who have ignored too many messages.

Then if you click on “view” or “create new”, you’ll see a screen like this one:

This page is a form to add new customers. You can add form validation rules to make sure that all data points are correct, such as the email or last contact date.

Then, there’s the messages screen:

The messages screen includes a list of all messages, along with a filter. Notice how some messages have a “reply” button and some don’t.

That’s because of the message status. The messages status in your Google Sheets GUI follows this pattern:

Waiting - You have sent a message but the customer hasn’t replied.
Replied - The customer replied, and you need to reply back.
Closed - You have replied to the message and there are no further replies needed or you can send a new message and it has its own ID and status (starting with waiting again).

Then if you click “reply” you’ll see this screen:

This seems like a simple form. But once you hit reply a lot of things will happen:

Budibase sends an email to your customer, using the title and contents from your form
The current message is updated to closed.
A new message is created with the “waiting” status.
Your customer profile is updated with the last message date.

Now let’s implement these screens

Step 1 - Create a Google Sheets GUI app

If you haven’t already, sign up for Budibase. Then create a new app and select Google Sheets as your data source.

Next, authorize the app to access your Google Sheets data, add the sheet link and fetch the tables.

Our demo app has the contacts sheet:

The messages sheet:

And the KPIs sheet:

Keep in mind that you can use the Google Sheets functions to make your life much easier.

Here are some examples.

Instead of using JS code to create our KPIs, we just use the Google Sheets formulas to get them.

Then, we include the customer email on the messages sheet. This saves you a lot of data processing to get the email based on the client ID. We are doing it with this function:

=VLOOKUP(B2,contacts!$A$2:$D$5,4,1)

In addition, since the messages and client IDs are just a list, you can use a formula to generate them (=ROW() ). This saves you a lot of trouble in getting the size of the current sheet, then adding a new row with that number + 1.

Furthermore, sometimes you can use APIs or Apps Script to auto-update your Google Sheets. The sky's the limit, from something simple such as getting the current date, to complex operations like reading incoming emails from your inbox.

In particular, if you can’t code, using the Google Sheets formulas is probably an easier route.

Now let’s create the queries to load and update data to and from your Google Sheets GUI.

You can create these queries:

close_message

Function: Update
Bindings:
- id
Sheet: messages
RowIndex: id
Row:

“status”: “closed”

add_message

Function: Create
Bindings:
- Contact_id
- Title
- Message
- Contact_email
- Date
Sheet: messages
Row:

“id”: “=ROW()”,
“contact_id”: “ contact_id”,
“method”: “email”,
“date”: “date”,
“title”: “ title ”,
“message”: "message”,
“status”: “waiting”,
“contact_email”: “ contact_email”

Update_customer

Function: Update
Bindings:
- id
- Date
Sheet: contacts
Row Index: id
Row:

“last contact”: "date”

That’s all for the “Data” tab. Now head over to the “Design” section and let’s create some screens.

Step 2 - Clients list screen

You can create this page with stat cards and a table. In general, each data point of your page requires a data provider component.

The data providers return your queries as an array, even if it’s just one item. So, to access the individual items you can use repeaters, tables, cards, or access the data provider data using bindings.

Here is the elements tree for that page:

First, use the “add component” button to add new components to your page. Then add the title container. In it, you can add your page’s title and then a button.

Buttons don’t do anything by themselves, but you can assign actions to them. So, use this option for the button actions:

Next, add the KPIs container, the data provider loading the KPIs sheet, and the repeater. Don’t forget to use the “direction” option to make all the stats cards appear on the same line:

Then, each of the cards is showing one of your KPIs. You just need to use the name/value pairs, so the Google Sheets data is loaded:

Title: New Repeater.kpis.name

Value: New Repeater.kpis.value

Next, there’s the contacts table. You can use the data provider with the Google Sheets table “contacts”. Then add a dynamic filter and a table component to load and filter your data.

You can add a “view” link by clicking on the contacts table, then add a component. This allows you to add a new column with that component (a link, a button, or anything else you want).

Then use this URL:

/contacts/contacts Table.contacts._id

This URL makes it so that Budibase loads a different link for each row. They all follow the same pattern: /contacts/:ID.

Thus, you can use this pattern on your screen. You can extract the ID from the URL and pass it to your data providers. Let’s see how you can do it on the next screen.

Step 3 - Add new and edit clients screens

Both forms are very similar in their structures. Here is the components tree for the add new screen:

And this is the components tree for the edit screen:

The main difference between them is that the edit form uses a data provider to load the client information to pre-populate the form fields.

Let’s build the edit form and you can use the same logic for the add new screen.

Create a new screen and use this route:

/contacts/:id

This is what allows Budibase to understand that whatever is after contacts/ should be stored in a variable called “ID”. You can access it using URL.id.

This screen starts with a data provider. The trick here is to load the contacts table just like you did for the previous screen, but use this filter:

This allows you to load only the current contact. And then you can use a repeater to expose the contact’s fields.

You can create a form component, and use an update method with the “contacts” schema. This makes your life easier when you are creating the fields.

The title container loads a title component loading the contact’s name, like this:

Repeater.contacts.Name

Then, create a save button, with these actions:

Validate form.
Save row - to the contacts table.
Navigate to the contacts page.

For the fields group, you can use the “update form fields” button:

This creates all the form fields automatically in your form.

That’s all you need for the edit form. The add new form is quite similar, just replace the save row action with the add new.

Step 4 - List messages

The messages list screen is quite similar to the contacts list page. You can follow the same logic, just use this components tree:

You can even use the option to copy the entire contacts page instead of creating a new one. Or maybe copy the components over. Click on the 3 dots next to the main container to access this.

The only difference is that this page is going to load the reply link only for messages that aren’t closed.

You can do it using conditionality, like this:

So you hide the link if messages Table.messages.status is closed.

Also, don’t forget to update the messages link to be:

/messages/messages Table.messages._id

And update the add new button as well.

Let’s build the message reply form.

Step 5 - Automatically email replies from your Google Sheets GUI

On the surface, the reply form is quite similar to the contacts edit page. You can create a new page and use the /messages/:id route.

Then use this components tree:

The overall page setup is quite similar, just make sure you use a long text form instead of a simple text field.

Also, in this case, you can use a custom schema for your form, like this:

But the general setup is the same. Use a data provider, use the filtering options matching the URL, and add the components.

The real magic is in the “send reply” button.

But before we do this, you need to do 2 things.

SMTP setup

Go to your main Budibase screen - before the app selection - and click on “Email”.

Add your email settings there and save. If everything is ok you’ll see a green confirmation message.

Automation setup

Now, go back to the app edit screen. At the top there are three tabs, Data, Design, and Automate. Click on Automate.

You can use this section to run automated actions in your app. But you can use it to create actions that are triggered by your app as well.

Create a new automation called “send email” that starts using an App Action:

Add three fields to it, email, subject, and text. Then on the “do this” options, select send email, use the “send to” field as the customer email, add the subject, and add the HTML contents as the text using their bindings:

trigger.fields.email

trigger.fields.subject

trigger.fields.text

Save it and test it. You can add a backup email as BCC, which is hidden from customers. This allows you to make sure that all emails are being sent correctly as you get a copy in your inbox.

The Send Reply action

Now that the email setup is ready, let’s configure your “send reply” actions.

You can use these actions:

Trigger Automation:

Use an existing automation
Automation: Send email
Fields
- Email: Repeater.messages.contact_email
- Subject: Form.Fields.title
- Text: Form.Fields.message

Execute Query:

Datasource: Google Sheets
Query: close_message
- Bindings:
- Id: URL.id

Execute Query:

Datasource: Google Sheets
Query: add_message
- Contact_id: Repeater.messages.contact_id
- Title: Form.Fields.title
- Message: Form.Fields.message
- Contact_email: Repeater.messages.contact_email
- Date (JS code): return new Date().toLocaleDateString()

Execute Query:

Datasource: Google Sheets
Query: update_customer
- Id: Repeater.messages.contact_id
- Date (JS code): return new Date().toLocaleDateString()

These queries perform the actions that we mentioned before. You send the email. Then you update the current message to closed. Next, you add a new message for the newly created email. And finally, you update your customer’s last contact.

That’s it. Your Google Sheets GUI is ready.

You might also like our guides to building a REST API GUI or a Google Sheets Dashboard.

Building a Google Sheets GUI with Budibase

Today we looked into how you can use Google Sheets as a database, and create a custom app to manage your data without coding. With your Google Sheets GUI, you can read Sheets data, manipulate it, and perform other actions such as sending emails or connecting to APIs.

Take a look at our ultimate guide to database GUIs for more inspiration.

We hope you enjoyed it, and see you again next time!

How to Build a Dashboard in 5 Steps with Budibase

Ronan McQuillan — Fri, 28 Apr 2023 10:44:35 +0000

Knowing how to build a dashboard is one of the core competencies for most knowledge-based jobs. Dashboards are the difference between raw data and clear, actionable insights.

Most of us can pull up a couple of charts based on a spreadsheet - or even use a visualization platform like Looker to create basic reports. But what happens when your business data doesn’t fit neatly into one of these tools?

See, businesses hold more data than ever before, but it’s rarely in just one place, in just one format. Instead, we often need to cut through a mess of databases, spreadsheets, SaaS platforms, and APIs to generate the insights we need.

This is where the reporting tools you’re used to can fall down - and, until recently - you might have found yourself running to internal developers to whip up a custom solution.

Today, we’ll see how Budibase empowers professionals to build advanced custom dashboards - with multiple data sources - for themselves. Even better - to do it in a fraction of the time you’d expect.

First, though, let’s think about a little bit of context.

What is a dashboard?

A dashboard is a very simple application that reports on key business data in real time. At least, they’re simple in theory, but in the real world, things can easily get a bit more complex.

The idea is that decision-makers have a UI that they can quickly access to check out a fixed report, that updates automatically - usually with a relatively small number of actual data points.

This means they don’t need to go searching through the data themselves every time they need a summary of whatever we’re reporting on - say, online sales figures for the month.

At a technical level, dashboards are made up of three layers:

The data layer - Where our data is stored and queried.
The presentation layer - The interface that users access to check out our report.
The process layer - Where we carry out any transformations or processing to get our data from the data layer to the presentation layer, in the format we need.

The process layer becomes more important the more complex our data layer is. For example, if we’re querying one database to create our report, we might not need much processing.

If we query two separate datasets, we’ll need to work a little bit of magic behind the scenes to unify these into a comprehensible report - and so on.

This is where traditional reporting tools, especially spreadsheets, fall down - they just don’t offer us enough capability to process and collate external data.

Therefore, anything but the most basic dashboards often need to be custom-built - typically as web apps or within more complex data visualization tools.

As we’ll see, Budibase offers a powerful alternative. You might also like our guide to how to build a crud app.

When are dashboards used?

Dashboards have countless use cases. These comprise any situation where colleagues need fast, easy access to a real-time, fixed report. Often, this relates to internal goals and KPIs, or other progress updates.

So, for example, departmental leaders will often use dashboards to understand how their team is progressing toward top-level strategic goals. For example, monthly revenue figures within sales or marketing departments.

In a product team, this could be used to monitor GitHub metrics in a faster, more intuitive way than we would get from GitHub itself.

Alternatively, dashboards can also be used to report on status updates - either internally or in a customer-facing context.

For instance, your IT team needs to make sure that all of your network infrastructure is running correctly. Uptime dashboards provide a fast, easy way to do this, as well as expediting the process of diagnosing problems if they do occur.

Or, in a customer-facing context, we might use a simple web-based dashboard to let users check out the status of your new feature releases, or even check out important data relating to their own accounts.

The possibilities are basically endless.

How to build a dashboard with multiple data sources

As we said earlier, things begin to get a bit trickier when it comes to how to build a dashboard that requires a more complex data model.

This is because the more data sources we draw on, the more collation, aggregation, and other transformation functions we’ll need to get our data into a presentable state.

There are a couple of key things that can cause us problems here.

One is if we store data on the same entities in separate, unrelated data sources. For example, if we have a database with our customer and order information, but we also need to draw on our fulfillment platform for data relating to shipping times - with no formal link between the two.

In an ideal world, we’d have a preconfigured single source of truth to help us here, but the reality for most businesses is that we won’t. So, we’ll need to query both and then use our process layer to collate data from each about individual customers and orders.

The other scenario is if the required data sets differ in terms of their format.

Say we were building a sales dashboard that comprised data from our ecommerce site for online orders and our invoicing platform for enterprise sales. Even minor variations in how dates are formatted between these tools could skew our figures.

So, we need transformations to achieve consistency before we can present our data.

In terms of implementation, one nice thing about dashboards is that we’ve got a few different options for performing transformations - depending on how we build our dashboards, that is.

Broadly speaking, these are:

Performing transformations within our data layer - for example, creating aggregated data sets that can be queried.
Performing transformations with a separate process layer - for example, building them into our queries, or applying them to the output data before it’s called by UI elements.
Performing transformations within our UI - for example, applying simple manipulations to individual values that are displayed by particular UI elements.

Each of these options offers distinct pros and cons, so they’re best leveraged in slightly different contexts. For instance, if we’re going to regularly apply the same transformation in different dashboards, it makes sense to create an aggregate data source.

If we’re going to utilize certain transformations throughout a single dashboard, performing these in a dedicated process layer is likely the best option.

For simple transformations that we only need in specific places, like rounding some values or basic mathematical functions, implementation at the UI level is often our best option.

Therefore, to build dashboards effectively, we need to be able to take advantage of a combination of these techniques.

We’ll see how Budibase handles each in the next section.

How to build a dashboard in 5 steps

Enough of the theory. You came here to learn how to build a dashboard - and that’s what we want to focus on.

To show off just what Budibase is capable of, we’ve opted for a relatively tricky use case. We’re going to build an online orders dashboard that draws on three different data sets.

These are:

A Postgres database that stores our online order data.
A Google Sheets table that stores our display advertising spend.
A REST API that retrieves customer complaints data from an external tool - in our case, a separate Budibase app.

Here’s what the finished product will look like:

We have two tabs. The first, displayed above displays daily and total order volumes within a user-specified date range. Users can also filter by specific items.

The second also leverages a user-specified date range, this time showing our daily advertising spend and the return on this over the period selected:

There’s actually quite a bit going on behind the scenes here, so let’s take things one step at a time.

Here’s how to build a dashboard in Budibase in 5 steps.

1. Define your goals

The first step when it comes to building a dashboard is figuring out what we want to achieve. As far as requirements-gathering exercises go, this is relatively straightforward. The big decision is simply what data we need to communicate.

In our case, we want to display the following for specified date ranges:

Daily online sales figures.
Sales volumes and total values.
Average daily order values.
Total customer complaints.
Daily advertising spend.
Total advertising spend.
Total return on investment.
Daily return on investment.

Once we know this information, our next step is to figure out where we can get the data that we need - taking into account any additional functionality we need for user interactions. For example, we want users to be able to filter our dashboard by individual items.

Therefore, we’ll need to account for this when choosing data sources.

We’ve already outlined where our data is going to come from, but in the real world, you’d need to do a bit of research on your business’s own data assets to figure this out.

Once we’ve determined exactly what we want to build, it’s time to create a new Budibase application.

2. Create your data layer

When we create a Budibase app, we’ll be prompted to choose a data source:

Recall, we’re going to use a Postgres database, a REST API request, and a Google Sheets table - so we’ll actually need to create three separate data sources.

We’ll start with our Postgres table. When we click on Postgres, we’ll see the following form where we can enter our credentials and fetch our tables:

Our demo database only has one table, but if we had multiple, we could select the specific ones we need to access.

Fetching tables imports everything we need into the Budibase builder UI:

Next, our Google Sheets table. This time, we only need to sign in to Google and paste in the URL of the spreadsheet we want, then we can fetch our tables just like before:

Et voila.

Finally, our REST data source. We’re using this to connect to an external Budibase app, but you could do something similar with any other platform that offers a public API.

We’re using a POST request targeting a URL with the format:

baseUrl/tables/:tableId/rows/search

Here, baseURL is the location of our Budibase tenant - so something like your-tenant.budibase.app for a cloud-based account. :tableId is the ID of the table we want to target.

We’ll pop this into the URL box and then add two headers:

x-budibase-api-key - for our unique API key.
x-budibase-app-id - for the ID of the specific Budibase app we want to target.

We can get our API key from the menu in the top right corner of the screen and the app ID from the manage section of any published app.

Once we’ve filled in those details, we can send the request to make sure it works. This is the JSON object that our’s returns:

For context, here’s what the table looks like in the app we’re requesting the data from:

So, now all of our data sources are connected, we can start performing any transformations we need to get the information into a usable format.

3. Perform transformations

We’re going to apply the bulk of our transformations within the Data section of Budibase. In terms of traditional app architecture, this will form the equivalent of our process layer. We could also supplement this with our built-in Automation editor.

But, for our purposes today, we can do everything we need without going down that road, so there’s no sense in over-complicating our app.

Here are the transformations that we applied to each data source.

Postgres

The daily sales figures for our dashboard can be accessed from our Postgres table as is. However, we need a couple of custom queries to get the summary figures for our order count, total value, and average value.

We’ll use one query to do this, filtered by date, and a second to do the same, filtered by date and item name.

So, we start with a SELECT statement for:

SUM(“value”) - the total of all of the values.
COUNT(“id”) - the number of unique IDs.
AVG(“value”) - the average of all of the IDs.
COUNT(“date”) AS “days” - the total number of unique dates, presented as a variable called “days”.

To filter this by date, we need to create two bindable values that we can include in a WHERE clause - for user-defined start and end dates:

We’ve called these startDate and endDate, and given them default values that we know match some of our rows. We can call these in our customer query as startDate and endDate respectively.

Our completed query looks like this:

The transformer box is set to return data by default, which we’ll keep. So, when we run the query, we get the following object:

This looks good.

We can then duplicate this query, and add a third binding called itemName to the new version - and add this to our WHERE clause, so our query this time is:

And that’s everything we need for our Postgres table.

Google Sheets

Next, we’ll check out Google Sheets. This time, we just need one custom query - again, to get the same statistics as before for our specific date range. We’ll start by creating bindings again for startDate and endDate - this time with arbitrary default values:

We can’t query Google Sheets with SQL, so we need to use JavaScript to perform our desired transformations on the data that’s returned when we read the table.

We need to do the following:

Create a new variable called responseData and set this to the data that Google Sheets provides.
Create an empty array called outputData.
Create three variables called totalValue, count, and avgValue - and set each to 0.
Use a for loop to iterate over responseData.
Use an if statement to assess if each row’s date falls between our specific range. If so:
- Add each of these rows’ Spend attribute to our totalValue.
- Increment our count variable.
- Set avgValue to (totalValue / count)
- Wrap these values in an object called rowData, with the keys “sum”, “count”, and “avg”.
After our for loop the final value for rowData is pushed to the outputData array.
Return outputData.

The Budibase JS editor is a little small for pulling clear screengrabs, so here’s what this would look like in VS code:

And here’s the JSON response when we run our query:

Note that in JavaScript, we access our bindings with the expression params.bindingName.

Hit Save Query and we’re done.

REST API

Lastly, we need to apply some similar transformations to our REST response.

First, if you were eagle-eyed, you’ll have noticed that the response earlier was wrapped in an object called “data”:

Each entity also includes some attributes we don’t strictly need, like the table’s autogenerated row _id and even the tableID.

So, we’ll do a little bit of cleaning up before we start using this in our UI.

We’ll start by creating the exact same startDate and endDate bindings as before. We’ll then use basically the same script as before, but with a couple of key changes:

We set responseData to data[“data”] - to access the contents of the “data” object - and then create an empty array called outputData
We iterate over responseData and add the same if statement as before.
Inside the if statement we create a rowData object with the keys “date”, customerID”, “complaintText”, and “orderID”, with values for the respective attributes from the current row in the iteration.
We then push rowData to outputData inside the for loop, adding each row to our array.
We return rowData.

Again, we’re how this would look in VS Code:

And here’s the output:

Then, we’ll duplicate our request, and write a new transformer to grab count of our complaints for a given date range.

This works just like the stats in our Google Sheets query, so we won’t go through it all again. Here’s the code:

And the response:

We’ll do a little bit more aggregation and reformatting as we build out our UI, but that’s the bulk of our transformation complete.

4. Build your UI

Next, we can start building our UI. Our dashboard is actually just one screen, but we’ve created tabs to flick between two slightly different reports.

Here’s the component tree for the overall layout and the Online Orders tab:

As you can see, we’ve just got one bar chart, a few stat cards, and three data providers with repeaters.

Here’s what that looks like in situ:

Let’s see how we’d build this, one step at a time.

Tabs and layout

We’ll start by creating a form to wrap everything, as this will allow us to filter data a little bit later using form field components.

We’ve called this Master Form.

Inside, we’ve added a Super Tabs component - which is actually a custom plug-in. It’s publicly available though, so you can grab the URL from our plug-ins repo and paste it into your accounts plug-ins section.

Basically, this creates tabs based on any components nested directly inside. It’s pretty nifty.

We’ve put two containers inside our Super Tabs, called Online Orders and Ad Spend. Here’s how this looks so far:

We’ll build our two reports inside these tabs.

Cards

Next we’ll add our stat cards. First, we add a Data Provider set to our SUM, COUNT, AVG by Date query to our orders table. Inside this, we’ll add a horizontal repeater and three stat cards with titles Total Orders, Total Value, and Average Value.

We’ll use handlebars expressions to bind the values for these to the sum, count, and avg attributes in the query response respectively, and add in dollar signs where we need to.

For example: stats Repeater.Sum, Count, AVG Orders By Date.count

Our fourth card will be the count of customer complaints, so we need an extra data provider and repeater pair inside our existing repeater to access this. We’ll set our new provider to the Complaints Data Stats query, and add a fourth stat card inside its repeater.

The title for this is Complaints and the value is bound to the count attribute.

This is what our cards look like for the Online Orders tab:

We’ll copy this and paste it into the Ad Spend Tab, then change the first data provider to the Ad Spend Stats query. This time, our cards are going to show:

Total Spend.
Total Return.
Average Daily Spend.
Daily Return.

The first and third cards can be bound to the sum and avg attributes from our updated existing data provider, plus dollar signs.

Cards two and four will need to be wrapped in data providers for our online order stats. So we’ll wrap these in data provider and repeater pairs, with the data source set to our SUM, COUNT, AVG by DATE query.

We’re going to use a bit of JavaScript to get aggregate values for these.

So, our total return is the revenue we generated - which is stored in Postgres - minus what we spend on advertising, as stored in the Google Sheets Table.

We use the following JavaScript as the card’s value, returning it to two decimal places:

Then for the daily average return card, we use a JavaScript binding to take the average advertising spend away from our daily average revenue:

And that’s our cards most of the way there. We’ll update these to account for our user-defined filtering shortly to finish them off.

Charts

Next, we’ll add a Chart Block to the bottom of each report. For the first, we’ll set the data to onlineOrders, the label column to date, and the data column to value. We’ll also change the height setting from 400 to 600, to make it a little taller.

Then, we’ll do the same with our Ad Spend tab, this time using Ad Spend as the data, Date as the label, and Spend as the data column.

And that’s it:

User filtering

Next, we’ll add our filtering UI. On the Online Orders tab, users can set a date range and filter by item name. On the Ad Spend tab, they can just set a date range.

We’ll create a horizontal container at the top of our orders tab. Inside this we’ll place a:

Title component.
Two date pickers inside another horizontal container, with their fields set to startDate and endDate.
A text box, with the field set to itemName.

This looks like this:

Now, we need to set these to alter each of the data providers. For the custom queries, we’ll click on the cog beside the data dropdown and set the bindings to our form fields value:

Repeat this step for all of our data providers.

Then for our charts, we’ll need to use the Define Filters menu to create equivalent filtering statements:

You can repeat this exact process for the Ad Spend tab, but simply leave out the Item Name text field.

Finally, we also need to make our data providers filterable by item, but only if the text field isn’t empty.

For each data provider, we add a condition to update the data to our other custom query that includes the itemName binding, whenever the form field is not null:

Use the cog icon highlighted above to access the bindings menu and set the itemName value to the form field input:

And you can create a condition similar to this for the chart block’s data and filtering to finish it off.

Now, we have all of our data displayed and filterable by user inputs.

Adding realtime refreshes

We’re just going to add one more little trick to our dashboard, using another custom component plug-in. This one is called the interval component.

This lets us define an interval in seconds, and an action to perform each time this is met.

We’re setting ours to 120 seconds, so it goes off every two minutes, with an action to refresh any data providers it’s nested inside. We’ll eject our chart block to expose the data provider inside it.

Then we’ll place an interval component inside every data provider:

Where we have data providers nested inside other data providers, we can nest an interval within the innermost one, and set multiple actions to refresh them all.

Now our dashboard will display our desired data in real-time, with a two-minute refresh rate. So, we’ll never be dealing with out-of-date figures. All of the data providers will also refresh whenever the screen is reloaded or refreshed.

5. Design tweaks and UX

Finally, we’ll tweak our design a little bit, just to make it a bit more appealing for users. First, we’ll set our theme to Lightest:

Then we’ll remove any navigational elements, the app logo, and the title, to give it a cleaner look:

And we’re done!

Here’s our finished app again:

To learn more, why not check out our ultimate guide to data management software solutions?

Turn data into action with Budibase

At Budibase, our mission is to help teams turn data into action. IT teams are under more pressure and businesses are more reliant on data-driven decision-making than ever before.

Let’s check out how Budibase empowers busy IT professionals to do more with less.

Our open-source, low-code platform

With intuitive design tools, autogenerated screens, extensive data support, and immense extensibility, our platform is the fast, easy way to build all kinds of custom web apps.

Check out our features overview to learn more.

Connect to external data

We lead the pack for external data support. Budibase offers dedicated connector UIs for SQL, Postgres, Google Sheets, Airtable, REST, Oracle, S3, Mongo, Couch, and more. Wherever your data lives, Budibase is the easy way to build professional UIs around it.

We also have our own built-in database with full support for CSV uploads, as well as an internal API.

Build automations

Build custom automation rules with minimal manual coding. Our built-in automation editor features an array of nestable, configurable actions and triggers, with support for looping and conditional logic.

You can even leverage external data and events within automation rules, using Zapier, webhooks, and REST.

Optional self-hosting

Budibase offers optional-self hosting. Deploy to your own infrastructure, using Kubernetes, Docker, Docker Compose, Digital Ocean, and more.

Or, choose Budibase Cloud, and let us handle everything. Check out our pricing page to learn more about both options.

Custom plug-ins

At Budibase, our design philosophy is simplicity by default; extensibility when you need it. So, we launched custom plug-ins. Build your own data sources and components, and ship them across all of your Budibase builds.

Check out our plug-ins documentation to learn more.

Configurable RBAC

Budibase makes it a breeze to perfectly balance security with user experience. Assign users to preconfigured roles and grant or restrict access at the level of data sources, queries, automation rules, screens, or individual components.

We also offer free SSO and user groups, so you can manage access however you need.

50+ free app templates

Budibase is the ideal solution for building all kinds of internal tools. To prove it, we’ve created more than fifty free, deployment-ready, and fully customizable app templates.

To start building web apps the fast, easy way, sign up to Budibase today for free.

Build a Postgres GUI in 3 Steps

Ronan McQuillan — Fri, 18 Nov 2022 09:11:24 +0000

A Postgres GUI helps you make the most out of your database, whether you are a coder or not.

Databases are incredible tools for businesses. They allow you to store and process data at scale so that you can make better-informed decisions.

You have two options to load data from databases. You can use code, via a command line or a programming language. So, you type in the commands in a terminal or send your code to a server and you get the results.

Or you can use a Graphical User Interface (GUI) to visually interact with your database. With a GUI you interact with your database using screens, buttons, and visual elements to see what is happening.

Thus, a GUI allows you to access your database, even if you aren’t a coder. If you are a coder, it is still a great option to run simple tasks. You can always run your queries manually in case you need them either way.

It is quite tricky to pick the right tool, though. There are many factors to consider, including licensing price, features, online access, number of users, ease of use, and many more.

But there's a simple solution.

You can build your own Postgres GUI using a free, open-source low-code platform like Budibase.

It's much easier than it sounds, it requires no coding knowledge and you can do it in three steps.

Connect to your Postgres Database.
Auto-generate the GUI screens.
Add your custom elements to your Postgres GUI.

Today we walk you through this process. You’ll learn how you can connect to your database, get data from it, and generate screens automatically. Then, you’ll learn how to customize the app to add your own functions.

All of that with no coding knowledge, using Budibase.

Let's get started!

Is there a GUI for Postgres?

Postgres is a database system. It is a tool to store and process data.

Thus, it doesn't have an official GUI.

But there are many Postgres GUI options available. In addition, you can build your own GUI in just three steps.

Let’s see what you can build in the next section.

You might also like our guide to building a MySQL GUI.

What is the best client for PostgreSQL?

The best Postgres GUI depends on your requirements. There are pre-made tools with a wide range of features and prices, yet there’s no guarantee that you’ll find the perfect fit for your business.

At the end of the day, the best PostgreSQL client is the one that does everything you need. And very rarely will you find this in a premade tool.

Building an app with code can take a lot of time and it can be very expensive. But you can use a low-code builder to do it for free.

Let's see what we consider the best PostgreSQL client if you just want to get started.

The demo app is connected to a PostgreSQL database for a blog.

There are two tables, one for posts and one for settings.

The first screen you see when you load up the app is the posts table:

In it, you can see a drop-down to select which table you want to work with. This is a useful tool if you have many tables. It allows you to navigate between screens without adding a menu item for each.

If you scroll down a little bit you can see the search toggle. When you click this button, you see the table fields and you can use it to search items, like this:

Next, you can see the table data. You can click on the "view" button or the add new button to manage items. These buttons open a modal screen with this form:

This is just a form with fields coming from the table. But you can spice it up in any way you want to. You can include reference data, API calls, data from other tables, and even actions to send emails.

The settings screen is very similar, but it uses different elements:

All elements are the same, but instead of the search toggle, there’s a dynamic filter. This is another option to filter table data, and you can see the pros and cons of each item when you are creating them.

Let's build your Postgres GUI now.

Step 1 - How do I connect to PostgreSQL GUI?

You can connect your Postgres GUI with your database with the host URL, database name, username, and password. Make sure you whitelist the Budibase IP address in case your server has a firewall.

To build your app, head over to Budibase and create an account if you don't have one. Then create a new app and use PostgreSQL as your data source.

Add your credentials, fetch your tables, and you are ready to use them on Budibase:

You don’t need to create any simple CRUD queries. Budibases does them for you automatically.

Now you can create a query to get a list of the tables in your database. It allows you to automatically add items to the tables selector dropdown, so you don’t need to manually add them to that component.

While still with the PostgreSQL data source selected, click on add query and use these options:

Just replace the username in this query with your own username. Run it and save it.

Next, let's follow the mandatory step in any app design and use a dark theme.

Head over to the design tab, pick the theme menu option, and use the theme options.

In addition to the overall app theme, you can edit the app header/navigation. Click on the navigation section to bring the options to edit the menu items along with the header colors:

You can pick any CSS color you want, from a simple hex value to CSS gradients.

Now let’s create your app screens.

Step 2 - Autogenerate Postgres GUI screens

Head over to the “screens” section. That’s where you can create each of your app routes:

Click on “add screen”, and select “autogenerated screens”.

This option automatically creates CRUD screens for one of your tables. Select the posts table and then repeat this process for the setting stable.

At the end of this process, you have six screens, three for each of your tables.

This is what each of these screens do:

/tablename - this screen shows you the database table, a button to add new items and edit buttons on each of the table rows.
/tablename/new/row - this screen contains a form to add new items, and it uses fields from the table schema.
/tablename/:id - this screen uses a route with a variable :id. So, anything that goes after the /tablename/ portion of your URL is saved in the :id variable. This allows you to prepopulate the edit form with data from the item you want to edit.

If you want a simple Postgres GUI, that is all you need to do.

The beauty of using autogenerated screens is that it gives you an immense headstart. And you can edit these screens if you want to add more options.

For example, you might want to connect to an API when editing items and populate a field with it. Or maybe you want to add search options to quickly find a specific entry from your tables. You can edit these screens, instead of starting from scratch.

Let’s see some customization examples next.

Step 3 - Customize the GUI screens

There are four main customizations in the demo app, and they show you how to do many different tasks with Budibase.

Add tables navigation with a dropdown.
Add dynamic filters to a table.
Add custom search filters to a table.
Open the edit/new screens in modals.

Here is how you can do each of them.

How to add a dropdown to navigate through Postgres tables

The logic of this feature is to list all your database tables, then when you select one of them your app loads the /tablename screen.

Edit the posts screen and add a new component to it using the blue button. You can use this structure:

The parent component for the selector is just a container. Inside of it, you can add a data provider. This provider loads the show_tables query. This provider is simply a data source for the actual options picker.

Now add a form component inside the data provider, just to make sure that the options picker works. Add the options picker and select the data source as the data provider. Labels and values are the table name.

Then, add an onChange action for it, like this:

That’s all you need to use this dropdown as a quick way to navigate to all the /tablename routes.

How to add dynamic filters to Budibase tables

Now, let’s edit the settings page and add a filtering component to it. Click on the data provider, add new component and select the dynamic filter.

This component automatically changes the data provider, allowing user inputs on each of the table columns, like this:

That’s a very quick way to add search options to your Postgres GUI. But since this is a premade component you can’t fully customize it. If you want a custom logic in your search, then the next component works better.

How to create a custom search form

Back to the posts page, add a form component above the table data provider. This form contains the search form itself. This is the final tree structure for that page so you can follow along:

The search form is a parent of the data provider, so you can use the form inputs in the data provider itself.

The “search items” button toggles the visibility of the fields group. You can do it with an app state.

Set the onclick actions of this button to run “Update State”, to “set value” with the key “search”. In the value click on the binding button, and then on the JS code.

Use this code:

if ( $("State.search") ) {
    return 0;
    } 
else {
    return 1;
    }

This code just alternates between 0 and 1. If the current app state is zero (search fields hidden), it sets this state as one (fields visible). If it is one, the app state is updated to zero (hiding the fields).

As for the form fields themselves, here is where you can go crazy. You can add anything you want there. For example, you can use referenced fields, but instead of displaying a client_id, you allow users to search by the client name or even their address.

Right now, we just use two simple text fields, as that’s what we have in our table.

Then, to make these fields affect your data provider, set its filters like this:

This is where you can set anything you want, from bindings to JS code.

That’s it, your custom search is ready.

How to open and close modal screens in Budibase

Lastly, you can make the edit and add new buttons to open in a modal screen, instead of reloading the app.

You can do it by editing the original table item, removing the default link, and using a button instead. Then, use this onClick action for the edit button:

You can make a very similar edit to the create new button, just check the “open in a modal’ option.

This is enough to make the forms open in a modal. But you need to close it.

Edit the delete, and save buttons (from the edit and add new screens). Instead of “navigate to” actions, use “close modal” actions.

How to build a Postgres GUI in Budibase

Today you learned how you can create a Postgres GUI to interact with your database in just 3 steps. Now you can quickly deploy your own GUI, and customize it in any way you want to.

Check out our in-depth guide to building database GUIs.

We hope you enjoyed it, and see you again next time!

Create a REST API GUI with Budibase

Ronan McQuillan — Wed, 16 Nov 2022 09:21:53 +0000

If you need a simple and flexible REST API GUI, you’ve come to the right place.

REST APIs are incredibly useful. They allow you to interact with services and sites without scraping them. You can just access a URL, send some parameters and get a response.

But when it comes to building your apps and actually communicating with them, things can get tricky. Sometimes they won’t respond as you’d expect. It can get quite tricky to pinpoint where the issue is- with your code or the API itself.

Thus, a REST API GUI helps you to identify what exactly is going on. You have visual elements to control what information is being sent, so you can read the response.

There are quite a few options for REST API GUIs though. But if you need something simple yet powerful, often building your own GUI is the best option.

And you can do it in just 4 steps.

This might seem like overkill, but building your own REST API GUI is, in most cases, faster than picking a premade tool and learning it. With the added bonus that, without coding, you can build a tool that perfectly fits your needs.

This is what you are going to learn today:

A one-step method to import and test endpoints for any REST API.
How to create an app without coding.
How to connect this app to a REST API.
How to build screens to send requests and output information.
How to save requests for later use.
How to build CRUD screens with one click.

Let's get started!

What is a REST API interface?

A REST API allows you to use HTTP requests to GET (read), PUT (update), POST (create) and DELETE data. Thus, it’s similar to performing CRUD operations, but instead of connecting to a database, you are connecting to an external service using URL calls.

This is one of the most fundamental techniques that developers use to connect web apps to different data sources.

An interface allows you to perform all these operations visually, with a tool such as a REST API GUI. Thus, you don’t need to rely solely on command lines or programming languages to test or perform your operations.

Are all APIs RESTful?

There are strict requirements for RESTful APIs. REST refers to the set of rules and the architecture of your API. Thus, if your API uses a different architecture it isn’t RESTful.

Essentially, an architecture is a defined framework for sending and requesting data.

APIs can use other architectures (such as SOAP or GraphQL) or they can be using their own architecture.

This is important because most clients and applications that connect to any RESTful API rely on these standards. It is like manufacturing a data transfer cable. If you pick USB-c, you need to use a physical format and transfer data with specific protocols. Otherwise, you can’t connect to other devices.

How do I test REST API calls?

You can test REST API calls using Budibase.

The first and simplest method is to create a free account, create a new app, then pick a REST API data source.

Next, just import your API specs using this option:

Then you can use the data source tab itself to quickly test your API calls and see the response.

Another way to do it is using a purpose-built REST API GUI. It might sound like a lot of work, but you can do it in just 4 steps with our intuitive low-code platform.

At the end of this process, you’ll get an app with these screens:

All Queries - List all saved queries
- Edit/add new
New POST - Run new POST calls
New GET - Run new GET calls

This is how the “all queries” screen looks:

You can see the main menu at the top. Then there’s a table with the currently saved queries, along with their method, a “run query” button, and an edit button. In addition, there’s an option to add new queries there.

Both the edit and add new screens load similar forms, like this one:

This is a form where you can edit the query, or delete it. If you click on “run query” on the previous screen, the post or get screen is loaded, depending on the query you are running.

This is the POST screen. In it, you can pass the route, and your data and you’ll see the query results. In addition, you can save queries if you want to.

This is the GET screen. It is very similar to the previous one, but it doesn’t require a request body. It allows you to run GET queries and see the results as well.

Now let’s see how you can implement this app.

Step 1 - Create your app

If you haven’t already, sign up for Budibase. It’s free and it takes just a couple of seconds.

Then, create a new app, and add two data sources:

REST API
BudibaseDB

You can use the Budibase DB to quickly store metadata about your app. In our demo, we use it to store the saved queries. You could use it to manage users, add settings, or anything you want.

Create a new table called rest_queries. Then add columns for URL, JSON Body, and Method.

Next, on the REST API settings let’s add some queries. In our demo app, we are connecting to the Budibase API. It allows us to manage data about apps and users.

Two headers are used in all queries:

Content-Type - application/JSON.
X-budibase-api-key - your API Key. You can find it on the main Budibase page and click on your profile icon:

Since these two headers are global, you can just add them on the main Data Source page. This loads them in all of your queries.

Now let’s create our queries. Since the actual query data is added on the front end, by your users, we just need one query for each of the methods you use. In our demo we just use GET and POST, so we need two queries.

If you have other methods in your app, you can add more queries there.

To make these queries flexible, you need to rely on bindings. With bindings, the query data is replaced with whatever your screens use when loading that query.

This is what the POST query looks like:

Notice how you are using a URL binding and a JSON body binding. Also, the actual request URL looks like this: {{ url }}#post. Thus, if you add example.com/, the processed URL is example.com/#post

This is just a small quality-of-life change so that you know which query you are working with on your screens. You could use different binding names for each if you want to. For example, urlPOST, and urlGET.

Don’t forget to use the JSON body binding on the “Body” tab for the post request. Set the body as raw (JSON) and use this code:

{{ Binding.JSON }}

Also in this particular API, the return data is inside a “data” variable. You can make your life easier by using this transformer:

return data["data"]

With it, you can use the API output directly in your components such as tables and repeaters.

Use the send button to test the call and save it.

The default call is search apps, using the https://budibase.app/api/public/v1/applications/search route.

Once this call is working, copy it, and replace POST with GET. Remove the JSON binding and the JSON body. You should get something like this:

In this case, we are testing it with this route:

https://budibase.app/api/public/v1/applications/\[add_id\]

This is a GET request to load a specific app. Don’t forget to replace [app_id] with a valid app id so you can test it out. Save this query.

You can edit other global settings for your app as well before building your screens. For example, you can head over to Design > Theme and pick a dark theme, or add a background color to your header.

Now let’s create your app screens.

Step 2 - Create the query management pages

The core goal of your REST API GUI app is to run custom queries and save them. But creating a screen to load saved queries is incredibly simple.

Just go to Design > Screens > Add Screen. Select the “Autogenerated screens” option. Pick the rest_queries table.

You’ll get three news screens when you pick this option:

rest_queries - This screen loads a table so you can see your queries along with a “view” button
rest_queries/:id - When you click on “view” you can see this form, to edit data or delete an item
rest_queries/new/row - This is quite similar to the previous form, but to create new items

You will notice that the rest_queries screen is a bit different from the demo app. You can edit this screen in any way you want to better suit your REST API GUI.

Now compare the element tree you have right now, with the elements tree from the demo app:

The main difference is in the rest_queries Table.

First, there’s the “Run query” button. With the table selected, create a new button component. Then open up its “onclick” actions:

This button has three actions:

Update State:

Set value
Key: url
Value: {{ rest_queries Table.rest_queries.URL }}
Persist value

Update State:

Set value
Key: json
Value: {{ rest_queries Table.rest_queries.JSON Body }}
Persist value

Navigate to page:

URL: /{{ rest_queries Table.rest_queries.method }}

With these actions, when you click on “run query”, you are going to save two variables, and then navigate to /post or /get, depending on the current row.

If you want, you can use different variables for urlget and urlpost. That would prevent the app from loading a GET URL when you visit the POST page and vice versa. In this case, you’ll need a different onclick logic using conditionals, which we won’t cover here.

Moving on, the edit link is just the view link with a different name. Feel free to edit that component if you want.

Lastly, there are the “post” and “get” tags. You can create them by adding both tag components to all rows. Then use the display conditions to show only the correct tag, like this:

You can use these conditions:

POST: Show if {{ rest_queries Table.rest_queries.method }} equals to post
GET: Show if {{ rest_queries Table.rest_queries.method }} equals to get

That’s all for the home screen. Let’s create the POST calls screen.

Step 3 - POST calls screen

Create a new screen with the /post route. The overall logic for this page is to load the {{ url }}#post query using bindings from the variables. This allows you to load data when users click on the saved queries table. But it allows you to set this variable on that page as well.

This is the elements tree:

Create the title container with a title component. There are no big surprises on these components, just use some text to show a title on your page.

Then, create a new form component. All form items need a form component as their parent. You can use a custom schema, then add the form fields components.

The text input for the URL is a simple text field, like this:

Notice how we load the default value from the {{ State.url }} data. This pre-populates this field with whatever URL you have saved in your app, coming from this page or the homepage.

Moving on, there’s the JSON Body multi-line component:

In this component, you can steer users on how they should add their data. It is done using the { bracket on the label, and a placeholder with just the JSON properties.

Then add a paragraph to close the bracket }.

The “run” and “save” buttons are inside a container with these properties so that they are in a single line:

The “run” button is the exact same button you have inside the rest_queries table. But it won't navigate to a page, and the bindings are based on the form components, not the table components. So:

Update state > Set value > url: {{ request data.Fields.URL }}
Update state > Set value > json: {{ request data.Fields.JSON Body }}

The “save” button runs the “Save row” action with these options:

Datasource: none
Table: rest_queries
URL: {{ request data.Fields.URL }}
JSON Body: {{ request data.Fields.JSON Body }}
Method: post

Now you just need to display your data.

Create the component for the query result container and a data provider. You can use these bindings in it:

Notice how the #post in the query name is useful now. Otherwise, you’d have two queries with the same name.

Then pass the bindings from the app state, this allows the data source to always load whatever your REST API GUI is telling it to.

Once you load data using a data provider, it is loaded as an array. You can access this data with a couple of different methods:

Directly, using bindings or JS code.
Using a repeater.
Using a table (like we did in the rest_queries CRUD screen).
Using cards.
Using charts.

A table isn’t the best option, since you don’t have a fixed schema. Each request can have a different number of columns with different data types.

Cards have preset fields. They work well in just a few fields, but we need more.

Thus, in this case, you can use a repeater and create your own output. Create a repeater and use the horizontal direction, just like you did for the buttons container.

Then create the cards container to hold the output. You can set it with width: 30% in case you want items to side by side. You can set borders and other styling options as well if you want.

The paragraph component contains this JS code:

   var row = $("New Repeater.Row Index");
    row = $("Main Query.Rows")[row];
    var ret = "";
    Object.entries(row).forEach(([key, val]) => {
    ret += "" + key + ":\n";
    ret += JSON.stringify(val, null, "\t");
    ret += "\n\n";
    });
    return ret;

This function gets the current row index (a number), then loads a variable from the data provider array with the entire row. Next, for each of the row properties, you display the key and a string representation of the value (in case you have elements inside an element).

That’s it, the POST screen of your REST API GUI is ready.

Step 4 - GET calls screen

You can duplicate the POST screen, and use the /get route for the new page.

It is quite similar to the previous screen, this is its elements tree:

You just need to update a few items on this page:

Update the data provider, from #post to #get.
Remove the JSON body field.
Update the “Save” button, so it sends an empty JSON body and the “get” method.
Update the page title.

That’s it!

You can follow this method and create other screens if you want. You can have all the REST methods by just copying these base screens.

How to build a REST API GUI with Budibase

Today we looked into how you can create your own REST API GUI in just 4 steps. You saw how you can use Budibase to import and test REST API calls. Then you learned how you can create an app, and connect to a REST API using flexible calls based on user inputs.

Lastly, you saw how you can bring it all together on screens to save queries, run them and use different REST API screens for each method.

We hope you enjoyed it, and see you again next time!

Check out our in-depth guide to building database GUIs for more inspiration.

Build a SQL GUI in 5 Steps with Budibase

Ronan McQuillan — Fri, 11 Nov 2022 14:42:17 +0000

SQL is a powerful query language that can be used to unlock valuable insights stored in databases. However, it can be difficult to learn and use, particularly for non-specialists.

That’s where a SQL GUI (Graphical User Interface) comes in handy.

SQL GUIs are useful for developers and non-developers alike. They provide intuitive interfaces to interact with the database without writing commands. You can visually interact with tables, rows, fields, searches, and more, without any specialist knowledge.

Thus, you don’t need to worry about the right code syntax to get the data you want to see. You just use the GUI components, like you would with any other app.

They can cater to advanced users as well. With them, you can quickly perform simple tasks. And if you want to write SQL code anyway, it’s always possible to do it.

Another benefit of a dedicated GUI is that you can have a single tool to load data from different databases with completely different architectures. Therefore, with a single tool, you can see all your MySQL, MariaDB, and even Oracle databases, wherever you are.

The bad news is that buying a SQL GUI might not be an easy decision. There are many things to consider, such as licensing, access control, user volumes, app installs, and custom workflows.

But you can solve this with low-code development.

It’s possible to build your own SQL GUI in just 5 steps. Using Budibase, you can create a tool that does exactly what you want it to, that loads all your data, for unlimited users and with web access.

This is the beauty of low-code development.

The truth is, you don’t even need the full 5 steps. If you just want a simple CRUD interface, you can do it with just steps one and two. But today, we’re going to look at how to create something a little more sophisticated.

Here is a quick overview of the steps to build your SQL GUI app:

Create your app and connect to your database.
Create your CRUD interface with one click.
Custom DB connections to read, write and delete data.
Custom screens to see and filter all your tables.
Forms to edit data from any table.

Let’s get started!

Does SQL have a GUI?

SQL is a standard language that lets you store, manipulate and retrieve data in databases. It doesn’t have a GUI in itself, but there are many applications you can use for that.

They come in a vast range of options and prices, and you can even build your own GUI for free, by following this tutorial.

In case you want to check out what options there are out there, here is a list of off-the-shelf solutions:

DBeaver.
SQLyog.
DataGrip by JetBrain.
DbVisualizer.
DronaHQ.
MySQL Workbench.
OmniDB.
DataGrip.
HeidiSQL.

Check out our guide on how to create forms for SQL databases.

What is the best GUI for SQL?

The best GUI depends on your needs. Some are more popular, due to how they are distributed. For example, phpMyAdmin is pretty ubiquitous.

Others cater to very specific needs.

Here are some points to consider when you pick a SQL GUI (or if you build your own):

License cost - be it one time, monthly or yearly.
Licensing limitations - number of users, number of instances.
*Local or web-based *- some are accessible online, others only work as desktop installations.
Databases supported - some work with a specific database, and others can work with multiple databases.
Ease of use - how complex the interface is.
Customization - if it’s possible to add new features to it via plugins, or even editing the source code.
Reliability - if the tool works all the time or if it breaks.

How do you create a GUI?

You can create your SQL GUI using Budibase. There are actually two methods to interact with your databases, and you’ll learn both:

Using the one-click interface to generate CRUD pages for a table.
Using custom connections, queries, and a little bit of custom code to make a flexible interface that loads any table from a database connection.

The end result is something like this:

This is the main app page. In it, you can select the tables, filter data, add new records, and edit rows. The add new/edit screen looks like this:

The form is the same for both the new row and edit options. The only difference is that one creates a new row, and the other updates a current row.

In addition, there is the App Settings page:

The settings page is where you add your DB tables. Using this method your end users don’t need to edit the Budibase app to add new data. They can just insert them using this screen.

If you take a closer look, it is quite similar to the home screen. The difference is that the settings screen is created using the auto-generated pages, while the home screen is custom-built.

This may seem like a small difference in terms of end result, but they have different use cases and capabilities.

It’s worth mentioning that we’re not going to dive too deep into security and user-control today. If you have a lot of users or critical data, make sure to include protections against SQL injection and proper user controls in your pages and queries.

1. Create your app and connect to a database

To get started, you can create a free Budibase account.

Once that’s done, it’s time to connect to your data source. In our demo, we are using a MySQL database.

Add the connection details on the interface:

Make sure you have whitelisted the Budibase IP in your server. Otherwise, even with the right credentials, you won’t be able to connect.

Next we need to store our App’s settings. You can use one of your tables to do it, but another option is to use Budibase DB.

In our demo app we have a table called “_app_setting_s” with these columns:

Table_name.
ID.
Title.
Value.

You’ll use this table to select which columns from the MySQL tables correspond to this format. This allows us to use a single table with a fixed structure to display data from any table we want.

For example, in the MySQL tables we have these tables and columns:

Posts:

id,
title,
content,
date,

Settings:

id,
settings_key,
value.

Therefore, we need to tell Budibase, which attributes from each of the tables go into each of the columns we have available.

These are the mappings:

2. Generate a SQL GUI automatically

If you just need a simple GUI, you can create it automatically with Budibase. Let’s do this with the App Settings so you can use this method for your tables.

Go to the Design tab, select the screens menu, and hit add screen.

Then select the “Autogenerated screens” option and pick the app_settings table.

This is what you should see:

And that’s all you need for a basic SQL GUI. You can repeat this process with all tables in case you want just these functions.

In our example, we’ve changed the app formatting a bit too. You can do this in the “Theme” section:

We picked a dark theme and a CSS gradient for the header background. You can add any valid CSS background values in here, including simple colors or complex values, such as gradients and even background images.

Now let’s see how you can spice up your SQL GUI with a single screen that loads multiple tables.

3. Custom database connections and queries

If you have a lot of tables to manage, maybe manually creating a CRUD interface for each of them isn’t the best option. It’s possible to create your own interface that loads tables and databases automatically.

For your end users, this might be an easier option, since they can add new tables and databases without editing the app itself.

They just need to add the tables in a settings table. As long as your current DB connection has the right capabilities to access data, you’ll see it automatically.

Now, as for how you can create this dynamic SQL GUI, there are a few important elements:

The DB commands - they depend on your database, so you’ll need a screen for each of your database connections.
The tables schema - here we use a mapping method so all tables respond to the same schema. There are other ways of doing this though.

The tricky part here is to take a user input (tables options) and add it to your queries dynamically. This is hard because SQL usually doesn’t allow functions or variables in table names.

So you can’t just use a command like:
_SELECT * FROM {{ table }}_,
Because this binding is treated like a plain string (wrapped by single quotes).

Check out our guide to database schemas for more information.

Some DBs such as MariaDB have a handy command for this, EXECUTE IMMEDIATE. It allows you to send a SQL command with variables or anything else you need.

If your DB has this command, then you can just use something like this in your queries:

EXECUTE IMMEDIATE ‘CONCAT ( ‘SELECT * from ‘, {{ table }} ) ’

But since we are using MySQL we need something else. If you look closely, the EXECUTE IMMEDIATE command is actually just a shorthand for this:

PREPARE stmt from ‘CONCAT ( 'SELECT * FROM ‘, {{ table }} ) ’; EXECUTE stmt; DEALLOCATE PREPARE stmt;

And this is actually possible with MySQL. You just need access to your DB (using PHPMyAdmin or similar) and you can create a procedure there with something like this:

BEGIN SET @q = query; PREPARE stmt FROM @q; EXECUTE stmt; DEALLOCATE PREPARE stmt; END

Now you are ready to perform the same actions as you could do with MariaDB, with a slightly different syntax. This is how we do it:

CALL execute_immediate( ‘CONCAT ( ‘SELECT * from ‘, {{ table }} ) ’ )

That’s it, you are ready to run any type of command that you need using variables.

Head over to the “Data” section, and let’s create three queries. One to select data, the second to edit/add data, and the last query to delete data.

Here is the setup for the variable_select query:

Then use this SQL command for the fields:

CALL execute_immediate( CONCAT ( ‘SELECT ‘, {{ID}}, ’ as “ID”, ‘, {{Title}}, ’ as “Title”, ‘, {{Value}}, ’ as “Value” FROM ‘, {{table}} ) )

This command takes the bindings and use them to build the query. Notice how everything is variable, including the table name and the column names, which are mapped to ID, Title, and Value.

Also, notice that we are mapping the table columns to standard names, ID, Title, and Value. This allows us to use a single table and a single form to view and edit all of them.

Make sure to use this transformer:

return data[0]
By default, when you execute a stored procedure, MySQL returns some extra data that you don’t really need. So you can just read the first value of the data array (data[0]) to get the query results.

You can run this query and save it.

Next, there’s the insert/edit query.

This time, the entire command is dynamic, given that we use the form fields to create it.

Here is the setup:

Lastly, there’s the delete query. Here is the setup for it:
Custom SQL Query

4. Load any table from your database

If you want to load your tables dynamically, you need a few data points. Each of these can be pulled with the data provider component in Budibase.

This should be the hierarchy of your data points:

Load all possible tables (from the Budibase DB app_settings table),
Dropdown for the user to pick their desired table,
Load the selected table’s mappings (ID, Value from app_settings)
Load the table data sending the correct bindings (table name, id, value) to the variable_select MySQL query.

If you want to implement this screen, you just need to follow that structure. This is your final element tree:

The first component is the main container. This is used to hold all of your other components.

Then there’s the first App Settings Provider. It has no settings, and it just loads data from the app_settings table.

Inside of that provider you’ll add the Select Table form. All form components require a form to be their parent, otherwise they won’t work. This form is there just to make the dropdown work, you don’t need to change its settings.

Next there’s the table options picker. We are using these settings for it:

When your user picks a new option, your table should change. You can do this by creating an app state. This is just a way to store variables temporarily in your app, based on user actions. To do that, use this option for the OnChange action of the button:

In addition, we want that dropdown to automatically select the current table. But when users first load your app there will be no selection. Thus, you can select the default value of that field to be a JS function, like this:

var ret = $("State.selected_table"); if ( ! ret ) { ret = "posts"; } return ret;

The next component to implement is the Selected Table Meta, which loads the metadata for the current user selection. It is a data provider just like the first one, loading data from the app_settings table.

The difference is that now we add a filter to it. You can set it to load items with table_name equals {{ State.selected_table }}.

Then add a repeater to expose the fields of that data provider. And inside that repeater it’s finally time to load the table data.

As the name suggests, the Selected Table Data Provider loads data for your selected table using the variable_select query. But you need to set the correct bindings to it to make sure it loads the current table’s data and maps it:

Now you can add a dynamic filter so users can view only what they want from that table.

The next component is the “add new” button. It is a simple button that has a “navigate to” action when users click on it:

You can add your table inside the selected table data provider, and add a button inside of it to edit rows. Just like the add new button, the edit button simply navigates to another screen. But this time we use the current row as part of our URL:

These URLs may look weird, but they are just a method to pass data to Budibase. In short, you are telling it to load the /edit/ screen with the row ID, or with zero, if it’s a new item. Then that screen handles these numbers as a variable.

Let’s see how to do it in the next section.

5. Insert, edit, and delete data

Just like the home screen, the edit screen requires some data points to work correctly.

First, you need a URL variable to get which item is being updated. To do that, use /:id in your route. For example, our demo uses /edit/:id.

Therefore, if you visit myapp/edit/130, then the id variable is set as 130. This works not just for numbers. If your app requires text-based variables, you can use them, for example, /edit/myitem. In this case, the id variable is myitem.

It’s worth noting here that this will only work if the variable you bind to your URL is unique. We’d normally use ID, because this is an automatically generated attribute that’s unique to each individual row.

In terms of data points, this is what you need for the edit screen:

Item ID (URL),
Current table (app state),
Table mappings from app_settings,
Add new item form inputs,
Data provider to load the default values for the edit mode,
Edit item form inputs,
Item update state (for the success message).

You can copy the following component tree:

Again, you have a main container to organize all your components.

Then, inside of it there’s the App Settings Provider. It loads the app_settings table. This time, we are filtering it by table name equal to this function:

var state=$("State.selected_table"); if ( state ) { return state } else { return "posts" }

This function returns either the currently selected table or the default table, which in our case is posts.

The Add New container should only be visible if we are adding a new item. To do this, add a new container and set the conditions to Hide component if {{ URL.id }} Not equals 0. So, if the url is anything other than /edit/0, we are editing an item.

Therefore, the add new option shouldn’t be visible.

The Add New form is just a placeholder for the components. This time, the entire action is accessed in the Add New button. You can insert the headline and text fields, then use this set of actions for the button:

Update State - Set the app state updated_rows to zero (so the success message is hidden).

Execute Query - the variable_update query, with settings for the “insert” mode and using the form values along with the bindings coming from the app_settings.

table = INSERT INTO {{ State.selected_table }}
fields = (`{{ App Settings Provider.Rows.[0].[Title] }}`, `{{ App Settings Provider.Rows.[0].[Value] }}`) VALUES ( “{{ Add New Form.Fields.New Title }}”, “{{ Add New Form.Fields.New Value }}” )
Where = “”

Important: Make sure to set the where as “ “, an empty space, so the default value won’t be used.

Update State - Set the app state updated_rows to one to display the success message
That’s all you need for the “add new” form. The edit form is quite similar, the main difference is that you have default values for the form items.

Add a “view row” data provider. In it, you can load the variable_select query and use the bindings coming from the App Settings Provider:

table = {{ App Settings Provider.Rows.[0].[table_name] }}
ID = {{ App Settings Provider.Rows.[0].[ID] }}
Title = {{ App Settings Provider.Rows.[0].[Title] }}
Value = {{ App Settings Provider.Rows.[0].[Value] }}

In addition, set the conditions for the data provider to “Hide Component” if {{ URL.id }} Equals zero. Therefore, if we are adding a new item (/edit/0), this entire block is hidden.

Next, add a repeater to expose the data provider fields so they are easier to work with.

Then, add a new button. This is the button to delete items. You can use this Custom CSS:

position: absolute;
right: 0;

Now you can add three actions when that button is clicked:

Execute the variable delete - Make sure to require confirmation and use these bindings:

table = {{ State.selected_table }}
ID = {{ App Settings Provider.Rows.[0].[ID] }}
IDValue = {{ New Repeater.variable_select.ID }}

Update State updated_rows to zero, so the next time you load the edit page this message is hidden.
Navigate to /home. Then, you can add a headline with the value

{{ New Repeater.variable_select.Title }}

This is the same value that you’ll use in the default value for the title text input. For the value text input, you can use {{ New Repeater.variable_select.Value }}.

Like the previous form, the update form is just a placeholder. The actions are in the update button. The logic for this button is quite similar to the previous one. Here are the actions you’ll run:

Update State updated_rows = 0
Execute Query variable_update:
table = UPDATE {{ State.selected_table }}
fields = SET {{ App Settings Provider.Rows.[0].[Title] }}="{{ Update Form.Fields.Title }}", {{ App Settings Provider.Rows.[0].[Value] }}="{{ Update Form.Fields.Value }}"
where = WHERE {{ App Settings Provider.Rows.[0].[ID] }}={{ New Repeater.variable_select.ID }}
Update State updated_rows = 1

That’s it, your form is ready to edit items.

The finishing touch is a message to let users know that the add new / update action worked. You can add a container and set its conditions to Show component if {{ State.updated_rows }} Equals to 1.

You can add a green border around it, and a headline component with your update message.

How to build a SQL GUI

We’ve only scratched the surface of what is possible with SQL GUI development, we hope you are now inspired to create your own.

In this article, we have explored many different techniques, such as autogenerated screens, dynamic queries, different data loading methods.

Only the sky is the limit when it comes to what you can do following the 5 steps from this tutorial.

We hope you’ve enjoyed, and see you again next time!

Check out our guide to building database GUIs.

To see more of what Budibase can do, why not check out our 50+ free, deployable app templates.

What is Data Access Control?

Ronan McQuillan — Tue, 07 Jun 2022 09:15:02 +0000

Data access control is a crucial element of any information security strategy.

Companies today face a more complex regulatory environment than ever when it comes to protecting personal data. However, this must be balanced with the need for efficient, data-driven decision-making.

As such, how users are assigned access to data across your organization has enormous implications.

Today, we’re going to cover everything your need to know about data access controls, including the theory, the specific methods available to you, and how to create the right framework for your needs.

First, though, let’s start with the basics.

What is data access control?

Data access control is any method you use to control how users interact with your company’s data. The goal is to ensure that data is accessed in a manner that meets your security, privacy, and compliance needs, without undermining efficiency or accessibility.

Users can be employees or third parties, like customers, partners, consultants, or members of the public.

In other words, data access control means defining and enforcing a system for how different users can use your organization's data.

As we’ll see later, there are various types of access control.

For now, though, let’s consider why this is such a critical issue for modern organizations.

Why do you need data access control?

Enterprises and SMBs alike store and process greater volumes of data than ever before. While the benefits of this are obvious, there are many other concerns to manage.

The key is balancing the benefits of data-driven decision-making, including increased creativity, innovation, and productivity, with the need to maintain high standards across security, privacy, and compliance.

This is where data access control comes into play.

Here are four specific issues that data access control addresses.

1. Security

The most obvious goal of access control is data security. In other words, ensuring that it is accessed only by the right people, in the right contexts. The idea is that only authorized entities can access data or carry out different actions on it.

An entity here can mean a user, an automated process, or a particular platform.

Of course, restricting access is just one weapon in your security arsenal. This sits alongside other tools including encryption, identity management, and hardware security solutions.

Far from replacing these other techniques, data access control complements them by ensuring that an otherwise secure system isn’t circumvented, either deliberately or unintentionally.

In fact, data access control is fundamental to any modern security strategy.

2. Compliance

Privacy regulations are increasingly complex, especially for enterprises, or other companies that process personal data internationally. This places several constraints on how companies must treat data, especially where this concerns identifiable people.

These constraints stem from established best practices, as well as formal regulations like GDPR, CCPA, HIPAA, PIPEDA, and NIST.

While these all differ in terms of their specific content and the requirements placed on organizations, each one places limits on how subjects’ personal data can be accessed, and by whom.

Most formal regulations stipulate in one way or another that only entities which need to access different data should be able to do so. Additionally, subjects’ data should only normally be accessed by the entities and for the reasons they initially consented to.

As such, effective access control will inevitably form a core part of your compliance efforts.

3. Efficiency

Data access control is also an important tool for maximizing efficiency across different applications, workflows, and processes. A key part of this is limiting the number of actions that users can take.

At the level of applications, this helps to ensure that user interfaces are as streamlined and effective as possible. At the database level, it helps to minimize labor costs stemming from unnecessary errors, security breaches, and administration tasks.

Additionally, different access control methods offer extra efficiency savings, particularly in terms of assigning and administering permissions. Check out our ultimate guide to role-based access control to find out more.

4. Validity, accuracy & integrity

Finally, data access control is an essential way to ensure validity, accuracy, and integrity in your company’s stored data. Essentially, by limiting the number of users who can perform certain actions on your data, you’re also reducing the risk of human error in doing so.

For example, if only certain users have permission to perform UPDATE or INSERT queries, there’s a far smaller risk of input errors, leading to incorrect values.

You might do this across entire datasets, or at the level of individual entries, tables, or views.

In any case, the goal is to limit the number of entities that can make changes to your data, reserving these permissions to users and processes that strictly need to add or update values.

Access control methods

Now that we have a firmer understanding of why data access control matters, we can think more concretely about how it actually works in practice. As ever, this depends on your specific needs, as well as the particular systems you’re using.

It’s worth noting as well that different methods are aimed at different outcomes. Some are aimed at preventing specific threats, while others are more concerned with managing the day-to-day access requirements of your employees.

As such, you’ll more than likely need to use a variety of methods in tandem.

With that in mind, here are the broad categories of access control methods that are available to you.

Entity-centric methods

Entity-centric data access control involves setting permissions based on different entities’ attributes. Remember, an entity here can be a user, an automated process, or an external platform.

Generally speaking, there are two ways to go about this:

Assigning entities a defined role, with set permissions.
Creating conditions to assign permissions to entities, based on their existing attributes.

These are the two most common ways of assigning user permissions within applications. To find out more about these frameworks, check out our guide on the differences between rule-based and role-based access control.

These models use existing organizational structures as the basis for determining permissions to different entities. For example, you might grant access to different employees based on their job title, department, or seniority.

Data-centric methods

Data-centric methods are centered around the type of data that’s being accessed. In other words, this involves setting rules to control access, at the level of the data itself, rather than based on entities.

For example, you might have a particular dataset that can only be accessed via a dedicated application.

This is a simple way to enforce uniform controls across all entities for a particular dataset.

Of course, one side effect of this is that any user who can access the application then has complete access to the dataset, unless you put additional measures in place.

We can work around this problem in a couple of ways. One would be to strictly control who can access the application, through authentication and identity management tools.

Alternatively, we could implement RBAC within the application itself, to more tightly control which users can carry out different queries on the affected dataset.

Context-centric methods

Finally, context-centric methods are based on the context of how data is accessed. The idea here is to use environmental factors to determine whether or not certain queries can be actioned.

For example, you might restrict the ability to export large volumes of data to certain times of day, to prevent large-scale breaches.

We could also restrict the ability to perform different actions on specific database objects, based on their current stage in a given workflow. For instance, we could create an approval app for accessing different pieces of data.

Rather than acting as the basis for your complete data access control system, context-centric methods are typically used to combat specific risks.

Relying on context-centric methods requires you to identify and map known threats. As such, these techniques are unlikely to be sufficient for protecting your data, unless used in tandem with the other methods discussed above.

Access control, authentication, and authorization

No discussion of data access would be complete without touching on the idea of authentication and authorization.

Authentication is how you verify the identity of different entities.

Authorization is whether or not an entity is allowed to access a particular piece of data or carry out a certain action. More specifically, we need to distinguish between authorized and unauthorized access.

Authorized vs unauthorized access

The distinction between authorized and unauthorized access is effectively a matter of whether or not an entity has permission to carry out an action. Most often reading, creating, updating, or deleting one or more entries.

As the name suggests, authorized access is when an entity carries out an action that they have permission to do.

On the other hand, unauthorized access means that a user has been able to take an action, without the required permission.

This could happen either accidentally or maliciously.

In either case, unauthorized access is a serious threat to any organization.

In the least severe cases, this could mean an internal user accidentally accesses non-sensitive data that they’re not supposed to have the permissions for. If this concerns an identifiable person, it’s still a breach, and should be reported and prevented from reoccurring.

In more serious cases, unauthorized access can easily lead to DOS attacks, data leaks, or other cyber threats.

In addition to the direct impact of these, you could face legal penalties, reputational damage, and other indirect costs.

Preventing unauthorized access

Naturally then, preventing unauthorized access is a top priority. There are several strategies you can employ to achieve this.

First, note that there are essentially two kinds of unauthorized access:

Users who should have no access at all viewing or querying your data.
Authorized users accessing data with the wrong permissions.

Of course, each of these has multiple different permutations. Just like before, either can be unintentional or malicious.

For example, an internal user might mistakenly gain access to a particular resource. This is a very different situation from a malicious actor deliberately hacking into the same system.

However, in both cases, prevention is critical. Effective data access control is a key tool here.

Here are three strategies you can employ to prevent unauthorized access.

1. Combine entity-centric methods and strong authentication

Combining entity-centric access control with effective authentication ensures two things:

Only intended entities can access your data.
Authenticated entities can only carry out actions where they have the correct permissions.

Strong authentication is essential for preventing unauthorized access, for users and other entities alike. That way, you can ensure any action can be associated with the entity that initiated it.

This includes effective password and identity management, as well as tools like SSO and two-factor authentication.

We can then combine this with the access control methods we outlined earlier to control permissions for all authenticated users. Remember, we can do this by defining discrete roles with associated permissions, or with rules based on users’ existing attributes.

2. Clearly define and audit permission authorization

Whichever data access control method you choose, permission authorization is vital. Permission authorization is the practice of allowing different kinds of users to take specific actions, either through their role, or attribute-based conditions.

One element of this is ensuring that your framework for granting permissions is thoroughly documented. That is, that you have a clear record of how and when different permissions are assigned to users.

This solves a few key problems:

Providing a blueprint for ongoing permission assignment.
Streamlining the process of auditing and testing implementation.
Creating a clear record of changes to your access control framework.

The first point helps to prevent unauthorized access by ensuring that entities are assigned the correct permissions in the first place.

The second and third are vital for allowing you to identify instances where users have been able to carry out actions that you didn’t intend to permit them to. You can then correct any oversights or issues that led to this.

Additionally, this helps you to preempt situations where unauthorized access might occur, and put measures in place to prevent them.

3. Configure and implement additional controls

Once you have a robust entity-centric framework in place, you can begin to think about other measures that will complement this. For example, using additional data and context-centric access control methods, to fill in specific gaps in your policy.

These come in two varieties:

Measures aimed at providing additional tailored controls over specific datasets.
Measures aimed at preventing specific threats.

Say we have a large dataset, based on our customers’ details, which we’ve already implemented an RBAC system across.

There are several ways we could use additional controls to further safeguard customer data.

For example, we might want to put additional measures in place to prevent large-scale theft of our data. As we saw earlier, one way to do this would be to create contextual control, so that bulk exports can only be carried out during normal business hours.

Alternatively, we could limit this permission to users with a particular IP address, to restrict bulk exports to head office staff.

Or, we could create a dedicated tool for users to take specific actions on our data. For example, we could add an extra layer of security by creating a framework where certain actions can only be taken using a dedicated, locally hosted application.

How to create a data access control model

As we said at the outset, building a successful data access control model is a tricky balancing act. Specifically, the challenge is balancing the need for efficient access to data, with the need to ensure security, compliance, and privacy safeguards.

However, these goals also intersect to a large extent.

For example, you might have a platform that meets all of your security requirements but is difficult and inefficient to use. This could cause employees to try and circumvent your intended controls, leading to new security issues.

This becomes even more difficult in the context of modern IT environments, where assets and resources are typically distributed across a wide range of physical locations.

The key is developing systems that intelligently provide the right permissions for the right users.

Budibase offers an advanced suite of features to help you secure your company data.

With a built-in RBAC system, custom conditionality rules, free SSO, external data support, optional self-hosting, and autogenerated CRUD screens, Budibase is the ideal solution for creating secure, data-driven applications, with minimal coding skills.

Check out our product page to find out more.