Skip to content

SQL

SQL (sometimes pronounced sequel) stands for Structured Query Language. In relational databases, this is the language used to create and manipulate them. Without going into much detail, typically in production-grade applications, databases servers are separate (basically it's the whole client-server architecture shebang). However, for learning and personal projects, we can spin our own virtual instances on our own machines.

Throughout my time as a student and now working in university, I am more familiar with MySQL (there's MariaDB too; they're essentially very similar apart from their shared history). I have also now dabbled with PostgreSQL, an open-source RDBMS during my time as a TA in NUS. This page will detail how I download and manage all my database servers in my own machine.

DBngin (Windows and macOS only)

I found out about this tool quite recently... this alongside Herd, actually. This randomly popped up when setting up my recently purchased M4 Pro MacBook Pro, actually. The proposition of being able to manage multiple instances and/or versions of databases, and it being free to use was a no-brainer for me. Normally, for this sort of thing, I believe you'll want to have them containerized, i.e., Docker, basically. However, I believe this solution is a lot more friendly in terms of pre-requisite steps and resource utilization than with Docker. Frank be told, I have still yet to practice this in action (despite actually learning about it in more detail during my Master's degree).. but I digress.

Installing DBngin

Installing DBngin is simple - I used Homebrew again (as with a lot of my programs).

brew install --cask dbngin

You may also choose to download it from the official website. DBngin is quite the nice tool and usable for free (especially when I only work on non-commercial stuff for now).. can't say the same for TablePlus, the app of the same name of the ones who made DBngin in the first place. 😬

You should now be able to view it running in the background. On macOS, it is on the top-right with a cylinder-like symbol. With traditionally installing each database server, it's very often that they will start immediately as soon as you boot up your machine. I appreciate how this is manual with DBngin, so at least starting up my machine will not take too much time.

Installing Database Servers in DBngin

To add a database instance,

Select Database Server to Add in DBngin

Upon selecting the database server instance you wish to add, you will be asked for some optional details if required. Typically the name would matter the most, especially if you were to spin up multiple instances of the same database type, for instance. You can keep the rest unchanged.

DBngin New Server Details

Root User and Password

The root user (postgres in PostgreSQL) in each database server instance created using DBngin has no password. In practice, using just the root user to access the servers is strongly not recommended from a security standpoint. It's the same logic as with root users in an operating system - if manipulating the database and its contents is that easy, one can easily compromise the confidentiality, integrity and availability of the data stored, and that's one way to break users' trust in any application. Having no password set for the root user by default is also another red flag for the same reason.

The next section will detail with accessing the database from the Terminal, which will allow a way to change the password and/or create new users to access the database server from.

Accessing SQL Database CLI from the Terminal

Before proceeding, ensure that these export statements are contained in your .zshrc file:

To include in .zshrc
export PATH=/Users/Shared/DBngin/postgresql/17.0/bin:$PATH    # suppose for PostgreSQL version 17.0
export PATH=/Users/Shared/DBngin/mysql/8.4.7_arm64/bin:$PATH  # suppose for MySQL version 8.4.7 (ARM64)

Running MySQL CLI from DBngin in Terminal

If you installed MySQL using something like Homebrew or the downloadable official installer, there are no extra steps required.

Traditional Terminal Command to Start up MySQL CLI
mysql -u root -p  # as root user
mysql -u henry -p # as user "henry"

# It's the same as this:
mysql --socket /tmp/mysql.sock -u root -p   # as root user
mysql --socket /tmp/mysql.sock -u henry -p  # as user "henry"

The options used in the Terminal commands demonstrated are as follows:

  1. -u specifies the username

  2. -p specifies a password is to be provided

    • Including it will prompt you for the password.
    • In database server instances with a set password for the specific user, this is compulsory. If a password isn't set, you can omit this altogether. If included in this case, just hit Enter immediately when prompted.
    • It's best that you do not type your password as part of the Terminal command, privy eyes are watching!

  3. --socket specifies the socket to use to access the database server instance

To exit the MySQL CLI, type in exit.

Because DBngin allows keeping different instances of SQL versions in your machine, the regular Terminal command to start up the MySQL command line interface (CLI) does not work. The error caused will relay this message:

Error when Starting MySQL CLI
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

Notice the mention of a socket (i.e., mysql.sock), like the other Terminal command mentioned much earlier.

SQL server instances in DBngin

You will need to pay attention to which port your SQL instances are running. I have kept mine to the defaults (by default, MySQL uses port 3306 and PostgreSQL uses 5432). Depending on what port you're using you will need to include it in the socket name used. In my case, instead of mysql.sock, I need to specify mysql_3306.sock instead.

