We at DoltHub recently launched the Dolt Workbench, a modern, browser-based, open source SQL workbench with version control features when connected to Dolt. It started with support for MySQL-compatible databases, including MySQL, MariaDB, and Dolt. We're actively developing the Dolt Workbench, and are excited to announce it now supports PostgreSQL (Postgres) databases in preparation of DoltgreSQL, Postgres-flavored Dolt. The Dolt Workbench will support Doltgres in the future when it is more stable.

Getting started with Postgres

It's easy to get started with the Dolt Workbench using our Docker image. Simply pull the image and run it:

% docker pull dolthub/dolt-workbench:latest
% docker run -p 9002:9002 -p 3000:3000 dolthub/dolt-workbench:latest

See the Docker Hub README for instructions for connecting to locally installed Postgres databases.

Navigate to http://localhost:3000 and add your connectivity information.

If you successfully connected to your database, you can start using the workbench. View your tables or create a new one, execute SQL queries, use cell buttons to filter or update your tables, upload files, and more. You can read more about the Dolt Workbench features here.

Dolt Workbench differences between MySQL and Postgres

While the Dolt Workbench functions mostly the same for different SQL flavors, there are a few differences between using MySQL and PostgreSQL databases.

1. Postgres has schemas

A MySQL connection can have multiple databases. Each database has tables and other metadata, like views, triggers, etc. You can switch between databases using a USE [database] statement.

A Postgres connection can have multiple databases, but each database can also have multiple schema. Each schema has tables and other metadata. When you connect to a database, you cannot change databases using SQL and must start a new connection to use another database. You can however change the schema using a SET SEARCH_PATH = '[schema]' statement.

When connecting to a MySQL database from the Dolt Workbench, you can choose a database and switch between databases using the top navigation breadcrumbs. For Postgres you can switch between schemas, but must create a new connection to use another database.

2. File upload duplicate key behavior

As described in this issue, file upload duplicate key behavior for MySQL and Postgres databases works differently. MySQL file upload uses a LOAD DATA statement to import file data into a table. This lets you specify whether you'd like to ignore or replace if a duplicate key is found in the uploaded file.

Postgres file upload uses a COPY FROM statement. It does not let you specify duplicate key behavior and therefore will currently fail if you upload a file that contains a duplicate key. There are workarounds for this where we can import to a temporary table, and then specify on conflict behavior on upsert. If you want this behavior, feel free to send us a pull request or comment on the GitHub issue.

3. Executing schema definitions

You can view schema definitions in the Schemas tab in the left navigation. For MySQL, this will execute SHOW CREATE statements for tables, views, triggers, events, and procedures to show the related definition.

Postgres does not have SHOW CREATE statements. There are some system information functions that return similar definitions for views, triggers, and procedures, but there is not one for tables. Other SQL workbenches will generate the CREATE TABLE statement using the information_schema.columns table. We have an issue opened for this here.

Implementation details

We needed to make some changes to the Dolt Workbench to support SQL flavors other than MySQL. The Dolt Workbench is based on the Hosted Dolt Workbench, a workbench for your Hosted Dolt database. Dolt is MySQL flavored and both workbenches were built to optimize for MySQL, but this will change as we continue to build Doltgres.

The Dolt Workbench is a React web app backed by a GraphQL server. This section will go through the changes we made to each in order to support Postgres. You can learn more about the architecture here.

GraphQL server changes

The Dolt Workbench is backed by a GraphQL server, which manages database connections and executes queries against the current database. It utilizes an ORM called TypeORM.

When the Dolt Workbench only supported MySQL, our GraphQL resolvers could execute raw queries to get certain information that wasn't accessible by built-in TypeORM query builder methods. Once Postgres came into the picture, many of these queries would no longer work. So we refactored the GraphQL server to support a query factory class that could be extended for different SQL dialects.

For example, we show a list of tables for each database or schema in the left navigation. Originally, our tableNames resolver looked something like this:

@Query(_returns => [String])
async tableNames(@Args() args: ListTableArgs): Promise<string[]> {
  return this.dss.query(
    async query => {
      const tables = await query("SHOW FULL TABLES WHERE table_type = 'BASE TABLE'");
      return mapTablesToStrings(tables);
    },
    args.databaseName,
  );
}

Postgres does not have a SHOW TABLES statement. In order to get the table list for both MySQL and Postgres, we added a getTableNames method to our new query factory class, which can use different queries for each SQL dialect.

// MySQL query factory
async getTableNames(args: t.DBArgs): Promise<string[]> {
  const res = await this.query(
    "SHOW FULL TABLES WHERE table_type = 'BASE TABLE'",
    [],
    args.databaseName,
  );
  return mapTablesToStrings(res);
}

// Postgres query factory
async getTableNames(args: t.SchemaArgs): Promise<string[]> {
  const res = await this.query(
    "SELECT * FROM pg_catalog.pg_tables where schemaname=$1;",
    [args.schemaName],
    args.schemaName,
  );
  return res.map(r => r.tablename);
}

And now in our resolver, we use this method:

@Query(_returns => [String])
async tableNames(@Args() args: ListTableArgs): Promise<string[]> {
  const conn = this.conn.connection(); // Gets the query factory based on the SQL dialect
  const tables = await conn.getTableNames(args);
  return tables;
}

These changes allow us to more easily add other SQL flavors in the future, so let us know if you have any requests.

React changes

The Dolt Workbench front end is built using Next.js, a React framework for the web. It uses Apollo Client to manage state for both local and remote data for GraphQL. Our GraphQL server handles most of the data fetching differences between MySQL and Postgres, so there weren't many changes we needed to make to our Apollo queries.

However, some workbench features, such as the SQL console and cell buttons that generate queries to insert, update, or remove rows and cells do not utilize the GraphQL server and instead use a library called node-sql-parser. This library helps us parse queries that are executed from the SQL console and build queries for the cell buttons. We only ever used the MySQL dialect, so we had a library of utility functions we could use for these operations.

Now that we are supporting more SQL dialects, we moved these functions to a React hook that can query the GraphQL server for the database type and return the same utility functions based on the type.

We were also building raw query strings for buttons and links that generate queries. For example, when you use the column sort cell button, we generate a SELECT * FROM `[table]` ORDER BY [col] statement. MySQL uses backticks to escape table names, whereas Postgres uses double quotes. All these raw generated query strings would break for Postgres. So we made an additional React hook to build queries using node-sql-parser based on the dialect.

To come

Since the beginning of Dolt, we've had people asking for Postgres. We hear you and want you to know we're taking concrete steps toward making it happen. We're actively developing Doltgres, and the Dolt Workbench will support Doltgres when it is more stable. In the meantime, use the Dolt Workbench with your existing Postgres database.

Want the Dolt Workbench to support other SQL flavors? Let us know on Discord or file a GitHub issue. The changes we made for Postgres will make it easier to support more SQL flavors in the future.