Connect Xero API to Appsmith
8
August
2022
Tutorial

Connect Xero API to Appsmith

Connect Xero API to Appsmith
Luke Arran
0
 minutes ↗
#
databases
Tutorial

In this guide – we’ll walk you through connecting your Xero account to Appsmith to create an application around its data.

Setting up Appsmith

Appsmith can be downloaded and hosted within your local business network. But this guide won’t cover the installation process; if you want to try Appsmith, you can use the cloud service provided by Appsmith or speak with a member of our team for advice on installing Appsmith within your local network.

Open Appsmith, create a new application called “Xero – Invoices,” and click “Build with drag & drop.” You’ll see an empty page with “Drag and drop a widget here.” We now need to connect Appsmith to your Xero API account.

Step 2) Setting Up the Data Source

On the left-hand side of Appsmith – you’ll find a toolbar combining “Widgets,” “Queries/JavaScript,” and “Data Sources.” Let’s start by connecting to your business account on Xero through the Xero API.

Click on the plus button to the right of “Datasources” and then select “Authenticated Account”. Appsmith will now create a new REST API datasource within your application. But before using it, we need to configure the datasource to communicate with Xero’s OAuth Authentication correctly.

  1. In the URL field, paste the base address of Xero’s API (https://api.xero.com/).
  2. Click on the Authentication Type dropdown, and select OAuth 2.0.
  3. Scroll down to Grant Type and select Authorization Code.
  4. Within the Access, Token URL field, paste the followinghttps://identity.xero.com/connect/token.
  5. We now need to provide the scope of our API access. In this guide, we’ll need to access our organization's contacts and accounting data. So paste the following values into the field OpenID, offline_access, accounting.transactions.read,profile,accounting.contacts.read,email.
  6. Within the Authorization URL, paste the following https://login.xero.com/identity/connect/authorize.

In order to get our Client ID and Client Secret – we need to make a quick diversion to Xero. Head over to the following link and click on the blue “New App” button once logged in. Give your application a name (e.g., Northwinds Appsmith), and then paste your generated “Redirect URL” from Appsmith into the box at the bottom.

Once created, go into the registered application and select “Configuration” on the left-hand side. Take note of the Client ID and Client Secret (if you don’t have one, click on the generation button). Now return to Appsmith and paste these values into the respective fields.

We should now be ready to authorize your access with Xero. Simply click on the “Save and Authorize” button at the bottom of the page and wait a few moments until you’re redirected to Xero (as shown in the screenshot above). Select the organization you want to grant access to, and click “Allow access.” You’ll then be taken back to Appsmith.

Step 3) Selecting Your Organization

Now you’ve successfully connected Xero to Appsmith – we can retrieve data from your Xero account. Let’s begin by getting the information of organizations from your Xero account.

On the left-hand side of Appsmith – click on your data source (e.g. Xero API) and click on New API +. You’ll now be taken to Appsmith’s query editor for REST APIs. In the URL bar, type in /connections after the URL. Appsmith will automatically handle the oAuth 2.0 authentication in the background and populate the request with the required headers. Once you click the Run button, you’ll see the JSON response from your request at the bottom of the page. It should contain an array of objects listing the Xero organizations you’ve granted access to.

Let’s now create a drop-down list on your application so that your users can select the organization which they want to view data from. On the left-hand side, under “Pages” select on the first page (e.g Page1) and then click on Widgets .

Click on your widget, and within the Options code block we need to bind the values from our API response to the Select widget. Some JavaScript code may have already been populated, which creates an array of items with two values for the widget, a label value to be displayed to the user and the value which should be the ID of the organization. Paste, or enter the below code into the Options block.

.
{{getTenants.data.map( (obj) =>{ return  {'label': obj.tenantName, 'value': obj.tenantId } })}}

Before we move onto the next stage, make sure to provide the widget with a name (e.g tenantSelect – we will need to reference back to the widget in our next query!

Step 4) Getting Invoice Data

Let’s now retrieve the invoice data from your Xero account! On the left-hand side, click on your Xero datasource and click on New API+. Name the query getInvoices and press return.

In the URL bar, enter /api.xro/2.0/Invoices after the base address.

Under the Headers tab, we need to specify the organization in Xero which we want to pull back the invoices for. You’ll see two rows under this tab, under Key 1 enter Xero-tenant-id and then in Value 1 we need to bind to the selected value from the drop-down we previously placed on the page.

Enter the value

{{tenantList.selectedOptionValue}}

Next, we need to tell Xero that we want the API to respond with JSON, and not XML which is the default behaviour. To do this, simply enter Accept in the next row under the Headers tab, and enter application/json .

Now press “Run”, and if everything has gone to plan – the API would have responded with a list of invoices from your Xero account.

Step 5) Displaying Invoice Data

We’ve now created a query which retrieves invoice data from your Xero account, but let’s now create a table which will display the list of invoices.

On your page, drag and drop a button next to the select button. In the Label field, give the name Refresh . When we click this button, we want Appsmith to execute the getInvoices query which we created in the previous stage – so under Events on the right-hand side, click on the drop-down under onClick and select getInvoices under Execute a query . Then let’s display a useful alert to the user informing them that the data has been refreshed, under onSuccess click on Show message and enter Successfully retrieved invoices from Xero .

Next, find the Table widget and place it on your page. Give the widget a useful name, such as invoiceTable . Then the right-hand side, under Table Data bind to the array of invoices from the API response by entering the following value {{getInvoices.data.Invoices}} . After a few moments, the table will display the list of invoices from your Xero account.

However, this will only display the first page of invoices from the Xero API which is limited to 100 records. In order to display all your invoices, you will have to enable server side pagination – on the right-hand side, scroll down to Server Side Pagination and make sure this switch is enabled.

Return to the getInvoices query – under the Pagination tab select on Paginate with Table Page No . And then go to the Params tab, under Key 1 enter page , and then in the value input bind to the current page of the table with the following value {{invoiceTable.pageNo}} .

Then back on the table widget, run the getInvoices query when onPageChange is triggered. Once this has been enabled you can click on the right arrows at the top of the table to navigate through all your invoices.

Next Steps

That’s it for this article! Click on the “Deploy” button at the top-right to see the the final version of your application.

In the next article, we’ll delve further into how we can modify our application to write changes back to Xero.

Build Custom UI on top of Airtable data
25
July
2022
Announcement

Build Custom UI on top of Airtable data

Build Custom UI on top of Airtable data
Rishabh Kaul
0
 minutes ↗
#
integrations
#
databases
#
announcement
Announcement

Today, our integration with Airtable comes out of beta and is available for everyone 🎉! You can now build custom UIs and interact with applications built on Airtable, with minimal configuration.

While it is possible to use the default API interface to connect to Airtable, we wanted to make it easier for you to directly connect your Airtable account and create applications faster than ever. This new data connector introduces a number of features:

  • Integration located in the “Datasources” section
  • Connect to your Airtable account with either an API Key or a Bearer Token (OAuth 2.0)
  • Create queries to fetch, create, retrieve, update and delete data from a datasource using the Appsmith query editor. 
  • List command lets you display all the data from Airtable, and can also present data that has been filtered and sorted based on fields, records, time zones, etc. 

For details and information on how to use this new integration (with videos!), check out our Airtable documentation here. See it in action on our full tutorial here, where we build an issue tracker with Airtable as backend. As always, let us know what you think!

Building an Inventory Management Tool Using HarperDB
7
June
2022
Resources

Building an Inventory Management Tool Using HarperDB

Building an Inventory Management Tool Using HarperDB
Dancia Jos
0
 minutes ↗
#
databases
#
admin
#
authentication
#
app-development
#
apis
Resources

This guide explains how to quickly build an Inventory Management tool to manage movie rentals by connecting a HarperDB backend to an Appsmith frontend. It also implements authentication using HarperDB’s role-based access control feature.

HarperDB is a distributed database that supports key-value, document store, and traditional SQL. As a flexible platform for distributed applications and data, HarperDB is read/write optimized and globally replicates data in under 100ms.

You will need a HarperDB account, which can be created by signing in here.

Appsmith is an open-source framework for building internal tools. It lets you drag-and-drop UI components to build pages, connect to any API, database, or GraphQL source and write logic with JavaScript objects

If you don't have an Appsmith account, create one here.

Let’s get started!

Step 1: Set up your Backend on HarperDB

We’ll be using the HarperDB cloud instance free - tier. Click on Create New HarperDB Cloud Instance → Create AWS or Verizon Wavelength Instance

Screenshot_2022-04-27_at_12.41.21_PM.png

Select Choose HarperDB Cloud on AWS and then click on Instance Info

Screenshot_2022-04-27_at_12.41.10_PM.png

Fill in the details about your instance and click on Instance Details button

Screenshot_2022-04-27_at_12.42.16_PM.png

Keep the default settings in the Instance Specs modal and click on Confirm Instance Details

Screenshot_2022-04-27_at_12.43.30_PM.png

Click on the Add Instance button

Screenshot_2022-04-27_at_12.43.58_PM.png

This will begin creating your database cloud instance on HarperDB. Your instance will be up and running in no time.

Screenshot_2022-04-27_at_12.49.01_PM.png

Step 2: Create the Database (Schema)

  • Create a schema called Movies
  • Next, create tables for Customer, Film and Rentals with id as the hash attr. The hash attr in HarperDB is equivalent to a primary key.
Screenshot_2022-04-27_at_1.32.02_PM.png

Use the Upload CSV feature of HarperDB to populate data into the three tables. You can find sample data here.

Screenshot_2022-04-27_at_1.34.49_PM.png

Step 3: Create Roles and Users on HarperDB

Click on the roles tab and create two users named admin_user and rental_user under Standard Roles. admin_user will have all privileges on the three tables so update all the permissions to true

Screenshot_2022-04-27_at_1.50.31_PM.png

rental_user will have all permissions on the Rental table and only the read permission on the Customer and Film tables

Screenshot_2022-04-27_at_2.13.52_PM.png

Under the users tab, create a user named assistant with the role of rental_user.

Screenshot_2022-04-27_at_2.19.01_PM.png

Similarly, create a user named manager with the role of admin_user

And just like that, the backend setup is complete.

Step 4: Build UI on Appsmith

  • On Appsmith, on the Entity Explorer pane, go to PAGES→ Page1 . Rename the page to Login. Drag and drop a Form widget onto the canvas. Add input widgets for Username and Password as shown below. For the Password input widget, choose Password as the Data Type so that the characters entered are masked.
Screenshot_2022-04-28_at_1.45.30_PM.png
  • Create a second page called Rental Inventory which will be the page that the user will be redirected to after login.
  • Drag and drop a Tab widget onto the canvas and add three tabs called Rental, Film, and Customer
Screenshot_2022-04-28_at_2.51.48_PM.png
  • On the Rental tab, add a Table widget and a Form widget to its right which will populate the details of the selected rental record that can be updated. On the Update Rental form, Customer and Movie are Text widgets, Status is a Select Widget, and Returned is a Datepicker Widget.
Screenshot_2022-05-02_at_3.36.53_PM.png
  • Add a modal widget onto the canvas to create a form for adding new rentals. Customer (rentalCustEmail) and Film (filmTitle) are select widgets
Screenshot_2022-05-02_at_4.59.02_PM.png
  • Set this modal to open on the onClick event of the New button
Screenshot_2022-05-02_at_5.31.02_PM.png
  • Create the same layout for the Film Tab. The title is Text widget, Rental Duration, Rental Rate, and Replacement Cost are all Input widgets of Number Datatype.
Screenshot_2022-05-02_at_3.37.52_PM.png
  • Drop a Modal widget on the canvas and create a form to add new movies to the collection. Title and Description are Input widgets of Data Type property set to Text and and the remaining are input widgets of Data Type property set to Number. Set this modal to open on the onClick event of the New button on the Film tab
Screenshot_2022-05-02_at_5.30.35_PM.png
  • Lastly, add widgets to the Customer tab as follows. Search is an input widget (Use the search icon under Icon Options and add “Type to search” under Placeholder in the property pane) and customerList is a List widget with two input widgets for Name and Email.
Screenshot_2022-05-02_at_3.38.41_PM.png
  • Drop a Modal widget on the canvas and create a form to add new customers to the database. All are input widgets. Set this modal to open on the onClick event of the New button on the Customer tab
Screenshot_2022-05-02_at_5.35.14_PM.png

Step 5: Configure the Login page

  • The next step is to create a query to retrieve the JWT tokens when the correct credentials are entered in the login form.
  • On the Login page, click on the + icon next to Queries/JS. Select New Blank API
Screenshot_2022-04-28_at_2.04.24_PM.png
  • On the API pane, rename Api1 to get_token and select the POST method. For the URL endpoint, enter the url of the HarperDB instance. You can copy paste this from your instance card on the HarperDB dashboard.
Screenshot_2022-04-28_at_2.20.06_PM.png

Under Headers, ensure that the content-type is application/json

