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.
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.
Here’s what the configuration would look like:
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.
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.
First, let’s read our seed data from the database and display it on a beautiful table widget. Follow the below steps:
We now have our query; let's bind this onto the table widget; for this follow the below steps:
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.
To add the create operation on MariaDB, let’s make UI.
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:
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:
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.
Let’s write the Edit query using SQL:
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:
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:
Now, let’s write the Delete query using SQL:
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.
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):
In this article, we will display data from our MySQL database to our app on Appsmith.
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:
After that, click on the Build with Drag & Drop widget. You’ll see this:
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:
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:
Once you select the data source (for example, MySQL), you now need to enter your credentials:
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:
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:
Once you click the “+ New Query” button, you now need to click on the “Select” option from the list to run the SELECT queries:
This will open a MySQL editor where you need to paste the below query:
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.
Hover the cursor on "public.orders" and click on the “Add” button and select the SELECT command option. Now, paste the below query:
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.
Hover the cursor on “public.orders” and click on the “Add” button and select the SELECT command option. Now, paste the below query:
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.
Hover the cursor on “public.employees,” and click on the “Add” button and select the “SELECT” command option. Now, paste the below query:
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.
Hover the cursor on “public.standup” and click on the “Add” button and select the “SELECT” command option. Now, paste the below query:
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.
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!
You can check out the live example here also.
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.
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.
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:
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 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.
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.
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.
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.
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.
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!
JUnit is one of the most popular unit testing frameworks used with Java to create repeatable tests. With JUnit each test is written as a separate method inside a Java class. IntelliJ IDEA provides an option to run these test cases from within the IDE.
In case you have a module that communicates with a MySQL database, you can unit test the module by providing it access to a MySQL server running inside a testcontainer. You can also configure this MySQL database instance with your desired username, password and database name (in MySQL server) using the API provided by Testcontainers framework.
Databases spwaned using Testcontainers when run from within the IDE can seem to 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 to connect to the MySQL database:
1.Add a debug point in the code such that the testcontainer has been brought up at this point.
2. Run the test program using debug mode and wait till it stops on the break point.
3. Click on the database tool.
4. Select your database type.
5. Fetch your credentials. You may read the credentials from the testcontainer using the following API when using with JUnit 4.
6. Test your connection and save credentials.
7. Run query.
It is noteworthy that Testcontainers provide containerized instances of many other popular databases as well, like Postgres, MongoDB and Neo4j. Similarly, IntelliJ IDEA's database tool also provides connectivity support for most of the popular databases. The steps described above, to integrate the testcontainers package or to investigate the containerized database, can be used with databases other than MySQL as well. In summary, the steps to write a unit test using JUnit and any testcontainer can be generalized as follows:
The steps to investigate the containerized database instantiated above can be generalized as follows:
In case you need access to more code examples to see the above steps in usage, do checkout the test files in Appsmith's GitHub repository. I hope this article was useful to you and do share your feedback in comments.