SQL Processor CLI

This project provides a command-line tool for processing SQL files on a PostgreSQL database. The tool is packaged as a Docker container for easy deployment and usage.

Usage of CLI

1. Build the SQL Processor CLI:

   export SSL_MODE=disable
   cd cmd/sqlprocessorcli
   go build
   ./sqlprocessorcli <filename.sql>

2. Configuration:

   • Use the following environment variables to configure the SQL Processor CLI:
   • SSL_MODE: Set to "disable" for an insecure connection or "require" for a secure connection. Defaults to "disable" if not set.

3. Dependencies:

   • github.com/lib/pq: PostgreSQL driver for Go.

4. Replace yourusername, youruser, yourpassword, and yourdbname with the appropriate values. The SslMode is set based on the environment variable SSL_MODE. If the system administrator does not provide the configuration, the default value is "disable", and a warning message is displayed.

Usage of docker-compose.yml

The docker-compose.yml file defines a single service named sqlprocessor. It uses the sqlprocessor-cli Docker image and builds it from the current directory. The SSL_MODE environment variable is set to disable. The ./sqlfiles directory is mounted as a volume to the /sqlfiles directory inside the container.

1. Build the Docker image:

   docker-compose build

2. Create a ./sqlfiles directory and place the .sql file you want to process.
3. Run the sqlprocessor service with Docker Compose:

   docker-compose up -d

4. The sqlprocessor service will process the .sql file in the ./sqlfiles directory.

Configuration You can configure the sqlprocessor service by setting environment variables in the docker-compose.yml file.

SSL_MODE: Set the SSL mode for the PostgreSQL connection. Options are disable, allow, prefer, require, verify-ca, and verify-full. The default is disable.

Install and run a local PostgreSQL database

1. Linux (Ubuntu)

   • Update the package lists and install the PostgreSQL server:

   sudo apt update
   sudo apt install postgresql postgresql-contrib

   • Start the PostgreSQL service:

   sudo systemctl start postgresql

   • Enable the PostgreSQL service to start on boot:

   sudo systemctl enable postgresql

   • Change to the postgres user and access the PostgreSQL shell:

   sudo -u postgres psql

2. macOS

   • Install the PostgreSQL server using Homebrew:

   brew install postgresql

   • Start the PostgreSQL service:

   brew services start postgresql

   • A homebrew install requires a default user:

   /usr/local/opt/postgresql\@14/bin/createuser -s postgres

   • Access the PostgreSQL shell:

   psql postgres

3. Windows

   • Download the Windows installer for PostgreSQL from the official website: https://www.enterprisedb.com/downloads/postgres-postgresql-downloads
   • Run the installer and follow the on-screen instructions to install PostgreSQL.
   • After the installation is complete, the PostgreSQL service should start automatically. You can manage the service using the "pgAdmin" GUI tool that was installed with PostgreSQL.
   • To access the PostgreSQL shell, open the "SQL Shell (psql)" application from the Start Menu.

Setup a PostgreSQL database, user, & privileges via terminal

Regardless of the operating system, once you are in the PostgreSQL shell, you can create and manage databases, users, and execute SQL commands. For example, to create a new database and user, follow these steps:

1. Create a new database:

   CREATE DATABASE onet;

2. Create a new user with a password:

   CREATE USER onet WITH PASSWORD 'fathomrocks';

3. Grant privileges to the new user on the new database:

   GRANT ALL PRIVILEGES ON DATABASE onet TO onet;

4. Exit the PostgreSQL shell:

   \q
   

Authenticate access to postgresql with pgAdmin

pgAdmin is a popular open-source administration and management tool for the PostgreSQL database. Here's how to connect to a PostgreSQL database using pgAdmin:

1. Install pgAdmin if you haven't already:

   • Linux (Ubuntu): You can install pgAdmin using the following commands:

   sudo apt install pgadmin4

   • macOS: Install pgAdmin using Homebrew:

   brew install --cask pgadmin4

   • Windows: The pgAdmin installer is bundled with the PostgreSQL installer. If you've already installed PostgreSQL using the installer, you should have pgAdmin installed as well.

2. Launch pgAdmin:

   • Linux: Run pgadmin4 from the terminal or search for it in your applications menu.

   • macOS: Search for "pgAdmin" in your applications, or run open /Applications/pgAdmin\ 4.app in the terminal.

   • Windows: Search for "pgAdmin" in the Start menu and open it.

3. Add a new server connection:

   • In the pgAdmin application window, go to the "Browser" panel on the left side, right-click "Servers" and select "Create" > "Server".

   • In the "Create - Server" window, switch to the "General" tab, and provide a name for your connection (e.g., "Local PostgreSQL").

4. Switch to the "Connection" tab, and fill in the following fields:

   • Hostname/address: Enter "localhost" if the PostgreSQL server is running on the same machine as pgAdmin, or provide the IP address or hostname of the remote server.
   • Port: The default PostgreSQL port is 5432, but enter the appropriate port if it's different.
   • Maintenance database: Enter "postgres" (the default maintenance database) or the name of the specific database you want to connect to.
   • Username: Enter the username of the PostgreSQL user you want to use for this connection.
   • Password: Enter the password for the PostgreSQL user.
   • Check the "Save password" box if you want to save the password for future connections.
   • Click the "Save" button to create the connection.

5. Explore the connected server:

   • In the "Browser" panel, you should now see the new server connection listed under "Servers". Click the arrow next to it to expand the server's tree view.
   • Expand "Databases" to see a list of available databases. You can interact with the databases, tables, and other objects by right-clicking them and selecting the desired operations from the context menu.

With pgAdmin, you can manage databases, tables, indexes, and other database objects, as well as run SQL queries, import/export data, and more.