PostgreSQL: From Idea to Database - Introduction

Databases are a critical component for nearly any modern application. The database is also a component that often is surrounded by confusion and apprehension.

This post is the first of the series PostgreSQL: From Idea to Database. The goal of this series to provide a guideline to how we approach database development at RustProof Labs. This includes covering both methodology and technology. This series provides working code examples, friendly explanations, and real-world database design scenarios. Hopefully this series helps explain and teach database design is a new, friendly format.

Project for reference

A major challenge with teaching database design is when you don't have a real project to use as an example. For this, I've chosen to use The PiWS project, an open source weather station designed around the Raspberry Pi. To read more about the PiWS, see my introductory post.

The PiWS project was chosen for a variety of reasons, but there are three main reasons:

• I know the code
• The project was developed in a way worth describing
• It's open source!

The third reason is a big benefit because the source code used in this series will come from the project's GitHub repository. This gives us a real project with real code to study how to approach the task of designing a database.

Throughout this series I provide examples of how the PiWS project was initially designed and how it has evolved.

Tools for Database Development

This list provides a look at the main tools (software) I use throughout any given development project. Each item has its purpose in the development work flow. The specific products I have chosen are not required, but the role that product fills is required. For example, we have chosen to use PostgreSQL. You may want to use MS SQL Server and that is OK. This also means that if you just don't like Jira for some reason, you don't get to simply cut out project management!

• Project Management: Jira
• Source Control: Git, GitHub, and BitBucket
• Database: PostgreSQL
• Database change management Sqitch
• Database backups: pgBackRest
• Design / Documentation: Draw.IO
• Configuration management: Ansible

The following sections provide a brief overview of each of these tools. These will be discussed in greater length throughout the series.

Project Management

I firmly believe in planning, designing and project management. Atlassian's Jira is our tool at RustProof Labs for that job. Jira, known as a "bug tracker", is software built for developers that allows us to track and categorize feature ideas, bugs, and other details. It's our tool for workload planning and prioritizing our outstanding project work.

Best practice: Do not write code without a corresponding issue in a bug tracker.

Source Control

Source control is another required tool for developers. Source control enables efficient and accurate change management for code. There are more reasons than I can count of why every developer should (must) use source control! Here are a couple resources on source control. I learned Git from Pro Git, though now they're on version 2!

Draw.IO

Draw.IO is another tool we use extensively during the planning and designing phase. It is a free browser-based tool that provides the ability to create good looking diagrams, such as Entity Relationship Diagrams (ERD) using a browser-based interface.

The following picture illustrates a table diagram planned to store weather data collected from the PiWS sensors. It includes the table observation that links to two other tables, time and calendar.

This type of tool and document is invaluable during the design process for a few reasons. First, it's very quick and easy to get a design like the above drafted and shared with the team for discussion. Discussion is the next benefit. This type of diagram should be used in team meetings to talk about what data you are capturing and how things relate to each other.

Ansible and Sqitch

Ansible and Sqitch are both tools used to help automate deployments. Ansible is the main tool for deploying configurations and changes while Sqitch is specific to deploying database changes. These two tools are used very closely together, including using Ansible to automate Sqitch database deployments.

The theme of these tools is automation. Humans are prone to error, especially during tedious tasks like deployments. Luckily for us, computers and software are great at taking over these tedious, error prone steps that both increases productivity and reduces the risk of error.

PostgreSQL and pgBackRest

PostgreSQL is the database server chosen for the PiWS project for a number of reasons. It's our standard database platform at RustProof Labs, so that's an influence. Further, our goal with the PiWS was a low cost, low power application. PostgreSQL runs surprisingly well, even on the tiny Raspberry Pi Zero W, with a single 900 MHz core and 512 MB RAM.

One of the most important things about databases are backups. It's widely agreed upon that you do not have a backup until that backup is tested and able to be restored. pgBackRest is a rock solid backup and restore tool for PostgreSQL.

Database backups, and testing the restore process, are not optional.

Summary

This post set the stage for the PostgreSQL: From Idea to Database series. We've established our project and defined the tools to use, at least at a very high level.

The remainder of the series will focus on one topic or phase of the project. The next post will cover the initial phase of the project: the design phase.

Thanks for reading!