Instant Postgres

Introduction

A demo that showcases how fast it is to provision a Postgres database on Neon.

Built using:

• Remix + Tailwindcss - frontend + styling
• Hono - API + end-to-end type-safety with Hono Client
• Upstash - rate limiting
• Cloudflare Pages - deployment
• Cloudflare Turnstile - bot protection
• DrizzleORM - ORM
• Neon - Database
• Turborepo - monorepo
• Github Actions - CI + CRON

How the app works

API

When you click on "Deploy Postgres", you send a POST request to a /postgres API endpoint that does the following:

Two middlewares run before the main handler:

• A rate limiter that limits requests based IP address.
• A validator that checks if the request body contains a cfTurnstileResponse string. This is a token generated by Cloudflare Turnstile that verifies if the request is coming from a real user and not a bot.

The endpoint then captures the request start time and retrieves the client's IP address from the request headers.

It checks for existing project data in a signed cookie, and if found, parses and returns this data in a success response (This way we don't provision a database for every API request). If no project data is found, it extracts the cfTurnstileResponse token from the request body and sends a POST request to the Cloudflare Turnstile API to verify the token.

Upon successful verification, it initializes a Neon API client and determines the closest region based on the IP's latitude and longitude, sending a request to create a new project with specified settings such as region, quota, and PostgreSQL version.

Then the created project info is inserted into a database so that it can be deleted after 5 mins (This is done using the CRON job defined in .github/workflows/cleanup-projects.yml). If successful, it constructs a new project data object with connection details and provisioning time, sets this data in a signed cookie with a five-minute expiration.

Finally, it calculates the total response time and returns a success response with the project data and the calculated response time.

Querying the database

When running queries using the inline SQL editor, you actually query the database directly from the browser. This is possible to the Neon Serverless Driver, which allows you to establish a connection to your database over WebSockets.

Local Development

To get started, you need to have Bun installed. You can then run the following command to install the project's dependencies:

bun install

This will install the dependencies for both the frontend and backend.

Both the API and the frontend require environment variables to be set in order to run properly.

For the API, you can run the following command to copy the .env.example file to a .env file with the necessary environment variables.

cp apps/api/.dev.vars.example apps/api/.dev.vars

For the frontend, you can run the following command to copy the .env.example file to a .env file with the necessary environment variables.

cp apps/web/.env.example apps/web/.env

Finally, you can then run bun dev to start the development server for both the frontend and backend. The frontend will be running athttp://localhost:3000 and the backend will be running at http://localhost:8787.

TODO

• Add Preview Deployments using Cloudflare Pages + Neon
• Add linting, formatting, and typechecking to existing Github Actions workflow
• [ ]