Screenshot_2022-04-28_at_2.21.01_PM.png
  • Under Body tab of the API pane, enter the following in Raw format. Notice here you are passing the username and password entered in the two input widgets on the form.

{
    "operation": "create_authentication_tokens",
    "username": "{{Input1.text}}",
    "password": "{{Input2.text}}"
}


You can enter the username and password on the Login form and then test run the query to view the response. This query returns two tokens - The operation_token which is used to authenticate all HarperDB operations in the Bearer Token Authorization Header and the refresh_token which is used to generate a new operation_token upon expiry.

Screenshot_2022-05-09_at_7.53.55_PM.png
  • Next, we are going to create a JavaScript function that will redirect the user to the main page (Rental Inventory) of the app based on credentials and their roles.
  • Click on the + icon next to Queries/JS. Select New JS Object. Rename JSObject1 to utils and paste the code snippet below in the JavaScript Editor.


export default {
    login: async () => {
        return get_token.run()
        .then(res => storeValue('user', {username: Input1.text, token: res.operation_token}))
        .then(() => navigateTo('App'))
        .catch(e => showAlert(e.message, 'error'))
    }
}


Note: storeValue is an Appsmith function that is used to store data in key-value pairs that can be accessed later anywhere in the application.

  • Bind the JS Object to the onClick event of the Submit button on the login form.
Screenshot_2022-04-28_at_2.44.06_PM.png

Step 6: Write queries and bind data to widgets

  • Create query to fetch data from Rental table in HarperDB and display it in the Table widget on Appsmith (rentalTable)
  • On the Rental Inventory page, click on the + icon next to Queries/JS. Select New Blank API
  • On the API pane, rename Api1 to get_rental and select the POST method. For the URL endpoint, enter the url of the HarperDB instance as shown in Step 5.
  • Under Headers, add the following key-value pairs. We have to pass the Bearer token generated on successful login under Authorization.
  • Under the Body tab of the API pane, enter the following in Raw format. This is an SQL query to fetch the data from the rental table and we are using server-side pagination to display data in the table widget (rentalTable), hence the limit and offset are set in the query.

{
    "operation": "sql",
    "sql": "SELECT * FROM movies.Rental LIMIT {{rentalTable.pageSize}} OFFSET {{(rentalTable.pageNo - 1) * rentalTable.pageSize}}"
}


  • Bind the data from this query under Table Data property of the rentalTable table using the following snippet


{{get_rental.data}}


  • You will notice the table widget populates with results from the query.
  • Next, we will bind data to the widgets on the Update form to the right of the table. When a user click on the table row, the corresponding details should be displayed on the form.
  • For the Rental Update form, set the widgets in the following manner. Notice that we are manually adding the values in the Status select widget so that the status of the rental can be updated on the form.
CleanShot 2022-06-07 at 13.21.52@2x.png
  • Create a query to update the Rental status and Returned date on this form.
  • On the Rental Inventory page, click on the + icon next to Queries/JS. Select New Blank API
  • On the API pane, rename Api1 to update_rental and select the POST method. Repeat the same steps as above for the URL endpoint and the Headers
  • In the Body tab of the API pane, enter the following under Raw tab

{
    "operation": "update",
    "schema": "movies",
    "table": "Rental",
    "records": [
        {
            "id": {{rentalTable.selectedRow.id}},
            "status": {{status.selectedOptionValue}},
                        "return_date": {{returnedDate.selectedDate}}
        }
    ]
}


  • Hook this query on the onClick event of the Submit button on the Update Rental form. On success of this query also run the get_rental query to repopulate the table widget with updated data.
Screenshot_2022-05-02_at_3.26.36_PM.png
  • Create API query named search_customer with headers same as before with the following code snippet under Body tab of the API pane in Raw format


  {
      "operation": "search_by_value",
      "schema": "movies",
      "table": "Customer",
      "search_attribute": "email",
          "limit": 10,
      "search_value": {{`*${rentalCustEmail.filterText}*`}},
      "get_attributes": [
          "id", "email"
      ]
  }


  • Create another API query named search_film with the following code snippet under Body tab of the API pane in Raw format.

  {
      "operation": "search_by_value",
      "schema": "movies",
      "table": "Film",
      "search_attribute": "title",
          "limit": 10,
      "search_value": {{`*${filmTitle.filterText}*`}},
      "get_attributes": [
          "id", "title"
      ]
  }


  • Create a third query name new_rental to add new rental information with the following code snippet under Body tab of the API pane in Raw format.

  {
      "operation": "upsert",
      "schema": "movies",
      "table": "Rental",
      "records": [
          {
                          "film_title": {{filmTitle.selectedOptionValue}},
                          "rental_date": {{moment().utc()}},
                          "staff_id": {{appsmith.store.user.username == 'assistant'? 2: 1}},
                          "customer_email": {{rentalCustEmail.selectedOptionValue}},
                          "status": "borrowed"
          }
      ]
  }


  • Open the modal by clicking the New button on the Rental tab. On Customer select widget write the following code snippet to populate the customer email ids from the search_customer query.
  •  {{search_customer.data.map(c => ({label: c.email, value: c.email}))}}
Screenshot_2022-05-02_at_5.46.54_PM.png
  • Similarly, populate the Film select widget from the search_film with below code snippet.

{{search_film.data.map(m => ({label: m.title, value: m.title}))}}


  • Set the new_rental query to run on the onClick event of the Confirm button on the modal.
Screenshot_2022-05-02_at_6.01.56_PM.png
  • Repeat the above steps for the Film Tab.
  • The Update Form on the Film tab has the following data bindings
CleanShot 2022-06-07 at 13.26.37@2x.png
  • Create an API query named update_film with the following code snippet under Body tab of the API pane in Raw format. Bind this query to run on the onClick action of the onClick event of the Submit button on the Update Film form.


{
    "operation": "update",
    "schema": "movies",
    "table": "Film",
    "records": [
        {
                    "id": {{filmTable.selectedRow.id}},
                    "rental_duration": {{ufDuration.text}},
                    "rental_rate": {{ufRate.text}},
                    "replacement_cost": {{ufCost.text}}
        }
    ]
}



  • Let’s configure the queries to run to add new film data. Create API query named create_film with headers same as before. Add the following code snippet under Body tab of the API pane, enter the following in Raw


  • Open the modal by clicking the New button on the Film tab. Set the create_film query to run on the onClick event of the Confirm button on the modal. Close the modal and rerun the get_film query to refresh the Film table widget on success of the query.
Screenshot_2022-05-02_at_6.24.53_PM.png
  • Finally on the Customer page, write queries for the search input widget (customerSearchInput) and for displaying data in the list(customerList) below.
  • Create query called get_customers with the following under Body tab of the API pane.

{
    "operation": "search_by_value",
    "schema": "movies",
    "table": "Customer",
    "search_attribute": "email",
        "limit": 10,
    "search_value": "{{`*${customerSearchInput.text}*`}}",
    "get_attributes": [
        "id", "email", "first_name", "last_name"
    ]
}


Bind this query to the onTextChanged action of the customerSearchInput box


{{get_customers.run()}}


  • Bind the results of this query {{get_customers.data}} to the Items property of the customerList box
Screenshot_2022-05-02_at_4.41.35_PM.png
  • Let’s configure the queries to run to add new Customer data. Create API query named new_customer with headers same as before. Add the following code snippet under Body tab of the API pane, and enter the following in Raw

  {
      "operation": "upsert",
      "schema": "movie",
      "table": "customer",
      "records": [
          {
                          "first_name": {{ncFNameInput.text}},
                          "last_name": {{ncLNameInput.text}},
                          "email": {{ncEmailInput.text}}
          }
      ]
  }


  • Open the modal by clicking the New button on the Film tab. Set the new_customer query to run on the onClick event of the Confirm button on the modal. Close the modal on success.
Screenshot_2022-05-02_at_6.22.38_PM.png

Step 7: Control access to information based on user roles

  • On the Rental Inventory page, set the Visibility of the Tab widget to show only if the login process returns tokens on success. Toggle the JS button next to the Visible property to enter the following code snippet

{{!!appsmith.store.user.token}}


Screenshot_2022-05-02_at_2.44.29_PM.png
  • The Rental tab is visible to both the Manager and Assistant roles so we leave its visibility as is
  • The Film and Customer tab should only be visible to the Manager
  • The Visible property of the individual tabs can be accessed by clicking on the gear icon next to the tab name
Screenshot_2022-05-02_at_4.51.56_PM.png
  • Add the following snippet of code in the Visible property of both the tabs

{{appsmith.store.user.username !== 'assistant'}}

Step 8: Configure the Logout button

When the user clicks on the Logout button, we will clear the username and token information in the store variable and redirect the user to the Login page. Add the following snippet of code on the onClick event of the Logout button.


{{storeValue('user', {}); navigateTo('Login')}}


Screenshot_2022-05-02_at_4.46.03_PM.png

So far, we've done a great job. The last part of this project will deploy our application. On the dashboard's top right section, click on the 'Deploy' button. You can also deploy this application by connecting to a Git repository. Our application is now deployed!

If you're interested in using a database not listed on our website as an integration, please let us know about it by raising a PR on Github, and we will do our best to include it at the earliest.

Join our growing community on Discord, and follow us on Youtube and Twitter to stay up to date.

A Simple Front-end for Your Redis Datasource
21
February
2022
Resources

A Simple Front-end for Your Redis Datasource

A Simple Front-end for Your Redis Datasource
Vihar Kurama
0
 minutes ↗
#
dashboard
#
databases
#
developer
Resources

Wrapping your mind around how Redis handles data structures and associations like other non-relational database engines can sometimes be challenging. This is particularly true when Redis is compared to more traditional relational databases with quarantined tables containing multiple rows and columns to house data. However, building UI on top and managing multiple queries on the Redis database is also a complicated process.

With Appsmith, it’s possible to create a fully functional and custom frontend in minutes. A vast array of pre-built UI components, that is, widgets, are available to help you build good-looking applications. Connecting data sources with Appsmith takes a few minutes, and you can quickly build tools on top of the database of your choice.

This blog will teach you how to build a frontend that can connect to Redis as a datasource.

Redis is a NoSQL document database built for automatic scaling, high performance, and ease of application development. While the Redis interface has many of the same features as traditional databases, as a NoSQL database, it differs from them in describing relationships between data objects.

Getting Started: Connecting Redis on Appsmith

On Appsmith, it’s pretty straightforward to establish a connection with any datasource, including Redis.

We need to make the connection to the Host Address, Port, and Database password. With this in mind, let’s get started.

  • Create a new account on Appsmith (it’s free!), if you are an existing user, log in to your Appsmith account.
  • Create a new application under the dashboard under your preferred organization.
  • On your Appsmith application, click on the + icon next to Datasources on the left navigation bar under Page1
  • Next, click on the Now, navigate to the Create New tab and choose Redis datasource; you’ll see the following screenshot:
CleanShot 2022-02-10 at 14.07.04@2x.png
  • When using Redis, all these details can be found under the REDIS APP console under project settings.
CleanShot 2022-02-10 at 14.08.45@2x.png
  • Rename the Datasource to Redis CRUD by double-clicking on the existing one.

Here’s what the configuration would look like:

CleanShot 2022-02-10 at 14.09.17@2x.png
  • Next, click on the Test button at the bottom right of the screen. This will help you with understanding whether your configuration is valid or not. If it returns a successful message, hit the Save button to establish a secure connection between Appsmith and Redis.

Creating Key-Values on Redis

We are done with the basic configuration. Now, let’s create some data on Redis and learn a few basic operations on Appsmith.

For the vast majority of data storage with Redis, data will be stored in a simple key/value pair. This can be done using GET and SET commands.

Using GET on Redis

Using this command, we can fetch all the key-value pairs on the Redis datasource. To query this on Appsmith, follow the below steps:

  • Click on the + icon next to the datasources and choose to Create New + from the Redis CRUD datasource
  • Rename the query to getKeys
  • Paste the following command under the Query tab:

Keys *


This will fetch all the keys from the data source; the following is the screenshot:

CleanShot 2022-02-15 at 02.37.11@2x.png

You can use this information to bind it on any widget on Appsmith using the moustache operator; in this case, the data from this query can be accessed by the following snippet:


{{ getKeys.data }}


Using SET on Redis

We may want to store some information from our application on Redis, for example, the title and author of a few of our favourite books.

To do this on Appsmith, you can use Input widgets, to collect the data dynamically or directly create it from the query pane. In the following screenshot, I’ve dragged and dropped two input widgets and a button widget to dynamically take inputs and create key-value pairs on Redis datasource.

CleanShot 2022-02-16 at 14.29.18@2x.png

Next, I've created a new query on the Redis datasource that will dynamically take inputs from the query, for this:

  • Click on the + icon next to the datasources and choose to Create New + from the Redis CRUD datasource
  • Rename the query to createKeys
  • And paste the following command:

