ToolUp is a series of articles where we take a deeper dive into the tools that make database developers' lives easier. In this article, we'll take a look at PostgreSQL Studio.
When your database is hosted in the cloud, it makes sense for your administration tools to be hosted in the cloud as well. PostgreSQL Studio is the cloud-first admin tool for distributed teams using cloud-hosted PostgreSQL.
Let's take a look at PostgreSQL Studio and see how it can be used to administer Compose for PostgreSQL.
PostgreSQL Studio is a Java web application so to install it we'll need a Java web server. The download page has several versions, including one that comes bundled with Apache Tomcat so we can get up-and-running quickly. That's the one we'll use here, but you can also download the
.war file and drop it into any Java-compatible web server.
Download the pgstudio_2.0-bin file from the downloads page and unzip it. Instructions for running PostgreSQL Studio vary for different platforms, and instructions for each can be found in the Running.txt file.
Once you see the
tomcat started message, open a web browser and navigate to
http://localhost:8080. If everything loaded correctly, you should see the application page:
Connecting to Compose for PostgreSQL
When PostgreSQL Studio starts, you'll see a form for entering the connection information for your PostgreSQL database. On Compose, these details can be found in the Connection Info section of your PostgreSQL deployment.
The connection string maps to the PostgreSQL Studio form fields in the following way:
Once you've successfully connected, you'll be taken to the Home screen where you can now administer your PostgreSQL database.
The home screen will be the main interface you use to interact with PostgreSQL. It contains an accordion menu on the left side listing each of the database objects and a main panel on the right where you'll do your administration work. Initially, you'll start out in the
tables view with the first table selected.
You can manage tables in your database with the toolbar on top of the
tables menu. This is where you can add new tables, drop existing tables, remove the data from a table by truncating it, and manage table-level security and permissions.
Let's try this out by creating a new table in our database called
employees. Click on the + button in the tables toolbar.
This will bring up the create table dialog. You can give the table a name and add columns in this interface as you create the table.
Clicking on the Add button will bring up a second dialog that allows you to add columns and their data types in an easy-to-use wizard format.
Once you've added a few columns and are happy with your new table, click on the Add button on the bottom of the Create Table page to create the table. You can update the column names, as well as add new columns and drop existing columns after you've created the table by selecting the table in the Tables menu, and opening the Columns tab from the main panel.
PostgreSQL Studio maintains a similar interface across all of the tabs in the Tables interface, with a toolbar near the top of the tab allowing you to perform various actions. On the Indexes tab, for example, we can use the + button in the toolbar to add a new index. The same goes with Constraints, Triggers, and Rules.
Let's try these out by creating a Primary Key on our database. First, create a new field called id and give it a type of serial. Make sure not null is checked, and click Add.
Next, we'll add a Primary Key constraint by clicking on the Constraints tab and clicking on the + sign at the top. From the drop-down, select Primary Key and move the id field from the available to the selected box. Click add to make the id field the primary key.
Probably one of the most useful features of PostgreSQL Studio is the SQL Worksheet, which allows developers to write queries that they can save at a later time. You can access the SQL worksheet by clicking on the worksheet button in the upper-right corner of the interface.
From here, you can directly execute SQL statements and save them in a
.sql file. You can also have the database display the execution strategy by clicking the Explain button.
The second item in the accordion menu allows you to create and manage views, including materialized views. You can create a new view by clicking the + button to bring up the Create Views dialog. A code editor embedded in the dialog allows you to enter the SQL that will fill the view. Finally, to make a materialized view, simply click the check button on the bottom of the dialog.
Managing Stored Procedures
One of the most powerful features of PostgreSQL is it's stored procedures, and PostgreSQL Studio provides a management interface for those under the Functions item in the accordion menu.
By default, there are already a few stored procedures included with Compose for PostgreSQL, mostly used internally to provide things like session cleanup and PostGIS upgrades. You can add a new stored procedure by clicking on the + button in the toolbar to bring up the Add Function dialog.
As with any tool, there are strengths and weaknesses with PostgreSQL Studio.
One weakness is that the interface can occasionally be confusing, as it always shows details of the selected table in the right panel even when using the Views or Functions menus. The many different menu bars with similar icons can also make the menus difficult to figure out.
While not necessarily a weakness, the use of the Java web server can be cumbersome for teams not already using Java-based web servers.
Finally, connectivity issues can cause temporary failures when attempting to commit changes through the tool. While these are generally recoverable, it can be a nuisance.
Summing it Up
PostgreSQL Studio is a powerful tool for administering your Compose for PostgreSQL database, offering a simple and intuitive interface for doing surprisingly complex administrative tasks. As a cloud-first offering, PostgreSQL Studio is a good fit for organizations that do most of their development in a cloud environment. The SQL Worksheet makes developing, tweaking, saving and analyzing queries a breeze and is one of the many features that makes PostgreSQL Studio a tool you'll definitely want to check out.
Read more articles about Compose databases - use our Curated Collections Guide for articles on each database type. If you have any feedback about this or any other Compose article, drop the Compose Articles team a line at firstname.lastname@example.org. We're happy to hear from you.
attribution João Silas