12 Days of DigitalOcean - Setting Up a PostgreSQL Database for Birthday Reminders

The holidays are the perfect time to try something new, especially when there’s a little extra downtime. That’s why we’re kicking off 12 Days of DigitalOcean, a fun learning series where we’ll build two real-world apps step by step. Over the next 12 days, you’ll learn how to combine DigitalOcean services to solve practical problems while building something useful.

Here’s what we’ll create:

1. Birthday Reminder Service: Sends SMS reminders for birthdays and anniversaries—without cluttering your calendar.
2. Email Receipt Processor: Automatically extracts key details from emailed receipts and stores them in a database.

These apps are simple, useful, and fun to build. Plus, they’ll show you how DigitalOcean’s tools—like databases, serverless functions, and object storage—can come together in real-world use cases.

Today, we’re starting with the Birthday Reminder Service by setting up a PostgreSQL database to store contacts. Let’s dive in!

✨ Building Birthday Reminder Service

We all want to remember important dates, but keeping every birthday or anniversary on your calendar can get messy fast. This app solves that problem with a lightweight service that sends you an SMS reminder at just the right time.

By the end of this series, you’ll have an app that’s useful, easy to maintain, and keeps your calendar clean.

🚀 What You’ll Learn

Here’s the plan for today:

1. Set up a PostgreSQL database on DigitalOcean.
2. Connect to it using the psql command-line tool.
3. Create a table to store contact details like names, and birthdays.
4. Add sample data to test your setup.

This database will serve as the foundation for our app, keeping everything organized and ready for the logic we’ll add later.

🛠 What You’ll Need

To get started, you’ll need a DigitalOcean account since we’ll be creating our PostgreSQL database using DigitalOcean Managed Databases (sign up here if you don’t already have one).

🧑‍🍳 Recipe for Day 1: Setting Up Your Database

Step 1: Create the Database 🗂

• Log in to your DigitalOcean dashboard.
• Navigate to the Databases section and create a new PostgreSQL database.
• Choose the smallest available plan for now (perfect for experimenting).
• Save your database credentials (hostname, username, password, and database name)—you’ll need these in the next step!

Step 2: Connect to the Database 🔗

There are a few different ways you can connect to a PostgreSQL hosted on DigitalOcean. You may choose to use a GUI tool like pgAdmin, or TablePlus. For this tutorial, we’ll focus on using psql - a lightweight command-line client that works across platforms, but feel free to follow along using your favorite tool.

If you haven’t already installed psql, head over to the PostgreSQL official download page for instructions on installing it for your operating system.

Once it’s ready, connect to your database using the credentials from Step 1 above. In the terminal, type:

psql -h <hostname> -U <username> -d <database_name> -p 5432

If you see the psql prompt, you’re connected! 🎉

Step 3: Create the Contacts Table 📋

With your database ready, let’s create a table for storing contact details. Run this SQL command in your psql session:

CREATE TABLE contacts (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    birthday DATE
);

This table includes:

• id: A unique identifier for each contact.
• first_name: The first name of the contact.
• last_name: The last name of the contact.
• birthday: The date of their birthday.

And that’s it—you’ve got a contacts table ready for action.

Step 4: Add Sample Contacts 📝

Now that your table is ready, let’s insert some sample data to test it out. Run the following INSERT commands in your psql session:

INSERT INTO contacts (first_name, last_name, birthday) 
VALUES ('Alice', 'Smith', '1990-05-15');

INSERT INTO contacts (first_name, last_name, birthday) 
VALUES ('Bob', 'Johnson', '1985-11-23');

INSERT INTO contacts (first_name, last_name, birthday) 
VALUES ('Charlie', 'Brown', '2000-01-10');

You can verify that the data was added successfully by running:

lSELECT * FROM contacts;

This should display a list of the contacts you just added. 🎉

(Optional) Step 5: Try a GUI 🖥

If you prefer managing databases with a GUI, here are two great options:

• pgAdmin: A free, open-source tool for PostgreSQL
• TablePlus: A modern, user-friendly option for database management.

Both tools let you connect to your DigitalOcean PostgreSQL instance, create tables, and manage data without needing to write SQL commands directly.

Here’s an example of TablePlus in action, showing the contacts table we created earlier:

(Optional) Step 6: Secure Your Database 🔒

Databases should always be secured to prevent unauthorized access. To keep things secure, go to the Trusted Sources section in your database settings on DigitalOcean and add your local machine’s IP address. This step ensures only your computer can access the database while you’re developing. For more details on advanced security, check out How to Secure PostgreSQL Managed Database Clusters.

🎁 Wrap-Up

Here’s what you accomplished today:

✅ Set up a PostgreSQL database on DigitalOcean.
✅ Connected to it using psql.
✅ Created a table to store contact details.
✅ Added some sample data to test your setup.
✅ Secured your database with trusted sources.

Up next: Tomorrow, we’ll connect this database to a Python script and start adding the logic to send SMS reminders. See you then!