SET {{Input1.text}} {{Input2.text}}

Here, we have an insert query that collects all the data from the input widgets we've created. Note that we use the moustache syntax to bind the data from the widgets onto the query body.

With this, we can customize and build any kind of UI on top of the Redis datasource.

If you’re interested in using a database that is not listed on our website as an integration, please let us know about it by raising a PR on Github and we will do our best to include it at the earliest.

Join our growing community on Discord, and follow us on Youtube and Twitter to stay up to date.

A simple front-end for your MariaDB datasource
11
February
2022
Resources

A simple front-end for your MariaDB datasource

A simple front-end for your MariaDB datasource
Vihar Kurama
0
 minutes ↗
#
crud
#
mysql
#
databases
#
guide
Resources

There are many parts to building an app, and designing UI elements can take up most of a developer’s time when building from scratch. However, with Appsmith, it’s possible to create a fully functional and custom frontend in minutes. A vast array of pre-built UI components that is widgets are available to help you build good-looking applications. Connecting data sources with Appsmith takes a few minutes, and you can quickly build tools on top of the database of your choice. For example, you can create admin panels to manage product catalogues, read content data from your database and use that to populate your e-commerce website, and then write more data and update your existing orders in the database. There are so many possibilities!

In this blog, I will teach you how to build a frontend that can connect to MariaDB as a datasource.

MariaDB Server is one of the most popular open-source relational databases. It’s made by the original developers of MySQL and guaranteed to stay open source. It is part of most cloud offerings and the default in most Linux distributions. It is built upon the values of performance, stability, and openness.

Getting Started: Connecting MariaDB on Appsmith

On Appsmith, it’s pretty straightforward to establish a connection with any datasource, including MariaDB; be it on cloud, self-hosted version or local environment.

What we need to make the connection are the endpoint, database name, and user credentials. With this in mind, let’s get started.

  • Create a new account on Appsmith (it’s free!), if you are an existing user, log in to your Appsmith account.
  • Create a new application under the dashboard under your preferred organization.
  • On your Appsmith application, click on the + icon next to Datasources on the left navigation bar under Page1
  • Next, click on the Now, navigate to the Create New tab and choose MySQL datasource, you’ll see the following screenshot:
CleanShot 2022-02-03 at 00.54.54@2x.png
  • When you’re using MariaDB on a hosted service, all these details can be found under the configuration settings. For this example, I’m using a free MariaDB RDS on Amazon Web Services (AWS).
CleanShot 2022-02-03 at 00.56.52@2x.png
  • Rename the Datasource to MariaDB CRUD by double-clicking on the existing one.

Here’s what the configuration would look like:

CleanShot 2022-02-03 at 00.57.58@2x.png
  • Next, click on the Test button at the bottom right of the screen. This will help you with understanding whether your configuration is valid or not. If it returns a successful message, hit the ‘Save’ button to establish a secure connection between Appsmith and MariaDB.

Creating a New Table on MariaDB

We are done with the basic configuration. Now, let’s create a new table and seed it to build a fully customisable CRUD app on MariaDB.

Note: After the connection is established, we can see all the (tables) under the connected datasource.

  • Click on the + icon next to Datasource and click on Create New + from the MYSQL CRUD we’ve just created.
  • This will redirect you to the query pane, where you can write MariaDB SQL queries, now rename the query to seedData, and choose to create from the template and paste the following in the query body:
create table projects(
    project_id int auto_increment,
    project_name varchar(255) not null,
    created_at timestamp default current_timestamp,
    primary key(project_id)
);

INSERT INTO projects (project_name, created_at)
  VALUES ('appsmith', '2022-02-02T19:11:23');


This is a simple SQL query that’ll create a projects table, the idea for us is to build a simple CRUD application, that’ll let us manage open-source projects.

Also, note that we’ve seeded the table with an insert statement where we added the appsmith project.

Alrighty, not that our table is created, let’s get into CRUD.

CRUD on MariaDB with Appsmith

Implementing the Read Operation

First, let’s read our seed data from the database and display it on a beautiful table widget. Follow the below steps:

  • Click on the + icon next to the datasources and choose to Create New + from the MariaDB CRUD datasource.
  • Rename the query to getProjects
  • Copy the following SQL script to query all the Projects from the projects table:
SELECT * FROM projects LIMIT 10;


  • This is a simple query that returns all the projects present in the sample data item. Hit the RUN button to view all the results.
CleanShot 2022-02-03 at 01.07.32@2x.png

We now have our query; let's bind this onto the table widget; for this follow the below steps:

  • Click the + icon next to widgets on the sidebar, search for the table widget, and drag and drop it onto the canvas.
  • Configurations to any widget can be made through the property pane. Click on the table widget on the canvas, you will see the property pane docked to the sidebar on the right. Now, under the Table Data property, use the moustache syntax to bind the query:
{{getProjects.data}}


With this, we should see all the data displayed on the table. The column names can be configured and re-organized under the property pane.

CleanShot 2022-02-03 at 01.08.54@2x.png
Implementing the Create Operation

To add the create operation on MariaDB, let’s make UI.

  • Drag and drop a button widget onto the canvas. Open its property pane, set the onClick property to Open a New Modal, and choose Create New.
  • This will open up a new modal now; let’s drag and drop a few widgets to create a form that we can use to add new projects into our database.
CleanShot 2022-02-03 at 01.10.07@2x.png

Here, we have two input widgets to add to our project. We can configure the default values, labels, and placeholders by selecting the respective property panes. Now, let’s write the query that lets us create a new project on MariaDB.

Follow the steps below:

  • Click on the + icon next to the datasources and choose to Create New + from the MariaDB CRUD datasource
  • Rename the query to createProject
  • Copy the following SQL script:


Here, we have an insert query that collects all the data from the form widgets we've created. Note that we use the moustache syntax to bind the data from the widgets onto the query body.

Lastly, we’ll need to configure the submit button; for this, go back to the modal and set the button’s onClick property to execute a query and choose createProject under the events property:

CleanShot 2022-02-03 at 01.12.51@2x.png
Implementing the Update Operation

The Update operation is quite similar to the create operation. First, let’s build UI by creating a new custom column on the table by clicking on ADD A NEW COLUMN under the columns property.

Now, rename the column to Edit Project, and click on the cog icon next to it, to configure column settings. Under this, we’ll see column-type properties set to a Button type. When clicked, a modal should open up with the necessary fields to update the item.

Now, copy-paste Modal1, and set the onClick property of the Edit Project button to open Modal2. Here, in the form, we can also set the default value to show existing information, to display this, use the selectedRow property from the table widget.

CleanShot 2022-02-03 at 01.14.09@2x.png

Let’s write the Edit query using SQL:

  • Click on the + icon next to the datasources and choose to Create New + from the MariaDB CRUD datasource
  • Rename the query to editProject
  • Copy the following SQL script:
UPDATE projects SET    project_name = {{Input1Copy.text}}'    created_at ='{{Input2Copy.text}}'WHERE  project_id = "{{Table1.selectedRow.project_id}}";


Here, we have an edit query that collects all the data from the form widgets on Modal2. Note that we use the moustache syntax to bind the data from the widgets onto the query body.

We’ll now need to configure the submit button; for this, go back to Modal2 and set the button’s onClick property to execute a query and choose **_editProject_** under the events property:

Implementing the Delete Operation

The delete operation is pretty straightforward with the Table’s selectedRow property; before we dive into it, let’s create a new column on the table and set it to the button. For this:

  • Create a new custom column on the table by clicking on Add a New Column under the columns property.
  • Now, rename this column to ‘Delete Project,’ and click on the cog icon next to it, to configure column settings. Under this, we’ll see column-type properties set to a button type.

Now, let’s write the Delete query using SQL:

  • Click on the + icon next to the datasources and choose the Create New + from the MariaDB CRUD datasource
  • Rename the query to deleteProject
  • Copy the following SQL script:
DELETE FROM projects  WHERE project_id = {{Table1.selectedRow.project_id}};


Set the Delete Project button’s onClick property to run the deleteProject query.

With these four operations configured, you will be able to read and analyze information from your database, edit the data, add or delete information and update records.

If you’re interested in using a database that is not listed on our website as an integration, please let us know about it by raising a PR on Github and we will do our best to include it at the earliest.

Join our growing community on Discord, and follow us on Youtube and Twitter to stay up to date.

A simple Front-end for Your SnowflakeDB Datasource
1
February
2022
Resources

A simple Front-end for Your SnowflakeDB Datasource

A simple Front-end for Your SnowflakeDB Datasource
Vihar Kurama
0
 minutes ↗
#
ui
#
admin
#
databases
#
crud
Resources

Designing UI elements can take up a majority of a developer’s time when building from scratch. However, with Appsmith, it’s possible to create a fully functional and custom frontend in minutes. A vast array of pre-built UI components, that is widgets are available to help you build good looking applications. Connecting data sources with Appsmith takes a few minutes, and you can easily build tools on top of the database of your choice. For example, you can build admin panels to manage product catalogues, read content data from your database and use that to populate your e-commerce website, and then write more data and update your existing orders in the database. The possibilities are countless.

In this blog, I will teach you how to build a frontend that can connect to SnowflakeDB as a datasource.

Snowflake is a cloud-based data warehouse-as-a-cloud-service (SaaS for DB) that requires no hardware or software installation. Snowflake handles the maintenance and tuning of cloud infrastructure. It is based on a new SQL database engine with unique features and advantages over a more traditional data warehousing technology approach.

Getting Started: Connecting Snowflake on Appsmith

On Appsmith, it’s pretty straightforward to establish a connection with any datasource, including SnowflakeDB; be it on cloud, self-hosted version or local environment.

What we need to make the connection are the endpoint, database name, and user credentials. With this in mind, let’s get started.

  • Create a new account on Appsmith (it’s free!), if you are an existing user, log in to your Appsmith account.
  • Create a new application under the dashboard under your preferred organization.
  • On your Appsmith application, click on the + icon next to Datasources on the left navigation bar under Page1
  • Next, click on the Now, navigate to the Create New tab and choose SnowflakeDB datasource, you’ll see the following screenshot:
CleanShot 2022-01-26 at 23.05.25@2x.png
  • When you’re using Snowflake cloud, all these details can be found under the SnowflakeCloud settings:
CleanShot 2022-01-26 at 23.07.29@2x.png
  • Rename the Datasource to SnowflakeDB CRUD by double-clicking on the existing one.

Here’s what the configuration would look like:

CleanShot 2022-01-26 at 23.09.58@2x.png
  • Next, click on the Test button at the bottom right of the screen. This will help you with understanding whether your configuration is valid or not. If it returns a successful message, hit the ‘Save’ button to establish a secure connection between Appsmith and SnowflakeDB.

We are done with the basic configuration. Now, let’s use the default database from SnowflakeDB to build a fully customisable CRUD app.

Note: After the connection is established, we can see all the sample data (tables) under the connected datasource.

CleanShot 2022-01-26 at 23.13.45@2x.png

Now that we have the sample data, in the next section, let’s build a fully-fledged CRUD application (on the customer table) on top of our SnowflakeDB using Appsmith.

CRUD on SnowflakeDB with Appsmith

Implementing the Read Operation

First, let’s read our seed data from the database and display it on a beautiful table widget. Follow the below steps:

  • Click on the + icon next to the datasources and choose to Create New + from the SnowflakeDB CRUD datasource
  • Rename the query to getCustomers
  • Copy the following SQL script to query all the Customers from the CUSTOMER table:
SELECT * FROM TPCDS_SF100TCL.CUSTOMER LIMIT 10;


  • This is a simple query that returns all the customers present in the sample data item. Hit the RUN button to view all the results.
CleanShot 2022-01-26 at 23.17.06@2x.png

We now have our query; let's bind this onto the table widget; for this follow the below steps:

  • Click the + icon next to widgets on the sidebar, search for the table widget, and drag and drop it onto the canvas.
  • Configurations to any widget can be made through the property pane. Click on the table widget on the canvas, you will see the property pane docked to the sidebar on the right. Now, under the Table Data property, use the moustache syntax to bind the query:
{{getCustomers.data}}


With this, we should see all the data displayed on the table. The column names can be configured and re-organized under the property pane.

CleanShot 2022-01-26 at 23.19.19@2x.png

Implementing the Create Operation

To add the create operation on SnowflakeDB, let’s make UI.

Drag and drop a button widget onto the canvas. Open its property pane, set the onClick property to Open a New Modal, and choose Create New.

This will open up a new modal now; let’s drag and drop a few widgets to create a form that we can use to add new customers into our database.

CleanShot 2022-01-26 at 23.36.01@2x.png

Here, we have five input widgets to add our customers. We can configure the default values, labels, and placeholders by selecting the respective property panes. Now, let’s write the query that lets us create a new customer on SnowflakeDB.

Follow the steps below:

  • Click on the + icon next to the datasources and choose to Create New + from the SnowflakeDB CRUD datasource
  • Rename the query to insertCustomer
  • Copy the following SQL script:

