update log
  • 2022-10-12: add fake data generation website.
  • 2022-10-11: add TUI client for MySQL and Postgres.
  • 2022-09-24: add sample database we can try.

In this post, I will talk about setting up SQL databases and also the tools needed to write SQL queries.

Install and set up SQL

MySQL install and setup

Install using HomeBrew:

brew install mysql

Start and stop the MySQL server:

brew services start mysql
brew services stop mysql

Connect to MySQL server in macOS

  • Host: localhost
  • Port: 3306
  • Username: root

Postgres install

Install using HomeBrew:

brew install postgresql

Start and stop the postgres server:

brew services start postgresql
brew services stop postgresql

Connect to postgres server in macOS

  • Host: localhost
  • Database: postgres
  • Username: jdhao

No password is needed.

Try sql online

If you do not want to install and just want to experiment with different SQL databases, there some several website where you can use the databases online:

Database to play with

Sample database

There are some sample databases with multiple tables so that we can toy with them easily.

Ref:

Generate fake data

We can also use some online data generation tool to generate fake data:

I tried both and they work well.

TUI client

You can use the MySQL and PostgreSQL executable to interact with databases on the command line. However, they do not support autocompletion or syntax highlighting. There are some 3rd party SQL client with auto-completion and syntax highlighting features:

  • mycli: TUI client for MySQL.
  • pgcli: TUI client for Postgres.

GUI client

Most people will probably choose a GUI client to work with the databases.

Mysql workbench

Mysql workbench is a GUI application to help us manage mysql server more easily.

Pgadmin

If you use PostgreSQL, you can try Pgadmin 4, which is specifically built for Postgres. However, some people say the UX sucks.

Postico

Postico is a lightweight tool built for Postgres. Simple interface, non-free, no auto-completion.

Datagrip

Datagrip is developed my JetBrains and it is not free. It has support for all kinds of databases.

It has autocompletion for SQL keywords, functions, table names, and column names etc. Highly recommended, intelligent and very good user experience.

DBeaver

DBeaver is an open source database client.

It also has autocompletion, but it is not as powerful as Datagrip.

Change keyword case in DBeaver: https://stackoverflow.com/a/60854946/6064933

BeeKeeper Studio

BeeKeeper is another database client.

No autocompletion, very simple GUI, less features than DBeaver and Datagrip. Not recommended.

Table plus

Table plus is a commercial product that supports a lot of databases. The interface is clean and easy to use.

Requires a license, otherwise very limited feature available.