Chapter 4: Connecting to Databases

In this chapter, we will finally connect a database to Metabase and start exploring data. This chapter is all about databases. Metabase is designed to connect to most of today's most popular relational, NoSQL, and analytical databases, and we will learn a little about each one.

We will also learn how to launch our own PostgreSQL database and load it with the sample data we will be exploring throughout the rest of the book. In addition to preparing us with a dataset to use in future chapters, this chapter is intended to help you learn the best practices when working with databases.

We will cover the following topics in this chapter:

• What is a database?
• Creating a PostgreSQL database in AWS
• Connecting to our PostgreSQL database in Metabase
• Connecting to a database with best practices
• Connecting to other types of databases in Metabase

Technical requirements

This chapter is the most technically challenging chapter in the book. As in the last two chapters, we will be mostly using Amazon Web Services (AWS). We will also be using the terminal a lot, making use of utilities such as ssh, psql and git. Finally, we will be connecting to a PostgreSQL database, both in Metabase and via the terminal.

What is a database?

While a thorough overview of databases is beyond the scope of this book, let's learn (or for some readers, refresh ourselves) about what databases are. According to Wikipedia, "a database is an organized collection of data, generally stored and accessed electronically from a computer system" (https://en.wikipedia.org/wiki/Database). Databases have been around for decades, and today, there are many different types of databases for different use cases.

Traditionally, the most common type of database has been the relational database. Relational databases are made up of tables, which can be thought of as spreadsheets. Tables are rectangular, meaning they have rows and columns. The relational part comes from how the tables link to one another, as depicted in Figure 4.1. Note how in Figure 4.1, we have an orders table and an users table, linked together by id_user. Rather than store all the user information for each order (such as name...

Creating a PostgreSQL database in AWS

In this section, we'll be creating a PostgreSQL database in AWS, downloading a utility called psql to connect to it, and finally, loading some sample data into it. Let's get started.

PostgreSQL is a popular open source database. It's actually what is running behind the scenes in our Metabase instance as the application database. In that case, it was created automatically by the Elastic Beanstalk configuration. Here, we will create one from scratch, using Relational Database Service (RDS) in AWS. Let's get started:

Important Note

You get 750 hours of RDS a month on the Free Tier. Since we're already using one of these to power our Elastic Beanstalk app, you will now be charged if you leave both running all month long. As of the time of writing, a db.t2.micro instance costs around USD 0.03 per hour.

1. Log in to AWS as the metabase-admin IAM user and find the RDS service in the AWS Management Console.
2. Click...

Connecting to our PostgreSQL database in Metabase

Connecting to our database in Metabase is very similar to how we connected to it on the command line, except it is more user-friendly. To get started, open the admin panel in Metabase:

1. From the top menu bar, click Databases.
2. You should have one database already, the sample dataset that comes with Metabase.
3. Click Add Database.
4. PostgreSQL should be the default option from the dropdown. If not, choose it.
5. For Name, enter Pies. This is just a user-friendly name that Metabase will use.
6. Under Host, enter the endpoint from RDS.
7. For Port, use 5432, which is the standard port for PostgreSQL traffic.
8. The database name will be pies.
9. The database username will be postgres.
10. Enter the password you made when you created the database.
11. Keep the Enabled slider on.
12. Click Save.

At this point, Metabase will store the connection parameters for our database. It will automatically start scanning...

Connecting to a database with best practices

Up to this point, we've learned how to launch a PostgreSQL database with AWS, load data into it, and connect it to Metabase. If you plan on using Metabase in an existing organization, chances are that there will already be a database full of data for you. At the same time, it's unlikely that the database your organization uses to store data can be connected to in the same way as we learned previously. This is unlikely for two main reasons:

• Most databases are not publicly accessible. Recall that in the last section, we made our database publicly accessible. That was to make loading data and connecting it to Metabase easy. Generally, it's a bad idea to make a database publicly accessible, as it lets anyone attempt to connect to it.
• Doing analytics on a production application database is dangerous. Your application database is what is serving critical information to your application. Sending queries to it for analytical...

Connecting to other types of databases in Metabase

Let's get to know all the other types of databases we can connect to in Metabase.

Relational databases

Besides PostgreSQL, which we've already covered extensively, the other relational databases you can connect to with Metabase are as follows.

MySQL

To connect to a MySQL database, choose MySQL from the list of database types in the Database section of the Metabase admin panel. Connecting to MySQL is nearly identical to PostgreSQL. The standard port for MySQL is 3306.

MariaDB

Although not listed in the database types, you can connect to a MariaDB database with Metabase using the MySQL option.

Microsoft SQL Server

To connect to Microsoft SQL Server, choose SQL Server from the list of database types in the Database section of the Metabase admin panel. Connecting to SQL Server is nearly identical to PostgreSQL. The standard ports are 1433 and 1521.

Amazon Aurora

Amazon Aurora is a relational database...

Summary

In this chapter, we learned the best way to connect a database to Metabase. We learned specifically how to create our own PostgreSQL database, fill it with sample data, and connect that database to Metabase. We then covered database best practices and learned how to set up a bastion host to connect to a private database.

In addition to looking deep into PostgreSQL-related examples, we learned about the other types of databases that Metabase can connect to.

In the next chapter, we'll learn how to build a data model and dictionary in Metabase for our newly connected database. This will make our data easy to understand and explore for our less technical users.