INSERT INTO TPCDS_SF100TCL.CUSTOMER (C_FIRST_NAME,C_LAST_NAME, C_BIRTH_YEAR, C_BIRTH_COUNTRY, C_EMAIL_ADDRESS) VALUES ({{Input1.text}}, {{Input2.text}}, {{Input3.text}}, {{Input4.text}}, {{Input5.text}});


Here, we have an insert query that collects all the data from the form widgets we've created. Note that we use the moustache syntax to bind the data from the widgets onto the query body.

Lastly, we’ll need to configure the submit button; for this, go back to the modal and set the button’s onClick property to execute a query and choose insertCustomer under the events property:

CleanShot 2022-01-26 at 23.54.51@2x.png

Implementing the Update Operation

The Update operation is quite similar to the create operation. First, let’s build UI by creating a new custom column on the table by clicking on ADD A NEW COLUMN under the columns property.

Now, rename the column to Edit Customer, and click on the cog icon next to it, to configure column settings. Under this, we’ll see column-type properties set to a Button type. When clicked, a modal should open up with the necessary fields to update the item.

Now, copy-paste Modal1, and set the onClick property of the Edit Customer button to open Modal2. Here, in the form, we can also set the default value to show existing information, to display this, use the selectedRow property from the table widget.

CleanShot 2022-01-27 at 00.44.39@2x.png

Let’s write the Edit query using SQL:

  • Click on the + icon next to the datasources and choose to Create New + from the SnowflakeDB CRUD datasource
  • Rename the query to editCustomer
  • Copy the following SQL script:
UPDATE TPCDS_SF100TCL.CUSTOMER SET    C_FIRST_NAME = "{{Input1Copy.text}}",    C_LAST_NAME = "{{Input2Copy.text}}",    C_BIRTH_YEAR = "{{Input3Copy.text}}",    C_BIRTH_COUNTRY = "{{Input4Copy.text}}",    C_EMAIL_ADDRESS = "{{Input5Copy.text}}",WHERE C_CUSTOMER_ID = {{Table1.selectedRow.C_CUSTOMER_ID}};


Here, we have an edit query that collects all the data from the form widgets on Modal2. Note that we use the moustache syntax to bind the data from the widgets onto the query body.

We’ll now need to configure the submit button; for this, go back to Modal2 and set the button’s onClick property to execute a query and choose **_editCustomer_** under the events property.

Implementing the Delete Operation

The delete operation is pretty straightforward with the Table’s selectedRow property; before we dive into it, let’s create a new column on the table and set it to the button. For this:

  • Create a new custom column on the table by clicking on Add a New Column under the columns property.
  • Now, rename this column to ‘Delete Customer,’ and click on the cog icon next to it, to configure column settings. Under this, we’ll see column-type properties set to a button type.

Now, let’s write the Delete query using SQL:

  • Click on the + icon next to the datasources and choose the Create New + from the SnowflakeDB CRUD datasource
  • Rename the query to deleteCharacter
  • Copy the following SQL script:

DELETE FROM TPCDS_SF100TCL.CUSTOMER_DEMOGRAPHICS  WHERE C_CUSTOMER_ID = {{Table1.selectedRow.C_CUSTOMER_ID}};


Set the Delete Character button’s onClick property to run the deleteCharacter query.

With these four operations configured, you will be able to read and analyze information from your database, edit the data, add or delete information and update records.


We’ve got extensive documentation on the SnowflakeDB datasource integration!

If you’re interested in using a database that is not listed on our website as an integration, please let us know about it by raising a PR on Github and we will do our best to include it at the earliest.

Join our growing community on Discord, and follow us on Youtube and Twitter to stay up to date.

A Simple Front-end for Your ArangoDB Datasource
27
January
2022
Resources

A Simple Front-end for Your ArangoDB Datasource

A Simple Front-end for Your ArangoDB Datasource
Vihar Kurama
0
 minutes ↗
#
crud
#
dashboard
#
ui
#
databases
#
graph-database
Resources

A major pain point around building apps is designing the UI elements. Fortunately, with Appsmith, you create a custom frontend in minutes. Connecting datasources with Appsmith takes a few minutes, and you can easily build tools on top of the database of your choice. For example, you can build admin panels to manage product catalogs, read content data from your database and use that to populate your e-commerce website, and then write more data and update your existing orders in the database. The possibilities are countless.

In this blog, I will teach you how to build a frontend that can connect to ArangoDB as a datasource. 

ArangoDB is a free and open-source native multi-model database system developed by ArangoDB GmbH. The database system supports three data models with one database core and a unified query language AQL. Being multi-model, ArangoDB allows you to run ad-hoc queries on data stored in different models.

Getting-Started: Connecting ArangoDB on Appsmith

On Appsmith, it’s pretty straightforward to establish a connection with any datasource, including ArangoDB; be it on cloud, self-hosted version or local environment. 

What we need to make the connection are the endpoint, database name, and user credentials. With this in mind, let’s get started. 

  • Create a new account on Appsmith (it’s free!), if you are an existing user, log in to your Appsmith account.
  • Create a new application under the dashboard under your preferred organization.
  • On your Appsmith application, click on the + icon next to Datasources on the left navigation bar under Page1
  • Now, navigate to the Create New tab and choose ArangoDB datasource, you’ll see the following screenshot:


  • When you’re using ArangoDB cloud or a self-hosted instance, all these details can be found under the instance settings:


  • Ensure that the SSL mode is enabled so as to establish a secure connection.
  • Rename the Datasource to ArangoDB CRUD, by double clicking on the existing one.

Here’s how the configuration would look like: 


  • Next, click on the Test button at the bottom right of the screen. This will help you with understanding whether your configuration is valid or not. If it returns a successful message, hit the ‘Save’ button to establish a secure connection between Appsmith and ArangoDB.

Adding Seed Data on ArangoDB

We are done with basic configuration. Now, let’s create a collection on ArangoDb and push some data from Appsmith to the database. For this, you’ll need to open the ArangoDB endpoint and use the graphical user interface (GUI).


Let’s name the collection as ‘Characters’ and set the type as ‘Document’ 

Now let’s seed the collection with some data on Appsmith. 

For this, follow the steps below:

  • Click on the + icon next to Datasource and click on Create New + from the ArangoDB CRUD we’ve just created.
  • This will redirect you to the query pane, where you can write ArangoDB AQL queries, now rename the query to seedData, and choose to create from the template and paste the following in the query body:
LET data = [
    { "name": "Robert", "surname": "Baratheon", "alive": false, "traits": ["A","H","C"] },
    { "name": "Jaime", "surname": "Lannister", "alive": true, "age": 36, "traits": ["A","F","B"] },
    { "name": "Catelyn", "surname": "Stark", "alive": false, "age": 40, "traits": ["D","H","C"] },
    { "name": "Cersei", "surname": "Lannister", "alive": true, "age": 36, "traits": ["H","E","F"] },
    { "name": "Daenerys", "surname": "Targaryen", "alive": true, "age": 16, "traits": ["D","H","C"] },
    { "name": "Stannis", "surname": "Baratheon", "alive": false, "traits": ["H","O","P","M"] },
    { "name": "Melisandre", "alive": true, "traits": ["G","E","H"] },
    { "name": "Margaery", "surname": "Tyrell", "alive": false, "traits": ["M","D","B"] },
    { "name": "Jeor", "surname": "Mormont", "alive": false, "traits": ["C","H","M","P"]}}
]
FOR d IN data
    INSERT d INTO Characters


  • Lastly hit the RUN button on the top-right on the query. This will add some seed characters to our database. 

Now that we have our seed data, in the next section, let’s build a fully-fledged CRUD application on top of our ArangoDB using Appsmith. 

CRUD on ArangoDB with Appsmith

Implementing the Read Operation

First, let’s read our seed data from the database and display it on a beautiful table widget. Follow the below steps:

  • Click on the + icon next to the datasources and choose to Create New + from the ArangoDB CRUD datasource
  • Rename the query to readCharacters
  • Copy the following AQL script to query all the items from the Characters collection:
FOR c IN Characters
    RETURN c
  • This is a simple for-loop that iterates on the collection and returns every item. Hit the RUN button to view all the results.


We now have our query; let's bind this onto the table widget; for this follow the below steps:


  • Click the + icon next to widgets on the sidebar, search for the table widget, and drag and drop it onto the canvas.
  • Configurations to any widget can be made through the property pane. Click on the table widget on the canvas, you will see the property pane docked to the sidebar on the right. Now, under the Table Data property, use the moustache syntax to bind the query:
{{readCharacters.data}}


With this, we should see all the data displayed on the table. The column names can be configured and re-organized under the property pane. 

Implementing the Create Operation

To add the create operation on ArangoDB, let’s make UI. 

  • Drag and drop a button widget onto the canvas. Open its property pane, set the onClick property to Open a New Modal, and choose Create New.
  • This will open up a new modal now; let’s drag and drop a few widgets to create a form that we can use to create new characters in our database.


Here, we have three input widgets, a checkbox, and a multi-select widget to add our characters. We can configure the default values, labels and placeholders by selecting the respective property panes. Now, let’s write the query that lets us create a new item on ArangoDB. 

Follow the steps below:

  • Click on the + icon next to the datasources and choose to Create New + from the ArangoDB CRUD datasource
  • Rename the query to insertCharacter
  • Copy the following AQL script:
INSERT {
    "name": "{{Input1.text}}",
    "surname": "{{Input2.text}}",
    "alive": {{Checkbox1.isChecked}},
    "age": {{Input3.text}},
    "traits": {{MultiSelect1.selectedOptionValues}}
} INTO Characters


Here, we have an insert query that collects all the data from the form widgets we've created. Note that we use the moustache syntax to bind the data from the widgets onto the query body.

Lastly, we’ll need to configure the submit button, for this, go back to the modal and set the button’s onClick property to execute a query and choose insertCharacter under the events property:


Implementing the Update Operation

The Update operation is quite similar to the create operation.

  • Let’s first build UI by creating a new custom column on the table by clicking on ADD A NEW COLUMN under columns property.
  • Now, rename the column to Edit Character, and click on the cog icon next to it, to configure column settings. Under this, we’ll see column-type properties set to a Button type. When clicked, a modal should open up with the necessary fields to be able to update the item. 
  • Now, copy paste Modal1, and set the onClick property of the Edit Character button to open Modal2. Here, in the form, we can also set the default value to show existing information, to display this, use the selectedRow property from the table widget.


Let’s write the Edit query using AQL:

  • Click on the + icon next to the datasources and choose to Create New + from the ArangoDB CRUD datasource
  • Rename the query to editCharacter
  • Copy the following AQL script:
REPLACE "{{Table1.selectedRow._key}}" WITH {
    "name": "{{Input1Copy.text}}",
    "surname": "{{Input2Copy.text}}",
    "alive": {{Checkbox1Copy.isChecked}},
    "age": {{Input3Copy.text}},
    "traits": {{MultiSelect1Copy.selectedOptionValues}}
} IN Characters


Here, we have an edit query that collects all the data from the form widgets on Modal2. Note that we use the moustache syntax to bind the data from the widgets onto the query body.

Next, configure the submit button, for this, go back to Modal2 and set the button’s onClick property to execute a query and choose editCharacter under the events property.


Implementing the Delete Operation

The delete operation is pretty straightforward with the Table’s selectedRow property, before we dive into it, let’s create a new column on the table and set it to the button. For this:

  • Create a new custom column on the table by clicking on Add a New Column under the columns property.
  • Now, rename this column to Delete Character, and click on the cog icon next to it, to configure column settings. Under this, we’ll see column-type properties set to a button type.

Now, let’s write the Delete query using AQL:

  • Click on the + icon next to the datasources and choose Create New + from the ArangoDB CRUD datasource
  • Rename the query to deleteCharacter
  • Copy the following AQL script:


REMOVE "{{Table1.selectedRow._key}}" IN Characters


Set the Delete Character button’s onClick property to run the deleteCharacter query. 


With these four operations configured, you will be able to read and analyze information from your database, edit the information, add or delete information and update records.

We’ve got extensive documentation on the ArangoDB datasource integration, along with a video explainer

If you’re interested in using a database that is not listed on our website as an integration, please let us know about it by raising a PR on Github and we will do our best to include it at the earliest. 
Join our growing community on Discord, and follow us on Youtube and Twitter to stay up to date.

Using the Notion API to Build a Content Management System
21
May
2021
Tutorial

Using the Notion API to Build a Content Management System

Using the Notion API to Build a Content Management System
Vihar Kurama
0
 minutes ↗
#
apis
#
databases
#
ui
#
applications
#
open-source
Tutorial

At Appsmith, we use Notion to manage our content calendar. We also work with a few external agencies and freelancers for some of our content. It is impossible to create granular access control and develop a workflow on Notion to run the process smoothly and thus, as soon as Notion released their API, we decided to build an application that helps us manage our entire content management in one place while giving our collaborators the necessary access only.

