How to Build a SQLite GUI in 5 Steps

SQLite is the most widely deployed database in the world. However, it works slightly differently than other relational database engines, such as MySQL or Postgres.

The key characteristic here is that SQLite databases are stored in a single file. This means that it’s a highly lightweight, portable solution that can be easily embedded into end-user applications.

Today, we’re exploring how Budibase can be used to build a simple GUI for writing, testing, and saving SQLite queries.

By the end, we’ll have a fully functional solution that you can customize to your exact requirements using our open-source, low-code platform.

Let’s jump right in.

Why would we want to build a SQLite GUI?

A GUI is a visual tool that enables users to interact with a database. The idea is to provide a streamlined experience for writing queries, including saving reusable commands and their responses.

Typically, SQLite is managed with CLI tools. Several off-the-shelf GUIs and admin panels also offer support for SQLite.

However, these are generally inflexible, dated, and sporadically maintained. This can make working with data in SQLite unnecessarily laborious.

By contrast, when we build a custom solution, we have complete control over its design and functionality, meaning we can tailor each to the specific needs of our project.

We’ll also have the power to maintain this how we want, including making any changes to our GUI when we need them - rather than waiting around for an off-the-shelf vendor to update their product.

What are we building?

We’re building a simple GUI that will empower users to do three things:

• Write, test, send, and save queries to their SQLite database.
• View the history of queries that have been sent using our GUI.
• Resend and update saved queries.

SQLite is most commonly locally stored and used as an embedded database within application projects. However, today, we’re using a cloud-based database tool called Turso . This includes a range of developer tools, including a platform API for interacting with our SQLite data.

However, we could just as easily use any other version of SQLite that offers a RESTful API.

Once we’ve set up our API request for querying our database, we’ll be using Budibase’s intuitive, low-code design tools to build interfaces for interacting with our data.

You might also like our guide on building a MariaDB GUI .

Here’s what our SQLite GUI will look like when we’re finished.

How to build a SQLite GUI in 5 steps

If you haven’t already, sign up for a free Budibase account to start building as many apps as you want.

The first thing we’ll do is create a new Budibase application. We have the option of starting with a template or importing an existing app dump, but today, we’re going to start from scratch.

When we choose this option, we’re prompted to give our app a name and a URL extension. We’ll call ours SQLite GUI.

1. Setting up our API request

Once we’ve created our app, we’ll be offered several choices for which kind of data we’d like to connect to. Budibase offers a range of dedicated connectors for relational databases, NoSQL tools, APIs, Google Sheets, and more alongside our internal database.

We’ll actually be using two separate data sources in our app. The first will be an API request that allows us to send queries to our database and receive responses. The second will use our internal database to store and manage saved queries and our GUI’s history.

So, we’ll start by choosing the option for REST API.

Here’s what we’ll see.

The first thing we’ll do is rename our API connection to something that will be a bit easier to recognize in the Budibase builder later on.

Next, we’ll hit Create New Query. This brings us to the following screen.

Here, we can give our API request a name, choose a method, and input our endpoint. We’re calling our request Custom Query. We’ve also selected the POST method and pasted in the API endpoint for our database.

We’ll also add an API Header to authenticate our request with our unique Authorization token.

We need to populate a body for our API request to tell it what SQL command to execute. We’ll do this by adding a JSON object under the Body tab in our request builder.

The specific piece of JSON we’re going to use is structured like this:

 1{
 2
 3 "requests": [
 4
 5  { "type": "execute", "stmt": { "sql": "SELECT * FROM users;" } },
 6
 7  { "type": "close" }
 8
 9 ]
10
11}

Essentially, we’re telling our database to execute a statement that we’re providing in SQL syntax. Then we provide the statement and close the request.

Here’s how this looks when we add it to our request body and hit send.

We’ll look at the response in a little more detail in a second, but for now, all that matters is that our query was successfully executed in the database.

