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’
![](https://www.siekman.io/wp-content/uploads/2023/05/image.png)
Now everything is ready , you can download the files from GitHub or create them yourself
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
![](https://www.siekman.io/wp-content/uploads/2023/05/image-1-1024x209.png)
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
![](https://www.siekman.io/wp-content/uploads/2023/05/image-2-1024x377.png)
Hope this whas helpful , questions can be asked.