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.

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!

JUnit Test With Containerized MySQL database (using IntelliJ IDEA)
25
January
2021
Resources

JUnit Test With Containerized MySQL database (using IntelliJ IDEA)

JUnit Test With Containerized MySQL database (using IntelliJ IDEA)
Sumit Kumar
0
 minutes ↗
#
mysql
#
testing
#
junit
#
java
#
ides
Resources

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.

In case you use Maven to manage dependencies in your project, as used in Appsmith , you can add the following snippet in your POM file to include all the required packages:

    org.testcontainers
    testcontainers
    1.15.1
    test

To create a new MySQL testcontainer instance with JUnit 4 you may follow these steps as used in Appsmith's unit test file to test its MySQL plugin:

public static MySQLContainer mySQLContainer = new MySQLContainer("mysql:5.7")
            .withUsername("username")
            .withPassword("password")
            .withDatabaseName("test_db");

Please note that Testcontainers framework is different for JUnit4 and JUnit5. Please use the framework as per the JUnit version that you have used. For more details please see Testcontainers page.

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.

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. Fetch your credentials. You may read the credentials from the testcontainer using the following API when using with JUnit 4.

address  = mySQLContainer.getContainerIpAddress();
port     = mySQLContainer.getFirstMappedPort();
username = mySQLContainer.getUsername();
password = mySQLContainer.getPassword();
database = mySQLContainer.getDatabaseName();

6. Test your connection and save credentials.

Screenshot 2020-12-17 at 11.17.17 AM.png

7. Run query.

Screenshot 2020-12-17 at 11.19.44 AM.png

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:

  1. Add dependency for JUnit package.
  2. Add dependency for testcontainers package.
  3. Write code snippet to start a containerized instance of your desired database.

The steps to investigate the containerized database instantiated above can be generalized as follows:

  1. Add a debug point after the container is instantiated but before the test ends.
  2. Start the test and wait till the execution stops at the debug point.
  3. Use the database tool to connect to the containerized database and run queries on it.

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.