Setup a simple adresbook database

I wanted to understand how I could setup a quick database for some data. What is easier than setup an address book in a database. In this example I will show you how it can be done.

First of all you need to have a CLI with mysql-client installed and a database server , for example a Synology Nas with MariaDB installed on it. Create a user on it with the rights to create and alter databases

I work on a Mac, I installed with homebrew mysql-client so if you need that please go to terminal

if you don’t have installed homebrew yet , all you have to to on the command line is the following command

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

After installing update brew and install mysql-client

brew update
brew install mysql-client

After installing you need to check if your are able to connect to your database with the following command. Change the username and host and try to connect.

mysql -u username -p -h host

When connected you see the following, when connected you can exit with the command ‘quit’

Now everything is ready , you can download the files from GitHub or create them yourself

Link to Github Repo

For manually follow the next steps

Step 1: Make a directory contacts.

mkdir ~/contacts
cd contacts

Step 2 : Create the files.

---

First we create the config file, change the values like host, database username and password:

nano config

## Paste the following and exit with CTRL X

#!/bin/bash

######################################################
#        _      _                            _
#    ___(_) ___| | ___ __ ___   __ _ _ __   (_) ___
#   / __| |/ _ \ |/ / '_ ` _ \ / _` | '_ \  | |/ _ \
#   \__ \ |  __/   <| | | | | | (_| | | | |_| | (_) |
#   |___/_|\___|_|\_\_| |_| |_|\__,_|_| |_(_)_|\___/
#              Add Contact to database
#              Created by Jouke Siekman
#             Netherlands 2023 Leerbroek
#####################################################
### CONFIGURATION FILE ###

## CONFIGURATION
HOST=localhost
DB=<FILL IN DBNAME>
TABLE=adresboek
USER=<FILL IN USERNAME>
PASSWORD=<FILL IN PASSWORD>

---

Then we create the sql file.

nano contacten.sql

## Paste the following and exit with CTRL X
--
-- Table structure for table `adresboek`
--

DROP TABLE IF EXISTS `adresboek`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `adresboek` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Voornaam` varchar(256) NOT NULL,
  `Tussenvoegsel` varchar(256) NOT NULL,
  `Achternaam` varchar(256) NOT NULL,
  `Straatnaam` varchar(256) DEFAULT NULL,
  `Huisnummer` varchar(265) NOT NULL,
  `Postcode` varchar(6) DEFAULT NULL,
  `Woonplaats` varchar(256) DEFAULT NULL,
  `Land` varchar(256) DEFAULT NULL,
  UNIQUE KEY `ID` (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

---

Now we create the create_db file

nano create_db.sh

## Paste the following and exit with CTRL X

#!/bin/bash

######################################################
#        _      _                            _
#    ___(_) ___| | ___ __ ___   __ _ _ __   (_) ___
#   / __| |/ _ \ |/ / '_ ` _ \ / _` | '_ \  | |/ _ \
#   \__ \ |  __/   <| | | | | | (_| | | | |_| | (_) |
#   |___/_|\___|_|\_\_| |_| |_|\__,_|_| |_(_)_|\___/
#              Add Contact to database
#              Created by Jouke Siekman
#             Netherlands 2023 Leerbroek
#####################################################

## GET VARIABLES FROM CONFIG
source config


## CREATE DATABASE
mysql -u$USER -p$PASSWORD -h $HOST -e "CREATE DATABASE $DB"
mysql -u$USER -p$PASSWORD -h $HOST $DB < contacten.sql

---

Create the file add-contact.sh

nano add-contact.sh


## Paste the following and exit with CTRL X
#!/bin/bash

######################################################
#        _      _                            _
#    ___(_) ___| | ___ __ ___   __ _ _ __   (_) ___
#   / __| |/ _ \ |/ / '_ ` _ \ / _` | '_ \  | |/ _ \
#   \__ \ |  __/   <| | | | | | (_| | | | |_| | (_) |
#   |___/_|\___|_|\_\_| |_| |_|\__,_|_| |_(_)_|\___/
#              Add Contact to database
#              Created by Jouke Siekman
#             Netherlands 2023 Leerbroek
#####################################################

## GET CONFIGURATION VARIABLES
source config

## USER INPUT
read -p "Voornaam          [ENTER] : " Voornaam
read -p "Tussenvoegsel     [ENTER] : " Tussenvoegsel
read -p "Achternaam        [ENTER] : " Achternaam
read -p "Straatnaam        [ENTER] : " Straatnaam
read -p "Huisnummer        [ENTER] : " Huisnummer
read -p "Postcode          [ENTER] : " Postcode
read -p "Woonplaats        [ENTER] : " Woonplaats
read -p "Land              [ENTER] : " Land


## DEBUG
#echo $Voornaam


## COMMAND
mysql -u $USER -p$PASSWORD -h $HOST $DB <<EOF
INSERT INTO $TABLE (Voornaam,Tussenvoegsel,Achternaam,Straatnaam,Huisnummer,Postcode,Woonplaats,Land) VALUES ('$Voornaam', '$Tussenvoegsel', '$Achternaam', '$Straatnaam', '$Huisnummer', '$Postcode', '$Woonplaats', '$Land');
EOF

---

Step 3: Set the permissions.

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

Step 4: Execute create the database and add the first contact.

Once that has finished you can create the database with executing the command

sh create_db.sh 

When that’s executed the database is created and the table and columns are filled . No you can add contacts by executing:

sh add-contact.sh and fill the fields. 

It is possible you get a warning about the use of password. That’s able to solve , we will handle that In a next blogpost. It looks like this

When you login with something like phpmyadmin or something locally like sequel ace you can see the posts in the database like the image under here

Hope this whas helpful , questions can be asked.