Adding bindings

Currently, our request works, but it only sends a static SELECT statement. We want to populate our SQLite query dynamically using the information provided by users in the UIs we’ll be building a little later.

To do this, we’ll head to the Bindings tab. Here, we can specify a name and default value for our binding. The name is used to reference it within the body of our request. We can then overwrite the default value each time our request is initiated.

We’ll call our binding ‘query’ and set the default value to ‘SELECT * FROM dummy_table;’.

We can then replace the static query in our JSON body with our binding, using {{ query }}. So, our new JSON object is:

 1{
 2
 3 "requests": [
 4
 5  { "type": "execute", "stmt": { "sql": "{{ query }}" } },
 6
 7  { "type": "close" }
 8
 9 ]
10
11}

Transforming our result

Currently, our response looks like this:

 1{
 2
 3 "baton": null,
 4
 5 "base_url": null,
 6
 7 "results": [
 8
 9  {
10
11   "type": "ok",
12
13   "response": {
14
15​    "type": "execute",
16
17​    "result": {
18
19​     "cols": [
20
21​      {
22
23​       "name": "id",
24
25​       "decltype": "INTEGER"
26
27​      },
28
29​      {
30
31​       "name": "name",
32
33​       "decltype": "TEXT"
34
35​      },
36
37​      {
38
39​       "name": "age",
40
41​       "decltype": "INTEGER"
42
43​      }
44
45​     ],
46
47​     "rows": [
48
49​      [
50
51​       {
52
53​        "type": "integer",
54
55​        "value": "1"
56
57​       },
58
59​       {
60
61​        "type": "text",
62
63​        "value": "Alice"
64
65​       },
66
67​       {
68
69​        "type": "integer",
70
71​        "value": "30"
72
73​       }
74
75​      ],
76
77...

This is the right data, but it isn’t very readable. The problem is that the columns and rows are displayed as separate objects.

We want to display each entry in our database table as a separate object, including all of its attributes and their values as distinct key/value pairs.

To do this, we can write a JavaScript transformer based on our request’s response. So, head to the Transformer. Here, we can see that, at present, we’re simply returning an object called data.

We’re going to add the following JavaScript to create separate objects for each of our database entries and then nest these within another object with other relevant information from our response.

 1// Extract cols and rows from data
 2
 3const cols = data.results[0].response.result.cols.map(col => col.name);
 4
 5const rows = [];
 6
 7data.results[0].response.result.rows.forEach(row => {
 8
 9 const obj = {};
10
11 row.forEach((cell, index) => {
12
13  obj[cols[index]] = cell.value;
14
15 });
16
17 rows.push(obj);
18
19});
20
21// Include other results in the final object
22
23const finalObject = {
24
25 type: data.results[0].type,
26
27​	responseType: data.results[0].response.type,
28
29​	rows: rows,
30
31​	affectedRowCount: data.results[0].response.result.affected_row_count,
32
33​	lastInsertRowId: data.results[0].response.result.last_insert_rowid,
34
35​	replicationIndex: data.results[0].response.result.replication_index,
36
37​	rowsRead: data.results[0].response.result.rows_read,
38
39​	rowsWritten: data.results[0].response.result.rows_written,
40
41​	queryDuration: data.results[0].response.result.query_duration_ms
42
43​	
44
45};
46
47// Return the final object
48
49return finalObject

Here’s what our request returns now:

 1{
 2
 3 "type": "ok",
 4
 5 "responseType": "execute",
 6
 7 "rows": [
 8
 9  {
10
11   "id": "1",
12
13   "name": "Alice",
14
15   "age": "30"
16
17  },
18
19...
20
21 

Not only is this much more human-readable, but it also means we can map our rows object to a table in our front end later if we want to.

When you’re happy, hit save, and that’s our SQLite connection ready to go.

2. Adding internal tables

As we said earlier, we also want to allow users to save queries and view previous commands sent from our SQLite GUI.

To do this, we’ll need somewhere to store the required information. We’re going to use two tables in Budibase’s built-in database.

We’ll start by hitting the plus icon to add a second data source.

This time, we’ll choose the option to create a new table in BudibaseDB.

We’ll call this queries.

Here’s our empty table. We can use the plus icon to start adding columns.

First, we’ll add a Text field and call it name. We’ve also set this to required.

Then, we’ll add a Long-Form Text field called query and a JSON field called response.

We’ll repeat this process to create a second table called history. This will have columns with the following names and data types:

• date_time: Date,
• query: Long-Form Text,
• response: JSON.

2. Building a query editor

Now, we’re ready to start building our SQLite GUI screens. Head over to the Design tab in the Budibase builder, and we’ll be offered several options for creating our first screen. We can use an autogenerated layout or start from scratch.

For now, we’re opting for a blank screen. When we choose this option, we’re prompted to give our new screen a URL extension. We’re simply using a single trailing slash.

We can then set a minimum role for accessing this screen. We’re going to leave this set to the default option of Basic.

Here’s our blank screen.

We’re going to base our query editor around a simple form where users can write, send, and save queries. Each time a request is made to our API, we’ll open a side panel showing the response.

So, we’ll start by adding a component called a Form Block. This outputs a working form UI based on whichever internal or SQL data table we point it at.

By default, this is a form for creating rows on our queries table, which is what we want to start with.

Before we configure this to initiate our API request, we’ll make a few design tweaks. First, we’ll add a heading. We’ll also deselect the response field and update the display texts so that the remaining fields are more human-readable.

Then, under Style, we’ll set our Size to Large and our Button Position to Top.

Executing and saving queries

Before we proceed, we need to access the underlying components that make up our Form Block, so we’ll hit the Eject Block.

Here’s what our component tree looks like now.

The next thing we’re going to do is configure what happens when a user hits save.

We’ll start by opening the actions drawer for our button.

Here, we can see the default actions that are assigned to this when we create a From Block.

We’re keeping the Validate Form and Save Row actions. We can delete everything else with the X icon.

Then, we’ll place an Execute Query action between Validate Form and Save Row. We’ll set this to the REST request we wrote earlier.

At the bottom left, we can hit the lightning bolt icon to open the bindings drawer for our query binding. We want to bind our query to the user input from the query field on our form. However, we’re going to add a little bit of code to this to clean out any special characters for linebreaks.

We’re going to reuse the same code a few times in our app so we can create a reusable JavaScript snippet. This allows us to define a function and pass user inputs to this as arguments elsewhere in the Budibase builder.

Within this, we’ll select JavaScript, then hit Create Snippet.

We want to remove any line breaks with a snippet that we’ll call cleanQuery. The code we’ll use is:

1return function cleanQuery(myString){
2
3 let str = myString;
4
5let cleanedStr = str.replace(/\n/g, ' ');
6
7return cleanedStr;
8
9} 

We’ll then call this in our binding with the form input as our argument, using:

1return snippets.cleanQuery($("Form.Fields.query"))

Next, we’ll add a button action called Update State. This stores a key/value pair as an app state. We want to use this to store the response from our API request. So, we’ll use response as our key.

Then, we’ll open the bindings drawer for our value. Here, we can access all of the data that our action is exposed to using handlebar expressions.

We’ll start by hitting Actions.

Then, we’ll choose Query Result.

Our Update State action should come between our Execute Query and Save Row actions.

Within the Save Row, we’ll hit Add Column to assign a value to our response column. We’ll set this to our response state, using {{ State.response }}.

At this point, we’ll preview our app and fill in our form to confirm that this works as we expect.

And we can see that this has been saved correctly to our queries table.

Saving a history row

When a user hits Save, their query is sent. So, we also need to record this using our history table.

We’ll start by adding another Save Row action to our button. This time, we’ll set the table to history and add all three columns.

We’ll bind date_time to the following JavaScript expression to get the current date and time.

1var date = new Date();
2
3return date

Just like before, we’ll set our query to:

1return snippets.cleanQuery($("Form.Fields.query"))

And we’ll set our response to {{ State.response }}.

We can test this again by previewing our app. When we’re happy, we can move on to building the last element of our query editor.

Adding a side panel

We’ll start by adding a component called a Side Panel. We’ll call this Response Side Panel.

Inside this, we’ll add a Headline component, with its Size set to Small and its Text set to Response. We’ll also tick the italic box.

Beneath this, we’ll add a Markdown Viewer.

We could simply display our response as it’s currently stored in our app state. But we’re going to use some more JavaScript to stylize this. We’ll start by creating another snippet and calling it stylizeJson.

Essentially, we want to apply appropriate spacing and color coding to differentiate between the keys and values in our JSON blob.

The code we’re using is:

 1return function stylizeJson(myString){
 2
 3 let originalString = myString;
 4
 5// Parse the original string into a JavaScript object
 6
 7let jsonObject = JSON.parse(originalString);
 8
 9// Convert the object back to a formatted JSON string with custom indentation and color coding
10
11let formattedJson = JSON.stringify(jsonObject, null, 2)
12
13 .replace(/(^\s+)|(\n\s+)/g, (match, p1, p2) => {
14
15  return p1 ? '' : '\n' + ' '.repeat(p2.length / 2);
16
17 })
18
19 .replace(/("[^"]+":)/g, '<span style="color: #6b6ceb;">$1</span>');
20
21// Wrap the formatted JSON string in HTML tags for styling
22
23let styledJson = `<pre style="color: white;">${formattedJson}</pre>`;
24
25return styledJson
26
27}

We’ll then call this as our binding, using our response state as an argument, with:

1return snippets.stylizeJson($("State.response"))

This will throw an error in the Budibase builder because our state isn’t populated until a user hits save. We’ll see how it looks in the app preview in a second.

First, though, we need to add one final button action to open our Side Panel.

So, head back to our button actions drawer, and we’ll add an action called Open Side Panel.

Then, we’ll save a query to show what our JSON response looks like.

This works fine. Note that we’ve chosen the colors for our stylized JSON to suit a darker app theme that we’re going to apply a little later, so for the moment, they look a little bit hard to read.

Adding a send button

The last thing we want to do is add a second button to our query editor so that users can send their queries without saving them.

This will essentially work the same way as our existing button, except we won’t validate the form or save a row to the queries table.

We’ll start by duplicating our button and then nesting the original and the duplicate inside a horizontal container.

The left-hand button is the one we’ll keep as our Save button. The one on the right will be used to send the query without saving it.

We’ll start by editing the design of the Save button, setting its Variant to Secondary, and selecting the option for Quiet.

We’ll then set the text for the right-hand button to Send.

We’ll then simply open the actions drawer, remove the actions to validate our form and save a row to our queries table.

Here’s how this will look when a user hits Send.

And that’s our query editor done!

3. Adding a history screen

Next, we can start building our other screens. One of these will be used to view the history of the queries that have been sent from our SQLite GUI. The other will be used to resend or update saved queries.

We’ll start by hitting the plus icon to add a new screen. This time, we’re going to use one of Budibase’s built-in layouts. Specifically, we’re choosing the option for a table with details side panels.

This will output a working CRUD UI based on whichever SQL or internal table we point it at. We’re choosing both of our internal tables and leaving our access role set to Basic again.

Here’s what our history screen looks like. We can access forms for creating or updating rows via side panels.

However, we want this screen to be read-only. So, we’ll start by deleting the Create Row button and corresponding side panel. We’ll also update the display texts for human readability.

All we need to do now is edit our remaining side panel form to display data in the format we want.

Here’s how this looks at present.

We’ll start by changing the Type to View and updating the title. We’ll also deselect the response field and update the remaining fields’ display texts, as before.

Then, we’ll hit Eject Block and add a Markdown viewer below our query field.

We want to bind this to the JSON object that’s stored in the relevant row of our history table. However, this is stored as a JSON object and our stylizeJson function accepts a string as an argument.

So, we’ll first use the JSON.stringify() method to convert this. The code we’re using for our binding is:

1var jsonOutput = JSON.stringify($("Repeater.history.response"))
2
3return snippets.stylizeJson(jsonOutput)

Here’s how this looks.

4. Managing saved queries

Next, we’ll make a few changes to our saved queries screen to better suit our needs. Just like before, we’ll start by removing the Create Row button and form and updating our display texts.

We’ll then make similar changes to our remaining form, by updating the title and display texts, and deselecting the response field, before hitting Eject Block.

We want to replicate the buttons that we had on our original query editor screen. Rather than building these from scratch, though, we‘re simply going to copy them. We’ll start by deleting our existing Button Group component.

Then, we’ll head back to our query editor screen and copy the entire container that our two buttons are nested within.

We’ll then paste these alongside the headline component on our queries form.

Now, the correct flow of on-click actions will be carried across, but we’ll need to make a few changes to individual actions for them to work correctly - since the components they’re pointed at don’t exist on this screen.

The first thing we want to do is update any bindings that reference our query form field.

Specifically, these are our Execute Query and Save Row actions for both buttons. Here’s how these will look now with the broken binding.

We’ll simply update these to:

1return snippets.cleanQuery($("Form.Fields.query"))

Make sure to do this for all Execute Query and Save Row actions across both buttons.

We also need to update the data source setting for any action that references a form. These are our Validate Form and Save Row actions.

For the Validate Form action and the Save Row actions for our queries table, we can simply change this from null to our current form.

However, this won’t work for saving a row to our history table, as the current form updates a row, but we want to create a new entry with this Save Row action.

To get around this, we’ll add a Form component at the bottom of our page, setting its Type to Create and its Schema to history. We’ll also rename this History Form, so we know which is which.

This will be invisible to end users because we’re not nesting any other components inside it.

Back within each of our button actions, we’ll update the Save Row actions for the history table to point them at this new form.

Lastly, we’re going to place a Headline component and Markdown Viewer beneath our query form field exactly as we did earlier.

Like before, we’re going to set our Markdown Viewer to display the output of:

1var jsonOutput = JSON.stringify($("Repeater.queries.response"))
2
3return snippets.stylizeJson(jsonOutput);

Now, we’re displaying the saved response from our queries table. However, if a user hits one of our buttons and sends a query, we want to display the new response.

We’ll do this using Budibase’s conditional UI capabilities. This lets us specify a rule to display, hide, or update the native settings of any component.

First, head to Conditions on our Markdown Viewer and add a rule to update the Markdown setting.

We’ll set this to:

1return snippets.stylizeJson($("State.response"))

If our response state is not empty.

Finally, we’ll return to our button actions and remove the Open Side Panel actions from both, as this is no longer needed.

We can then resend or update our saved queries to confirm that everything works as expected.

In terms of functionality, our SQLite GUI is now ready to go.

5. Design tweaks and publishing

Before we launch our app, we’ll make a couple of minor UX improvements.

We said earlier that we want to apply a darker theme to our app so that our stylized JSON really pops. Under Screen and Theme, we’re going to choose midnight.

While we’re here, we’ll also update our button colors to match.

Here’s how this will look in our app preview.

Then, we’ll head to Navigation, add a menu item for our Home screen, and set the Position option to the left-hand side.

When we’re happy, we can hit Publish to push our app live.

Here’s a reminder of what our finished app looks like.

Budibase is the open-source, low-code tool that empowers IT teams to turn data into action.

To learn more about using our platform to ship professional solutions in a fraction of the time, take a look at our features overview .