Bit.io

It’s about time a web database system was designed for usability

Finding a relational database for business or education sounds easy but, in many instances, it is not. There may be connection, configuration, or installation challenges creating a new database. Charges for use may be difficult to understand. For those of us who teach data science, a relational database must be affordable and intuitive.

There are a variety of online, client-server, and client databases available but are they affordable and intuitive? In most cases, the answer is no. For example, MySQL is a popular free and open-source relational database. However, there are multiple installation choices even among the Community Version that gives you ten choices to choose from. SQLite is another option, but databases created would be difficult to share and analyze remotely.

It was by serendipity that I discovered bit.io, a new (beta) online PostgreSQL database. The main page says it all: there are three ways to begin — drag and drop a new file to create a new database (repo)(schema), add a URL or start with an empty database. See the image below.

Bit.io accepts the following file types: csv, xls, xlsx, json, and zip. After uploading your files, you will encounter the main webpage with features I have labeled 1–12. The file I chose was a comprehensive database of about 5000 patients from NHANES consisting of 9 tables. The following image shows the options.

Features:

1. A dropdown list of available databases (repos) you created
2. The choice to set the database (repo) as public or private
3. Share button to invite others to view your data
4. Connect button to allow remote programs to connect to the repo
5. List of tables in the current repo
6. Name of repo and description using Markdown language
7. Ability to add documentation to the table or columns. For example, a data dictionary
8. Where you can drag and drop files to create a new repo
9. Table data view
10. SQL script editor — where you enter SQL script and then run the query
11. Plot data option
12. Chat option which is a useful feature for inexperienced users

A SQL query is straightforward with the exception that you must enter both the name of the owner and the name of the table in double quotes (“owner/repo”.”table”). The next image shows a query to select all subjects over age 60 with a grip strength of 100 or greater. This query should select the healthiest of older subjects for further analysis.

Bit.io integrates with multiple external programs such as:

1. Tableau
2. Jupyter Notebook
3. DeepNote — new data science notebook aimed at cloud collaboration and education
4. PopSQL
5. PowerBI
6. Postico
7. DataGrip

The program also can be called up using R or Python programming. Data can also be uploaded using the REST API function and key.

Connection to the database is straightforward. The following screenshot shows the connection window. Each function has a copy button.

The Help menu consists of multiple guides, API instructions, and a discussion section to report bugs or issues. In the guide section, there are multiple “how-to” instructions. In the Connect to Bit.io section, there are 16 ways to connect to their platform. There is a SQL section that provides sample data, common SQL commands, and a PostgreSQL reference document. There is a command line interface tool that is imported as a Python package. Lastly, there is a “recipes and projects” section that covers the following topics: editing tables, programmatic access, IoT and Data Logging, Internal dashboards with Retool, REST API, CRUD to-do list App, and building a Grafana dashboard using bit.io.

The pricing model is as follows. There is a free version with the following stipulations — up to 10 million rows per month, unlimited repositories with up to 3 collaborators, and free storage on active repositories. There is a waived $5/month option that can have unlimited collaborators and web support. There is an enterprise and team (pending) option as well.

Conclusion

Bit.io is a new web-based database platform that is comprehensive yet elegantly intuitive. The graphical user interface (GUI) is highly functional and well organized. For those new to databases or who are seeking a good educational platform, bit.io is a welcome addition.