Setting up a simple address book with MySQL and Bash

An address book in a spreadsheet works fine, until you want to link it to other scripts or automations. A simple MySQL database gives you more flexibility - and it's a good way to get familiar with databases and Bash scripting.

In this example, the database runs on a Synology NAS with MariaDB, but it works on any MySQL/MariaDB instance.

The full code is on GitHub: github.com/siekman-io/contacts

Requirements

• MySQL client on your machine (macOS, Linux)
• A running MySQL or MariaDB server
• A user with rights to create databases

Installing MySQL client on macOS

/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"
brew update
brew install mysql client

Testing connection

First, check that you can connect to the database:

mysql -u username -p -h 10.0.0.1

Replace 10.0.0.1 with the IP of your server (or localhost if the database is running locally).

If you see the mysql> prompt, the connection is OK. Exit the client with exit.

Setting up project structure

Create a folder for the scripts and configuration:

mkdir ~/contacts
cd ~/contacts

The folder contains four files:

contacts/
├── config # database data
├── contacts.sql # table schema
├── create_db.sh # create database
└── add-contact.sh # add contact

Configuration file

Create a file config with your database details:

#!/bin/bash
HOST=10.0.0.1
DB=addressbook
TABLE=addressbook
USER=username
PASSWORD=password

Database schema

Create contacts.sql with the table structure:

CREATE TABLE address book (
    ID INT AUTO_INCREMENT PRIMARY KEY,
    First name VARCHAR(50),
    Insertion VARCHAR(20),
    Last name VARCHAR(50),
    Street name VARCHAR(100),
    House number VARCHAR(10),
    Postal code VARCHAR(10),
    City VARCHAR(50),
    Country VARCHAR(50)
);

Create database

Create create_db.sh:

#!/bin/bash
source config
mysql -u$USER -p$PASSWORD -h $HOST -e "CREATE DATABASE $DB"
mysql -u$USER -p$PASSWORD -h $HOST $DB < contacts.sql
echo "Database created."

Give execution rights and run the script:

chmod +x create_db.sh
sh create_db.sh

Add contact

Create add-contact.sh. This script interactively requests the data and writes it to the database:

#!/bin/bash
source config

read -p "First name: " first name
read -p "Insertion: " insertion
read -p "Surname: " surname
read -p "Street name: " street name
read -p "House number: " house number
read -p "Postcode: " postcode
read -p "City: " place of residence
read -p "Country: " country

mysql -u$USER -p$PASSWORD -h $HOST $DB -e "
INSERT INTO $TABLE
  (First name, Middle name, Last name, Street name, House number, Postcode, City, Country)
VALUES
  ('$first name','$insert','$last name','$street name','$house number','$postcode','$residence','$country')
"
echo "Contact saved."

chmod +x add-contact.sh
sh add-contact.sh

Result

After adding a contact, you can check the data via phpMyAdmin or directly via the terminal:

mysql -u$USER -p$PASSWORD -h $HOST $DB -e "SELECT * FROM address book;"

Next steps

This is a deliberate initial design. Expansion options:

• list-contacts.sh - show all contacts with filtering
• search-contact.sh - search by name or postal code
• delete-contact.sh - delete by ID
• Remove password from config and arrange it via .my.cnf so it is not in plain text