Building an Appsmith Dashboard with Cube
24
January
2022
Tutorial

Building an Appsmith Dashboard with Cube

Building an Appsmith Dashboard with Cube
Vyom Srivastava
0
 minutes ↗
#
community
#
tutorial
#
dashboard
#
javascript
Tutorial

Every project contains several moving parts. We developers call them features. They can be dashboards, performance overviews, a blog, or web pages that include forms. Features take time to develop from scratch. That can impact delivery and deadlines.

In this article, I want to create a metrics dashboard with Appsmith. I'll use API endpoints generated from Cube with a public dataset from the Museum of Modern Art (MoMA).

Here's what the end-product will look like:

dashboard-image

I want to showcase how it would be more efficient for your team to have a tool to create custom dashboards for internal metrics. A huge bonus point is that it can be used by anyone, including non-devs in your team. This is where low-code tools come into play.

Appsmith is an open-source low-code platform that lets you create web applications like dashboards and admin panels by using drag-and-drop widgets. With Appsmith, you can connect multiple data sources like MySQL, MongoDB, REST API, Google Sheets, and perform CRUD operations.

I'll be using a hosted Cube deployment on Cube Cloud to get data from the Museum of Modern Art (MoMA) dataset.

A Quick Look at Appsmith

You don't need to be a developer to create metrics dashboards using Appsmith. It comes with a set of drag-and-drop widgets for forms, charts, images, and more, that you can use in your application to enhance its functionalities.

A few of the benefits of Appsmith include:

  • The ability to create apps without development knowledge
  • Saving the developer team's time
  • Saving your company's money by saving man-hours
  • Increased productivity
  • Drag-and-drop widgets

However, keep in mind that despite all of its functionality, Appsmith can't compare to the customization level of a custom-built app.

A Quick Look at Cube

Cube is an open-source analytics API platform that lets you connect with data sources and then access the data through an API. You can connect to data sources like MySQL, AWS Athena, and Google BigQuery, among others. It's agnostic for visualization tools, meaning you can connect to any front-end charting framework and build your own customized UI.

The Cube API lets you sort, group, and filter through the dataset itself. It acts as a centralized back-end metrics layer for any dataset. The multi-staging query system allows Cube to handle trillions of data points.

Creating a Dashboard with Appsmith and Cube

You're going to display a public dataset from the MoMA on the dashboard you're creating on Appsmith. The MoMA is one of the largest and most influential museums in the world, with around 300,000 books and catalogs, and more than a thousand periodic files.

This is how your dashboard will look:

dashboard-image

You'll use db4free.net, a free database service, to upload and host the database. After uploading the data to the database, you'll connect to the database using Cube. You'll be able to integrate the API generated through Cube into the different widgets of Appsmith.