Note that it need not strictly be 3306, it all really depends on what was available to you when spinning up an instance of these SQL servers. For all we know, you may be working on projects that require two different versions of MySQL, or two instances of the same MySQL version. Here would be where you may set a second SQL server instance to use port 3307 instead of 3306, for example.

mysql --socket /tmp/mysql_<port no>.sock -uroot
mysql --socket /tmp/mysql_<port_no>.sock -u <username> -p  # if you have a separate user and/or set a password

# For example, if the set port is 3306, enter this:
mysql --socket /tmp/mysql_3306.sock -u root

If you modified the name of the socket when you created a new database server instance, you will want to use that name instead as accordingly.

Running PostgreSQL CLI from DBngin in Terminal

For PostgreSQL, use this Terminal command.

Traditional Terminal Command to Start up PostgreSQL CLI
psql -U postgres -d <database name>

Enter the password when prompted.

The options used here are as follows:

  1. -U specifies the username
  2. d specifies the database to use in PostgreSQL

  3. Not specifying it would be fine. To specify it later on (or switch databases), use \c.

    \c <database name>

To exit the PostgreSQL CLI, type in \q.

Adding New Users and Changing User Passwords

Individual Installations

Before using DBngin, I had my instance of MySQL installed using Homebrew. The commands for installing MySQL and PostgreSQL using Homebrew are as follows:

Homebrew Install Commands for MySQL and PostgreSQL
brew install mysql          # for MySQL

brew install postgresql@18  # for PostgreSQL
                            # type `brew search postgresql` to view the
                            # versions available for download

# Apparently upgrading `postgresql` using Homebrew can break databases.
# If you meant "postgresql" specifically:
# postgresql breaks existing databases on upgrade without human intervention.

If you chose to install using the official installers downloadable from online, the first few instructions should be applicable to you. Otherwise, skip over to check whether it is officially installed using the Terminal.

MySQL

  1. Head over to this link and download the DMG Archive installer for the MySQL Community Server.
  2. Proceed with the installation up until you reach Configuration stage (this should be the second last part of the installation process).
  3. At the Configuration stage, you will be required to select a Password Encryption type. Select Legacy Password Encryption if you plan on using phpMyAdmin. Otherwise, leaving it at Strong Password Encryption would be more ideal.
  4. Enter in your password of choice.

PostgreSQL

The Postgres app provides a simple installation experience you can use. Alternatively, head over to the official webpage for PostgreSQL and download the DMG installer.

Tools

MySQL Workbench

This was the tool introduced by my lecturer during my undergraduate days. It's something by the same folks behind MySQL.

phpMyAdmin (for MySQL)

phpMyAdmin is a popular tool you will find when dealing with LAMP stacks. It provides an easy way to manage your MySQL/MariaDB databases from the server where your (PHP?) website resides, much more so than dealing with the CLI.

This is a tool you can use if you have PHP installed in your machine - I use Herd for this. Thus, I will include instructions for this case; check here for instructions before proceeding.

Note

If you installed PHP using other tools like XAMPP, WAMP or MAMP (all are obvious rhyming inspirations from the LAMP stack), you're already set. phpMyAdmin comes included with MariaDB also installed as part of the bundle.

If you installed PHP a different way, I will address that later in this section.

In any case, download and unzip the files from the official site here first. Keep them in the same produced folder after unzipping, don't "unleash" them out just yet. A part of this process is to move this folder to an appropriate location. Rename this folder phpmyadmin. (Technically this is optional, but it's more than just a convenience, trust me.)

phpMyAdmin with Herd

Move over the folder produced into a desired location of your choice. I have a Herd folder dedicated for my PHP projects, so I will contain them there.

In Herd, click on "Add Site", followed by "Link existing project". Then, navigate to that folder containing the phpMyAdmin files and click on "Select Project Folder".

Herd - Link Project

You should see something like as follows.

Herd Sites List

If you type in http://phpmyadmin.test into the browser's address bar (https://phpmyadmin.test if you clicked on that lock icon from the Herd Sites list), you should see the following:

phpMyAdmin Login Page

You can now enter your credentials to view the dashboard and start using it to create and manipulate your MySQL databases.

phpMyAdmin Dashboard

phpMyAdmin without Herd

pgAdmin

This is the tool of choice for NUS IT5008.

DataGrip by JetBrains

It used to be kept behind a paywall, but now it's apparently free for commercial use. You could still get the most out of it by registering as a student or educator though.

TablePlus

I tried using this, but the fact that it's not fully free (I'm on a trial version right now) put me off from daily driving this.

References