We generate a simple web interface for viewing PostgreSQL tables
In publications on Habré, most often the most interesting things are in the comments. So in my last post “Generating a web interface from a database or an object model has not become easier even 10 years later” I collected a jackpot from SharePoint, Vaadin and several libraries either outdated or supported by “one author”.
What I tried to explain to readers is that at the stage of testing ideas, additional complexity in the form of frameworks, server infrastructure is not needed just for the sake of this very infrastructure and a new programming language / platform. That is why I do not need Yii, Symfony and Laravel now, ADF will not work. Precious time is wasted on any training, and therefore it is necessary to understand why waste time on something that you may then have to forget forever after a week or two of playing with a prototype.
In my experiments with JavaScript noodle code, I quickly developed a completely functional (to test the idea), but not a user-friendly interface. As an example, one of my screens:
At the same time, all this time I did not program the backend at all. All that is in the application is a set of static files on the jetty http server, and PostgREST to turn the PostgreSQL database into an Open API service, and several new stored functions in the database for the interface.
And jetty for good would be superfluous here, if not for…
If only PostgREST could serve static HTML and images. After all, when I tried to run a page from a local folder, interaction with PostgREST did not work due to security restrictions in modern browsers.
What can I share and what will be useful to you
What from my experience of developing “on the knee” can be useful for backenders who want to quickly show tabular data from PostgreSQL?
After reviewing several libraries on GitHub for displaying tables, I settled for myself on tabulator which out of the box can display tables with sorting, filtering and pagination, automatically recognizes column types, with excellent documentation, understandable by non-frontenders. It also allows you to quickly make a prototype, allowing you to customize the field display types even in auto-detect mode.
Trying to Program in Java 21
In a few minutes (not taking into account the time of reading the documentation), I sketched a prototype that allows you to view all the tables in the database. And I did it in Java version 21, which has not yet been released, and then for another ten minutes I tried to start it, looking for where in the new version of IntelliJ Idea CE for me and in the old version of maven, where to add the JDK experimental parameter enablePreview.
Honestly, this is a habit to develop automatically with a reserve for the future, in order to someday parse PostgreSQL metadata and comments to database objects, all that PostgREST does not export. I went to make tea and wondered if I would get to this postgres metadata and when, that all this is also superfluous, overengineering is not needed here and it needs to be rewritten in HTML + JavaScript.
The feeling is that with Servlet API 5.0, development has become easier without web.xml, and String Templates from JDK 21 will make the code more readable and, in simple cases, eliminate the need to connect an external Apache Velocity templating engine:
For the curious under the spoiler, a new Java servlet that is no longer needed:
package com.github.com.github.isuhorukov.postgrest.crud;
import jakarta.servlet.annotation.WebServlet;
import jakarta.servlet.http.HttpServlet;
import jakarta.servlet.http.HttpServletRequest;
import jakarta.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URI;
import java.net.http.HttpClient;
import java.net.http.HttpRequest;
import java.net.http.HttpResponse;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Iterator;
import java.util.List;
import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.ObjectMapper;
import static java.lang.StringTemplate.STR;
@WebServlet("/table")
public class TableServlet extends HttpServlet {
@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws IOException {
String endpoint = request.getParameter("endpoint");
if(endpoint==null || endpoint.isBlank()){
List<String> postgRestAvailablePaths = getPostgRestAvailablePaths();
String options = postgRestAvailablePaths.stream().map(path ->
STR. """
<option value="\{path}">\{path}</option>
""").collect(Collectors.joining("\n"));
response.getWriter().append(STR."""
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/html">
<head>
</head>
<body>
<form method="GET" action="/table">
<select name="endpoint">\{options}</select>
<input type="submit" value="Show">
</form>
</body>
</html>
""");
response.setStatus(HttpServletResponse.SC_BAD_REQUEST);
return;
}
response.getWriter().append(STR."""
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/html">
<head>
<link href="https://unpkg.com/tabulator-tables@5.5.2/dist/css/tabulator.min.css" rel="stylesheet">
<script type="text/javascript" src="https://unpkg.com/tabulator-tables@5.5.2/dist/js/tabulator.min.js"></script>
</head>
<body>
<div id="table-result"></div>
<script>
var table = new Tabulator("#table-result", {
ajaxURL: '\{endpoint}',
pagination:true,
paginationMode:"remote",
dataSendParams:{
"size" : "limit" //rename page request parameter to "limit"
} ,
ajaxURLGenerator:function(url, config, params){
let sortParam = '';
if (params.sort && params.sort.length > 0) {
sortParam = '&order="+params.sort.map(sorter => `${sorter.field}.${sorter.dir}`).join(",');
}
return url + (url.includes("?")?"&":"?") + "limit=" + params.limit+"&offset=" + params.limit*(params.page-1) + sortParam;
},
ajaxResponse: function (url, params, response) {
document.querySelector(".tabulator-footer .tabulator-page[data-page="last"]").style.display = "none";
return {
last_page: 10000000000,
data: response
};
},
autoColumns:true,
paginationSize: 35,
sortMode: 'remote',
filterMode:'remote',
ajaxSorting: true,
ajaxFiltering: true
});
</script>
</body>
</html>""");
}
List<String> getPostgRestAvailablePaths() {
String postgRestUrl = System.getenv("postgrest_url");
if(postgRestUrl==null || postgRestUrl.isBlank()){
throw new IllegalArgumentException("Please provide PostgREST / endpoint in environment variable: postgrest_url");
}
List<String> paths = new ArrayList<>();
try (HttpClient httpClient = HttpClient.newHttpClient()){
HttpRequest request = HttpRequest.newBuilder().uri(URI.create(postgRestUrl)).build();
HttpResponse<String> postgRestResponse = httpClient.send(request, HttpResponse.BodyHandlers.ofString());
if (postgRestResponse.statusCode() == 200) {
try {
HttpResponse<String> response = httpClient.send(request, HttpResponse.BodyHandlers.ofString());
ObjectMapper objectMapper = new ObjectMapper();
if (response.statusCode() == 200) {
String responseBody = response.body();
JsonNode openApiSpec = objectMapper.readTree(responseBody);
JsonNode pathsNode = openApiSpec.get("paths");
if (pathsNode != null && pathsNode.isObject()) {
for (Iterator<String> it = pathsNode.fieldNames(); it.hasNext(); ) {
String path = it.next();
if (!"/".equals(path) && !path.startsWith("/rpc")) {
paths.add(postgRestUrl + path);
}
}
} else {
throw new RuntimeException("No table paths found.");
}
} else {
throw new RuntimeException("PostgREST failed with status code: " + response.statusCode());
}
} catch (IOException | InterruptedException e) {
throw new RuntimeException(e);
}
} else {
throw new RuntimeException("PostgREST request failed with status code: " + postgRestResponse.statusCode());
}
} catch (IOException | InterruptedException e) {
throw new RuntimeException(e);
}
Collections.sort(paths);
return paths;
}
}
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.github.igor-suhorukov</groupId>
<artifactId>postgrest-crud</artifactId>
<version>1.0-SNAPSHOT</version>
<packaging>war</packaging>
<properties>
<maven.compiler.source>21</maven.compiler.source>
<maven.compiler.target>21</maven.compiler.target>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
</properties>
<dependencies>
<dependency>
<groupId>org.eclipse.jetty.toolchain</groupId>
<artifactId>jetty-jakarta-servlet-api</artifactId>
<version>5.0.2</version>
</dependency>
<dependency>
<groupId>com.fasterxml.jackson.core</groupId>
<artifactId>jackson-databind</artifactId>
<version>2.15.2</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<configuration>
<enablePreview>true</enablePreview>
</configuration>
</plugin>
<plugin>
<groupId>org.eclipse.jetty</groupId>
<artifactId>jetty-maven-plugin</artifactId>
<version>11.0.15</version>
<configuration>
<scanIntervalSeconds>10</scanIntervalSeconds>
<connectors>
<connector implementation="org.mortbay.jetty.nio.SelectChannelConnector">
<port>8080</port>
<maxIdleTime>60000</maxIdleTime>
</connector>
</connectors>
</configuration>
</plugin>
</plugins>
</build>
</project>So, I did everything that I didn’t want, added an extra component between PostgREST and the browser. Feel free to throw away this code/approach and move on!
We do everything the same, but without a server and only in the browser
Tabulator will do all the magic for us. It works great on a laptop and mobile browser and just needs to be properly configured to work with the PostgREST API.
The first attempt was to create a task to adapt it for PostgREST using a community project, but to no avail.
Well, let’s do it ourselves. Its ajaxURL parameter, which downloads data from the server, will not work with PostgREST because in the response it expects a JSON object with last_page – the last page number and the data array, which contains the data itself.
Our API returns data as an array at once and does not return how many pages. But we don’t need this, as a first approximation, since this “rabbit hole” having fallen through where I will never return to the main task.
By the way, the API can return how many rows as a result using several algorithms. You just need to set the HTTP Header:
Prefer: count=exact
Prefer: count=planned
Prefer: count=estimated
The result can be retrieved from the Content-Range in the Response after the “/”. I acted much easier – removed the button to go to the last page.
I continue to analyze the problems: Tabulator passes the page number to the parameters, and I need to translate them into limit / offset for the API. I do this in the ajaxURLGenerator function and using the dataSendParams configuration.
I do the conversion to the data format required for the widget from the server on the client in the function for ajaxResponse.
In total, my page has parameters:
postgres_url points to the PostgREST API. If not specified, it will default to “knocking” on localhost:3000
end point – table or view in postgres. If you do not specify it, then the code will request all endpoint paths from PostgREST, filter out functions and metadata, and give you a form with the choice of endpoint. Specify in the select field which table you want to see, after “Click the button and get the result.”
To work, you need your database, a server for the PostgREST API that “looks” at this database, any web server where you can put this page (I use mvn jetty:run). If browsers didn’t struggle with cross site scripting and the security of your local data as they used to, then a web server wouldn’t be needed to serve static.
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/html">
<head>
<link href="https://unpkg.com/tabulator-tables@5.5.2/dist/css/tabulator.min.css" rel="stylesheet">
<script type="text/javascript" src="https://unpkg.com/tabulator-tables@5.5.2/dist/js/tabulator.min.js"></script>
</head>
<body>
<form id="endpoint_selector" method="GET" action="tables.html" style="display:none">
<select id="endpoint" name="endpoint"></select>
<input id="postgrest_url" name="postgrest_url" type="hidden" value="">
<input type="submit" value="Show">
</form>
<div id="table-result"></div>
<script>
const params = new URLSearchParams(window.location.search);
const postgRestUrl = params.has('postgrest_url')?params.get('postgrest_url') :'http://localhost:3000';
async function fetchAndExtractPaths(postgRestUrl) {
try {
const response = await fetch(postgRestUrl);
if (!response.ok) {
throw new Error(`Failed to fetch data from ${postgRestUrl}`);
}
const data = await response.json();
const paths = [];
if (data && typeof data.paths==='object') {
paths.push(...Object.keys(data.paths).filter(key => !key.startsWith('/rpc') && key!='/' ));
}
return paths;
} catch (error) {
alert('Error:', error);
return [];
}
}
if (params.has('endpoint')) {
const endpoint = params.get('endpoint');
var table = new Tabulator("#table-result", {
ajaxURL: postgRestUrl+endpoint,
pagination:true,
paginationMode:"remote",
dataSendParams:{
"size" : "limit"
} ,
ajaxURLGenerator:function(url, config, params){
let sortParam = '';
if (params.sort && params.sort.length > 0) {
sortParam = '&order="+params.sort.map(sorter => `${sorter.field}.${sorter.dir}`).join(",');
}
return url + (url.includes("?")?"&":"?") + "limit=" + params.limit+"&offset=" + params.limit*(params.page-1) + sortParam;
},
ajaxResponse: function (url, params, response) {
document.querySelector(".tabulator-footer .tabulator-page[data-page="last"]").style.display = "none";
return {
last_page: 10000000000,
data: response
};
},
autoColumns:true,
paginationSize: 35,
sortMode: 'remote',
filterMode:'remote',
ajaxSorting: true,
ajaxFiltering: true
});
} else {
fetchAndExtractPaths(postgRestUrl)
.then(paths => {
const container = document.getElementById('endpoint');
paths.forEach(path => {
const optionElement = document.createElement('option');
optionElement.setAttribute('option',path);
optionElement.textContent = path;
container.appendChild(optionElement);
});
document.getElementById("endpoint_selector").style.display = 'block';
document.getElementById("postgrest_url").value = postgRestUrl;
});
}
</script>
</body>
</html>I run PostgREST locally in Docker:
docker run --name postgrest --net=host -e PGRST_DB_URI="postgres://USER:PASSWORD@127.0.0.1:5432/DATABASE" -e PGRST_DB_ANON_ROLE="YOUR_ROLE" postgrest/postgrest:v11.2.0I also have PostgreSQL with data running in a Docker image, as I already said on Habré.
What else can be done until it is universal – depending on the data types, change the display in the columns.
Result
It turns out to display table data for hundreds of gigabytes using paging with the ability to sort in the database.
I have no plans to create or develop a new low code/no code platform. I solve my problem quickly. Perhaps for someone else who uses PostgreSQL and is not a front-end developer, this experience will help to test the idea or create a “admin” to the database for users within the company.
