Home » BASH » Write a bash script accessing SQL databases remotely or locally

Write a bash script accessing SQL databases remotely or locally

Last updated May 17, 2021

In this article, we are going to learn how to automate SQL queries by connecting to a server using a shell script. Bash scripting is used for automating things.

Prerequisites

Make sure that mysqlpostgres, and sqlite are installed. Ensure that the user is created in MySQL and that you have granted permission to that user.

Write Script:

MySQL queries in script: We are going to write a script called mysql_version.shto get the latest version of MySQL:

#!/bin/bash 
mysql -u root -pTraining2@^ <<MY_QUERY 
SELECT VERSION(); 
MY_QUERY

Now, we are going to create a script called create_database.sh to create the database:

#!/bin/bash 
mysql -u root -pTraining2@^ <<MY_QUERY 
create database testdb; 
MY_QUERY

SQLite queries in script: Now, we are going to create a sqlite database. You can create the sqlite database by simply writing sqlite3 and a name for the database. For example:

$ sqlite3 testdb

Now, we are going to create a table in the sqlite console. Enter sqlite3 testdb and press Enter—you will see the sqlite3 console. Now, write the create table command to create a table:

$ sqlite3 testdb 
SQLite version 3.11.0 2016-02-15 17:29:24 
Enter ".help" for usage hints. 
sqlite> .databases 
seq name file 
--- --------------- ---------------------------------------------------------- 
0 main /home/student/testdb 
sqlite> CREATE TABLE bookslist(title text, author text); 
sqlite> .tables 
bookslist

Postgres queries in scripting: Now, we are going to check the postgresql database version. Here, testdb is our database name, which is what we created earlier. For that, run the following command:

student@ubuntu:~$ sudo -i -u postgres 
postgres@ubuntu:~$ psql 
psql (9.5.13) 
Type "help" for help. 
postgres=# create database testdb; 
CREATE DATABASE 
postgres=# \quit 
postgres@ubuntu:~$ psql testdb 
psql (9.5.13) 
Type "help" for help. 
testdb=# select version(); 
			version 
------------------------------------------------------------------------------------------- 
PostgreSQL 9.5.13 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.9) 5.4.0 20160609, 64-bit 
(1 row)

Now, we are going to create a table. For that, run the following command:

postgres@ubuntu:~$ psql testdb 
psql (9.5.13) 
Type "help" for help. 
testdb=# create table employee(id integer, name text, address text, designation text, salary integer); 
CREATE TABLE 
testdb=#

How it works

  • We are creating bash scripts to check the version of a database and to create a new database. In these scripts, we are using the root user and there is a password present right after -p for that user. You can use the root user or else you can create a new user, assign a password to it, and use that user in your scripts.
  • SQLite software provides us with a simple command-line interface. Using this interface, we can manually enter and execute SQL commands. You can list the database using the dot (.) operator. .databases and .tables are used to list all the tables in the database.
  • In PostgreSQL, first we are changing the user from student to postgres. Then, we enter psql to start the postgres command-line console. In that console, we must create the testdb database. To come out of the console, run the \quit command. Now, once again, start the testdb console by typing psql testdb and pressing Enter. Now, create a table in that database.

Related Posts

Creating a config file and using it in tandem with your scripts

In this article, we are going to create a config file and use it in our shell script.PrerequisitesBesides having a terminal open, you need basic knowledge of creating scripts and config files.Write scriptNow, we are going to create a script and config file. The...

Calculating and reducing the runtime of a script

In this article, we are going to learn how to calculate and reduce the script’s runtime. A simple time command will help in calculating the execution time.PrerequisitesBesides having a terminal open, make sure you have the necessary scripts present in your...

Using Bash to monitor battery life and optimize it

In this article, we will learn about the TLP Linux tool. TLP is a command-line tool; it is used for power management and will optimize the battery life.PrerequisitesBesides having a Terminal open, you need to ensure that you have TLP installed on your system.How to do...

Creating a simple NAT and DMZ firewall using bash script

In this article, we will create a simple NAT firewall with DMZ using iptables.PrerequisitesBesides having a Terminal open, you need to ensure that iptables is installed in your machine.Write scriptWe will write a script to set up a DMZ using iptables. Create...

Follow Us

Our Communities

More on BASH

The Ultimate Managed Hosting Platform
Load WordPress Sites in as fast as 37ms!

0 Comments

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

2 × 3 =

Shares