Browsing PostgreSQL Data with Compose

Compose is all about immediacy. You want a new database - you can have it in seconds. You want your database metrics - they're just a click away in your dashboard. And, for MongoDB and Redis, we've also provided immediate access to the database through our browsers.

Now, PostgreSQL joins them in allowing for immediate data browsing from the Compose Dashboard. We've just launched the first edition of our PostgreSQL data browser for PostgreSQL Compose deployments so you can try it out now.

What the browser is designed for is quick, no local-footprint, viewing and modification of the database. No connections have to be set up, no ports opened, no URIs memorized – just log in to Compose, select your database and get browsing. It's a complement to the extensive and complex client tools that exist for PostgreSQL which, once configured and mastered, can let you peer into every corner of the database.

We'll be expanding the Compose browser's capabilities over time with that philosophy in mind, but first, let's take a look at the capabilities already available in the PostgreSQL browser.

What can you do?

We'll begin our tour of the PostgreSQL browser from the top. To get to the browser, log into the Compose dashboard, select 'Browser' in the sidebar to see this...

The "top" of the browser's view shows the databases created in the PostgreSQL instance. In the screenshot above you can see that there are two, the default compose and a dvdrental database. You can also see the on-disk size of each of the databases. The dvdrental is having data imported into it for a future demonstration so let's take a look inside there by clicking on its row to reveal:

A number of tables. These are the tables in the database, each one displayed with an estimated row count. If selected, the Admin tab in the sidebar only offers the option to delete the current database, but we're more interested in looking at some of the data here. If we click into the film table, we get a better view of that data:

This is the Query view of the film table. The default query reads the first 20 items from the table and displays their contents in a table below the query. This table will include all the fields so some horizontal scrolling could be involved. You can edit the query to adjust and LIMIT the number of items displayed, add a WHERE clause to include your own selection criteria, add an ORDER BY clause and sort according to a field or add an OFFSET to skip a number of returned results. Using all of these would look like this.

That OFFSET value can also be changed, by the LIMIT value, using the Next and Last buttons at the bottom of the table view so you can page through the data.

Updating, Editing and Adding

If there's a primary key on the table, then you'll also be able to get a better look at the data in a row by clicking anywhere on a row to get to the update row view. We're going to give you the whole view of one here, though if you have a field-rich table, expect to scroll:

Here we see nearly all the fields of the row; the only one missing is the primary key field, which if you look just above the field list is being used in the WHERE clause to select this record. The rest of the fields are displayed with both the field name and, usually, the type of that field along with an editable field to allow for modifications.

So, we can see, going down this page, a "title" field, defined as a "varchar(255)" with a text area to edit its contents, and below that, a "description" field, defined as "text" also with a text area. Field validation takes place on submitting the update to the database, so if you put too much text in the "title" field, it's at update time that you'll be told there's too much text.

The same goes for validating the numeric fields, like the smallint and numeric types, and the date intervals like the year field. The observant will notice a field with the type "mpaa_rating" further down the table. This field has its type set to a user defined enum type like this:

CREATE TYPE mpaa_rating AS ENUM (  
    'G',
    'PG',
    'PG-13',
    'R',
    'NC-17'
);

Because the browser lets the database flag errors at update time, it means this field is also validated; enter a string value which doesn't match with one of the enum values and when you press update you'll get an error and the update will be completely rolled back.

Back to the fields types in the table. The "lastupdate" field is effectively read-only as it'll be overwritten during updating. The "fulltext" field is a tsvector field and is also not editable - in this database's case it is updated on insert or update by a trigger. There is one field you can edit, the text array that is "specialfeatures", which takes PostgreSQL syntax for an array literal – { "string","string","string",... }.

That covers editing, but you can also add new rows. You'll find the button for that in the query view in the top right marked "Insert row". It'll bring up an unpopulated page similar to the edit row page:

This form is more forgiving of validation errors than the "Update Row" page in that if you do have a change which is rejected by the validation process, the fields you have entered are not cleared. Apart from that, it is functionally the same as the "Update Row" page.

Back to the Table

If we go back to the top table view, there are two tabs we haven't mentioned at the top of the page. The Indexes tab shows the current indexes that apply to the table we are looking at:

Here, for example we can see the unique primary key on the film_id, a fulltext index on the tsvector fulltext field, a foreign key index on the language id, and a simple index on the movie title. There's also the option to drop any one of these indexes with the right side's drop button. As well as displaying indexes, you can create indexes, albeit, currently, simply unique or non-unique btree indexes. Enter the fields you want indexed between the parentheses, click Unique if you want a unique index and click name and enter an index name if you want to set a particular name for the index - then just click Create Index.

The Settings tab currently offers one option, to drop the table. But this also comes with options:

The option in this case is whether or not to drop any database objects that depend on the table, when you drop the table, using a CASCADE operator. Remember to check you have a working backup of your table, or whole database, before you drop the table as there's no going back on the drop without doing a restore. The browser doesn't have a "Create Table" option yet, so you can't manually rebuild the table without reaching for the psql command-line tool.

Wrapping up

We’ll be enhancing the PostgreSQL browser in the future. As you can see, there’s already a useful range of functionality for the database user on the go and we aim to make it your first stop for PostgreSQL control on Compose.