Our application uses our Notion (mock) Table as a data source and lets you plant, submit and edit articles on the application, while at the same time has a provision for integrating with an email service of your choice (we use SendGrid here) to send reminder emails or updates to people in the project.

In this tutorial, we’ll build an essential Content Management System, which will let you:

  • Submit new content idea submissions from authors
  • Set deadlines for submissions
  • Follow up with authors directly with their Emails
  • Organise all data on a Notion page

Check out the live demo here. Here's a screenshot of how the app looks like:

Notion App Screenshot.png
Appsmith is an open-source framework that lets developers build dashboards, workflows, and CRUD apps with only the necessary code. You can connect to any API or databases like MongoDB, PostgreSQL, or MYSQL and get access to multiple widgets, including charts, tables and forms, for building a UI fast.

Let’s dive right in!

Configuring the Notion API

Notion API lets us connect to Notion Pages and Databases. Now, let's look at how we can set up the API, gather all the required keys and connect to Appsmith.

  • Firstly, choose the workspace you want to work with on your Notion. If you want to test it, feel free to create a new workspace.
  • Now, open the workspace setting by clicking on the Settings and Members option on the left navigation bar. We should see a new modal with all the settings.
  • Find the select the Integrations option under Workspace options in the settings modal. This will redirect to the integrations tab.
  • Next, choose the Develop your integration option. Now we'll be redirected to our integration page.
Image One.png
  • On the integrations page, select the New integration option and add a name and associated workspace you want to work with and hit submit. This will create us a new secret key!
Image Two.png
Image Three.png
  • Now head back to our workspace, choose a specific page and click on the share option (we can find this on the top-right corner)
  • Next, hit the invite button, a new modal opens, here we can find our created integration. Select the integration and confirm the invite.
  • With this, we should be able to add an integration to a Notion page; also, make sure to save your integration key; we’ll have to use it for using the Notion API
In Notion, integration has a secret key; when added to a workspace, we could perform all the actions (create, read, update). We'll use the secret key of the integration to connect with third-party applications.

Setting up a Notion Page

To work with the Notion API, firstly, let’s create a database for our content calendar.

  • On your page, add a new table with the following fields that are required for our content manager:
Title: Text Field
Description: Text Field
Author: Text Field
Category: Text Field
Status: Dropdown Field
Notes: Text Field
Deadline: Date Field
Author Email: EMail Field

You can also use this Notion page of ours, which can be used as a mock database. You can click on this link duplicate to one of your workspaces. Also, make sure to add the integration to this page to use the Notion API.

Now that we have our page and integration-ready, we’ll start building an App on Appsmith.

Querying Data from Notion API

In this section, we’ll be querying data from Notion API. Follow the below steps:

  • Firstly, we should have an Appsmith account; if not, sign-up here (it's free)! Next, create a new application under an organisation by clicking on the Create New button.
  • We’ll now find Widgets, APIs, and DB Queries on the left navigation; we can use these features to build our application. We’ll start by creating a Notion API.
  • Click on the + icon next to the APIs and click on Create new and name the API as query_data_from_database
  • Now, add the following in the URL for the API:
https://api.notion.com/v1/databases//query
  • Here, db-id is the database id for a notion page. To find this, we’ll have to open our Notion Table as a page and pick the database id from the URL.
  • Following is an example URL showing the id of a Notion page.
https://www.notion.so/myworkspace/a8aec43384f447ed84390e8e42c2e089?v=...
                                  |--------- Database ID --------|
  • Next, add the following keys under the Headers tab.
	Authorization: 
	Notion-Version: 2021-05-13
	Content-type: application/json

Below is a screenshot of how the configuration looks like:

Image Four.png
  • Lastly, hit the RUN button, and we can find all the data from our page in the Response Body pane.

Awesome, we now have the response from the Notion API; let’s bind this onto a List Widget.

Binding Notion APIs onto Appsmith

Now that we have an API containing data from the Notion Table let’s bind this onto a list widget to display all the details. Follow the below steps:

  • Select Page1 on the left navigation and click on the + icon next to the widgets section. Now we should see different UI widgets that we can use to build UI.
  • *Based on your preference, you can either choose a table widget or a list widget to bind the data from Notion API. In this example, we’ll be using a List widget.
  • Now drag and drop the list widget onto the canvas; we should see list items filled with Pokemon information. To inspect these, we can click on the cog icon on the top of the list widget and check out the data in the Items property. You can find more information from the docs here.
  • Now, we’ll be using the moustache syntax to write JS in Appsmith, replace the Items property on the list widget with the following code snippet:
In Appsmith, you can access the API’s anywhere inside the moustache syntax using the API name.
{
{
	query_data_from_database.data.results.map(
    	(item) => {
        	return (item.properties)
    	})
}
}

Here, we’re using the query_data_from_database and mapping it to return item properties. This how the response looks like:

Image Five.png

Awesome, now let’s add some widgets onto the list widget and show the details from the Notion API. Drag and drop six text widgets and set its Text property to the following:

- Title
- Description
- Status
- Author
- Email
- Deadline

Add six more text widgets next to them, where we’ll be binding their corresponding values from the Notion API.

Now in the text widget next to the Title text widget, set the Text property to the following:

{{ currentItem.Title.title[0].text.content }}

Here the currentItem corresponds to a unique item in the list widget, and next, we access the Title property from the Items on our list and parse the JSON. Below is the screenshot of how the response looks like:

Image Six.png

Great, we can now see our Titles from the Notion API on items of our list widget; similarly, let’s set the Text property of other text widgets to show the contents from the API:

  • Description text widget set the Text property to:
{{currentItem.Description.rich_text[0].text.content}}
  • Status text widget, set the Text property to:
{{currentItem.Description.rich_text[0].text.content}}
  • Author text widget, set the Text property to:
{{currentItem.Author.rich_text[0].text.content}}
  • EMail text widget set, the Text property to:
{{currentItem["Author Email"].email || "Not Added"}}
  • Deadline text widget set, the Text property to:
{{currentItem.Deadline.date.start}}
  • Category text widget set the Text property to:
{{currentItem.Category.rich_text.map(row => {return row.plain_text})[0]}}
The code for parsing through items changes based on the API response from the Notion API.

We’ve added some additional styles and added a button widget at the end so that admins can send Emails to the authors directly from Appsmith. Below is how the App looks like after we parse all the data from the Notion Table onto the Appsmith List widget:

Notion App Screenshot.png

We can also add a refresh button to the query_data_from_database query. For this, drag and drop the Button widget onto the canvas, set the Label property to Refresh. Next, open the onClick property, click the Call an API option, and select the query_data_from_database API. Now, whenever we click the refresh button, we should see all the new data updated on the Notion page.

Adding an Item into Notion Database

For our content calendar, we can list all the details from the Notion Table. Now let’s add a feature to add new items on the table from Appsmith. Follow the below steps.

  • First, let’s create a new page named “Author Submission” now drag and drop a form widget onto the canvas.
  • Inside the Form widget, let’s add some text widgets and input widgets from which the authors should submit their new content ideas.
  • This is how our form should look like:
Image Eight.png

Following are the fields we’ve created for reference:

Now, let’s name these widgets (so that we can use them to refer in the POST API method) to the following:

Title - titleInput
Description - descriptionInput
Author Name - authorInput
Email - emailInput
Category - categoryInput
Submission Date - dateInput

Let’s create a new API that’ll add a new value to the Notion page when making submissions from the Appsmith form. Follow the below steps:

  • First, create a new API under the Author Submission page and name it as add_an_item_to_database
  • Next, add the following keys under the Headers tab.
Authorization: 
Notion-Version: 2021-05-13
Content-type: application/json
  • Now in the body tab, paste the following code:
	"parent": { "database_id": "" },
 "properties": {
           "Author": {
          "type": "rich_text",
          "rich_text": [
            {
              "type": "text",
              "text": {
                "content": "{{authorInput.text}}",
                "link": null
              }
            }
          ]
        },
        "Author Email": {
          "type": "email",
          "email": "{{emailInput.text}}"
        },
        "Category": {
          "type": "rich_text",
          "rich_text": [
            {
              "type": "text",
              "text": {
                "content": "{{categoryInput.text}}",
                "link": null
              }
            }
          ]
        },
        "Status": {
          "type": "select",
          "select": {
            "name": "Not Started",
    "color": "blue"
          }
        },
        "Description": {
          "type": "rich_text",
          "rich_text": [
            {
              "type": "text",
              "text": {
                "content": "{{descriptionInput.text}}",
                "link": null
              }
            }
          ]
        },
        "Deadline": {
          "type": "date",
          "date": {
            "start": "{{dateInput.selectedDate}}",
            "end": null
          }
        },
        "Title": {
          "type": "title",
          "title": [
            {
              "type": "text",
              "text": {
                "content": "{{titleInput.text}}",
                "link": null
              }
            }
          ]
        }
      }
  }

This is how Notion API allows us to add new items to a database. Here we'll also have to add the database id in the place of <db-id>. If we observe the body inside each property's content field, we added a moustache operation from which we'll take the input from the Appsmith input forms.

Now open the Submit button’s property pane, update the onClick property to Call an API, and choose add_an_item_to_database API. With this, we should add new data to the Notion table using the Appsmith form!

Send Grid Email Integration

Our content manager now shows all the data and also has a feature to add new content ideas. Now let’s add an email integration to communicate with Authors via Appsmith. We’ll be using Sendgrid to achieve this.

Creating a Modal for sending emails:

  • First, create a new modal when the Send Mail button is clicked on the list item. We can do this by setting the onClick property to Open Modal and selecting Create New.
  • Set the modal type to Form modal by toggle the option in the property pane.
  • Inside the Modal widget, let’s add some text widgets and input widgets from which we should send Emails.
  • Following are the fields we’ve created for reference:
Sending To: Input Widget
Email subject: Input Widget
Email content: Rich Text Editor Widget
  • Now, let’s name these widgets (so that we can use them to refer in the POST API method) to the following:
Sending To: emailInput
Email subject: subjectInput
Email content: contentInput

Below is a screenshot of how the Modal should look like:

Image Nine.png

Configuring the SendGrid API:

  • Firstly, we should have a SendGrid account, and if you don’t have one, you can create a free account here.
  • Next, navigate to the Integration Guide by clicking on the Email API on the left navigation pane.
  • Choose the Web API and choose the cURL language. Now you’ll be redirected to the verification tab.
  • On the verification, tab click on the Create API by giving it a name. With this, we should have our SendGrid API key.
  • Next, open Appsmith and create a new API under Page1, name it as send_email. Set the request type to POST and add the following under the URL form:

https://api.sendgrid.com/v3/mail/send

Authorization : Bearer 
Content-Type : application/json
  • Lastly, set the body to the following:
{"personalizations": [{"to": [{"email": "{{emailInput.text}}"}]}],"from": {"email": "test@example.com"},"subject": "{{subjectInput.text}}","content": [{"type": "text/plain", "value": "{{contentInput.text}}"}]}

This is the default configuration from SendGrid that lets us send Emails, but here we are dynamically passing the to-email, from-email, subject and content field from the widget’s we’ve created on the Modal. Now, set the onClick property of the form to Call an API and choose the send_email query. With this, we should be able to send Emails from Appsmith successfully!

Conclusion

Deploy your application on the cloud and share it with others, and that's it. We're done!

We've seen how easy it is to build an application on Appsmith, specifically a CMS with Notion as a backend. This guide covered how to create a CRM and connect it to a Notion API and how to create and read data. You learned how to build interactive pages, work with widgets, and customize them for your purposes.

We have made a slightly more robust application public here; try it out and let us know what you think. You can also check the live demo of our app here.

The Complete Table Widget: Building Complex Tables with Appsmith
25
May
2021
Tutorial

The Complete Table Widget: Building Complex Tables with Appsmith

The Complete Table Widget: Building Complex Tables with Appsmith
Vihar Kurama
0
 minutes ↗
#
html5
#
css
#
ui
#
apis
#
databases
Tutorial

By: Veronica Stork

What Are Web Tables?

Tables are an important component of HTML that allows us to display data in rows and columns. There are several different ways to create tables.

If you don’t have many values to display, you can create your table using pure HTML. This is fairly straightforward but quite cumbersome if you have many values or if your data may change.

Alternatively, a table can be used to display information from a database programmatically or an API. Just fetch your data, then use JavaScript to append table rows for each datapoint retrieved.

If you need more interactivity, you can write JavaScript functions that implement more advanced features such as search and filter. This adds more functionality to your app but also more code and complexity.

When you build your table using these methods, you will also need to style and structure it in a readable way for the user. This is not difficult, but it can be tedious. Ultimately, the minutiae of correctly structuring and displaying your data will eat up time that could be better spent on the underlying logic of your app.

Appsmith to the Rescue

