Running PostgreSQL in the Browser

image
·

August 13, 2024

Supabase has recently unveiled a new tool that brings the power of Postgres directly to the browser. This innovative product allows developers to run a fully functional Postgres database locally, without needing any server or installation. Powered by WebAssembly (Wasm), it enables rapid prototyping, development, and even learning Postgres, all within a browser environment.

Why This Matters

Traditionally, running a database like Postgres required server setups or local installations, which could be cumbersome for quick experiments or learning. With Supabase’s new tool, you can now spin up a Postgres instance instantly, directly in your browser, making it incredibly convenient for developers to test queries, design schemas, and explore Postgres features without any setup overhead.

Who Can Benefit?

  • Developers and Data Scientists: Quickly test out ideas, run queries, and experiment with Postgres without leaving the browser.
  • Students and Learners: A perfect tool for those learning SQL or Postgres, offering a hands-on experience without the technical overhead.
  • Educators: Provide an interactive and accessible way for students to explore databases.

Features

AI assistant

Postgres.new tool pairs Pglite with a large language model (currently GPT-4o) and gives it full reign over the database with no restricted permissions or confirmations required from the user.

CSV imports and exports

Drag-and-drop a CSV file directly onto the chat to instantly receive a new table with the data automatically imported into it. The language model will scan the CSV’s header and a few sample rows to decide which data types to use for each column.

ER diagrams and migrations

ER Diagrams are generated after the language model spits out create and alter statements and executes those statements against the running instance in the browser, it’s intuitive and rather straght forward.

Semantic search and RAG

ElectricSQL has been working hard to support real Postgres extensions in PGlite (compiled to WASM). One extension that was high on the priority list was pgvector which enables in-browser vector search.

Charts

Charts are a first-class feature within the chat. By simply adding the word “chart” (or similar) to the message, AI will execute the appropriate query using SQL then build a chart representing that data:

Chart created based on the generated SQL

First time use

For a fictive partner dealbook software, I inputed following natural language command to the model

“Create a partner dealbook schema. The user can put their own member benefits in the platform, import benefits from other companies”.

And then proceeded with another query to create organization level hiearchy.

“a partner account can be managed by multiple users that reside inside an organization that is created when a root user registers its account.”

The result is the following schema that the tool created.

Schema created by postgres.new tool

Here is the migration file generated from the queries above:

1\-- Migrations will appear here as you chat with AI 2 3create table partners ( 4 id bigint primary key generated always as identity, 5 name text not null, 6 description text, 7 contact\_info text 8); 9 10create table deals ( 11 id bigint primary key generated always as identity, 12 partner\_id bigint not null references partners (id), 13 title text not null, 14 description text, 15 start\_date date not null, 16 end\_date date not null 17); 18 19create table benefits ( 20 id bigint primary key generated always as identity, 21 deal\_id bigint not null references deals (id), 22 description text not null, 23 terms text 24); 25 26create table organizations ( 27 id bigint primary key generated always as identity, 28 name text not null, 29 description text 30); 31 32create table users ( 33 id bigint primary key generated always as identity, 34 organization\_id bigint not null references organizations (id), 35 name text not null, 36 email text not null unique, 37 password text not null 38); 39 40create table organization\_users ( 41 id bigint primary key generated always as identity, 42 organization\_id bigint not null references organizations (id), 43 user\_id bigint not null references users (id) 44); 45 46create table partner\_users ( 47 id bigint primary key generated always as identity, 48 partner\_id bigint not null references partners (id), 49 user\_id bigint not null references users (id) 50); 51 52create table deal\_analytics ( 53 id bigint primary key generated always as identity, 54 deal\_id bigint not null references deals (id), 55 views int default 0, 56 claims int default 0, 57 last\_viewed\_at timestamp with time zone, 58 last\_claimed\_at timestamp with time zone 59); 60

This is already good enough for quick schema prototyping, it seems that the days you had to make an ER diagram and then create SQL statements based on that diagram are gone. Now with one natural query, the LLM generates the necessary SQL statements executes those in the instance running inside the browser and ER diagram of that schema is generated.

The tool that made it possible — PGlite

PGlite is a WASM (web assembly) build of Postgres packaged into a TypeScript/JavaScript client library. You can use it to run Postgres in the browser, Node.js, and Bun with no additional dependencies.

GitHub — electric-sql/pglite: Lightweight Postgres packaged as WASM into a TypeScript library for…
_Lightweight Postgres packaged as WASM into a TypeScript library for the browser, Node.js, Bun and Deno from…_github.com

This provides a number of use cases where it might be better than a full Postgres database:

  • Unit and CI testing.
  • Local development.
  • Remote development, or local web containers.
  • Generate and export reports.
  • Generate charts.
  • Build database diagrams.

Data persistance

PGlite supports multiple backends for data persistence:

  • The native file system when used in Node/Deno environments
  • IndexedDB and OPFS when used in the browser

The technical details

Postgres normally runs under a multi-process forking model, each client connection is handed off to a child process by the postmaster process. However, in WASM there is no support for forking processes, and limited support for threads.

Postgres has a relatively unknown built-in “single user mode” that is both single-process, and single-threaded. This is primarily designed to enable bootstrapping a new database, or for disaster recovery.

PGlite builds on the single user mode by adding Postgres wire protocol support, as standard Postgres only supports a minimal basic cancel REPL in single user mode. You can read more from the original blog post.


In conclusion, Supabase’s new tool is a significant advancement for developers, educators, and learners alike. This tool helps to streamline workflows, making it easier than ever to prototype, teach, and learn database concepts without the need for complex setups. Whether you’re testing queries or designing schemas, this innovation opens up new possibilities for working with Postgres in a lightweight, accessible environment.

We at Codeks are excited on using this tool to quickly prototype on new ideas for our clients.


Tired of starting from scratch when you want to build a new thing? Try out Codepilot, the ultimate SaaS Starter Kit with all the features you need to build websites fast, in addition if you need help with quickly implementing MVP, don’t hesitate to reach out.

Let's Build Something Amazing Together

Ready to transform your digital landscape? Contact Codeks today to book a free consultation. Let's embark on a journey to innovation, excellence, and success together.