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:

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:

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:

• https://dbfiddle.uk/ (very good site, you can run multiple SQL queries at the same time)
• db-fiddle: https://www.db-fiddle.com/
• sqliteonline: https://sqliteonline.com/ (supports SQLite, MariaDB, PostgreSQL, MS SQL)
• w3school: https://www.w3schools.com/Sql/trysql.asp?filename=trysql_select_like_not (it provides some sample tables to try sql)

Database to play with#

Sample database#

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

• northwind database:
  • https://github.com/dalers/mywind
  • https://github.com/harryho/db-samples
• sakila database: https://github.com/ivanceras/sakila
• chinook database: https://github.com/lerocha/chinook-database

Ref:

• mentioned sqliteonline and chinook database: https://www.youtube.com/watch?v=DVllkQtabNE

Generate fake data#

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

• mockaroo: https://www.mockaroo.com/
• https://generatedata.com/

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.