A React web application to query and share any PostgreSQL database.
This app is currently being updated in the
v2 branch. This new version is written in Typescript, and follows a set of modern React development standards.
PostGUI is a React web application that serves as a front-end to any PostgreSQL database using the PostgREST automatic API tool. PostGUI can automatically adapt any PostgreSQL database to provide an overview of the schema, a visual query builder to execute a SQL query, and a login system to control access to the data. The entire framework can run on a personal computer, or on a web server (for data sharing over the internet).
This is a Masters project intended to be a web-based framework for data sharing and making data more accessible to non-computer programmers. Originally intended for biology community, the generic approach towards data has made this framework into a simple alternative to creating a new custom web application each time data set(s) need to be shared. These web applications are usually non-adaptive and require significant changes if the data source changes.
A PostgreSQL database is a pre-requisite to setting up this framework - specific tips on database schema design are provided in the Installation section. Configuration of PostgREST and PostGUI can be done in a matter of minutes. The PostGUI configuration file (at
/data/config.json) in this repository demonstrates how to customize the front-end for a sample PostgreSQL database. PostgREST configuration is provided, along with some suggestions on PostgreSQL database schema design for optimal experience.
To get help with confirming use cases/feasibility or anything else, please make a new issue on this GitHub repository. This data sharing framework relies on popular open-source works such as: Material UI, PostgREST, JS Query Builder, etc.
PostGUI is designed to be database agnostic by automatically adapting to the client's database schema. This framework can be setup to query and share a biology, finance, or a sports statistics PostgreSQL database. In short, any tabular data set can be accessed and shared using this framework. If the data set(s) in question can be represented as tables, then the data set(s) is compatible with this framework. Complex data types such as images, videos, or sound clips are currently untested and unsupported.
The client must organize the data to be shared in a PostgreSQL database running on a local computer (only data access, no data sharing over the internet) or a web server (data access, and data sharing over the internet). Certain features of PostGUI require specific database schema design. For example, to allow users to edit the database tables, the database tables must have a defined primary key (PK) attribute. However, if a PK is not defined for a table, PostGUI will gracefully disable the edit feature for the table.
The database picker allows multiple PostgreSQL databases to be shared from a single instance of PostGUI. The database name is customizable using the
The database schema (tables, columns, and foreign keys) is shown in the left panel of the PostGUI user interface (UI). The tables and columns can be renamed for a more user-friendly table and column name. Foreign Keys also are shown with “Referenced by” and “FK to” labels.
Search feature can be used to find a table or column quickly. To search for a specific table vs. a column, each search term can be tagged with ‘[table]’ or ‘[column]’.
Integration of the JS Query Builder in this web application makes it easily usable by users unfamiliar with SQL programming language. Query options can be used to fine tune the data being extracted from the database, and to ensure the full result is being shown (exact row count feature).
The query result component features a high-performance data table (React Table) that can sort columns by their values and edit individual cell values (if edit feature is enabled).
The Downloads card features options to download the currently loaded data in CSV, JSON, and XML formats. In addition, it allows for column values to be copied as comma-separated values – this may be used in lieu of a join-table VIEW.
Basic authentication system gets you started with the “database-first” approach to secure authentication system. Three basic users are available by simply executing the Authentication SQL script – read, edit, and admin. The Installation section provides further instructions on how to set up the authentication system. Further comments in the Security Checklist section.
Edit Content feature allows authenticated users to change the table contents if a primary key is defined for the table. A record of database changes is also kept in a separate table.
This project was built to solve three main scenarios:
Set up the PostgreSQL database, PostgREST API tool, and the PostGUI web application on a local computer for database access without any data sharing over the internet.
Set up the PostgreSQL database, PostgREST API tool, and the PostGUI web application on a web server for data sharing over the internet.
Set up the PostgreSQL database, PostgREST API tool, and the PostGUI web application on a web server. In addition, enable the edit feature by ensuring each table has a primary key defined, presence of the primary_keys function in the PostgreSQL database, and enabling the feature in the configuration file of PostGUI.
This installation guide will walk you through setting up a production-like version of the framework with all features enabled on your personal computer. After testing on your personal computer, deploying the three components of the framework on a web server will allow you to share the data over the internet.
This framework is compatible with any data set(s) that can be represented as a data table. The data set(s) is required to be inserted into the PostgreSQL database system to ensure data integrity and consistent data access. Having a well designed PostgreSQL database is important because PostGUI does not enforce any hard requirements on the database schema design. However, to take full advantage of all PostGUI features, do consider the following when designing your PostgreSQL database schema:
pg_trgmidnex would be important to improve performance.
db.confin the same folder as the PostgREST binary.
db.conffile should contain the following:
db-uri = "postgres://DB_USER:[email protected]:5432/DB_NAME" db-schema = "public" db-anon-role = "DB_USER" db-pool = 10 server-host = "*4" server-port = 3001
./postgrest.exe ./db.conf(remove the .exe extension if not on Windows operating system).
git clone [email protected]:priyank-purohit/PostGUI.git
urlif any changes were made to the PostgREST port, or if the PostgREST instance is running on a web server.
npm startto run the web application.
Before deploying the framework on a web server for, consider the following items to improve security.
/scripts/Sample NGINX Config File.txt.
/scripts/directory to create an authentication system schema in the database.
Priyank K. Purohit, Dr. David Guttman, Dr. Nicholas Provart
University of Toronto