PostgreSQL Graphical User Interface (GUI)

[nelsonic]

Today I learned about "DBeaver". (thanks to @Danwhy!) it's an Open Source Database GUI that supports PostgreSQL and has several nice features! see: https://github.com/dbeaver/dbeaver and https://dbeaver.jkiss.org/docs/features

I particularly like the Schema (Table Relationships) Viewer: Which allowed us to visualise the schema of our DB and show it to the rest of the team without having to manually "draw" it ourselves!

Download the "free" (Community) version: https://dbeaver.jkiss.org/download

It was "hidden" on this list: https://wiki.postgresql.org/wiki/Community_Guide_to_PostgreSQL_GUI_Tools but we never noticed it before, so thanks Dan for demo-ing it to us! ❤️

[nelsonic]

DBeaver is compatible with a lot of Database engines! 😮

Had to download the JDBC PostgreSQL driver to use it:

[nelsonic]

https://dbeaver.io

Visit: https://dbeaver.io/download and pick the Community Edition installer for your Operating System:

Intro:

License: Apache License Version 2.0

Agree:

Space:

Installing:

Success:

Move installer to trash: (you can always restore/retrieve it from the trash if needed)

Open the App:

Loading screen:

Select your database connection:

Same screen but constrained to highlight the modal:

Select PostgreSQL and click "Next >"

Test the connection:

Download PostgreSQL driver files:

Connection Success:

Change name of Database to "test":

Re-test the connection:

No Tables:

My first impression of the UI is that it's overwhelming. 🙄 I think if I was totally new to software development, this screen with all the tiny icons and very little in the way of labels would be intimidating. Like "What Now?", "What do I do Next...?" Good thing I'm on a mission and (comparatively) small obstacles aren't going to deter me from achieving my goal! 🎯 I know what to do when nobody tells me what to to; explore the interface!

In the top left (5th from the left), there's an icon (which I discovered by hovering my mouse cursor over all the icons and reading the tool-tips) that opens an SQL Editor:

Create Table: copy-paste the create script from schema.sql, click the ▶️ button to run the script:

Looks like it succeeded:

Confirm that the table was created, you may need to refresh the schema:

Attempt to insert a person into the people table:

Confirm that the test person was inserted into the people table by viewing the data in the table:

No idea why the inserted_at field is being displayed as a number ... Oh, that's right, because I defined it that way (above)! Derp! Let's fix it.

Drop it ... https://youtu.be/RaCodgL9cvk Note: you will need to disconnect from test and instead connect to postgres to perform this query:

DROP DATABASE test;

CREATE DATABASE test;

How does Phoenix do it...? Open up a Phoenix app https://github.com/dwyl/phoenix-chat-example and check inserted_at: Looked up how to define it: https://stackoverflow.com/questions/28506589/default-datetime-elixir

Repeat the previous CREATE TABLE query:

CREATE TABLE IF NOT EXISTS people (
  id SERIAL PRIMARY KEY,
  inserted_at TIMESTAMP DEFAULT EXTRACT(EPOCH FROM CURRENT_TIMESTAMP),
  name VARCHAR(100) DEFAULT NULL,
  username VARCHAR(50) DEFAULT NULL
);

Re-insert the dummy data:

INSERT INTO people (name, username)
  SELECT name, username FROM people
  UNION
  VALUES (
    'Jimmy Testuser',
    'jimmy'
  )
  EXCEPT
  SELECT name, username FROM people;

That's better, inserted_at is timestamp. ✅

Ok. that's DBeaver setup & working ... now back to #51