Appsmith is an open-source framework that lets developers build dashboards, workflows, and CRUD apps with only the necessary code. You can connect to any API or databases like MongoDB, PostgreSQL, or MYSQL and get access to multiple widgets, including charts, tables and forms, for building a UI fast.

Using Appsmith allows you to focus on the logic behind your tables instead of fiddling around creating the UI, connecting to the API, and reinventing the wheel in the form of filter and search functions. You can also see your table working in real-time as you build, a feature that is super handy when you’re trying to develop something quickly.

Creating a Table in the Appsmith

  • Firstly, you should have an Appsmith account; if not, sign-up here (it's free)!
  • Next, create a new application under an organisation by clicking on the Create New button.
  • Now, to create a table, first, click the + icon next to the widgets directory in the sidebar.
  • Then, drag the table widget to the canvas. Once it is on the canvas, the table will be pre-populated with dummy data, and the property pane will be open to the right side of the widget.
New table in Appsmith with property pane open on the right side
  • You can rename the table to something descriptive by clicking on the default name in the property pane (Table1 in the above example), then entering your data.

Your data should be an array of objects, with each object representing a row in the table. The object keys represent column names. You can populate your table with data in a few different ways: by typing the data directly into the property pane manually, by importing it from a data source using queries, or by connecting to an API.

Connecting to an API

To connect to an API, hover over the table data field of the property pane, and click on the lightning bolt icon. You will be presented with several options. Choose Use data from an API, then create new API. This will bring up a screen where you can paste the API URL in if you want to know more about creating a new API check out the docs.

Below is a screenshot showing the Table Data property and how we can connect to APIs:

Appsmith table property pane with data options displayed

Features

Appsmith tables include features relevant to some of the most common use cases, including pagination, filters, downloading, searching, actions, and drilling into individual rows of data. You can use JavaScript to customise these features based on different use-cases.

Pagination

Pagination is often required to display large amounts of data. If you have more data than rows in your table, you will need some way of splitting the data into separate pages. This can be accomplished using either offset-based or key-based pagination.

Offset pagination is based on the table’s page number and page size. For example, if you have a table with ten rows, your app will use ten and the current page number to determine which data to retrieve next. Let’s walk through how to set up offset pagination.

Enable server-side pagination in the property pane of your table. Make a query or API call that utilizes the pageSize and pageNo variables.

Query of users database that uses server-side pagination

Run that query or API call from the table onPageChange.

Appsmith table property pane with the query being run onPageChange

Key-based pagination uses a value returned by the API to make the next page’s API call. If the API’s response includes URLs for the next and previous pages, you can enter those on the API settings.

API settings with key-based pagination enabled and previous and next pages set

Filters

Client-side filtering is included in Appsmith out of the box, but implementing server-side filtering requires a few steps. First, create a dropdown widget and fill it with filter values. Then call that API or Query onOptionChange from the dropdown.

Property pane for dropdown widget populated with filter values
Query that returns filtered results

In the above example, we are filtering based on the user status.

Searching

As with filtering, client-side searching is included in Appsmith out of the box. To implement server-side searching, you must call the API or query onSearchTextChange from the table and pass in the search box contents, represented by the searchText property.

Query for server-side searching that uses searchText property

Downloads

Downloading also works with no additional setup: users can click the included download button on a table to download all of its data. You can also bind the download function to an action of your choice and set it to download whatever portion of the data you want.

Property panel with onRowSelected set to download the current row’s data

In the above example, you choose the option to download the currently selected row’s data, but you can specify which portion of the data you want and give the file whatever name you want.

Actions

Actions in Appsmith represent ways in which your users can interact with the app and describe what you want to happen when they do. You can set the app to download data (as you saw above), call an API, open a modal, and more. All of the available options are listed when you modify action in the property pane.

Actions include the following:

  • onRowSelected describes what happens when a user selects a row.
  • onPageChange describes what happens when a user navigates to the next page of data.
  • onSearchTextChange describes what happens when a user types in the search bar.

A table is set to display Modal1 when onRowSelected is triggered in the example below.

Property pane with onRowSelected option set to show a modal

Drilling into a single row of data

Drilling into a single row of data is as simple as referencing the currently selected row using the selectedRow property. For example, {{Table1.selectedRow}} targets the currently selected row of our table. You can add a column name to narrow down the targeted data further. In the following example, {{Table1.selectedRow.name}} targets the name of the user represented by the currently selected row.

Text area using the `selectedRow` property to display the value of the name column from the currently selected row
Modal displaying name obtained from`selectedRow property

Conclusion

Making tables can be time-consuming and finicky, but with Appsmith’s rich table creation tool, you can focus on bigger structural and logical issues while letting it take care of all the nuts and bolts. Writing less code might seem strange at first, but you’ll quickly realize the benefits of working smarter, not harder.

I hope you enjoyed this introduction to Appsmith’s table features, which is just a taste of the whole suite of tools and features Appsmith can provide.

You can check some of the example app's that use the Table widget here. If you found this tutorial helpful, please consider giving us a star on Github.

Introducing the All-new SnowflakeDB Integration on Appsmith
29
July
2021
Announcement

Introducing the All-new SnowflakeDB Integration on Appsmith

Introducing the All-new SnowflakeDB Integration on Appsmith
Vihar Kurama
0
 minutes ↗
#
open-source
#
community
#
databases
#
ui
Announcement

Today, we are super proud to announce Snowflake DB integrations on Appsmith in our latest release (>v1.5.5). It was one of the highly requested integrations by our community, and we've shipped this right on time. This blog is an attempt to share with you our motivation and experience in building this integration.

We first received a request for this SnowflakeDB integration on our GitHub Issues. This was posted by one of our community members in the last week of April. We then researched SnowflakeDB's architecture and data sharing capabilities and decided to supercharge it with Appsmith for querying and building applications.

CleanShot 2021-06-29 at 07.13.15@2x.png

On Appsmith, it's super fast and easy to build new Integrations. This is because we've architectured each integration as an individual module; with this, it's super handy for us and contributors to build, customise and organise any new integration.

To learn more about Appsmith's backend architecture, we recommend you watch the session about integrations by our co-founders here.

Considering the amount of interest this integration had gathered within our community, we decided to fast-track the work on Snowflake DB Integration. It barely took us two weeks to build, test and ship it to production. Super C❄️❄️L, right?

CleanShot 2021-06-29 at 05.43.57@2x.png

Our existing integrations helped us organise most of the code, and with Snowflake DB's JDBC driver support, we were able to establish a connection with our backend quickly. We've made a few easy-to-get-started decisions on the UX front, but we'll enhance these based on feedback from our community.

Connecting Snowflake DB on Appsmith

It is super easy to connect databases with our integrations on Appsmith. Now, let’s look at how we can connect SnowflakeDB on Appsmith to build applications.

Step #1 Find Snowflake DB Integration under Datasources

CleanShot 2021-06-29 at 06.50.21@2x.png

Step #2 Add connection details for SnowflakeDB and save a data source

CleanShot 2021-06-29 at 06.51.44@2x.png

Step #3 Create Queries and Build UI

Awesome, in just three steps, we're able to connect to SnowflakeDB on Appsmith! Now we can power UI widgets with queries from the created data source.


Credits to Nidhi Nair, who had worked hard on getting this integration out!

We appreciate each contribution and piece of feedback that you share. Join the community! Come chat with us on Discord, or jump in on Github directly.

Generate a CRUD App from Any Database with One Click!
26
August
2021
Announcement

Generate a CRUD App from Any Database with One Click!

Generate a CRUD App from Any Database with One Click!
Vihar Kurama
0
 minutes ↗
#
crud
#
databases
#
ui
#
open-source
#
javascript
Announcement

Most apps on the internet are actually CRUD applications. What this means is that any basic app does the following functions: Create, Read, Update, and Delete (CRUD). In fact, if you look at Twitter, it’s a simple CRUD app where users can create tweets, read tweets, update tweets (like re-tweets) and delete tweets! CRUD apps are used on a daily basis by several businesses and organizations to maintain their day-to-day workflows. For example, in an enterprise organization, HR departments would be using a CRUD application to manage staff and keep track of existing employees.

Well, at Appsmith, we know how important CRUD applications are for internal operations. Why should something so critical to an organization's success be difficult to make? Well, we took matters into our hands and we made it super easy to make one. What if we said that you can now make the entire CRUD application with all the UI, logic, and complete control in just one click? Yes. You read that right!

We’re super excited to announce our new feature called the “Generate New Page from Database” With this, you can build CRUD applications within no time. We’ll discuss a bit more about our approach in shipping this feature in this blog, and also talk about how you can use this feature on the Appsmith framework.

So What’s This Feature All About?

The idea of the “Generate New Page” feature is rather simple, we wanted to ship a feature that would help developers save time in building UI and writing queries for basic CRUD applications. Now, all that users have to do is just open the canvas for a new application, connect a data source and then click on the “Generate New Page” option. And here’s what this will do:

First, it will create four new queries for you from the selected data source that will do the following:

  • CREATE new data (rows) into the table
  • Read all the data from the selected table
  • Update a particular row item in the table
  • Delete the selected row item in the table

These straightforward CRUD operations will be automatically generated for users!

As for the UI, this feature will automatically create a new table and fill all the data in the table using the read query. However, users can customize the rows based on specific use-cases after the application is generated.

Next, when users click on the ‘Create Row’ button, it will open a modal to insert new rows into the table. This option will use the ‘Create’ query.

To update the data in a particular row, users can use the Update Row container on the right, where they will be able to update any row data by selecting from the table and using the form. This will be using the ‘Update’ query.

Finally, inside the table, the last column will be populated with buttons which when clicked will delete the entire selected row by using the ‘Delete’ query.

What a breeze! Not just that, the table comes with powerful features where users can search, filter or sort contents of the table by configuring it.

Following is the screenshot of the entire generated UI.

UI.png

Now, we’ll talk about how to use this feature!

How To Use the Generate New Page from Database Feature

In order to be able to use this feature, the first step is to connect a new data source on the Appsmith application.

If you’re new to Appsmith follow the steps below:

  1. If you’re an existing user sign in to your account or you can sign-up here for free.
  2. After you sign in you’ll be redirected to the Appsmith dashboard where you can create a new application by clicking on the Create New button.
  3. This will create a new Appsmith application, now to connect a data source click on the + icon next to the Datasource option on the left navigation bar.
  4. You can choose from a wide range of options here, in case if you want to test it you can use the mock-users database.
  5. Next, under the active data sources click on the GENERATE NEW PAGE option.

You’ll see the following options:

Options.png

Now, you can select the table and add a searchable column. As soon you click on the Generate Page, you’ll see the UI where all the CRUD operations can be performed.

Hola, and with just one click the entire application is generated. Hope you love this new feature.


Follow us on Twitter and Linkedin to stay up to date with more news and announcements!

We love feedback, and we want to know your thoughts. Write to us with your ideas, opinions, or even send us brickbats on marketing@appsmith.com.

If you want to write a blog for us, send me an email and I’ll make it happen: vihar@appsmtih.com.

How To Display Data Visually From a Database in under 5 Minutes
1
September
2021
Resources

How To Display Data Visually From a Database in under 5 Minutes

How To Display Data Visually From a Database in under 5 Minutes
Vyom Srivastava
0
 minutes ↗
#
databases
#
analytics
#
dashboard
#
mysql
Resources

If you’re dealing with numbers, graphs and charts happen to be the best way to make sense out of those numbers! Charts and graphs can help you understand your data and help you make decisions based on them. With Appsmith, you can connect your data sources in just a few steps and generate beautiful graphs and charts.

As of now, Appsmith supports the following databases (in addition to using any REST APIs):

  • MySQL
  • MS SQL
  • Postgres
  • Amazon S3
  • Google Sheets
  • Snowflake
  • Redshift
  • Redis
  • Elastic Search
  • Firestore
  • ArangoDB

In this article, we will display data from our MySQL database to our app on Appsmith.

Step 1: Create app, and connect database

Login to your Appsmith account or create a new account (if you don’t have one). Once you're logged in, click on the “Create New” button:

Screen Shot 2021-08-23 at 10.35.33 AM.png

After that, click on the Build with Drag & Drop widget. You’ll see this:

Screen Shot 2021-08-23 at 10.39.42 AM.png

On the left sidebar, you can see an option for “Datasources”. Click on the “+” button and it will open a list of all supported data sources by Appsmith:

Screen Shot 2021-08-23 at 11.20.45 AM.png

For this blog, I will use the Mock Database (provided to all users of Appsmith to help with quick prototyping), which is based on MySQL; you can go for your preferred database.

NOTE: Since I am using Mock Database, it’s not asking for database credentials. But, when you select another data source, you’ll need to enter the database credentials. Let’s say you want to connect to a new data source. You’ll see the below page:

Screen Shot 2021-08-28 at 5.49.10 PM.png

Once you select the data source (for example, MySQL), you now need to enter your credentials:

Screen Shot 2021-08-28 at 5.49.43 PM.png

Step -2: Add the first query and widget

Once you have added the database (in this case, selected the data source), you should see the list of all the tables present in your database.

Here’s how it will look:

Screen Shot 2021-08-23 at 11.21.49 PM.png

This allows you to perform CRUD operations against those tables. Let’s try to add our first widget. Now, click on the “+” button in front of Datasources. You should see the list of all connected data sources. Now click on “+ New Query” for the data source you want to run the query. Since we’re using a mock database so we’ll select that:

Screen Shot 2021-08-28 at 5.56.11 PM.png

Once you click the “+ New Query” button, you now need to click on the “Select” option from the list to run the SELECT queries:

Screen Shot 2021-08-28 at 6.02.01 PM.png

This will open a MySQL editor where you need to paste the below query:

SELECT count(public."users"."name"), date(public."users"."createdAt") FROM public."users"group by date(public."users"."createdAt") order by date(public."users"."createdAt") asc;
Screen Shot 2021-08-28 at 6.05.07 PM.png

Now, to add the widget, we need to add it as a Chart. So, just select the chart widget, which is available at the right sidebar of the page.

Screen Shot 2021-08-24 at 8.44.21 AM.png

Step -3: Add Stats for Orders

Hover the cursor on "public.orders" and click on the “Add” button and select the SELECT command option. Now, paste the below query:

SELECT count(public."orders"."deliveryAddress"), public."orders"."orderDate" FROM public."orders" group by public."orders"."orderDate"  LIMIT 7;

This will return the orders data for the last seven days. We now need to select the chart widget so that the data can be displayed.

Screen Shot 2021-08-24 at 8.52.29 AM.png

Step -4: Add Stats for Revenue

Hover the cursor on “public.orders” and click on the “Add” button and select the SELECT command option. Now, paste the below query:

SELECT sum(public."orders"."orderAmount"), public."orders"."orderDate" FROM public."orders" group by public."orders"."orderDate"  LIMIT 7;

This will return the revenue data for the last seven days. We now need to select the chart widget so that the data can be displayed.

Screen Shot 2021-08-24 at 8.56.52 AM.png

Step -5: Adding employees’ data

Hover the cursor on “public.employees,” and click on the “Add” button and select the “SELECT” command option. Now, paste the below query:

select public."employees"."employee_id", public."employees"."employee_name", public."departments"."department_name" from public."employees" LEFT JOIN   public."departments"  on public."departments"."department_id" = public."employees"."department_id";

This will return the employees’ data which includes employee id, employee name, and employee department. We now need to select the chart widget so that the data can be displayed.

Screen Shot 2021-08-24 at 9.00.25 AM.png

Step -6: Add To-Do List for Employees

Hover the cursor on “public.standup” and click on the “Add” button and select the “SELECT” command option. Now, paste the below query:

SELECT public."standup_updates"."date",public."standup_updates"."name", public."standup_updates"."notes" FROM public."standup_updates" LIMIT 10;

This will return the employees’ standup data which includes the date, employee name, and employee notes. We now need to select the chart widget so that the data can be displayed.

Screen Shot 2021-08-24 at 9.03.34 AM.png

Step -7: Deploying the dashboard

Once we’re done with adding and aligning all the widgets, we also need to deploy it; just click on the “Deploy” button at the top of the page including, and it’ll get deployed instantly!

screencapture-app-appsmith-applications-61231d24695af65b46d7449b-pages-61231d24695af65b46d7449d-2021-08-24-09_12_41.png

You can check out the live example here also.

Final Words

Displaying data from the database is very easy with Appsmith. Our plug-n-play UI allows you to add any database and display the data in graphs, charts, tables, dropdown or even as a normal text. You can even add a lot more functionalities in this dashboard like create new orders, create/update employee data, or even perform CRUD operations using our widgets to your database tables.

Five Open Source Database Managers to Connect Your Data
4
August
2021
Resources

Five Open Source Database Managers to Connect Your Data

Five Open Source Database Managers to Connect Your Data
Vyom Srivastava
0
 minutes ↗
#
databases
#
open-source
#
mysql
#
mongodb
Resources

The database is one of the most critical parts of an application. Why? All the information you receive is stored in the database; the app pulls up that information in the way you want it. So the first step to building an app would be to connect your data. It’s no wonder that databases dominate the world of apps.

Some of the proprietary databases can be expensive and tend to offer limited technologies for data storage; however, you can always choose to go with open-source databases for your next project. You can self-host open-source databases and configure them as you like since the source code is available. Not just this, open-source databases are very flexible as well as cost-effective. There are a lot of applications that use more than one technology for data storage. For example, to deal with real-time data like (for example, data of real-time visitors), MySQL is not a good choice because it is not designed for high concurrency as it takes a lot of time to run multiple queries at the same time. App developers tend to go with a database like MongoDB as it supports a high level of concurrency. However, there could be a situation where the data science team for the same application would probably use MySQL for running complex queries. See how developers can use more than one database technology to connect and perform CRUD operations.

Why should you use a database manager?

  • It helps you to manage multiple databases at the same time.
  • It helps you to optimize your queries by providing meaningful insights such as query execution time, size of the data, etc.
  • You can easily update your databases.
  • They’re safer to use since the credentials are being encrypted first and then stored.

There are many database managers available out there, but only a few of them support multiple database technologies. Any good database manager should be able to support multiple databases and the following features:

  • Code linting
  • Error Highlight
  • Persistent Database Connection
  • Ability to store multiple credentials in a secured way
  • Ability to generate raw code if required

Of course, needless to say, the more the merrier! The ones I’ve mentioned above are the minimum standard across leading open-source database managers.

We’ve curated a list of some of the popular database managers for your next project. Dive right in!

OmniDB

Screen Shot 2021-08-04 at 4.44.00 PM.png

Omnidb is an open-source database manager which provides a collaborative environment for developing and managing your database. It comes in two variants: a hosted version and a stand-alone app version.

It’s powered by WebSockets and allows you to run multiple queries on multiple databases in the background efficiently. The application is built keeping simplicity in mind, making it one of the most user-friendly database managers.

It also supports multiple tabs, which allow you to write clean code and make multiple database connections in one session. It also has a smart SQL editor, which comes with linting, auto-complete, beautifies, etc.

Pros:

  • Support for multiple database technologies like MariaDB, MySQL, Oracle, PostgreSQL.
  • Dark theme support
  • Support for SSH terminal.
  • Monitoring dashboard: it provides a graphical interface to monitor all your configured units using Python scripting and other configuration files.
  • Auto-complete

Cons:

  • Doesn’t support NoSQL databases

HeidiSQL

Screen Shot 2021-08-04 at 5.02.18 PM.png

HeidiSQL is another open-source database manager which is extremely user-friendly and lets you connect multiple databases. It is one of the most potent database managers out there and enables you to create tables, logs, and manage users on MySQL databases and other database technologies.

This database was initially developed to make connections with MySQL only. However, it was extended to the MS SQL server, and now it also includes PostgreSQL support. HeidiSQL’s UI is pretty clean and allows you to create multiple connections. Once you install it, a setup screen follows, collecting essential information like IP, port, username, and password.

You can also export your data in CSV, Excel, CSV, HTML, SQL, LaTex, Wiki Markup, and PHP array. You can also edit multiple tables together by using the bulk edit option. Not just this, The monitor allows you to kill the costly processes.

Pros:

  • Supports multiple database technologies.
  • Export data in multiple formats.

Cons:

  • HeidiSQL's UI is minimal, and there's definitely room for improvement, but you can create multiple connections seamlessly.
  • Doesn’t support NoSQL databases

RockMongo

Screen Shot 2021-08-02 at 11.19.35 PM.png

RockMongo is an open-source MongoDB GUI tool that is written in PHP and is an entirely web-based application. It looks very similar to PHPMyAdmin and comes with a classic 90s UI (Windows 98 style buttons and a non-responsive layout). It supports all the common standards that make it easy to work with the collections, stats, etc.

Like the ones above, you can connect and store the credentials of multiple MongoDB servers, but it doesn’t support tabs. This means you can only work on a single MongoDB connection at a time.

Another drawback of using this tool is the dependency on the PHP server. To run and execute queries on this tool, you need to install and run a PHP server.

Pros:

  • Very light-weight
  • Easy to use

Cons:

  • Dependency on PHP server.
  • Doesn’t support multiple tabs.

Robo 3T

Screen Shot 2021-08-04 at 5.04.23 PM.png

Robo 3T, formerly known as RoboMongo, is another open-source MongoDB GUI client. The application is available for platforms like Ubuntu, Mac, and Windows. It comes embedded with the default MongoDB shell and allows you to run complex queries in a much faster way.

Robo 3T is one of the most popular projects on Github, which means there's an experienced community to help you out. Since it uses the default MongoDB shell, the consumption of resources is likely to be relatively low.

Pros:

  • Auto completion
  • MongoDB shell
  • One of the fastest database managers.

Cons:

  • The UI becomes cluttered sometimes.

Navicat

Screen Shot 2021-08-04 at 5.05.12 PM.png

Navicat is another powerful database management and design application that supports multiple drivers and databases. It comes in a standalone application for Mac, Windows, and Linux and allows you to manage drivers like MySQL, MariaDB, SQL Server, SQLite, Oracle & PostgreSQL DB very easily.

This application comes with a lot of functionalities like export to excel, stored procedures, scheduling out of the box, and data transfer. One of the interesting features is data transfer, it allows you to transfer tables from one database to another even if they’re not on the same server.

Pros:

  • Support for multiple databases and drivers.
  • Data transfer functionality.
  • Available on all platforms.

What’s next?

Once you're done with your database development and design you’ll need a platform to work with the data right? You can use Appsmith and easily connect your databases. Let's say you’re building an API, you can use Appsmith to connect the database and deploy your APIs. Or let's say you want to fetch data from a database and plot a graph using the data. You can very easily use our drag-and-drop widgets to create a graph and deploy it.

Guess what, you can also connect your data on Appsmith either through APIs or through our native integrations with popular databases like Postgres, MongoDB, and Snowflake, among others, as well as apps like Google Sheets!

610a2b25eaeb381bf46cab45_Da_9-DEJByCND9d3I8Nn7edxWsIMG3pMfle3l7q3p-SGYp4ha7_g5WaDabixDg5p7fKc0WOBzyVXL5vaHhg0vvRxUkzOmePdvSNOkWJ7isz9sDTmZyCp_yqBfUxNlk39ebouSEH2.jpeg

Are you interested in building something with Appsmith? Take it for a spin. Join our vibrant community on Discord. To get regular updates on what we’re up to, follow us on Twitter!

How to Run Manual Jobs in Gitlab CI/CD
12
March
2021
Resources

How to Run Manual Jobs in Gitlab CI/CD

How to Run Manual Jobs in Gitlab CI/CD
Vihar Kurama
0
 minutes ↗
#
ci-cd
#
gitlab
#
ui
#
databases
#
automation
Resources

Gitlab is eating the world, or so we thought till we moved to GitHub as an opensource company. That should tell you enough about our love for Gitlab, but there is one thorny problem with Gitlab CI that didn't have a solution in any pricing tier. It's the ability to trigger CI jobs with custom parameter values manually. This article will explore the benefits and drawbacks of manual jobs, potential workarounds, and finally, how using Gitlab API and forms, we can get around this problem.

Why are manual jobs important in CI/CD?

Although CI is primarily aimed to be continuous (I didn't see that coming, did you?), there is still a precious place for manual triggering of jobs in a CI pipeline. This becomes especially apparent when we consider parameters in the build process. For example, you are working on a separate branch that does a PoC of a new feature. You want to ensure that the CI can handle the codebase in this branch but don't want to push your official organization. So, if your build script took parameters (with sane defaults, of course) that can be configured to have the image pushed to your personal organization, you'll be able to test it to your heart's content. What's more, you can even run the Docker image and check all is in order. Basically, you don't break the app.

Another simple example would be when you have a specific set of servers you want to push your changes to from a specific branch in your repo. This is a case of CD, with finer control. You can use the same deploy pipeline but set the branch and server(s) variables and run this job to get the desired outcome. This becomes incredibly useful as the organization scales and has multiple branches, teams, and servers, especially in this age of mono-repos.

If you're a pipeline wizard, you can probably spin off a new script each time for these jobs and run these scripts each time, but a more practical solution would be to use a pipeline template and have a few variables (set defaults for a standard run) that can be populated for manual runs. This is where Gitlab comes a bit short.

gitlab.png

I have no idea what I am doing here!

How do I run manual jobs using GitLab?

If you're willing to compromise on everything else Gitlab has to offer, Jenkins is your go-to for CI/CD and boy does it ship with an out of the box manual job runner. But of course, who wants to compromise on all the other awesome stuff from Gitlab?

The biggest inconvenience stems from the fact that Jenkins and Gitlab are two separate systems. It's up to the developer to ensure that these two talk to each other well and stay on good terms. For example, If the source code on Gitlab moves from being a Java project to a Golang project, the Jenkins build jobs must be configured to stop using Maven and start using go. Having Gitlab be the integrated solution for both your source code and your CI pipelines is just too convenient. The overheads of running Jenkins and Gitlab will just make your team grumble much more than usual.

Let's say you do end up integrating with Jenkins, are you sure this is enough for you to sacrifice all of Gitlab's CI/CD efficiencies for?

project-build.png

Do you want to integrate this with your repo?

Manual Jobs For Gitlab

Assuming you haven't ripped apart your Gitlab instance and installed Jenkins, and you're still asking yourself "So how does one trigger manual jobs for Gitlab?", the main (and surprisingly the simplest) suggestion is to build a form for each job and use Gitlab API to trigger it - from the discussion for this issue from four years ago (remember, Google is your friend). Why don't we give that a shot? Sounds simple enough, right?

<html lang=en>  
<head>    
<meta charset=utf-8>    
<title>Gitlab CI Trigger</title>  
</head>  
<body>    
<form method=post action='gitlab-api-endpoint'>      
<p>        
<label for=branch>Branch</label>        
<select id=branch name=branch>          
<option value=master>master</option>          
<option value='feature/one'>feature/one</option>          
<option value='feature/two'>feature/two</option>          
<option value='bug/that-nasty-bug'>bug/that-nasty-bug</option>        
</select>      
</p>      
<p>        
<input type=checkbox id=debug name=debug>        
<label for=debug>Print debug output</label>      
</p>     
<p>        
<label for=reason>Reason for trigger</label>        
<input name=reason id=reason>      
</p>      
<p>        
<button type=submit>Run pipeline!</button>      
</p>    
</form>  
</body>
</html>

We're not even getting started here 😰

screen.png

Voila? 🤔

But a few things to remember before we deploy that in production. You need to update the branch list whenever that changes, which is almost every day. Also, remember to add or remove any parameters along with your build pipeline. Every time you run a manual job, you essentially have to rebuild this file, or scope all potential manual jobs and have files ready. This is really inconvenient.

Beyond just being inconvenient, this method doesn't scale. Even if you maintain this HTML form as part of your source code, it still warrants a significant maintenance effort. You don't want to spend time building/debugging/fixing/maintaining your pipeline-runner, you want to build the software you are actually working on.

The better way to manage CI/CD workflows

Fear not, Appsmith is here. While not an official workaround, appsmith makes it incredibly easy to create your forms and set up the API needed to run manual forms. Building the form will be easier than in Jenkins, API calls can be managed gracefully in the interface, AND you can build a job runner that looks like this in 10 minutes.

We've built a small tutorial to walk you through how we create a manual pipeline to deploy your code on multiple production servers. Let’s get started!

Appsmith is an open-source cloud or self-hosted platform to build admin panels, CRUD apps and workflows. Appsmith helps you speed up application building through fast UI creation, and making you write code only when necessary.

Here’s a video to get you started!

We’ll be building a dashboard on Appsmith to simplify and manage a simple CI/CD workflow that allows developers to deploy software by selecting a specific branch and server.

Additionally, we’ll create options to

  • View a Specific CI/CD Workflow
  • Cancel a Running Workflow
  • Retry a Failed Workflow
  • Delete a Workflow
  • Save Workflow History

This dashboard is going to be created entirely using Appsmith and the Gitlab API.

We’ll be using a Gitlab repository named ci_cd to follow along and set up workflows. It’s a basic “Hello World!” Node.JS application that is deployed to multiple servers on Heroku. We have our .gitlab-ci.yml file configured in such a way as to run two jobs. The first job is to perform a test with the npm test command, and the second is t deploy our app onto Heroku using a ruby gem called dpl.

The entire workflow is configured dynamically so that it can be deployed to any Heroku server given a Heroku <app-name> and an <api-key>. To hide these key’s we’ll be setting these out in our environment variables to the workflow. Lastly, on Heroku, we have two deployment servers appsmith-ci-cd-server1 and appsmith-ci-cd-server2.

Below is a snippet showing the content of .gitlab-ci.yml file.

image: node:latest
stages:
  - test
  - deploy

testing:
  stage: test
  script: npm test

deploying:
  stage: deploy
  image: ruby:latest
  script:
    - gem install dpl
    - dpl --provider=heroku --app=$heroku_app_name --api-key=$heroku_api_key

Building Dashboard on Appsmith

Now that we have a clear picture of what’s cooking, let’s head to Appsmith and start building our Dashboard. If you haven’t signed up, you can get started for free, here.

Next, let’s head over to our Appsmith dashboard and create a new application by clicking on the Create New button. We’ll be now redirected to a new application named “Untitled Application 1”. Now, let’s give our app a creative name, let’s call it Deploy Dashboard. Our first step is to build a feature that would list all the existing and future workflow history. To do this, let’s utilise Gitlab’s pipeline API, fetch all the details and display it onto the Appsmith table widget.

Let’s go ahead and configure our workflow API Gitlab.

Setting up Gitlab API as Datasource

In this section, we’ll be using the GitLab API as a data source to configure it with the Appsmith Deploy Dashboard application. Let’s now create a new API, we can do it by navigating to the API section under Page1 and clicking on Create New option.

Paste the following in the request form and click on Save as Data Source

https://gitlab.com/api/v4/projects/:id

Next, let’s follow the below steps to display all the existing workflows onto the Appsmith dashboard.

  1. Firstly, the id in the above GitLab API is a parameter to access details of a particular repository on Gitlab, you can find it on your repository page on GitLab.
  2. To make things more clear, let’s rename the Datasource name as GitLab, we can do it by double-clicking on the existing one.
  3. Next, let’s add a new key named PRIVATE-TOKEN under the headers section. We can find the value of PRIVATE-TOKEN from GitLab.
  4. Navigate to the Preferences -> Access Tokens on GitLab and create a new access token and paste it to the PRIVATE-TOKEN key on the Appsmith data source. (Also be sure to give it at list api scope)
  5. Lastly, save the data source!

Getting Workflows List

Our Datasource is up and running, now let’s head back to the API section on Appsmith and create a new API to fetch all the existing workflows on the repository. Now create a new API and set the name as get_pipelines. On clicking on the URL we have suggestions to use our previously configured GitLab data source. Use the suggestions and add /pipelines to its end. The URL should now look like the following:

GET https://gitlab.com/api/v4/projects/:id/pipelines

Hit the Run button and you will be greeted with an array of the workflow linked to that repository! Sweet, isn’t it?

Now to make things cooler, let’s build UI to display all these CI/CD workflows on the Appsmith page. Click on the widgets on the navigation bar and drag and drop a table widget onto the canvas. You should see a new table with some pre-populated data. We should also see a floating pane, that consists of all the table properties. Under that pane, edit the Table Data property to the following:

{{get_pipelines.data}}

Now, we can see data from the get_pipelines API rendered on the table. You can go ahead to rearrange the column and disable columns that you don’t want showing up on the table i.e sha and updated_at.

table.png

Triggering A Pipeline

Now let’s add a new feature to trigger a new workflow on the same dashboard. To do this, we’ll create a new button by dragging and dropping a button widget. Rename the button to Trigger New Pipeline. Also, drag in a modal widget to the canvas. The button should be configured such that the modal is opened whenever it’s clicked. On the other hand, the modal’s type should be set to form modal and we’ll drag in two dropdowns with corresponding labels into it. The first dropdown should be configured to select a branch with the following options:

[
  {
    "label": "master",
    "value": "master"
  },
  {
    "label": "staging",
    "value": "staging"
  }
]

Similarly, we configure the second dropdown to show the server options that are configured on Heroku namely, appsmith-ci-cd-server1 and appsmith-ci-cd-server2:

[
  {
    "label": "appsmith-ci-cd-server1",
    "value": "appsmith-ci-cd-server1"
  },
  {
    "label": "appsmith-ci-cd-server2",
    "value": "appsmith-ci-cd-server2"
  }
]

Perfect, we should now see a great looking modal on our Appsmith dashboard.

form.png

Let’s head back to the API section and create a new API to trigger a workflow whenever the Confirm button from our modal is clicked. Let’s name this API as create_pipeline and set the value to the following:

POST https://gitlab.com/api/v4/projects/:id/pipeline

Additionally, we have the option to provide variables and ref (meaning the source branch) in the body of this endpoint. We should configure the body as given in the below snippet.

{
    "ref": "{{Dropdown1.selectedOptionValue}}",
    "variables": [
       {
            "key": "heroku_app_name",
            "variable_type": "env_var",
            "value": "{{Dropdown2.selectedOptionValue}}"
        },
       {
            "key": "heroku_api_key",
            "variable_type": "env_var",
            "value": "your_heroku_api_key_here"
        }
    ]
}

By looking at the above snippet, the ref key is obtained from the branch dropdown, which was previously configured and in the variables section, the value of the heroku_app_name key is obtained from our server dropdown.

"You can find the value of heroku_api_key from your Heroku account under the Settings-> API Keys section."

Lastly, let’s head back to the modal and configure the onclick action of the confirm button to trigger the create_pipeline endpoint. Add the following JS snippet to the onclick property under the button settings.

{{
create_pipeline.run(() => get_pipelines.run( closeModal('Modal1')), () => {})
}}

Kudos! With this, we should be able to trigger a workflow from the Appsmith dashboard itself. Let’s now fine-tune this to have more features in the next section.

More Fine Tuning

Alright, in the last section, we’ll now add fine-grained controls such as deleting, cancelling, retrying, and viewing workflows. These are quite similar to each other, let’s look at the delete workflow option and you can try adding the rest as an exercise :)

Deleting the CI/CD Workflow from Appsmith Dashboard

To implement this feature, let’s add head back to our table and add a new “Custom Colum”. You can find it under the table settings pane. Let’s name this column as delete. To make our dashboard more intuitive, we can set the column type to button and add the label as delete. Now let’s create a delete API under the APIs section and name it as delete_pipeline. Use the following endpoint to add functionality.

DELETE https://gitlab.com/api/v4/projects/:id/pipelines/{{Table1.selectedRow.id}}

This API grabs the id of the selected row from the table, which is automatically set when the delete button is clicked. Heading back to the delete button, let’s configure the onclick action to run the delete_pipeline api and call the get_pipelines api on success to update the table. Here’s the configuration JS snippet:

{{delete_pipeline.run(() => get_pipelines.run(), () => {})}}

Perfect, now we have the ability to delete a specific workflow from the Appsmith dashboard itself.

"Disclaimer: Clicking on this button will delete that pipeline for good. As a safety measure, you can add a confirmation modal to prevent accidental delete."

Here’s a quick lookup to configure the other actions to play with our workflows:

Retry Workflow:

POST https://gitlab.com/api/v4/projects/24888719/pipelines/{{Table1.selectedRow.id}}/retry

Cancel Workflow:

POST https://gitlab.com/api/v4/projects/24888719/pipelines/{{Table1.selectedRow.id}}/cancel

Final Thoughts

We are glad you made it this far. As a quick summary, here’s a gif to show everything we’ve built so far!

Honestly, that was a lot to take in, but we’ve been able to go through a complete flow of how you can build a custom dashboard to manage your ci/cd process. Also, here's a quick demo of the app that we've built! And we are quite sure you’re bubbling with ideas on the limitless use cases you can build with this. So go into the world and build amazing stuff!

Image Credits: Photo by Ryan Quintal on Unsplash

Connect to a Database Instance Running Inside a Java Testcontainer Using IntelliJ IDEA
17
December
2020
Resources

Connect to a Database Instance Running Inside a Java Testcontainer Using IntelliJ IDEA

Connect to a Database Instance Running Inside a Java Testcontainer Using IntelliJ IDEA
Sumit Kumar
0
 minutes ↗
#
databases
#
java
#
ides
#
testing
#
container
Resources

Testcontainers library (Java) is generally used along with Junit test cases to spawn a test environment within a docker container. IntelliJ IDEA provides an option to run these test cases from within the IDE.

Databases spwaned using testcontainers when run from within the IDE can become inaccessible from outside the IDE. In order to connect to such databases you can uses the database tool that comes with IDEA ultimate version.

Steps:

1. Add a debug point in the code such that the testcontainer has been brought up at this point.

Screenshot 2020-12-17 at 10.58.27 AM.png

2. Run the test program using debug mode and wait till it stops on the break point.

Screenshot 2020-12-17 at 10.59.27 AM.png
Screenshot 2020-12-17 at 11.09.16 AM.png

3. Click on the database tool.

Screenshot 2020-12-17 at 11.10.00 AM.png

4. Select your database type.

Screenshot 2020-12-17 at 11.12.06 AM.png

5. Enter your credentials. Test your connection and save credentials.

Screenshot 2020-12-17 at 11.17.17 AM.png

6. Run query.

Screenshot 2020-12-17 at 11.19.44 AM.png