Write a Bash Script Accessing SQL Databases Remotely Or Locally

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 mysql, postgres, 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.