Learn SQL in a browser with PostgreSQL and pgweb

PostgreSQL is a very versatile database. If you want to learn SQL, then a quick way to start is to 1) grab some data you want to analyze 2) insert into a PostgreSQL table and 3) use a SQL client such as pgweb and get started analyzing data. You can use any SQL client of your choice but pgweb is easy to use and is browser based which makes it a very convenient choice.

As with many of my posts, I'll use Docker to run PostgreSQL and pgweb.

START A POSTGRESQL CONTAINER

docker run -d -p 5432:5432 --name postgres_db -e POSTGRES_PASSWORD=postgres postgres

START A PGWEB CONTAINER

docker run -d -p 8081:8081 --link postgres_db:postgres_db -e DATABASE_URL=postgres://postgres:postgres@postgres_db:5432/postgres?sslmode=disable sosedoff/pgweb --readonly

The --link flag here is being used to allow the pgweb container to access the PostgreSQL database running inside another container. Recall the --name flag to set the name of the PostgreSQL container to postgres_db. The name is now being used to point the pgweb container to where PostgreSQL is running. This is evident in the environment variable DATABASE_URL that provides the connection details needed by pgweb to connect to PostgreSQL.

Go over to your browser and type localhost:8081 to access pgweb. There isn't any data available yet which will be fixed below.

INSERT DATA INTO POSTGRESQL

In the snippet below, I use pandas to grab the famous iris dataset. I then define a connection using sqlalchemy to the PostgreSQL container. Since I'm doing this on the host system, I can use localhost to connect to the container. The containers by default are available on localhost on the host system.

Finally I use to_sql function to write the dataset to the database.

import pandas as pd
from sqlalchemy import create_engine
iris = pd.read_csv('https://raw.githubusercontent.com/mwaskom/seaborn-data/master/iris.csv')

engine = create_engine('postgresql://postgres:postgres@localhost:5432/postgres')

iris.to_sql("iris", engine, if_exists = 'replace', index=False, chunksize=1000)

Now when I go over to localhost:8081 in my browser I see iris in the list of tables.

NOTE

PostgreSQL Persisting your PostgreSQL data inside your container requires you to set the appropriate -v flags. Besides data persistence there are other factors you may have to deal with when running PostgreSQL inside a container. Please find more details here for some great suggestions on this topic.

pgweb You may want to limit access to the SQL client, run pgweb on a different port, allow read-only access to the database and so on. A large number of options can be found here. To enable these options you may need to create a custom Dockerfile for pgweb and enable the options of your choice. You can reuse the pgweb Dockerfile found here.