Prerequisites

  • Any public dataset (in this case we're using one from MoMA)
  • A Cube account
  • An Appsmith account
  • Any database service; this tutorial will be using db4free.net

1: Clone the Dataset and Host It on Your Database

Once you clone the dataset, use the PHPMyAdmin or the cPanel of the database hosting service and import the data via CSV.

2: Connect Cube to Your Database

Now log in to your Cube account and click Create deployment. Give the deployment a name; the images in this example will use Appsmith Dashboard.

setup-1

On the next screen, select MySQL from the data source list. If you're using some other data source like Google BigQuery or AWS Athena, select the option accordingly.

step-2

Now, just fill in your database credentials, and you're good to go!

step-3

Once you've generated the data schema of your dataset, you can create REST APIs.

3: Get API Credentials and API Endpoints from Cube

Once you have created a deployment, you'll be redirected to the overview page of the deployment.

overview-page

Copy the API endpoint and click How to connect your application to get the Authorization key.

And you're done creating Cube's API. It's time to move ahead with Appsmith to create your dashboard.

4: Set Up Appsmith

Log in to your Appsmith account, create a new application, and select Build with drag & drop.

create-app-1

5: Create the First Widget

Once you see an empty canvas, click the + button next to Datasources in the sidebar. Select Create new API.

create-app-2

You should see the following page:

create-app-3

For the URL, paste the REST API endpoint, and in the Authorization header, paste your API code. In the case of this tutorial, the REST API endpoint is https://fuchsia-shark.aws-eu-central-1.cubecloudapp.dev/cubejs-api/v1/load.

Pass the following JSON as a GET param:

{
  "dimensions": [
    "Artists.begindate",
    "Artists.displayname"
  ],
  "timeDimensions": [],
  "order": [
    [
      "Artists.begindate",
      "desc"
    ]
  ],
  "limit": 10
}

In this code block, you're asking the REST API to return the top ten begin dates and display name. The rows are in descending order.

Once you start to get the data, you'll see a list of widgets on the right side of the page. Select Table from this list:

A table will appear in the canvas.

6: Create the Second Widget

Repeat the previous step but with a different query. Just add another API data source with the following query:

{
    "dimensions": [     
        "Artists.nationality"   
    ],   
    "timeDimensions": [],   
    "order": {     
        "Artists.count": "desc"   
    },   
    "ungrouped": false,   
    "measures": [     
        "Artists.count"   
    ] 
}

This query will return the count of artists according to country.

Again, select Table from the widget list. Your canvas should look something like this:

create-app-6

7: Add Different Widgets as Per Your Requirements

In this example, I've experimented with different widgets and data customizations. I've added three more charts using this query.

Query 1

This query will return the count of all artists with their begin date.

{   
    "dimensions": [     
        "Artists.gender", 
        "Artists.begindate"   
    ], 
    "timeDimensions": [],   
    "order": {     
        "Artists.count": "desc"   
    },   
    "ungrouped": false,   
    "measures": [     
        "Artists.count"   
    ], 
    "filters": [
        {
            "member": "Artists.gender",
            "operator": "set"
        },
        {
            "member": "Artists.begindate",
            "operator": "notEquals",
            "values": [
                "0"
            ]
        }
    ]
}

Query 2

This query will return the gender breakdown of the complete dataset. I have used the data coming from this API in the pie chart.

{
  "dimensions": [
    "Artists.gender"
  ],
  "timeDimensions": [],
  "order": {
    "Artists.count": "desc"
  },
  "measures": [
    "Artists.count"
  ],
  "filters": []
}

Query 3

This query will return the count of artists from each country listed in the dataset.

{
  "dimensions": [
    "Artists.nationality"
  ],
  "timeDimensions": [],
  "order": {
    "Artists.count": "desc"
  },
  "limit": 10,
  "measures": [
    "Artists.count"
  ],
  "filters": []
}

You can use this query with any widget to represent the data efficiently.

Create Your Own Internal Apps with Appsmith

In this article, you learned how to create a metrics dashboard with Appsmith and Cube to display data from the public dataset from the Museum of Modern Art (MoMA). You now know how to use low-code tools to save time and money on development.

Check out the live example here and try adding some more widgets to your metrics dashboard with Appsmith.

I think Cube and Appsmith are a great match. With Cube, you focus on creating business value without wasting time and effort on maintaining infrastructure.

Appsmith lets you focus on creating business value with metrics dashboards with minimal effort to both create and maintain.

You can register for Cube Cloud right away, and check out Appsmith as well!

I'd love to hear your feedback about building metrics dashboards with Cube Cloud in the Cube Community Slack. Click here to join!

Until next time, stay curious, and have fun coding. Also, feel free to leave Cube a ⭐ on GitHub if you liked this article. ✌️

Connect Multiple Data Sources in Your App using these 6 Open Source Tools
16
August
2021
Resources

Connect Multiple Data Sources in Your App using these 6 Open Source Tools

Connect Multiple Data Sources in Your App using these 6 Open Source Tools
Vyom Srivastava
0
 minutes ↗
No items found.
Resources

While building an application, there are times when you have to use multiple data sources. It could be for security reasons or availability, or even cost issues. Often, connecting and managing those data sources can be challenging! You either need to build a system from scratch to connect those data sources or use a tool.

Let’s say you’re running an analytics dashboard, and you have data coming from multiple sources like MySQL, Redshift, S3, and MongoDB. Would you want your data scientists to focus on making all API connections, connecting all the data sources, and processing them in a specific format? Or, would you instead let them focus on the other critical parts of their job by using an easy solution and connect those data sources through an app?

Building a complete system from scratch is generally not advisable because of the enormous constraints on time. And, teams also have to go through the documentation for each data source to build a system from scratch. They also have to keep track of everything, such as changelogs/updates in the documentation, and change the code base accordingly. Handling this work would mean hiring resources dedicated to this work or putting the team under pressure to manage all this.  Using an application to oversee the connection makes sense while ensuring that data sources are secure and reliable.

Building everything from scratch can be a time-consuming part that may also distract you from your path.

To help you stay on course, we’ve put together a nifty list of great tools to help you connect multiple data sources without hassles.

Talend

Talend is an open-source data automation tool that can be used to connect multiple data sources. It makes the process of ETL (Extract Transform and Load) pipeline setup more uncomplicated and quicker. It offers a scalable architecture and robust data integration to maximize its value and a suite of open-source tools divided into several components. All combined, they become a potent tool for ETL and connecting multiple data sources.

Talend provides an easy and intuitive way to transform the data. Instead of mapping databases and filling out the forms for different databases, you can just use their graphical tool for mapping and transforming the data.

It also supports data conversion into multiple business formats such as OLAP, Jasper, SPSS, Splunk.

Supported data sources:

  • MSSQL
  • DB2
  • PostGres
  • MySQL
  • Teradata
  • Greenplum
  • DB2
  • Oracle
  • Sybase
  • Vertica
  • Informix

Arcesb

Arcesb is another open-source data integration and pipeline setup tool that can connect multiple data sources. It synchronizes the data in real-time, which means as soon as data gets available in the source, it gets publicly available through the Arcesb.

It supports a wide array of protocols such as AS2, AS4, OFTP, SFTP, etc. The drag-and-drop approach makes it easy to connect complex workflows and quickly transform the data in popular formats like JSON, XML, and CSV.

Supported data sources:

  • MongoDB
  • Dropbox
  • Google Sheets
  • MySQL
  • CSV
  • HubSpot
  • SQLite
  • AWS S3

Apache Camel

Apache Camel is an open-source data integration tool that is a kind of underrated software in ETL. It can be deployed as a standalone application in a web container, connecting complex workflows easily and quickly transformingJEE, OSGi, or even Spring container. It allows programmers to split integration problems into smaller pieces that close the gap between integration and implementation.

It has 3.4k stars on Github and 4.3k Github forks. It supports almost every available protocol like HTTP, HTTP, FTP, JMS, EJB, JPA, RMI, JMS, JMX, LDAP, Netty, and many more. Therefore, it is good to use when you’re dealing with multiple internet protocols. One of the best parts of Apache Camel is that it uses the same workflow for all the supported protocols.

Supported data sources:

  • MySQL
  • ActiveMQ
  • Async HTTP
  • REST API
  • AWS
  • Azure
  • Cassandra CQL
  • MongoDB
  • Digital Ocean
  • FTP/SFTP
  • Google BigQuery
  • GoogleSheets
  • GraphQL
  • RabbitMQ and many more which are listed here

Pentaho Kettle

Pentaho Kettle, also known as the Pentaho Data Integration tool, supports multiple data integrations, OLAP, data mining, reporting, and ETP capabilities. It is also known for its ease to use and quick learning curve.  It allows users to create their data manipulation jobs with a user-friendly graphical creator without entering a single line of code.

It comes with a set of tools that includes:

  • Spoon: This is a graphical tool that lets you create and set up ETL pipelines without using a single line of code. You can perform tasks like validation, refine, transform and write the data to various data destinations.
  • Pan: This is a tool to run data transformations.
  • Chef: This tool is used to create jobs.
  • Kitchen: This tool is used to execute jobs created by Chef.
  • Carte: It is a web server where you can host all your Pans.

Hevo Data

Hevo is a not-code ETL tool that you can use to connect multiple data sources. It supports more than 100 data sources and follows a 3-step data connection setup across all the data sources: select the data source, enter the correct credentials and select the destination.

It uploads the data to the selected destination and allows you to perform data manipulation and transformations. It also comes with a fault-tolerant system that ensures that the data is being transferred in a secured manner consistently using encryption.

Supported data sources:

  • AWS
  • MySQL
  • MongoDB
  • Snowflake
  • TokuDB and many more which you can check here

Appsmith

We have created Appsmith in such a way that you can easily connect multiple data sources with just a few steps. You can even pass the data through an API using our platform. The drag-and-drop UI makes the connection part very easy and allows you to connect data sources like AWS, MySQL, MongoDB, etc.

The credentials that you store in our application are first encrypted before storing. So, you don’t have to worry about security. Since our platform acts as a proxy layer, we do not store any data from your data sources.

To improve the performance, our platform creates a pool of connections with the database server. This tool allows you to run multiple queries as it is impossible to run multiple queries simultaneously in a single link. We have also created in-depth tutorials that you can check out to understand the benefits of other features as well.

Supported data sources:

  • Amazon S3 (also Upcloud, Digital Ocean Spaces, Wasabi, DreamObjects)
  • ArangoDB
  • DynamoDB
  • ElasticSearch
  • Firestore
  • MongoDB
  • MS SQL
  • MySQL
  • PostgreSQL
  • Redis
  • Redshift
  • Snowflake

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 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.

Test and Deploy your APIs Using These Open Source Tools
28
July
2021
Resources

Test and Deploy your APIs Using These Open Source Tools

Test and Deploy your APIs Using These Open Source Tools
Vyom Srivastava
0
 minutes ↗
#
apis
#
open-source
#
testing
#
app-development
Resources

Testing an API is one of the most important phases of the API development lifecycle. It ensures that the API you’re deploying on the server is bug-free and highly optimized. But the testing phase can be very complex as it involves different types of testing, such as load testing, regression testing, security testing, etc. There are a lot of challenges developers and testing teams face during the testing of API. Let's discuss them first:

Why is API Testing a Difficult Task?

We all know that APIs involve a lot of modules that have a lot of functionalities. Let’s take a simple example of an e-commerce application. You’re going to have many endpoints such as /login, /logout, /cart, /wishlist, /profile, and so on. You need to ensure that each endpoint is delivering what it is supposed to. For example, /cart should only show products associated with a particular profile and shouldn’t mix with other products.

An application like the one mentioned above can have about 300-400 endpoints or sometimes even more! On top of that, you need to make sure that the validations are working fine, response time is low or at least optimized, there’s no bug in the API, and good performance even when 1000s of requests are being made simultaneously. You also need to ensure that the API returns an appropriate status code such as 20x, 40x, 50x, etc. All this makes API testing not only tricky but also a time-consuming task.

To reduce the complexity of the whole testing and deployment process of the API, there are a lot of open-source tools available on the internet (if you prefer non-open-source tools, you can check out Postman or Firecamp ). These tools not only save a lot of time but also give you insights like the response time of the API, among others.

Once you’re done with testing, you can deploy the API on a server. Deployment is the process where you’re ready to go live with the API and need to move it to the live server. Every time you make a change to the API, you need to redeploy the API on the server (after testing, obviously! 😬)

Here's our list of open source tools that you can use to test and deploy your API:

SoapUI

Screen Shot 2021-07-27 at 10.54.02 PM.png

SoapUI is another API tool that allows you to test and deploy your APIs. This is one of the most matured and trusted API testing tools. One of the unique features of this tool is, it supports SOAP APIs too. The tool is mainly used for QA and API testing. It also allows you to connect external data sheets to retrieve data for executions.

Soap UI also allows you to send multiple API requests; triggering a single test case and supports a wide variety of testing such as load testing, functional testing, security testing, etc.

Pros

  • Easy to define variables and pass them in parameters.
  • Support for SOAP API

Cons

  • Slower to perform tests on complex APIs
  • Not completely free
  • UI takes a little bit of getting used to

Apache Jmeter

Screen Shot 2021-07-27 at 10.58.03 PM.png

Apache Jmeter is an open-source testing tool that not only tests APIs but scripts too. You can create your own test cases, and it’ll perform different types of testing like module testing, regression testing, etc.

The UI is quite simple and easy to use. You can test the APIs in two ways: either use direct API requests or write a code to make the requests to an API endpoint. The tool is entirely written in Java and supports multiple languages such as Python, C, Java, etc.

It also comes with a marketplace where you can just download the plugins to expand the platform’s functionalities. It supports multiple protocols such as FTP, HTTP, LDAP, SOAP, etc. JMeter also supports graphs and charts, so the results can be visualized easily. To perform UI testing, you can run Selenium test cases as well.

Pros

  • Free to use and completely open source
  • Can be connected to third party platforms like Jenkins
  • Can be scaled easily
  • Marketplace is a unique feature

Cons

  • Very slow
  • UI is dated

Hoppscotch

Screen Shot 2021-07-27 at 10.59.48 PM.png

Hoppscotch, previously known as Postwoman, is another popular open-source API development and testing platform. It has a dark UI and a minimalistic design scheme. It is one of the fastest API testing tools allowing you to send requests and copy responses in real-time.

It comes with a variety of themes and you can even install it as a PWA (Progressive Web App) on your mobile device. The tool also lets you make a full-duplex communication channel over a single TCP, in other words you can make Websocket connections. Another big feature of this tool is that you can also test GraphQL queries.

Pros

  • Support for Websocket
  • PWA
  • Easily create documentation

Cons

  • Comes in web variant only
  • It doesn’t support testing like regression, load, etc

Karate

Screen Shot 2021-07-27 at 11.07.24 PM.png

This platform has been developed by Intuit and is used for multiple purposes like API testing, deployment, creating mock test servers, web browser automation, etc. It is written in Java but doesn’t require the end-users to write anything in Java. It’s so easy to use that even non-programmers can write the test cases. It supports YAML as andV, so you can easily use them to write data drives tests. You can also perform cross-browser-based Web UI testing.

Pros

  • Support for multiple testing
  • Includes a lot of functionalities

Cons

  • It doesn’t have a great UI so you might have to write a lot of code

Insomnia

Screen Shot 2021-07-27 at 11.08.14 PM.png

Insomnia is another open-source tool that lets you track the development and deployment of API endpoints very easily. It uses a Swagger-inspired editor, so if you’re familiar with it you’ll be able to easily use this tool.

It allows you to import API specs from OpenSpec API as well as Postman. It also comes with a CLI tool called Inso, which lets you go in-depth with the API testing. You can also connect version control software like GitHub, Bitbucket, SVN, etc.

Pros

  • Support for .env files
  • Support for Gitsync

Cons

  • Process to test the API is a bit lengthy

What’s Next?

Now that you’re equipped with your APIs, you can use Appsmith to create full-fledged applications by connecting your data to our extensive repository of pre-built UI widgets like forms, buttons, lists, maps and so much more. And since Appsmith is a GUI based platform, you can drag and drop these widgets to create your applications. You can also invite your colleagues to collaborate with them and then deploy it to be shared with internal or external users.

Psst! You can 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!

610002bbe68fa271933cee6e_ABK8x87Kne_y4oROzmhaoMwpShsXIcXMr_VubVdsoztQaUGrIxFdgKnwTNUm_Pb4vEDGDNjuVk1t0UgKWrOWSKaZ5pF1HIWNdqm4kqNg6_nPuTZTgaXEAJepVbZKRuW3SHAdmi4u.png

Also, by running CURL commands directly on the platform, you can test and deploy your apps easily and quickly.

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!

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!