Forem: Xata

PostgreSQL Branching: Xata vs. Neon vs. Supabase - Part 2

AlexF — Mon, 30 Jun 2025 18:58:59 +0000

Introduction

In Part 1 we dissected how each platform branches a Postgres database under the hood. This post zooms in on pricing. We’ll explore how Xata, Neon, and Supabase pricing strategies stack up when you require:

CI preview databases for a five engineer team
HA deployment with two read replicas + always-on staging
Per-tenant isolation for SaaS offerings
Data-science sandboxes for model training
Autonomous agents with high branch churn

Pricing

One of the first questions any team asks when evaluating platforms that are critical to their infrastructure is “how much is this actually going to cost us?” Since each service has a different pricing model, this won't be an exact apples-to-apples comparison. I’ll break down the pricing for Xata, Neon, and Supabase, then provide some scenario-based comparisons to illustrate the differences. Please note that the pricing and packaging of each solution were collected on June 25, 2025.

Neon vs. Supabase vs. Xata

Focusing on branching, here’s a quick breakdown of the pricing plans.

Provider	Plans	What’s Included	Typical Instance Rates	Overage / Extras
Xata	Pay-as-you-go	Unlimited branches, pay for vCPU-hrs & GB-months only	Example: xata.small (2 vCPU / 2 GB) → $0.024 hr xata.xlarge (4 vCPU / 16 GB) → $0.192 hr	Storage $0.30 GB / mo
Neon	Launch $19 / mo Scale $69 / mo Business $700 / mo	Launch: 300 CPU-hrs & 10 GB Scale: 750 CPU-hrs & 50 GB Business: 1k CPU-hrs & 500GB storage	Compute charged in CPU-hrs Example: 2 vCPU for 1 hr = 2 CPU-hrs	CPU overage $0.16 per CPU-hr Storage overage $0.50–1.75 GB / mo
Supabase	Pro $25 / mo Team $599 / mo	$10 compute credit Pro: 1 Micro instance (2 CPU / 1 GB) & 8 GB storage Team: higher limits + support	Add-on instances: Micro $10 / mo Small $15 / mo Medium $60 / mo Large $110 mo XL $210 / mo	Instance add-ons charged by the month Defaults to Micro rate of $0.01344 / h Storage overage $0.125 GB /mo

Xata

We aimed for simple, transparent pricing. Xata’s pay-as-you-go model charges for compute instance hours and storage GB months, period. There are predefined instance sizes with a certain vCPU and RAM combination and an hourly rate for each. For example, a small dev instance (2 vCPUs, 2 GB RAM) is $0.024 per hour (~$18/month if running 24/7). Storage is $0.30 per GB per month, charged only on actual data usage since we don’t pre-allocate disk. All the additional features are included at no extra cost. We don’t charge separately for number of branches or anything like that. You can create unlimited branches and you’ll just pay for the storage difference they incur and any compute hours if those branches are spun up and running.

Neon

Neon uses a tiered plan model with a mix of free quotas and pay-as-you-go for overages. For instance, Neon’s Launch plan is $19/month which includes 300 hours of compute and 10 GB of storage. The Scale plan is $69/month and includes 750 compute hours and 50 GB storage. If you exceed the included hours or storage, you pay overage fees. Neon measures compute in “compute hours” which are basically CPU-hours; e.g., running a 2 CPU instance for 1 hour consumes 2 compute hours. Overage compute hours are billed at $0.16 per CPU-hour beyond the included amount. Storage overages are around $1.50–$1.75 per GB-month depending on tier.

Supabase

Supabase’s pricing has two components: the base plan fee and usage add-ons. Their Pro plan is $25/month base, which includes certain limits (like 8 GB database space, 100GB file storage, etc.) and importantly includes $10 of compute credits. Those credits cover a certain amount of database instance time. Supabase essentially offers fixed-size instance add-ons: a micro instance (roughly 2 CPU, 1GB RAM on ARM) costs $10/month, Small is $15, Medium $60, Large $110, etc. The $10 credit in the base plan means you can run one micro instance included. If you upgrade your database to a larger size or add more branches, you incur additional instance charges. Each branch environment is treated like an additional instance in terms of billing, and you’re only charged for the hours it’s actually running. The base $25 plan’s $10 credit does not apply to branch instances, it only covers the main project instance. So if you spin up branches, those consume compute credits separately (about $0.32 per day on a micro instance).

Cost scenarios

To make the analysis concrete, let’s compare costs in a few different theoretical scenarios. Please note that all scenarios are estimates, actual bills will vary. These scenarios assume certain architectural decisions and also do not take into account enterprise level discounts provided at certain scales for each offering.

Scenario 1: CI preview databases for a five engineer team

In this scenario, you have a production Postgres (~10 GB data) and a dev workflow where 5 developers frequently create isolated databases for feature work. Each dev branch is used ~3 hours per weekday for active development or running CI tests, then torn down. Additionally, you maintain a long-lived staging environment that is refreshed with production data nightly for final QA. Let’s compare monthly costs for enabling this on each platform.

TL;DR

Cost Breakdown	Xata (Pay-as-you-go)	Neon (Scale plan)	Supabase (Pro plan)
Baseline env (staging only*)	~$21. `xata.small` (2 vCPU / 2 GB) 24×7 ($18) + 10 GB storage ($3).	~$180. Persistent 2 vCPU staging instance; exceeds 750 CPU-hr quota, so ~$113 overage on top of the $69 base.	~$25 - 30. $25 Pro plan covers one always-on Micro instance for staging; add +$5 if you upsize staging to a Small ($15/mo, first $10 covered by plan credits).
Dev-branch usage (5 devs × 3 h/day)	~$4. 330h on micro instances ($0.012 / h).	~$5. 330 CPU-h of overage at ~$0.016 / CPU-h.	~$4.44. 330 branch hours ($0.01344 / h).
Estimated monthly cost	$25 - 30	$180 - 185	$29 - 34

Xata

Staging on xata.small runs $18 in compute plus $3 storage. Ephemeral dev branches add about $4 (minute-billed at $0.012 / h) and negligible storage, keeping the bill around $25 – 30/mo. Copy-on-write means you never pay to duplicate the 10 GB dataset. Granular billing also lets you leave a branch up for an unusually long CI run without being penalized by hourly rounding.

Neon

The Scale plan’s 750 compute-hour quota is mostly eaten by the always-on staging node (1,460 CPU-h), so 710h spill into overage at $0.16 / h. Add the $69 subscription and you arrive at about $183/mo. The short-lived dev branches fit inside what’s left of the allowance. Any spike in staging load or an extra CPU added for performance pushes more hours into overage, so monthly spend can fluctuate noticeably.

Supabase

The Pro plan’s $25 base buys you one always-on Micro instance, which is perfect for staging. Dev branch compute is only about $4.44 (330h × $0.01344/h). With that surcharge, monthly spend climbs to about $29–34. Keeping branch counts low or recycling a single shared preview databases are some ways to avoid this steady drip.

Scenario 2: HA deployment with two read replicas + always-on staging

To meet strict uptime and guarantee zero-downtime fail-over with realistic testing environments you’ll want to run one primary writer and two read replicas (three AZs total) with an always-on staging branch. This is a pretty common production setup. In our scenario, every node is an 8 vCPU / 32 GB instance holding the same 100 GB dataset. All four nodes will stay online 24 × 7.

Cost Breakdown	Xata (Pay-as-you-go)	Neon (Business plan)	Supabase (Team plan)
Primary + 2 replicas + staging (4 × 8 vCPU)	~$1,121. `xata.2xlarge` $0.384/h × 4 × 730h	~$4,278. $700 plan + (23,360 CPU-h − 1,000 incl.) × $0.16	~$2,240. $599 plan + 4 × 2XL compute ($0.562/h)
Storage	~$30. 100 GB × $0.30 / GB-mo	In-plan (≤500 GB included)	~$49. 400 GB total - 8 GB quota × $0.125/GB-mo
Estimated monthly cost	$1,151	$4,278	$2,289

Xata

Four always-on xata.2xlarge nodes run about $1,121/mo in compute. With copy-on-write, the primary, two replicas, and staging branch all reference the same 100 GB dataset. Adding only $30 for storage costs. The total cost ends up being $1,151/mo. Fail-over or promotion is simply a metadata flip and recovery time is nearly instant.

Neon

Each 8-vCPU endpoint burns 5,840 CPU-h per month. With four nodes that is 23,360 CPU-h. After the Business plan’s 1,000 CPU-h allowance, 22,360 CPU-h are billed at $0.16 ($3,578), plus the $700 subscription. Storage is CoW-backed, so the single 100 GB copy stays inside the 500 GB quota of the Business plan, keeping the bill at around $4.3k/mo.

Supabase

The primary, two read replicas and the staging branch all run on 2 XL compute add-ons (8 vCPU / 32 GB, $0.562 / h). Four nodes for a full month total about $1,641 in compute. Adding the $599 Team-plan base fee and storage for four physical copies (400 GB billed at $49) brings the cluster to about $2.3k per month. Because Supabase stores full files per database, storage grows linearly with every replica and the staging branch.

Scenario 3: Per-tenant isolation for SaaS offerings

A common SaaS enterprise pattern is tenant isolation, also described as having a database-per-customer. In this scenario, we assume 200 isolated customer branches, each holding about 10 GB of data and running on smaller instances (2 vCPU / 1 GB).

In this scenario, branches are live 9 AM – 5 PM on work days (~ 176h/month), then hibernate outside of those hours. Storage is always allocated, so the fleet’s overall footprint is about 2 TB.

TL;DR

Cost Breakdown	Xata (Pay-as-you-go)	Neon (Business plan)	Supabase (Team plan)
Tenant compute (200 × 176h)	~$422. 35,200h × $0.012 / h (`xata.micro`)	~$6,172. 35,200 CPU-h: 1,000 incl. = 34,200h × $0.16 + $700 base	~$1,072. 35,200h × $0.01344 / h + $599 base
Storage (~2 TB total)	$600. 2,000 GB × $0.30 / GB-mo	~$750. 1.5 TB over 500 GB allowance × $0.50 / GB-mo	~$249. 1,992 GB over 8 GB × $0.125 / GB-mo
Estimated monthly cost	$1,022	$6,922	$1,322

Xata

We bill strictly for what’s used. When a tenant branch sleeps, its compute cost drops to zero. Only storage continues at $0.30 / GB-mo. With all 200 customer databases active during the day, the PostgreSQL fleet’s compute adds up to about $422. Copy-on-write means each branch stores just the pages it changes, keeping the 2 TB footprint flat. As a result, for roughly $1k/month you can have 200 isolated tenants.

Neon

Every active vCPU is metered. Running even a single-CU compute for 176 hours racks up 176 CPU-h. Multiply that by 200 branches and it quickly adds up to 35,200 CPU-h. After the 1,000h allowance the overage alone is $5.5k, plus the $700 plan fee. Storage beyond 500 GB is another $750. Scale-to-zero helps overnight, but eight hours a day across 200 databases still adds up, landing the total near $7k/month.

Supabase

Branches are billed exactly like any other database, defaulting to $0.01344/h on the Micro instance. The fleet’s 35,200h therefore costs about $474 per month. Assuming certain features and support are required for this use case, add the $599 Team plan and about $249 for disk. The monthly total is roughly $1.3k.

Scenario 4: Data-science sandboxes for model training

In this scenario, five analysts spin up 2 TB clones on 32 vCPU / 128 GB nodes for supervised fine-tuning runs against their models one day each week. Each clone runs about 8 hours every Monday (~32 h/mo), so the platform logs 160 instance-hours a month. We estimate the storage growth by assuming the training job rewrites about 10% of the source pages (roughly 200 GB per branch). Multiplied by five analysts that yields to about 1 TB of new data for training.

TL;DR

Cost Breakdown	Xata (Pay-as-you-go)	Neon (Business plan)	Supabase (Team plan)
Model-training (32 vCPU × 160h)	~$246. `xata.8xlarge` at $1.536 / h × 160h	~$1,359. $700 plan + 5,120 CPU-h – 1,000 incl. = 4,120 × $0.16	~$410. 8 XL compute at $2.562 / h × 160h
Storage overhead (2 TB base + 1 TB deltas)	~$900. 3 TB total × $0.30/GB-mo	~$1,250. 2.5 TB over 500 GB allowance × $0.50/GB-mo	~$1,535. 12 TB total × $0.125 / GB-mo
Estimated monthly cost	$1,146	$2,609	$2,544

Xata

You pay strictly for the 160 hours on xata.8xlarge, about $246. Because the primary 2 TB production database also lives inside Xata, storage now totals 3 TB (2 TB base + 1 TB dirty pages) at $0.30/GB-mo, or roughly $900. Branches hibernate the remaining 672h each month, so there’s no idle-time compute charge. The total cost is about $1,146/mo, still linear to the minutes the big node is powered on and the pages that are actually modified.

Neon

The Business tier includes 1,000 CPU-hours and 500 GB storage for $700/mo. One 32-vCPU node burns 32 CPU per hour. Across 160h, that’s 5,120 CPU-h and 4,120h over that will be charged an overage cost $0.16 ($659). On top of this, you can add 2.5 TB of extra storage at $0.50/GB ($1,250), resulting at roughly $2.6k/mo for this use case.

Supabase

Each sandbox uses the 8 XL compute add-on ($2.562/h), so 160h costs $410. Supabase lacks copy-on-write for data pages, so each sandbox must hold a full 2 TB copy, bringing total stored data to 12 TB vs 3 TB like Xata and Neon. Add the $599 Team plan and $1.535k for 12 TB of storage, the total comes to $2.54 k/mo. Note that 12 TB exceeds the Team cap and an enterprise tier may be required, and multi-terabyte disk resizes add lead time before each training run.

Scenario 5: Autonomous agents with high branch churn

In the last few weeks, we’ve seen the importance of databases in the AI agent race explode. With Databricks recent acquisition of Neon and Snowflakes recent acquisition of CrunchyData, it’s fair to say PostgreSQL is a well positioned database for the use case. In this scenario, an AI platform creates 1,000 ephemeral branches every day, each on a small instance (2 vCPU/1 GB).

Because of the task oriented nature of the agents and speed of iteration, every branch lives only for about 30 min. In total, the PostgreSQL fleet logs 15,000 instance hours per month. In this hypothetical, the working set is tiny (20 GB total), and branches are dropped as soon as the agent finishes.

TL;DR

Cost Breakdown	Xata (Pay-as-you-go)	Neon (Scale plan)	Supabase (Pro plan)
Agent compute (15,000h)	~$180. 15,000h × $0.012 / h (`xata.micro`)	~$2,349. $69 plan + (15,000 CPU-h - 750 incl.) × $0.16	~$418. $25 plan - $10 credit + 30,000 billable h × $0.01344
Storage (20 GB)	~$6. 20 GB × $0.30 / GB-mo	Covered by the plan	$1.5. (20 GB – 8 GB) × $0.125
Estimated monthly cost	$186	$2,355	$420

Xata

Billing is minute-level at Xata. Each 30-minute micro branch costs $0.006. Aggregate that over the month and it’s about $180 in compute. Because branches are copy-on-write, the 20 GB hot set is only stored once, adding only $6. There are no control-plane limits throttle the 1,000-per-day create / delete cycle. In total, it’s about $186/mo for effectively unlimited amounts of ephemeral databases for your agents.

Neon

The Scale plan includes 750 CPU-h and 50 GB storage for $69. The workload uses 15,000 CPU-h, which after the 750h included, 14,250h overage at $0.16 adds $2 280. Bringing the bill to $2.35k. Storage fits inside the quota, and scale-to-zero doesn’t help because branches expire after 30 min.

Supabase

Branches are metered by the hour and rounded up. 30,000 billable hours × $0.01344 brings us to $403 for compute. Subtract the Pro plan’s $10 compute credit, add the $25 subscription fee and $1.5 for 12 GB extra storage the monthly total comes to just under $420.

Pricing analysis conclusion

Across all five scenarios the headline prices turn out to be only part of the story. And realistically, at certain scales discounts would come into play or different architectural decisions would be made. At the end of the day, Xata’s pay-as-you-go model remains linear. You add up vCPU-minutes and the copy-on-write storage actually in use. A 2 vCPU dev instance always costs $0.024h and an 8 vCPU production instance always costs $0.384h, no matter how many branches you keep around. Neon, by contrast, bundles a fixed pool of compute hours with each plan. Once a project’s vCPU-hours cross that quota, every extra hour is billed at $0.16 per CPU-hour. Supabase sets a low entry price for compute, but every branch is rounded up to the next full hour and large fleets for production-like data sets require full physical storage.

Those hidden costs quickly become the surprise line-items in your monthly invoice. In Neon, keeping a single 8-vCPU database online 24×7 burns 5,840 CPU-hours a month, over five times the Scale plans allowance. Overage quickly eclipses the base fee in long-running, high-CPU workloads. With Supabase, ephemeral use cases are rounded up to the hour and if there are a lot of them, can double your cost. Because Xata has neither quota overages nor per-branch levies, its costs stay proportional to the resources you use, making final invoices more predictable when workloads scale up or branch counts explode.

Wrapping it up

Branching a PostgreSQL database has officially moved from an interesting idea to being common in daily developer workflows. Xata, Neon, and Supabase each prove the pattern works and is reshaping the way developers and agents build, test, and ship software. With branching, the old bottlenecks of one shared developer database or the risk of production data leaking into your staging environment can disappear. Teams get parallel feature work, reproducible CI pipelines, and more efficient release practices.

Where the platforms diverge is in how their architectures convert that experience into dollars and developer experience. Xata keeps costs linear to the resources you actually hold open, Neon prioritizes burstable, serverless efficiency for short-lived workloads, and Supabase offers an all-in-one stack at a predictable entry price. Each model suits a different style of workflow. When considering adopting branching, make sure your choice matches your reality and expectations.

At Xata we spent a lot of time weighing those trade-offs and identifying who we’re building this platform for. Ultimately we landed on a vanilla-Postgres, copy-on-write storage layer that scales and improves performance, with an invoice that’s easily predictable. If that sounds interesting to you, we’re currently in private beta onboarding new teams every day. You can request access here or drop by our Discord to chat.

PostgreSQL Branching: Xata vs. Neon vs. Supabase - Part 1

AlexF — Wed, 25 Jun 2025 16:24:42 +0000

Introduction

As head of product at Xata, I’ve had a front-row seat into the recent (r)evolution of Postgres and the rise of database branching these last few years. Functionally, this capability mirrors the branching behavior in git allowing you to quickly clone or fork your data and schema alongside your application feature branch. With modern development practices and the speed at which agents are being adopted to build applications, the ability to branch a database has moved from a nice-to-have to a must-have.

Multiple platforms have emerged to tackle this, each with a unique approach. Neon popularized the idea of copy-on-write branches in Postgres, letting teams spin up full data copies in seconds. Supabase integrated database branching with git workflows, providing full-stack preview environments for every feature branch. With our new PostgreSQL platform at Xata, we’ve reimagined branching from the ground up to address the limitations we saw in existing solutions.

In this blog series I’ll compare the features, architecture and cost for Xata, Neon, and Supabase implementations. Because I go into a bit of detail, I’ll start each section with a comparison table and TL;DR for those of you that want the cliff notes.

Features

In this section, we’ll compare the different database branching and related features that each platforms supports.

TL;DR

All three platforms recognize the value of database branching, but they differ in both features and philosophy.

Feature	Xata	Neon	Supabase
Branching types	Full schema + data (copy-on-write) branches	Full schema + data (copy-on-write) branches	Schema only branches
PII and sensitive data	Built-in anonymization masking of PII and sensitive data	Masking possible through extensions or scripts	Masking is possible through seed scripts
Isolation and creation time	Fully isolated and instant branches regardless of data size	Fully isolated and instant branches regardless of data size	Fully isolated and branch creation time dependent on seed scripts
Merging schema changes	Built-in, zero-downtime merge of branch schema back to production	No built-in merge back to production, requires external tooling	No built-in merge back to production, requires external tooling and leans on code-as-truth
Deployment flexibility	Managed cloud offering, BYOC and on-premise supported, built on open-source solutions	Managed cloud service only, core offering open source managed by you	Managed cloud service only, core offering open source managed by you
Redundancy and HA	At both Postgres level and at storage level	At storage level only	At Postgres level only
Compatibility	Unmodified Postgres	Modified Postgres	Unmodified Postgres

Here’s a more in-depth overview of how Xata, Neon, and Supabase compare in these feature categories.

Branching types

Xata and Neon support instant copy-on-write branching that duplicates both schema and data. This means a new branch starts as a complete copy of the parent database’s data without physically copying it. Supabase’s branching, on the other hand, is currently schema-only. New branches include the schema and run migrations, but no data is copied from the parent. Supabase currently requires you to provide a seed script for the data. That could be random sample data or pulled from an existing database.

PII and sensitive data

Xata’s pipeline removes PII before branches exist. We use pgstream to replicate production into an internal staging replica, applying masking / anonymization rules during the initial snapshot and every subsequent WAL change. Because the replica already contains only scrubbed data, any branch you spin off that replica inherits the same protection automatically with no risk of an engineer seeing real emails or SSNs or PII ever leaving your prod environment.

Neon and Supabase have no built-in masking. Neon users typically install the open-source pg_anonymizer extension on a staging database and script a pg_dump → pg_restore workflow. It works whether production is on Neon or elsewhere but still requires exporting sensitive data out of production first. Supabase offers schema-only branches, so teams either rely on synthetic seed data or build a similar dump/anonymize/import process themselves.

Isolation and creation time

On all platforms, branch creation is effectively instant and the child branch is fully isolated from the parent. In Xata and Neon, this is achieved by copy-on-write at the storage layer. The new branch initially references the same data, and only deviates when writes occur. This means branch creation time does not depend on database size at all. A 1 TB database can be branched instantly on both Xata and Neon. A branch in Supabase depends on how fast migrations run and seed data loads. You pay the cost of applying DDL and inserting seed rows, which can take minutes to hours depending on your setup. Once created, changes in a branch don’t affect the parent.

Merging schema changes

Branching is only half the battle, eventually you want to merge changes to your schema back to the parent branch. Xata provides an integrated schema migration workflow that works with branches to facilitate zero-downtime schema changes (leveraging pgroll). Neither Neon nor Supabase offer an out-of-the-box solution for merging branch changes. With Neon, you typically apply schema migrations to the primary database manually (or with your migration tool / ORM) after testing in a branch. Supabase’s model relies heavily on your git workflow. When you merge your code branch to main, any new database migrations in that branch are automatically run on the production database.

Deployment flexibility

Xata can be run as a managed cloud service, in your own cloud (BYOC) or on-premises. Our new platform is cloud-agnostic. This optionality means you can keep data in-house or in-region for compliance purposes. It also allows you to use your existing cloud credits or pre-committed spend. The platform is built entirely from open-source solutions. Neon is a fully managed cloud service with its core technology open-sourced. Supabase is also a managed service with its core service open-sourced. Both solutions can be run in a self-managed fashion if you are willing to run, manage and support the infrastructure yourself.

Redundancy and HA

Xata offers double the protection at both the Postgres and storage layer with a multi-AZ Postgres replica set handled by CloudNativePG and an erasure-coded Simplyblock storage cluster. If either a compute pod or an entire zone fails, a new pod mounts the same copy-on-write volume and traffic resumes in seconds with no data loss. Neon concentrates its HA logic in the storage engine. WAL is synchronously streamed to Safekeepers across zones and rebuilt by the Pageserver, so the data is always durable. If a stateless compute endpoint dies you just spin up another one, but in-flight sessions are dropped. Supabase has simple Postgres semantics with each branch having its own VM/container. You can add a read replica or enable PITR, yet fail-over and cold-start behavior remain per-branch responsibilities, making HA simple but largely self-managed.

Compatibility

Xata’s platform runs vanilla PostgreSQL with no proprietary forking, it supports all Postgres extensions and features out of the box. In contrast, Neon’s approach involves a custom storage engine and components, meaning they maintain a modified Postgres backend to integrate with that system. As a result, extensions that assume low level disk access might require modifications. Similar to Xata, Supabase operates standard Postgres, so most extensions that don’t conflict with their platform can be used. From a developer’s standpoint, all three should feel like Postgres, but when you’re 100% compatible you’ll never hit a weird edge-case of “oh that extension isn’t supported”.

Architecture

Underneath the hood, Xata, Neon, and Supabase employ very different architectures to achieve database branching. The design decisions at this level have big implications for performance, reliability, and cost.

TL;DR

Here’s a quick overview of the different ways each platform approached branching in PostgreSQL.

Platform	Architecture	Branch creation	Pros
Xata	Stateless Postgres pods in Kubernetes via CNPG
NVMe-oF block-storage cluster (Simplyblock)	Controller writes a new block-index, CoW at block level	100 % vanilla PG, BYOC/on-prem, NVMe-class latency, built-in PII masking	Requires dedicated storage cluster; scale-to-zero in active development.
Neon	Stateless compute streams WAL to Safekeepers to Pageserver
Storage in S3	New timeline at an LSN, CoW at page level via WAL	Instant branches/PITR, scale-to-zero, active OSS community	Extra network hop means higher tail latency, overage costs, no managed BYOC
Supabase	One full Postgres + Auth + Edge stack per branch
Local disk per VM/container.	Forks schema, runs migrations, optional manual seed script	Simple “just Postgres”, whole backend cloned (auth, storage, funcs) for branches	Schema-only by default, heavy per-branch resources, cold-start after auto-pause, slow & costly to copy large datasets.

That’s the 10,000 foot view, let’s go a bit deeper.

Xata

Xata’s new platform uses a decoupled storage and compute architecture similar in spirit to Aurora, but with a critical difference. We do it strictly at the storage layer, without modifying Postgres itself. On the compute side, we run unmodified PostgreSQL instances in containers orchestrated by Kubernetes, using the CloudNativePG operator for high availability and failover. On the storage side, we’ve partnered with Simplyblock to provide a distributed block storage cluster accessible over NVMe-oF (NVMe over Fabrics).

In practical terms, when a Xata Postgres instance writes to disk, it’s writing to a virtual NVMe volume backed by our storage cluster. That storage cluster handles replication, durability, and importantly copy-on-write branching at the block level. We mount and unmount these virtual volumes dynamically for each branch and database.

The copy-on-write mechanism works roughly like this. Our storage cluster breaks the database into chunks (data blocks). When you create a new branch, it creates a new metadata index that initially points to all the same data blocks as the parent. No actual data is copied, so it’s instantaneous. If neither the parent nor child branch makes any writes, they remain two “views” of the same underlying data. When a write does occur, the affected chunk is copied and written to a new location, and the child’s index now points to that new chunk. The parent keeps pointing at the original chunk. This is textbook copy-on-write (CoW). Only diverging data is duplicated, and only at first write. Most chunks remain shared as long as they aren’t modified, saving a ton of space when branches are short-lived or only lightly edited (which has become quite a common use case with AI agents).

The benefit of Xata’s approach is that it behaves exactly like a normal Postgres instance, because it’s just Postgres, with CoW branching and elastic storage behind the scenes. We didn’t have to fork or alter Postgres to do this, and instead innovated at the storage layer and in orchestration. Our Postgres instances see what looks like a very fast disk. The heavy lifting of making that disk CoW-capable, highly redundant, and network-accessible is handled by the Simplyblock storage layer. We chose this path so that we maintain 100% Postgres compatibility (extensions, planner behavior, etc.) and can take advantage of the huge ecosystem around Postgres.

From a performance standpoint, Xata’s storage architecture is built for low-latency and high throughput. The storage nodes bypass the kernel using user-space drivers via SPDK (Storage Performance Development Kit) to squeeze every drop of performance from NVMe drives. Data is synchronously replicated across nodes with an erasure-coding scheme for fault tolerance (think RAID, but distributed at cluster level). In plain English: it’s fast and durable. We’ve benchmarked it and, even with a network hop, observed throughput and latency very close to well-tuned local-NVMe setups and markedly faster than typical cloud block storage (e.g., EBS/RDS) or modern serverless solutions thanks to these optimizations. We plan to publish these benchmarks soon.

Because the Xata stack has been built with a cloud-agnostic architecture that is out-of-the-box Postgres on Kubernetes with a self-contained storage layer, we can offer Bring-Your-Own-Cloud deployments with minimal lift. Our control plane can launch the Postgres compute nodes in your cloud account or bare metal servers, attaching to the Xata storage cluster over the network. The data stays in your environment and never traverses the public internet. This is a big advantage in enterprise scenarios where this level of scrutiny matters.

Neon

Neon can be credited for building the first open-source storage-compute separation design for Postgres. They implemented it by building a bespoke storage engine for Postgres. In Neon’s architecture, when a Postgres compute node runs, it does not write to local disk. Instead, it streams all its WAL (write-ahead log) to a service called Safekeepers. Safekeepers are like a durable WAL buffer. They accept the WAL entries and replicate them across availability zones to ensure they’re not lost. Meanwhile, another component called the Pageserver is responsible for actual data storage. Pageservers consume the WAL stream from Safekeepers and apply it to a database page store. They keep recently used pages in memory and also store data on disk or in cloud object storage as the source of truth.

How does Neon achieve branching? They use a non-overwriting storage format in the pageserver. Essentially copy-on-write at the page/version level. When you create a branch in Neon, under the hood it’s like creating a new timeline starting at a specific WAL LSN (log sequence number). The pageserver doesn’t duplicate all pages for the new branch; it just starts a new sequence of WAL for it. Initially, the branch shares all history with its parent until a divergence occurs. Only when new WAL comes in (writes on the branch) do new page versions get written. This is effectively the same outcome as Xata’s copy-on-write, just implemented via a logical WAL timeline mechanism rather than at the block device layer.

One effect of Neon’s approach is that the Postgres compute nodes are stateless. If a compute node crashes or is stopped (say due to inactivity), you can later spin up a new compute node, point it at the appropriate timeline (branch), and it will retrieve whatever pages it needs from the pageserver. This is how Neon achieves scaling to zero and fast cold starts. The database state persists in the pageserver/S3, and a compute node can be brought up on demand to service queries, then shut down when idle.

The trade-off is that Neon’s architecture introduces additional indirection and complexity in the performance-critical data path (WAL network hops, page retrieval on cache miss). In other words, there is some inevitable overhead compared to a single-node Postgres with network attached storage. Particularly for very write-heavy workloads or very random OLTP reads that can’t all fit in memory. For many developer and test workloads, this overhead is might be acceptable. At larger scale or sustained high throughput, data retrieval might observe higher tail latencies due to that extra network/storage indirection. This additional complexity is one of the disadvantages of running a modified version of Postgres.

Supabase

Supabase’s approach to branching is the simplest conceptually. Each branch is just a new Postgres instance (plus the accompanying services) created from scratch. When you create a branch in Supabase, the platform essentially provisions a new Postgres container (or VM) for that branch, sets up a blank database, runs your migration + seed scripts to create the schema and to populate data.

There is no special storage magic here like copy-on-write. If you want a branch to have production-like data, you have to script it. Today, that means writing a seed.sql that inserts sample rows or copying a subset of data from prod through your own processes. At the moment, Supabase explicitly does not clone your data into the branch.

Because each branch is a full Postgres instance, they are fully isolated at the OS level (not just at data level). Aside from shared control plane components, one branch’s activity can’t impact another’s performance since they don’t share underlying storage or CPU. This is good for isolation, but it means branches are heavier weight. Supabase mitigates cost by auto-pausing these preview branches when not in use. Unless the branch is marked as persistent, you’ll incur a cold start after it’s been idle for > 5 minutes. You can mark the branch as persistent and flip between ephemeral and persistent options.

Supabase branching is leveraging the standard Postgres stack and as a result, the performance on a branch is the same as performance on any Postgres instance of that size. There’s no extra overhead of remote storage or CoW bookkeeping. The trade-off is that branch creation is much slower if you wanted to copy large amounts of data. In practice, Supabase expects you to only seed a small amount of data that is enough for simple tests. That is fine for testing logic and migrations, but it’s not adequate if you want to, say, test a complex query on a full-size dataset or train your latest model on production-like data. In those cases, Xata’s or Neon’s approach of branching the actual data shines.

Because each Supabase branch is a standalone Postgres, if your production DB is, say, 50 GB, and you wanted a full copy for staging, you’d need another 50 GB in storage for the branch. Xata and Neon would not double your storage unless you modified all that data in the branch. So for storage-intensive scenarios, Supabase’s model can be costlier.

Conclusion

Database branching isn’t a checklist feature, it’s a fresh take on how you should operate and interact with a database.

Xata treats branches as light, block-level snapshots, so large teams can clone production-sized datasets safely and predictably.
Neon re-architects Postgres for serverless elasticity and instant branches.
Supabase keeps things familiar, favoring full stack branches with additional microservices around Postgres.

Features and architecture tell only half the story. What does this look like in the real world and how do these choices impact pricing and cost for customers? In part 2 we’ll translate architectures into invoices, walking through each pricing model and three common branching scenarios.

If you’re a Postgres user and are branch-curious, I invite you to give Xata a try. We’re currently in private beta and onboarding new teams on a daily basis. You can sign up for early access here. Feel free to pop into our Discord to ask any questions or simply say hi.

Writing an LLM Eval with Vercel's AI SDK and Vitest

Richard Gill — Thu, 03 Apr 2025 10:53:06 +0000

The Xata Agent

Recently we launched Xata Agent, an open-source AI agent which helps diagnose issues and suggest optimizations for PostgreSQL databases.

To make sure that Xata Agent still works well after modifying a prompt or switching LLM models we decided to test it with an Eval. Here, we'll explain how we used Vercel's AI SDK and Vitest to build an Eval in TypeScript.

Testing the Agent with an Eval

The problem with building applications on top of LLMs is that LLMs are a black box:

async function llm(prompt: string): string {
  // 1 Trillion parameter LLM model no human understands
  ...
}

The Xata Agent contains multiple prompts and tool calls. How do we know that Xata Agent still works well after modifying a prompt or switching model?

To 'evaluate' how our LLM system is working we write a special kind of test - an Eval.

An Eval is usually similar to a system test or an integration test, but specifically built to deal with the uncertainty of making calls to an LLM.

The Eval run output

When we run the Eval the output is a directory with one folder for each Eval test case.

The folder contains the output files of the run along with 'trace' information so we can debug what happened.

./eval-run-output/
├── evalResults.json
├── eval_id_1
│   ├── evalResult.json
│   ├── human.txt
│   ├── judgeResponse.txt
│   └── response.json
├── eval_id_2
│   ├── evalResult.json
│   ├── human.txt
│   ├── judgeResponse.txt
│   └── response.json

We’re using Vercel's AI SDK to perform tool calling with different models. The response.json files represent a full response object from Vercel’s AI SDK. This contains everything we need to evaluate the Xata Agent’s performance:

Final text response
Tool calls and intermediate ‘thinking’ the model does
System + User Prompts.

We then convert this to a human readable format:

System Prompt:
You are an AI assistant expert in PostgreSQL and database administration.
Your name is Xata Agent.
...

--------
User Prompt: What tables do I have in my db?
--------
Step: 1

I'll help you get an overview of the tables in your database. I'll use the getTablesAndInstanceInfo tool to retrieve this information.

getTablesAndInstanceInfo with args: {}

Tool Result:
Here are the tables, their sizes, and usage counts:

[{"name":"dogs","schema":"public","rows":150,"size":"24 kB","seqScans":45,"idxScans":120,"nTupIns":200,"nTupUpd":50,"nTupDel":10}]
...

--------

Step: 2

Based on the results, you have one table in your database: `dogs`

We then built a custom UI to see all all these outputs in so we can quickly debug what happened in a particular Eval run:

Using Vitest to run an Eval

Vitest is a popular TypeScript testing framework. To create our desired folder structure we have to hook into Vitest in a few places:

Get an id for the Eval run

To get a consistent id for each run of all our Eval tests we can set a TEST_RUN_ID environment variable in Vitest’s globalSetup.

export default defineConfig({
  test: {
    globalSetup: './src/evals/global-setup.ts'
    ...

import { randomUUID } from 'crypto';

export default async function globalSetup() {
  process.env.TEST_RUN_ID = randomUUID();
}

We can then create and reference the folder for our eval run like this: path.join('/tmp/eval-runs/', process.env.TEST_RUN_ID)

Get an id for an individual Eval

Getting an id for each individual Eval test case is a bit more tricky.

Since LLM calls take some time, we need to run Vitest tests in parallel using describe.concurrent . But we must then use a local copy of the expect variable from the test to ensure the test name is correct.

We can use the Vitest describe + test name as the Eval id:

import { describe, it } from 'vitest';

describe.concurrent('judge', () => {
  it.test('eval_id_1', ({ expect }) => {
    // note: we must use a local version of expect when running tests concurrently
    const fullEvalId = getEvalId(expect);
  });
});

export const getEvalId = (expect: ExpectStatic) => {
  const testName = expect.getState().currentTestName;
  return testNameToEvalId(testName);
};

export const testNameToEvalId = (testName: string | undefined) => {
  if (!testName) {
    throw new Error('Expected testName to be defined');
  }
  return testName?.replace(' > ', '_');
};

From here it’s pretty straightforward to create a folder like this: path.join('/tmp/eval-runs/', process.env.TEST_RUN_ID, testNameToEvalId(expect)).

Combining the results with a Vitest Reporter

We can use Vitest’s reporters to execute code during/after our test run:

import fs from 'fs';
import path from 'path';
import { TestCase } from 'vitest/node';
import { Reporter } from 'vitest/reporters';
import { testNameToEvalId } from './lib/test-id';

export default class EvalReporter implements Reporter {
  async onTestRunEnd() {
    const traceFolder = path.join('/tmp/eval-runs/', process.env.TEST_RUN_ID);
    const folders = fs.readdirSync(evalTraceFolder);

    // post run processing goes here

    console.log(`View eval results: http://localhost:4001/evals?folder=${evalTraceFolder}`);
  }

  onTestCaseResult(testCase: TestCase) {
    if (['skipped', 'pending'].includes(testCase.result().state)) {
      return;
    }

    const evalId = testNameToEvalId(testCase.fullName);

    const testCaseResult = {
      id: evalId,
      result: testCase.result().state as 'passed' | 'failed'
      // other stuff..
    };

    const traceFolder = path.join('/tmp/eval-runs/', process.env.TEST_RUN_ID, testNameToEvalId(expect));
    fs.writeFileSync(path.join(traceFolder, 'result.json'), JSON.stringify(testCaseResult, null, 2));
  }
}

Conclusion

Vitest is a powerful and versatile test runner for the TypeScript which can be straightforwardly adapted to run an Eval.

Vercel AI’s Response objects contain almost everything needed to see what happened in an Eval.

For full details check out the Pull Request which introduces in the open source Xata Agent.

If you're interested in monitoring and diagnosing issues with your PostgreSQL database check out the Xata Agent repo - issues and contributions are always welcome. You can also join the waitlist for our cloud-hosted version.

If pg_dump is not a backup tool, what is?

Cezzaine Zaher — Thu, 29 Aug 2024 12:30:00 +0000

We all remember the sad day when Pluto was decommissioned as a planet. I’ve always liked Pluto—maybe it’s the name, or maybe it’s because it was the smallest, and I like supporting the underdog. Either way, it was sort of my favorite planet.

Recently, while writing about the vulnerability affecting pg_dump, the topic of decommissioning pg_dump came up on Twitter. Unlike the nostalgic feelings many had for Pluto, there was less reluctance to see pg_dump reclassified. In fact, some people were eager to retire it as a backup utility, and I even got a bit of pushback for still referring to pg_dump as one 🙂

I was talking to my colleague Simona the other day, and she mentioned that everybody in Postgres circles says, "pg_dump is not a backup tool," but perhaps it’s not always explained well why it is not.

If you visit the pg_dump documentation page, it literally says this:

pg_dump is a utility for backing up a PostgreSQL database. It makes consistent backups even if the database is being used concurrently.

But there is an upcoming change in its future, starting from Postgres 18, thanks to a commit from Peter Eisentraut.

Commit: 4f29394ea941f688fd4faf7260d2c198931ca797

doc: Avoid too prominent use of "backup" on pg_dump man page

Some users inadvertently rely on pg_dump as their primary backup tool,
when better solutions exist. The pg_dump man page is arguably
misleading in that it starts with:

"pg_dump is a utility for backing up a PostgreSQL database."

This tones this down a little bit, by replacing most uses of "backup"
with "export" and adding a short note that pg_dump is not a
general-purpose backup tool.

Discussion: https://www.postgresql.org/message-id/flat/70b48475-7706-4268-990d-fd522b038d96%40eisentraut.org

I am pleased that we are using better wording to explain the scope of pg_dump in the Postgres documentation.

What pg_dump does is take a snapshot of the database at that moment. However, it excludes certain crucial components, like WAL files and the global objects in the entire Postgres cluster.

In a moment of database disruption, you will need to restore your backups. And if you only have a pg_dump export and did not also back up your WAL directory, you would lose some of the transaction history. If you are doing replication, that would complicate things and could result in longer downtime or a more extended maintenance window if it’s a planned operation.

Backups ideally should be continuous. You shouldn’t have to worry about managing WALs and other objects back and forth between servers. You should have regular backups, continuously adding to them, and ideally restore them regularly to ensure everything is going well. It’s also important to monitor their health.

I won’t go into details about different backup tools here, but suffice it to say, pg_dump is not sufficient alone as a backup mechanism. It is a useful utility and helps a lot, but it’s not something you should consider a complete solution for backing up your Postgres clusters.

Conclusion

To recap, let’s discuss a few shortcomings of pg_dump and clarify why a dump and a backup are different things.

pg_dump creates a snapshot of the database at a specific point in time. This means it captures the state of the database up to that moment but does not account for any changes made afterward. The standard pg_dump output is a plain-text file containing SQL statements to recreate the database schema and its contents. While it can be in various formats, SQL is the most common. This output is what we refer to as a dump.

However, this dump file does not include WAL, which are essential for point-in-time recovery and replication. Additionally, global objects such as roles and tablespaces are excluded from the dump, which can lead to inconsistencies during restoration, especially in multi-database setups.

In contrast, a backup is a broader concept that involves creating copies of both data and metadata to ensure that a database can be restored in case of failure, corruption, or data loss. Backups can be:

Full backup: A complete copy of the entire database, including all data and metadata.
Incremental backup: Captures only the changes made since the last backup.
Continuous backup: Continuously captures changes, providing ongoing protection.

Backups generally include WAL files, allowing for point-in-time recovery. They should be performed regularly, monitored for health, and restored periodically to ensure their integrity.

While pg_dump serves well for exporting data for migrations or versioning, it is not designed to handle full disaster recovery scenarios. For a robust and reliable backup strategy, tools like Barman, WAL-G, and pgBackRest are better suited to ensure data safety and quick recovery.

Why should you upgrade your PostgreSQL today?

Cezzaine Zaher — Wed, 28 Aug 2024 15:15:35 +0000

If you're running PostgreSQL versions 12, 13, 14, 15, or 16 and haven’t yet upgraded to the latest minor versions released on August 8, 2024, you might be exposed to a critical security vulnerability known as CVE-2024-7348. This vulnerability could allow attackers to execute arbitrary SQL code during pg_dump operations as the user running pg_dump, potentially compromising your entire database.

The vulnerability: CVE-2024-7348

CVE-2024-7348 is a Time-of-Check Time-of-Use (TOCTOU) race condition vulnerability in pg_dump, a utility used for backing up PostgreSQL databases. This vulnerability allows an attacker to replace a relation type (such as a table or sequence) with a view or foreign table right when pg_dump is running. Because pg_dump often runs with superuser privileges, this attack could execute arbitrary SQL code, leading to unauthorized actions or data corruption.

Affected versions:

PostgreSQL 12 (before 12.20)
PostgreSQL 13 (before 13.16)
PostgreSQL 14 (before 14.13)
PostgreSQL 15 (before 15.8)
PostgreSQL 16 (before 16.4)

If your PostgreSQL instance falls within any of these versions and has not been upgraded to the latest minor release, you are vulnerable to this exploit.

Understanding the risk

The attack described in CVE-2024-7348 is not just theoretical. An attacker with sufficient privileges to create and drop objects in the database could inject malicious SQL that pg_dump would then execute. This could be devastating, especially if pg_dump is running as a superuser, which is often the case in many environments.

Here’s how the attack works:

The attacker creates a non-temporary object in the database, like a sequence.
Just before pg_dump begins, the attacker replaces this object with a view or a foreign table that contains malicious SQL.
When pg_dump attempts to back up the database, it inadvertently executes the injected SQL code.

This sequence of actions is possible because pg_dump checks the type of object before starting the backup, but due to the race condition, the object can be replaced before the actual backup begins. The vulnerability hinges on the fact that the backup process trusts the integrity of database objects without verifying them at every step of the operation.

How the PostgreSQL team addressed the issue

To mitigate this vulnerability, the PostgreSQL team introduced a fix in the latest minor releases. The fix involves a new server parameter, restrict_nonsystem_relation_kind, which limits the ability to expand non-builtin views and access foreign tables during pg_dump. This parameter, when set, effectively closes the loophole that allows the race condition to be exploited.

However, it's important to note that the protection is only active if both pg_dump and the server are updated to the versions containing the fix. If either side is running an older version, the vulnerability could still be exploited.

Our experience at Xata

We first noticed the issue when a user reported a failing pg_dump query in the Xata Discord help channel.

The same error soon started appearing in some of our CI tests:

pg_dump: error: query failed: couldn't get first string argument
pg_dump: detail: Query was: SELECT set_config(name, 'view, foreign-table', false) FROM pg_settings WHERE name = 'restrict_nonsystem_relation_kind'

While checking the query, we noticed the restrict_nonsystem_relation_kind parameter being introduced that is linked to CVE-2024-7348.

For the query itself, we noticed the issue is on our side and began working on a fix, as shared by Tudor in our Discord channel:

If you'd like to join our friendly community, here's an invite to our Discord: Xata Discord.

What you should do next

Upgrade Immediately: If you're running an affected version of PostgreSQL, the solution is simple—upgrade to the latest minor version immediately. The following versions include the fix:

PostgreSQL 12.20
PostgreSQL 13.16
PostgreSQL 14.13
PostgreSQL 15.8
PostgreSQL 16.4

Test After Upgrading: After upgrading, thoroughly test your database operations, especially any automated backups or pg_dump processes, to ensure they function correctly and securely.

Review User Permissions: Consider reviewing the permissions of users who can create or drop database objects. Restricting these privileges can further reduce the risk of exploitation, even if a similar vulnerability arises in the future.

Conclusion

Security vulnerabilities like CVE-2024-7348 remind us of the importance of staying current with software updates, especially for mission-critical infrastructure like databases. By upgrading to the latest PostgreSQL versions, you not only protect your data but also ensure the continued reliability and security of your applications.

pgroll update 0.6.0

Cezzaine Zaher — Mon, 05 Aug 2024 10:41:14 +0000

pgroll is Xata's open-source schema migration tool for Postgres. The feature that sets it apart from other migration tools is its ability to perform multi-version schema migrations using the expand/contract pattern: while a migration is in progress, pgroll is able to present two versions of your database schema to client applications - the old version and the new. This means that client applications that depend on the old version of the database schema continue to function while the migration is in progress, making application deployments much simpler. Gone are the days of having to perform complicated multi-step workflows to do simple schema changes without breaking clients.

pgroll is open-source under the Apache-2.0 license and developed in the open on GitHub.

We released pgroll v0.1.0 in October 2023. Since then we've made eight further releases as we continue to build pgroll and turn it into a first-class open-source schema migration tool for Postgres.

In this post we'll have a look at the changes that went into the most recent release, v0.6.0.

What's new in pgroll v0.6.0

There was a three month gap between the release of v0.5.0 and v0.6.0. As a result the changelog for v0.6.0 is fairly extensive:

We'll pick out a few highlights from the changelog rather than going through every change in detail 😴

Retry on lock timeout errors

We actually wrote an entire blog post about the Postgres lock graph and why it's important for a migration tool like pgroll to use appropriate lock_timeout values on DDL statements and to automatically backoff and retry on lock aquisition failures. As of v0.6.0, pgroll now does exactly this. Rather than repeat the same information here, take a look at our blog post on schema migrations and the Postgres lock queue.

Run all DDL operations before running any data migrations

pgroll runs migrations using the expand/contract pattern which means that, for example, when adding a constraint to a column pgroll will create a new column, add the constraint to the new column and run a data migration to move data from the old column to the new, using the user-defined up and down SQL expressions in the migration file. The old column will be removed on migration completion.

As of v0.6.0, pgroll will now run all DDL operations before running any data migrations. Previously, any data migrations required by a pgroll operation would run inline immediately after the operation that required it. Data migrations are the most time-consuming part of a migration so by running them in a group after DDL has completed, pgroll ensures that the database schema is fully expanded before running data migrations. This also opens the door to allowing finer control over the data migration process in future releases, for example providing control over batch sizes, controlling the rate of data migrations or even running the data migration phase as an entirely separate step some time after DDL has completed.

Modification of column default values

pgroll now supports changing column DEFAULT values. A migration to do so looks like this:

{
  "name": "02_change_default",
  "operations": [
    {
      "alter_column": {
        "table": "events",
        "column": "name",
        "default": "'new default value'"
      }
    }
  ]
}

Changing a column default is a versioned operation so the column to which the new default is applied is duplicated and backfilled according to the up and down SQL supplied with the 'alter column' operation. up and down default to a simple copy of the field between new and old columns.

See the documentation for the alter column operation for more information.

Changing multiple column properties in one operation

One significant limitation in versions of pgroll prior to v0.6.0 was that alter_column operations could only change one 'property' of a column at a time. If, for example, you wanted to set a column to NOT NULL and add a CHECK constraint at the same time this would have to be done as successive migrations. As of v0.6.0 however, there is no restriction on the number of changes that can be made to the same column in the one operation.

A migration that changes multiple column properties in one operation looks like this:

{
  "name": "35_alter_column_multiple",
  "operations": [
    {
      "alter_column": {
        "table": "events",
        "column": "name",
        "name": "event_name",
        "type": "text",
        "default": "'new default value'",
        "nullable": false,
        "unique": {
          "name": "events_event_name_unique"
        },
        "check": {
          "name": "event_name_length",
          "constraint": "length(name) > 3"
        },
        "up": "(SELECT CASE WHEN name IS NULL THEN 'placeholder' ELSE name END)",
        "down": "name"
      }
    }
  ]
}

See the documentation for the alter column operation for more information.

Renaming constraints

Perhaps it's a smaller change than others listed here, but the ability to rename constraints is a useful addition to pgroll v0.6.0. A migration to rename a constraint looks like this:

{
  "name": "33_rename_check_constraint",
  "operations": [
    {
      "rename_constraint": {
        "table": "people",
        "from": "name_length",
        "to": "name_length_check"
      }
    }
  ]
}

See the documentation for the rename constraint operation for more information.

Running raw SQL migrations on completion

pgroll supports raw SQL migrations as an 'escape hatch' when there is no other way to perform a migration using the existing pgroll operations. SQL migrations offer none of the multi-version guarantees inherent in other pgroll operation types; when a SQL migration runs it is applied directly to the underlying schema and pgroll does not present versions of the schema before and after the change as it does for other operation types.

In versions of pgroll before v0.6.0, SQL migrations would always be run on migration start. As of v0.6.0, SQL migrations have the option to run on migration complete instead by setting the onComplete field. Such a migration looks like:

{
  "name": "32_sql_on_complete",
  "operations": [
    {
      "sql": {
        "up": "ALTER TABLE people ADD COLUMN birth_date timestamp",
        "onComplete": true
      }
    }
  ]
}

The ability to run a raw SQL migration on completion allows the SQL statement to reference the versioned views that pgroll creates for any non raw SQL operations in the same migration.

See the documentation for the raw SQL operation for more information.

Using pgroll as a module

The next two features relate to using pgroll from within other programs rather than as a standalone CLI tool. pgroll is written in Go and its core APIs can be imported and used as a module from other Go programs. These APIs are not stable yet; they will become stable as part of a v1.0.0 release, but can be used today by importing the relevant packages from the github.com/xataio/pgroll/ module:

import (
    "github.com/xataio/pgroll/pkg/migrations"
    "github.com/xataio/pgroll/pkg/roll"
    "github.com/xataio/pgroll/pkg/state"
)

pgroll v0.6.0 adds two new features that are exposed only when using pgroll as a module from within another Go program:

Rewriting or rejecting user input SQL

pgroll takes SQL as input from the user in various places in migration files:

up and down SQL for data migrations
up and down SQL for raw SQL migrations
column DEFAULT values

In most scenarios the fact that this is untrusted user input is not a problem; the typical use case for a migration tool like pgroll is for running migrations authored by the same set of users that own the database to which the migrations are applied. However in a multi-tenant environment, migration authors aren't the owners of the database instance where the migrations are run. In such a scenario, it may be desirable to restrict the range of allowable SQL expressions usable within pgroll migrations.

As of v0.6.0, pgroll allows rewriting and rejecting user input SQL expressions using the SQLTransformer interface and the roll.WithSQLTransformer option.

type SQLTransformer interface {
    TransformSQL(sql string) (string, error)
}

A SQL transformer can be provided as an option to the roll.New function:

m, err := roll.New(ctx, pgURL, schema, state,
  roll.WithSQLTransformer(sqlTransformer),
)

// ...

m.Start(...)

The provided SQL transformer will be used to rewrite and if necessary, reject, user input SQL expressions before they are executed by pgroll.

Hooks

pgroll v0.6.0 adds a new MigrationHooks struct that can be used to provide hooks that are called at various points during the migration execution process. The four hooks available in v0.6.0 are:

BeforeStartDDL - called before the DDL phase of migration start
AfterStartDDL - called after the DDL phase of migration start is complete
BeforeCompleteDDL - called before the DDL phase of migration complete
AfterCompleteDDL - called after the DDL phase of migration complete is complete

And the hooks struct itself is defined like this:

// MigrationHooks defines hooks that can be set to be called at various points
// during the migration process
type MigrationHooks struct {
    // BeforeStartDDL is called before the DDL phase of migration start
    BeforeStartDDL func(*Roll) error
    // AfterStartDDL is called after the DDL phase of migration start is complete
    AfterStartDDL func(*Roll) error
    // BeforeCompleteDDL is called before the DDL phase of migration complete
    BeforeCompleteDDL func(*Roll) error
    // AfterCompleteDDL is called after the DDL phase of migration complete is complete
    AfterCompleteDDL func(*Roll) error
}

Hooks can be provided to the roll.New function via an option on construction:

m, err := roll.New(ctx, pgURL, schema, state,
        roll.WithMigrationHooks(roll.MigrationHooks{
            BeforeStartDDL: func(m *roll.Roll) error {
                // ...
            },
            AfterStartDDL: func(m *roll.Roll) error {
                // ...
            },
            BeforeCompleteDDL: func(m *roll.Roll) error {
                // ...
            },
            AfterCompleteDDL: func(m *roll.Roll) error {
                // ...
            },
        }),
)


// Hooks will be executed at the appropriate points during the migration
m.Start(...)
m.Complete(...)

Using hooks allows for custom logic to be run at various points during the migration process. For example, a hook could be used to log the start and end of a migration, or to send a notification when a migration completes.

Conclusion

pgroll v0.6.0 is a significant release that adds a number of new features and improvements to the tool. But of course, we're not done yet - work on the next version continues apace, as we work towards a v1.0.0 release of pgroll.

We'd love to hear from you if you're using pgroll in your projects. We welcome external contributions to pgroll, either in the form of issues on the issue tracker or by getting involved with development directly by opening pull requests.

You can follow along with all the news from our recent launch week , or just pop into Discord and say hi 👋

Postgres major version upgrades with minimal downtime

Cezzaine Zaher — Thu, 01 Aug 2024 13:24:50 +0000

One of the challenges of running a Postgres database is upgrading to a new major version. Major version upgrades can introduce new features, performance improvements, and bug fixes, but they can also be complex and time-consuming.

The most accepted methods for upgrading Postgres to a new major are:

pg_upgrade for in-place upgrade, which involves stopping the server while the upgrade is being performed. Even if this usually happens very fast, it means taking the service down for a few minutes, plus leading with issues in the process during that downtime.
blue-green deployments, which leverages logical replication to create a standby replica (running a newer version) of the primary server. Once replication is done, a switch over is performed, involving cutting writes to the primary, and switching traffic to the replica. Details on this approach later.

Common issues with major Postgres upgrades are:

Downtime: Planned (and unplanned) downtime is not uncommon when a major version upgrade happens, impacting users and business.
Complex orchestration: Orchestrating a migration is a multi-step process that requires careful planning, with little room for errors.
Adapting clients: Not only the infra part, in many cases also client apps also need to be aware of the migration and update their database connection details to point to a new host once a migration is done (DNS or Proxies can help here).

For all these reasons, major version upgrades are often postponed and become a major event across multiple engineering teams.

Moving Database Branches in Xata

The most interesting feature from our dedicated clusters public beta announcement this week is the ability to move databases and branches between clusters. This allows Xata customers to move a database between two Postgres clusters while it's still being accessed. You can use this as a way to move a database from a shared cluster in our free tier to your own dedicated cluster, for better performance & isolation.

This feature also allows to perform near zero-downtime major Postgres version upgrades by moving a database between two clusters running on different versions.

How it works

We implemented a blue-green approach in order to offer the ability to move databases, hiding all the complexity behind a simple user experience.

In order to understand the process of moving a database, it is useful to explain some key parts of our infrastructure that contribute to the process: All Xata databases can be accessed from the same proxy service, that handles authentication and routes traffic to the cluster hosting the target database:

This allows us to have fine grained control over connections, ensure security and many features to come. In the case of moving databases, it guarantees that we are able to redirect traffic to the correct cluster as needed.

This is the process to move a database branch when requested:

We can divide it in four main steps:

Setup

In this process we recreate the database in the target cluster and configure replication to start syncing it.

Block schema changes in the source cluster. This allows us to replicate the database consistently, without missing any change.
Replicate the database schema in the target cluster.
Create a [publication (https://www.postgresql.org/docs/current/logical-replication-publication.html) in the source cluster.
Subscribe to it in the target.

Wait for initial sync

With the databases now replicating, we need to wait for the sync process to be up to date, so the replica is following close enough to the primary.

Check the replication slots created by the subscription, wait for their LSN distance to be low enough.

Switch

Target cluster (replica) is close enough to be promoted as the new primary. We start the switch process. It's critical that this step is performed as quickly as possible, as we want to keep downtime to a minimum:

Block writes to the source cell. We do this by revoking write permissions from the accessing roles.
Wait for the replication slot. With writes stopped, we need to ensure that all the ones that went through make it to the target cluster.
Switch traffic to the target cluster (new primary). All new connections will go to it.
Kill previously existing connections to the source cluster, ensuring they reconnect to the new one.

Cleanup

Cleanup all created slots + garbage in the source cell.

With these steps the process is done, the database is now accessible in the newer version. It's important to note that this process offers reasonable guarantees: reads work during the whole process, and writes are only blocked for a brief period of time (normally under 100ms).

Future work

We are excited about this new feature, and we have many ideas about how we could evolve it in the future, for example:

Providing more detailed information about the move progress.
Achieve real zero-downtime by holding, then redirecting writes in the proxy, effectively avoiding killing any connection.
Allow for schema changes while move happens.
Provide more fine grained control over the process. For instance, allowing users to decide when to switch, and allow to send traffic to the target cell before the switch, for instance to perform custom tests.

Let us know what you think

We are excited to share this with you and look forward to hearing your feedback! Want to see major version upgrades in action? Check out this quick demo video.

If you have any suggestions or questions, please open an feature request in Canny, reach out to us on Discord or follow us on X / Twitter.

You can recap on all of our launch week announcements every day on our launch week page 🦋

Introducing pgstream: Postgres replication with DDL changes

Cezzaine Zaher — Tue, 30 Jul 2024 16:57:35 +0000

Why?

At Xata, Postgres takes centre stage. And while it is our main database, we also offer other features that require us to extend its reach while keeping the data in sync. A good example of this is our full-text search feature, which enables the use of Elasticsearch on top of Postgres. To keep these two datastores in sync, we capture and identify data and schema changes in Postgres and push these modifications downstream to Elasticsearch with minimal latency. This is often referred to as CDC (Change Data Capture).

So now you know our use case, but why did we build our own replication tool? There's many established solutions out there, but we had very specific requirements, which included support for continuous tracking of schema changes (DDL). This was something that existing tooling didn't support at the time. Database schemas have a tendency to change over time - if your CDC tool doesn't support replicating them, you risk data loss and manual intervention to fix your pipeline. There had to be a better way!

We also wanted a solution that was easy to deploy and operate for both big and small setups, which isn't always the case for existing tooling.

Introducing pgstream

And so pgstream was born! pgstream is an open source CDC command-line tool and library. Some of its key features include:

Schema change tracking and replication of DDL changes: it's no surprise that this became an integral feature of pgstream, since it was one of the biggest requirements. We will go into a bit more detail on how this is implemented below.
Modular deployment configuration: pgstream modular implementation allows it to be configured for simple use cases, removing unnecessary complexity and deployment challenges - the only requirement for pgstream is a Postgres database! However, it can also easily integrate with Kafka for more complex use cases.
Elasticsearch/OpenSearch replication support: out of the box support for replicating Postgres data and schema changes to an Elasticsearch compatible store, with special handling of field IDs to minimise re-indexing caused by column renames.
Webhook support: out of the box support to invoke a webhook endpoint whenever your source data changes. Helpful for reacting to specific data changes seamlessly.

How does pgstream work?

Internally, pgstream is constructed as a streaming pipeline, where data from one module streams into the next, eventually reaching the configured output plugins. pgstream keeps track of schema changes and replicates them alongside the data changes to maintain a consistent view of the source data downstream. This modular approach makes adding and integrating output plugin implementations simple and painless.

Tracking schema changes

One of the main differentiators of pgstream is that it tracks and replicates schema changes automatically. How? It relies on SQL triggers that will populate a Postgres table (pgstream.schema_log) containing a history log of all DDL changes for a given schema. Whenever a schema change occurs, this trigger creates a new row in the schema log table with the schema encoded as a JSON value. This table tracks all the schema changes, forming a linearised change log that is then parsed and used within the pgstream pipeline to identify modifications and push the relevant changes downstream.

The schema and data changes are part of the same linear stream - the downstream consumers always observe the schema changes as soon as they happen, before any data arrives that relies on the new schema. This prevents data loss and manual intervention.

Architecture

Disclaimer: There are a lot of references in this section to the WAL (Write Ahead Logging). It refers to a sequential record of all changes made to a database, and a key component to Postgres replication.

Now, let's dive a little deeper into the stream!

At a high level, the internal implementation is split into WAL listeners and WAL processors.

WAL listener

A listener is anything that listens to WAL data, regardless of the source. It has a single responsibility: consume and manage the WAL events, delegating the processing of those entries to modules that form the processing pipeline. Depending on the listener implementation, it might be required to also have a checkpointer to flag the events as processed once the processor is done.

There are currently two implementations of the listener:

Postgres listener: listens to WAL events directly from the replication slot. Since the WAL replication slot is sequential, the Postgres WAL listener is limited to run as a single process. The associated Postgres checkpointer will sync the LSN (Log Sequence Number) so that the replication lag doesn't grow indefinitely.
Kafka reader: reads WAL events from a Kafka topic. It can be configured to run concurrently by using partitions and Kafka consumer groups, applying a fan-out strategy to the WAL events. The data will be partitioned by database schema by default, but can be configured when using pgstream as a library. The associated Kafka checkpointer will commit the message offsets per topic/partition so that the consumer group doesn't process the same message twice.

WAL processor

A processor processes a WAL event. Depending on the implementation it might also be required to checkpoint the event once it's done processing it as described above.

There are currently three implementations of the processor:

Kafka batch writer: it writes the WAL events into a Kafka topic, using the event schema as the Kafka key for partitioning. This implementation allows the sequential WAL events to fan-out, while acting as an intermediate buffer to avoid the replication slot to grow when there are slow consumers. It has an internal memory-guarded buffering system to limit the memory usage of the buffer. The buffer is sent to Kafka based on the configured linger time and maximum size. It treats both data and schema events equally, since it disregards the content.
Search batch indexer: it indexes the WAL events into an OpenSearch/Elasticsearch compatible search store. It implements the same kind of mechanism as the Kafka batch writer to ensure continuous processing from the listener, and it also uses a batching mechanism to minimise search store calls. The search mapping logic is configurable when used as a library. The WAL event identity is used as the search store document id, and if no other version is provided, the LSN is used as the document version. Events that do not have an identity are not indexed. Schema events are stored in a separate search store index (pgstream), where the schema log history is kept for use within the search store (i.e. read queries).
Webhook notifier: it sends a notification to any webhooks that have subscribed to the relevant WAL event. It relies on a subscription HTTP server receiving the subscription requests and storing them in the shared subscription store which is accessed whenever a WAL event is processed. It sends the notifications to the different subscribed webhooks in parallel based on a configurable number of workers (client timeouts apply). Similar to the two previous processor implementations, it uses an internal memory-guarded buffering system which separates the WAL event processing from the webhook sending, optimising the processor latency.

In addition to the implementations described above, there's an optional processor decorator, the translator, that injects some of the pgstream logic into the WAL event. This includes:

Data events:
- Setting the WAL event identity. If provided, it will use the configured id finder (only available when used as a library), otherwise it will default to using the table primary key/unique not null column.
- Setting the WAL event version. If provided, it will use the configured version finder (only available when used as a library), otherwise it will default to using the event LSN.
- Adding pgstream IDs to all columns. This allows us to have a constant identifier for a column, so that if there are renames, the column id doesn't change. This is particularly helpful for the search store, where a rename would require a re-index, which can be costly depending on the data.
Schema events:
- Acknowledging the new incoming schema in the Postgres pgstream.schema_log table.

What's next?

This is only the beginning! We plan to continue developing pgstream and exploring how it can make it easier to replicate data.

Here are some of the items in our development pipeline:

Support for multiple Kafka topics
Additional Postgres plugin support (pgoutput)
Advanced data stream filtering
Automatic backfill of existing data
Additional Kafka serialisation formats (avro)
Additional output plugin support (postgres,clickhouse, snowflake)

We are excited to share this with you and look forward to your feedback! Want to see pgstream in action? Check out this quick demo video.

If you have any suggestions or questions, please open an issue in our GitHub repo,
reach out to us on Discord or follow us on X / Twitter. We'd love
to hear from you and keep you up to date with the latest progress on pgstream.

Introducing multi-version schema migrations

Cezzaine Zaher — Sat, 27 Jul 2024 13:10:00 +0000

Today we are excited to announce multi-version schema migrations in Xata, which utilizes our open-source library pgroll under the hood. 🎉

Multi-version schema migrations tackle one of the most painful aspects of application deployment: keeping your application code and database schema in sync.

Multi-version schema migrations allow both the old and new versions of your database schema to be live and accessible during a schema migration. This greatly simplifies deployment of your applications as both the currently deployed and the new version of your application see the version of the database schema that they expect. And because both versions of the database schema are live at the same time, rollbacks become trivial.

Behind the scenes, Xata does the heavy-lifting to make this work by presenting different versioned views of your application schema to each version of your application. It then transparently migrates data between the old and new versions of your database schema as data is written to each version.

Application rollouts and database schema changes

Rolling out changes to applications in production can be complicated and error prone when there are also database schema changes required to support a new version. During the deployment of the new application version, there will be a period during which both old and new versions of the application are live simultaneously.

Developers have to carefully consider how the two application versions will be able to run simultaneously while the rollout is in progress. This has led to the rise of complicated multi-step workflows for performing simple schema changes, such as PlanetScale's six-step guide for performing a column rename operation.

Wouldn't it be easier if you could keep both the old and new versions of your database schema live at the same time during a schema migration and have applications connect to the right version?

Multi-version schema migrations in Xata

Multi-version schema migrations in Xata allow you to start a migration from Xata and have both versions of your database schema available to applications for the duration of the migration. When your application rollout is complete, the migration can be completed leaving you with just the final version of your database schema.

Let's take a look at how this works in practice.

First, we'll create a table in a fresh database, populate it with some data and then create a migration to add a NOT NULL constraint to a column in the table. We'll see how multi-version schema migrations allow two versions of the database schema, one with the constraint and one without, to be made available to applications.

Creating the schema

We'll use Xata to:

Create a simple users table with a name column
Populate the table with some random data

In a new Xata database (with Postgres access enabled), we can create a new table in the application and populate it with some random data:

With the table and data in place, we can create a migration to add a NOT NULL constraint to the name column.

The migration editor

The migration editor is used to apply migrations to the database schema. Migrations in Xata use pgroll, our open-source migration library, under the hood so the format of the migration is a JSON object that describes the changes to be made to the database schema.

We'll create a migration to add a NOT NULL constraint to the name column in the users table. The migration to do so looks like this:

[
  {
    "alter_column": {
      "table": "users",
      "column": "name",
      "nullable": false,
      "up": "(SELECT CASE WHEN name IS NULL THEN '(unknown)' ELSE name END)",
      "down": "name"
    }
  }
]

The SQL expression in the up field is used to migrate data from the old version of the name column that did not have the NOT NULL constraint to the new version of the column that does. This means the SQL expression needs to describe what to do with any NULL values that may already exist in the name field; here we are going to rewrite any NULLs to a placeholder value of (unknown). Any non-NULL values are copied over to the new version of the name column without modification.

The down SQL expression does the opposite; it describes how values written to the new version of the name column will be migrated back to the old version of the column. Here we simply copy the value without modification.

Arbitrary transformations can be applied to the data during the migration, allowing for complex data migrations to be performed as part of the schema migration.

We can take this migration and run it via the Migration Editor:

Once the migration is started, the table UI shows that we have two versions of the name column in the users table, one with the NOT NULL constraint and one without (this is the expand/contract pattern in practice):

The new version of the name column has been filled with the data from the old version of the column. Had there been any NULL values in the column, these would have been rewritten by the up data migration we specified in the migration file. The two versions of the name column are always kept in sync for the duration of the migration; as data is written into either one, it is migrated to the other using the up and down SQL expressions from the migration file.

The migration is now in progress and the two versions of the database schema are live and available to applications.

Connecting to each schema version

In order to be able to take advantage of multi-version schema migrations, applications need to be able to connect to the correct version of the database schema. This is done using the Postgres search_path setting. The search_path setting determines which schema will be searched for non-schema-qualified table access. By setting the search_path appropriately, client applications can access the version of the database schema that is compatible with the deployed application code.

Applications connect to a Xata database using a connection string:

postgresql://<YOUR_WORKSPACE_ID>:<YOUR_API_KEY>@us-east-1.sql.xata.sh/exampledb:main?sslmode=require

With the connection established, the application can now read and write data to the database using the correct version of the database schema by setting the search_path for the session. For example:

SET search_path TO <migration name>;

The name of the migration to use is the name of the active migration, which can be found in the schema history of the branch:

By setting the search_path to the name of the either the current version of the database schema or the previous version, an application can access the appropriate version of the database schema.

In the following example session, we can switch the search_path back and forth between the two versions and see how reads work from each version of the schema:

-- Work with the new version of the schema:
SET search_path TO mig_cq778qtlu0oe0bpredl0;

-- Retrieve the data from the `users` table
SELECT * FROM users ORDER BY name;

-- +--------------------------+----------------------+
-- | xata_id                  | name                 |
-- |--------------------------|----------------------|
-- | rec_cq7v4a18fe4f6dj1qjeg | as until             |
-- | rec_cq7v4a18fe4f6dj1qjhg | beautify ragged      |
-- | rec_cq7v4a18fe4f6dj1qjag | because up           |
-- | rec_cq7v4a18fe4f6dj1qj90 | cruelly oof          |
-- | rec_cq7v4a18fe4f6dj1qjbg | distant queasy       |
-- ...

-- Work with the old version of the schema:
SET search_path TO mig_cq778jdlu0oe0bpredk0;

-- Retrieve the data from the `users` table
SELECT * FROM users ORDER BY name;

-- +--------------------------+----------------------+
-- | xata_id                  | name                 |
-- |--------------------------|----------------------|
-- | rec_cq7v4a18fe4f6dj1qjeg | as until             |
-- | rec_cq7v4a18fe4f6dj1qjhg | beautify ragged      |
-- | rec_cq7v4a18fe4f6dj1qjag | because up           |
-- | rec_cq7v4a18fe4f6dj1qj90 | cruelly oof          |
-- | rec_cq7v4a18fe4f6dj1qjbg | distant queasy       |
-- ...

Both versions of the schema show the same data in their view of the users table - the data migration that was specified in the migration has kept the two in sync.

Next, we'll see how writes into each version of the schema work, particularly when a NULL value is written into the name field.

-- Switch back the new schema, which disallows `NULL`s in the `name` field
SET search_path TO mig_cq778qtlu0oe0bpredl0;

-- Attempt to insert a `NULL` value in the name field
INSERT INTO users(name) VALUES (NULL)
-- ERROR null value in column "name" of relation "users" violates not-null constraint

-- Switch back the old schema, which allows `NULL`s in the `name` field
SET search_path TO mig_cq778jdlu0oe0bpredk0;

-- Attempt to insert a `NULL` value in the name field
INSERT INTO users(name) VALUES (NULL)

-- Retrieve the data from the `users` table
SELECT * FROM users ORDER BY name DESC;

-- +--------------------------+----------------------+
-- | xata_id                  | name                 |
-- |--------------------------|----------------------|
-- | rec_cq7v88tqrj67kk4bk9l0 | <null>               |
-- | rec_cq7v4a18fe4f6dj1qj8g | yuck frantically     |
-- | rec_cq7v4a18fe4f6dj1qjf0 | worriedly disclose   |
-- | rec_cq7v4a18fe4f6dj1qjj0 | weaponize well-to-do |
-- | rec_cq7v4a18fe4f6dj1qjcg | tensely drafty       |
-- ...

-- Switch back the new schema
SET search_path TO mig_cq778qtlu0oe0bpredl0;

-- Retrieve the data from the `users` table
SELECT * FROM users ORDER BY name DESC;

-- +--------------------------+----------------------+
-- | xata_id                  | name                 |
-- |--------------------------|----------------------|
-- | rec_cq7v4a18fe4f6dj1qj8g | yuck frantically     |
-- | rec_cq7v4a18fe4f6dj1qjf0 | worriedly disclose   |
-- | rec_cq7v4a18fe4f6dj1qjj0 | weaponize well-to-do |
-- | rec_cq7v88tqrj67kk4bk9l0 | (unknown)            |
-- | rec_cq7v4a18fe4f6dj1qjcg | tensely drafty       |
-- ...

In the old version of the schema, the name field allows NULL values, so the INSERT statement succeeds and reads from the table show the new row with a NULL value in the name field. When the same data is read in the new version of the schema, the same row has the placeholder value of (unknown) that was defined in the migration.

This example session shows that two applications, connected to the same database but with their search_path set to different versions, are subject to different constraints and can have different views of the same data.

Completing a migration

Once the application rollout is complete and all applications have been updated to the new version, the migration can be completed. Completing a migration means that the old version of the database schema is removed, leaving only the new version.

Once a migration is completed, it cannot be rolled back. Before completing a migration, make sure that all applications that depend on the old version of the schema have been updated to the new version so that the old version of the database schema is no longer required.

Completing an in-progress migration can be done from various places in Xata, including the migrations widget on the dashboard:

Once completed, the table shows just one version of the name column in the users table again:

Rolling back a migration

Because multi-version schema migrations use the expand/contract pattern to apply schema changes, rollbacks become much easier. The old version of the database schema was never removed so a rollback means simply removing the expanded parts of the database schema (the new version of the users.name column in our example).

Like completing a migration, a migration can be rolled back from the migrations widget on the dashboard:

Once the migration is rolled back, the new version of the name column is removed from the users table and the database schema and data is in the same state as it was before the migration started.

Only active migrations can be rolled back; once a migration has been completed, it cannot be rolled back.

Conclusion

Multi-version schema migrations simplify one of the most common pain points when deploying applications: keeping the deployed versions of your applications and the database schema in sync.

This new feature allows you to deploy your applications and make database schema changes independently and with confidence.

We are excited to share this with you and look forward to your feedback! Want to see multi-version schema migrations in action? Check out this quick demo video.

Announcing the public beta for dedicated clusters

Cezzaine Zaher — Thu, 25 Jul 2024 18:58:29 +0000

Earlier this year we announced our serverless Postgres platform and an early access program for dedicated clusters. Both of these offerings are core to Xata's evolution to becoming a Postgres database platform for faster development and a worry-free data layer. Serverless Postgres unlocks decades worth of community, tooling and support for our customers. While dedicated clusters provide more predictable costs, better performance and a more scalable solution for large production workloads. The two pair perfectly together, and we'll be diving into what's new in this blog.

We're extremely excited to announce the next milestone in this evolution, our public beta for dedicated clusters 🎉

We also want to whole-heartedly thank our EAP customers for not only being early adopters, but providing amazing levels of feedback throughout the journey. Keep a look out, you'll all be receiving some premium Xata swag soon 💜

What is a dedicated cluster?

Today, most Xata databases live in a shared cell architecture. Our multi-tenant cells include a Postgres Aurora cluster, an Elasticsearch cluster, high availability built in and all the APIs and services required for development. Within each cell, we split the system for each workspace to include up to 2,000 active databases per cell before we have to spin up a new one. Unless you were an EAP customer, your Xata database is using this shared cell architecture.

Our dedicated clusters are essentially dedicated cells. So you have all the same benefits of the shared cell architecture, with less limitations caused by shared resources, more control over your infrastructure, decoupled usage based pricing and the confidence that Xata can scale with your business.

I won't get into the weeds today, but if you're interested in learning more, this blog and our documentation are great resources.

Get started with $1,000 in credit

To kick off this launch week right and celebrate our public beta release, we will be offering $1,000 in credit for Pro customers that start using dedicated cluster between now and its general availability later this year. This credit will be valid through the end of 2025, but you will only have a few months to qualify. As a customer, you do not need to apply for the credit. Any usage from a dedicated cluster will automatically be deducted and you have full control over what the deployment looks like.

If you're already a happy Xata customer, you can easily move all of your databases or a single branch to a dedicated cluster in just a few clicks.

What's new

The team has been hard at work over the last few months building out features and functionality to provide the best experience to our beta customers. Starting today you can spin up dedicated Postgres cluster classes ranging in size from db.t4g.medium to db.r6g.2xlarge in both us-east-1 and eu-central-1. We plan to introduce more classes, versions, regions and extensions in the coming months, so please let us know what you'd like to see.

Here are just some of the many features and functionality available starting today.

Customize your infrastructure for your use case

Whether you're provisioning a new cluster or editing the one you already have, there are numerous configuration options to make sure you have the appropriate infrastructure for your use case.

Today, our Postgres databases are Amazon Aurora instances. You can trust that your database will have the scalability, reliability and security that AWS is known for. With dedicated clusters you can configure both the Postgres engine version, cluster class and number of replicas for failover and query distribution.

Autoscaling is a great option to consider if you expect variable usage of your application and database. This will scale up or down as needed, with a simple capacity range you can define so you do not incur any unexpected costs.

Weekly maintenance windows and daily backup windows can be defined, with full control over when updates and version upgrades are applied.

Most of the configuration options we provide will impact the monthly cost, and there is a very simple estimated monthly cost presented to you before you provision the cluster.

As we look ahead, we plan to provide templates that include infrastructure and configuration best practices for specific use cases.

Visibility into usage of your cluster

Every dedicated cluster has an overview page, where you can get a sense of what the usage of your infrastructure looks like and where the bottlenecks may be occurring. This view provides visibility into CPU utilization, database connections, freeable memory, storage, receive and transmit throughput and read/write IO.

These are not only useful for monitoring usage, but will give you a sense for what the usage will cost on top of the dedicated infrastructure. For more details around the what the pricing looks like for dedicated clusters, you can look at our pricing page.

Extensions now supported

One of the drawbacks (and reasons for building dedicated clusters) is that shared clusters, while useful for most projects, have certain limitations because of the shared resources. There are simply more security precautions for every new supported piece of functionality we add. Naturally, we cannot provide the same supported extensions in shared clusters as we can in dedicated clusters.

We've started adding Postgres extensions based on popularity amongst our customer base, starting with plpgsql, uuid-ossp, pgcrypto, vector, postgres_fdw, pg_walinspect and postgis. We will continue to grow this library of extensions over time, but please let us know if there's one missing you'd like to see.

Move your database and branches between clusters

One of the most powerful features that come with this beta release, is the ability to move branches between clusters. You are able to take your main database or one of its branches and move it to another cluster in just a few clicks. We are using this internally to provide a forever-free tier, automatically moving inactive databases to a special cell meant for longer term storage and moving it back to an active cell if re-activated. Our CTO, Tudor Golubenco, dives into the economics of our free tier and the technical details behind it in this blog.

That is one internal use case for this functionality, but it opens up many possibilities for blue-green deployments, testing / developer environments and database management with zero downtime.

Complimenting the ability to move branches between clusters, you are now able to define a default cluster for your database.

When you choose a default cluster, any new branch within that database will automatically be created there. This is perfect for development workflows that use branching for staging environments or pull request based development. Your dedicated cluster could only be for your production database, while each branch created could be automatically created in a shared cluster or a dedicated development cluster.

Stay tuned, we'll dive into some use cases and workflows that are now possible with the movement of branches later this week.

SQL, unlocked

As you'll soon seen, dedicated clusters has only been a portion of our focus since our last launch week. We've also spent time improving our Postgres offering, the tooling around it and better supporting the use cases and workflows our customers have been asking for.

Our SQL offering is built on a proxy. This approach allows us to provide the best and most secure serverless experience possible. Over the last few months, we've been heads down on making sure that our Postgres offering works with the ORMs and tooling you know and love.

Even more compatible with your favorite clients and tooling

We've put a special focus on making sure Drizzle, Prisma, SQLAlchemy and Django ORMs work well with our platform. Common administrative and data exploration tools like DataGrip, pgAdmin and TablePlus have been put through the wringer to resolve any compatibility hiccups seen over the past few months.

With our recent support for multiple Postgres schemas, and a configurable search_path, our offering is now much more compatible with a growing set of clients and tools. Our list of supported statements continues to expand, and we aim to maximize compatibility in both shared and dedicated clusters in the coming months.

Thank you to all our early adopters for providing feedback early on, we couldn't have made this progress without you 🦋

Playground has been upgraded to Queries

Outside of the table editor, the most popular part of our application is the Playground. And we've seen significant growth in this workflow for SQL queries over the past year. With this release, we're upgrading the Playground to Queries. Queries will be much more prominent in the navigation, making it really easy to pick up where you left off. You can still run TypeScript and Python code, but we've significantly improved the SQL experience.

Previously, the output had only been JSON. We now default to a table view. You can run multiple query statements at once, automatically creating tabs for each of the outputs. All queries are persisted serverside, so they're ready for re-use wherever you are.

This is just the beginning for Queries. We plan to do much more here. Providing a table output with full feature parity to the editor, the ability to favorite or pin queries and eventually, make them shareable between users in (and outside of) your workspace. Keep on eye on this view, we'll be incrementally improving over time. If you have a specific feature request, feel free to drop it in here.

Watch a demo

Want to see dedicated clusters in action? Check out this quick demo video.

If you haven't tried out our serverless Postgres offering yet, it's really easy to get started. Simply sign up for Xata, use pg_dump to export your existing database and pg_restore to import into Xata.

We can't wait to see what you build 🦋

Build your own image gallery CMS

Cezzaine Zaher — Wed, 12 Jun 2024 11:00:00 +0000

In this post, I talk about how to create an image gallery CMS with Astro, Xata and Cloudflare Pages. You'll learn how to:

Set up Xata
Create a schema with different column types
Resize and blur images
Fetch all records without pagination
Handle forms in Astro using view transitions

Before you begin

Prerequisites

You'll need the following:

A Xata account
Node.js 18 or later
A Cloudflare account

Tech stack

Technology	Description
Xata	Serverless database platform for scalable, real-time applications.
Astro	Framework for building fast, modern websites with serverless backend support.
Tailwind CSS	CSS framework for building custom designs
Cloudflare Pages	Platform for deploying and hosting web apps with global distribution.

Setting up a Xata database

After you've created a Xata account and are logged in, create a database.

The next step is to create a table, in this instance uploads, that contains all the uploaded images.

Great! Now, click Schema in the left sidebar and create two more tables profiles and photographs. You can do this by clicking Add a table. The newly created tables will contain user profile data and user uploaded photograph(s) data respectively.

With that completed, you will see the schema.

Let’s move on to adding relevant columns in the tables you've just created.

Creating the schema

In the uploads table, you want to store all the images only (and no other attributes) so that you can create references to the same image object again, if needed.

Proceed with adding the column named image. This column is responsible for storing the file type objects. In our case, the file type object is for images, but you can use this for storing any kind of blob (e.g. PDF, fonts, etc.) that’s sized up to 1 GB.

First, click + Add column and select File.

Set the column name to image and to make files public (so that they can be shown to users when they visit the image gallery), check the Make files public by default option.

In the profiles table, we want to store the attributes such as user’s unique slug (the path of the url where gallery will be displayed of that user), their name, their image with it’s dimension, and it’s transformed image’s base64 hash. You’ll reap the benefits of storing the hash to create literally 0 Cumulative Layout Shift (CLS) page(s).

Proceed with adding the column named slug. It is responsible for maintaining the uniqueness of each profile that gets created. Click + Add a column, select String type and enter the column name as slug. To associate a slug with only one user, check the Unique attribute to make sure that duplicate entries do not get inserted.

In similar fashion, create name, image, height and width columns as String type (but not Unique).

Great, you can also store imageHash as Text type so that you can instantly retrieve the image’s blur hash sized up to 200 KB. While String is a great default type, storing more than 2048 characters would require you to switch to the Text type. Read more about the limits in Xata Column limits.

Click + Add a column and select the Text type.

Enter the column name as imageHash and press Create column.

Much similar what we did above, in the photographs table, we create name, tagline, image, height, width, profile-slug, and slug as String type and imageHash as the Text type column. The columns slug and profile-slug refer to photograph’s and user profile’s slug respectively.

Lovely! With all that done, the final schema will look something like the following...

Setting up your project

To set up, clone the app repo and follow this tutorial to learn everything that's in it. To fork the project, run:

git clone https://github.com/rishi-raj-jain/image-gallery-cms-with-astro-xata-cloudflare
cd image-gallery-cms-with-astro-xata-cloudflare
pnpm install

Configure Xata with Astro

To use Xata with Astro seamlessly, install the Xata CLI globally:

npm install @xata.io/cli -g

Then, authorize the Xata CLI so it is associated with the logged in account:

xata auth login

Great! Now, initialize your project locally with the Xata CLI command:

xata init --db https://Rishi-Raj-Jain-s-workspace-80514q.ap-southeast-2.xata.sh/db/image-gallery-cms-with-xata-astro-cloudflare

Answer some quick one-time questions from the CLI to integrate with Astro.

Implementing form actions in Astro

You can also allow transitions on form submissions by adding the ViewTransitions component.
Here’s an example of the enabled form actions with view transitions in src/layouts/Layout.astro:

---
// File: src/layouts/Layout.astro

import { ViewTransitions } from "astro:transitions";
---

<html>
  <head>
    <ViewTransitions />
    <!-- stuff here -->
  </head>
  <body>
    <!-- stuff here -->
  </body>
</html>

This allows you to colocate the backend and frontend flow for a given page in Astro. Say, you accept a form submission containing the name, slug, and the image URL of the user, process it on the server to generate a blur Base64 hash, and sync it with your Xata serverless database. Here’s how you’d do all of that in a single Astro route (src/pages/profie/create.astro).

---
// File: src/pages/profile/create.astro

const response = { form: false, message: '', created: false, redirect: null }

// ...

if (Astro.request.method === 'POST') {
  try {
    // Indicate that the request is being processed
    response.form = true

    // Get the user email from the form submissions
    const data = await Astro.request.formData()

        // Get the user slug, name, and image: URL, width, and height from the form submissions
    const userSlug = data.get('slug') as string
    const userName = data.get('name') as string
    const userImage = data.get('custom_upload_user__uploaded_image_url') as string
    const userImageW = data.get('custom_upload_user__uploaded_w') as string
    const userImageH = data.get('custom_upload_user__uploaded_h') as string

        // Create a blur url of the user image

        // Create the user record with the slug

        // Redirect user to the next step
  } catch (e) {
    // pass
  }
}
---

<form method="post" autocomplete="off">
  <Upload selector="user" />
  <input required name="name" type="text" placeholder="Name" />
  <input
    required
    name="slug"
    type="text"
    placeholder="Slug (e.g. rishi-raj-jain)"
  />
  <button type="submit">
    Create Profile &rarr;
  </button>
</form>

Handling image uploads server-side with the Xata SDK

As Cloudflare Pages request body size allows up to 100 MB, you’ll be able to handle image uploads on the server-side. Create an Astro endpoint (src/pages/api/upload/index.ts) to receive POST requests containing image binaries and use the Xata SDK to store them in the uploads table.

After doing sanity checks on the request body, first create a new (empty) record in your uploads table, and then use it as a reference to place the image (buffer) using the Xata TypeScript SDK. Once successfully completed, the endpoint responds with the image’s public URL, height and width back to the front-end to include in the form fields.

// File: src/pages/api/upload/index.ts

import { json } from '@/lib/response';
import { getXataClient } from '@/xata';
import type { APIContext } from 'astro';

// Import the Xata Client created by the Xata CLI in src/xata.ts
const xata = getXataClient();

export async function POST({ request }: APIContext) {
  const data = await request.formData();
  const file = data.get('file');

  // Do sanity checks on file

  try {
    // Obtain the uploaded file as an ArrayBuffer
    const fileBuffer = await file.arrayBuffer();

    // Create an empty record in the uploads table
    const record = await xata.db.uploads.create({});
    // Using the id of the record, insert the file using upload method
    await xata.files.upload({ table: 'uploads', record: record.id, column: 'image' }, fileBuffer, {
      mediaType: file.type
    });

    // Read the inserted image
    const { image } = await xata.db.uploads.read(record.id);

    // Destructure its dimension and public URL
    const { url, attributes } = image;
    const { height, width } = attributes;
    return json({ height, width, url }, 200);
  } catch (error) {
    // Handle errors
  }
}

Using Xata image transformations to create blurred images

Once a user submits their profile on the /profile/create page, before creating a record in the profiles table, generate a Base64 buffer of their blurred images. To create blured images from the originals, use Xata image transformations. With Xata image transformations, you’re able to request an on-demand public URL which resizes the image to given height and width, and blur the image. In this particular example, you can resize the image to 100 x 100 dimensions and blur it up to 75% from the original.

---
// File: src/pages/profile/create.astro

// Import the Xata Client created by the Xata CLI in src/xata.ts
import { getXataClient } from '@/xata'

// Import the transformImage function by Xata Client
import { transformImage } from '@xata.io/client'

const response = { form: false, message: '', created: false, redirect: null }

// ...

if (Astro.request.method === 'POST') {

    // Fetch the Xata instance
    const xata = getXataClient()

    // ...

    // Create a blur URL of the user image

  // Using Xata image transformations to obtain the image URL
    // with a fixed height and width and 75% of it blurred
  const userBlurURL = transformImage(userImageURL, {
    blur: 75,
    width: 100,
    height: 100,
  })

  // Create a Base64 hash of the blur image URL
  const userBlurHash = await createBlurHash(userBlurURL)

    // Create the user record with the slug

    // Redirect user to the next step
}
---

Syncing profiles using the Xata SDK

After you have generated blurred images, the last step in publishing profiles (similar to what’s done in publishing photographs) is to create a user record with relevant details using the Xata TypeScript SDK’s create command. In Astro, we then set the successful message for the user before they are redirected to the photograph upload page. The integrated conditional rendering ensures a visual cue of the operation's success or failure, providing a responsive and user-friendly experience.

---
// File: src/pages/profile/create.astro

// Import the Xata Client created by Xata CLI in src/xata.ts
import { getXataClient } from '@/xata'

const response = { form: false, message: '', created: false, redirect: null }

// ...

if (Astro.request.method === 'POST') {

    // ...

    // Create the user record with the slug
  await xata.db.profiles.create({
    slug: userSlug,
    name: userName,
    image: userImage,
    width: userImageW,
    height: userImageH,
    imageHash: userBlurHash,
  })

    // Send the user to photograph upload page
  response.redirect = '/photograph/create'

  // Set the relevant message for the user
  response.message = 'Published profile succesfully. Redirecting you to upload your first photograph...'
}
---

<!-- Render conditional states using the onboarded flag -->
{
  response.form &&
    (response.created ? (
      <p class="rounded bg-green-100 px-3 py-1">{response.message}</p>
    ) : (
      <p class="rounded bg-red-100 px-3 py-1">{response.message}</p>
    ))
}

<!-- Profile Form -->

<!-- Redirect user to the next location -->
<script define:vars={{ success: response.created, location: response.redirect }}>
  if (success) {
    setTimeout(() => {
      window.location.href = location
    }, 1000)
  }
</script>

Using Xata query

The user profile page (src/pages/[profile]/index.astro) leverages the Xata Client to dynamically fetch and display all the photographs (not paginated) for a specific user profile. To engage users visually as soon as they open the gallery, we use the stored imageHash value as the background of the images (to be loaded). To prevent CLS, we use the stored width and height values to inform the browser of the expected dimensions of the images.

---
// File: src/pages/[profile]/index.astro

// Import the Xata Client created by Xata CLI in src/xata.ts
import { getXataClient } from '@/xata'

import Layout from '@/layouts/Layout.astro'

// Get the profile slug from url path
const { profile } = Astro.params

// Fetch the Xata instance
const xata = getXataClient()

// Get all the photographs related to the profile
const profilePhotographs = await xata.db.photographs
                                                        // Filter the results to the specific profile
                                                        .filter({ 'profile-slug': profile })
                                                        // Get all the photographs
                                                        .getAll()
---

<Layout className="flex flex-col">
  <div class="columns-1 gap-0 md:columns-2 lg:columns-3">
    {
      profilePhotographs.map(
        ({ width: photoW, height: photoH, name: photoName, image: photoImageURL, tagline: photoTagline, imageHash: photoImageHash }, _) => (
                    {/* Destructure the width and height to prevent CLS */}
          <img
            width={photoW}
            height={photoH}
            alt={photoName}
            src={photoImageURL}
                        {/* Do not lazy load the first image that's loaded into the DOM */}
            loading={_ === 0 ? 'eager' : 'lazy'}
            class="transform bg-cover bg-center bg-no-repeat will-change-auto"
                        {/* Create a blur effect with the imageHash stored */}
            style={`background-image: url(${photoImageHash}); transform: translate3d(0px, 0px, 0px);`}
          />
        ),
      )
    }
  </div>
</Layout>

Deploy to Cloudflare Pages

The repository is ready to deploy to Cloudflare. Follow the steps below to deploy seamlessly with Cloudflare 👇🏻

Create a GitHub repository with the app code.
Click Create application in the Workers & Pages section of Cloudflare dashboard.
Navigate to the Pages tab and select Connect to Git.
Link the created GitHub repository` as your new project.
Scroll down and update the Framework preset to Astro.
Update the environment variables from the .env locally.
Click Save and Deploy and go back to the project Settings > Functions.
Add nodejs_compat to the Compatibility flags section
Deploy! 🚀

Why Cloudflare Pages?

Cloudflare Pages stood out for this particular use case as it offers up to 100 MB request body size in their Free plan. This helped to bypass the 4.5MB request body size limit in various serverless hosting providers.

More information

For a more detailed insights, explore the references cited in this post.

Demo Image Gallery	https://image-gallery-cms-with-astro-xata-cloudflare.pages.dev/rishi
GitHub Repo	https://github.com/rishi-raj-jain/image-gallery-cms-with-astro-xata-cloudflare
Astro with Xata	https://xata.io/docs/getting-started/astro
Astro View Transition Forms	https://docs.astro.build/en/guides/view-transitions/#transitions-with-forms
Xata File Attachments	https://xata.io/docs/sdk/file-attachments#upload-a-file-using-file-apis
Xata Transformations	https://xata.io/docs/sdk/image-transformations
Xata Get Records	https://xata.io/docs/sdk/get#the-typescript-sdk-functions-for-querying
Cloudflare Workers Limits	https://developers.cloudflare.com/workers/platform/limits/#request-limits

What's next?

We'd love to hear from you if you have any feedback on this tutorial, would like to know more about Xata, or if you'd like to contribute a community blog or tutorial. Reach out to us on Discord or join us on X | Twitter. Happy building 🦋

Create your own content management system with Remix and Xata

Cezzaine Zaher — Fri, 07 Jun 2024 14:37:44 +0000

In this post, you'll create a content CMS using Xata, Remix, Novel, LiteLLM, and Vercel. You'll learn how to:

Set up Xata
Create a schema with different column types
Handle forms in Remix using Form Actions
Implement Client Side Image Uploads
Use an AI-powered WYSIWYG Editor
Implement content-wide search
Create dynamic content routes with Remix

Before you begin

Prerequisites

You'll need the following:

A Xata account
Node.js 18 or later
An OpenAI account
A Vercel Account

Tech Stack

Technology	Description
Xata	Serverless database platform for scalable, real-time applications.
Remix	Framework for building full-stack web applications with focus on Web Standards.
litelln	Call all LLM APIs using the OpenAI format.
Nove	A Notion-style WYSIWYG editor with AI-powered autocompletion
TailwindCSS	CSS framework for building custom designs
Vercel	A cloud platform for deploying and scaling web applications.

Setting up a Xata Database

After you've created a Xata account and are logged in, create a database.

The next step is to create a table, in this instance uploads, that contains all the uploaded images.

Great, now click on Schema in the left sidebar and create one more table content. You can do this by clicking Add a table. The tables will contain user content and user uploaded photographs. With that completed, you will see the schema as below.

Let’s move on to adding relevant columns in the tables you've just created.

Creating the Schema

In the uploads table, you want to store all the images only (and no other attributes) so that you can create references to the same image object again, if needed.

First, click + Add column and select File.

Set the column name to photo and to make files public (so that they can be shown to users when they visit the image gallery), check the Make files public by default option.

In the content table, we want to store the attributes such as content’s unique slug (the path of the url where content will be displayed), title, author name, author’s image with it’s dimensions, and content’s og image with it’s dimensions.

Proceed with adding the column named slug. It is responsible for maintaining the uniqueness of each content that gets created. Click + Add a column, select String type and enter the column name as slug. To associate a slug with only one content, check the Unique attribute to make sure that duplicate entries do not get inserted.

In similar fashion, create title, author_name, author_image_url, og_image_url, author_image_w, author_image_h, og_image_w, og_image_h as String type (but not Unique).

Great, you can also store the user content as Text type. While String is a great default type, storing more than 2048 characters would require you to switch to the Text type. Read more about the limits in Xata Column limits.

Lovely! With all that done, the final schema shall be as below 👇🏻

Setting up the project

Clone the app repository and follow this tutorial; you can fork the project by running the following command:

git clone https://github.com/rishi-raj-jain/remix-wysiwyg-litellm-xata-vercel
cd remix-wysiwyg-litellm-xata-vercel
npm install

Configure Xata with Remix

To seamlessly use Xata with Remix, install the Xata CLI globally:

npm install @xata.io/cli -g

Then, authorize the Xata CLI so it is associated with the logged in account:

xata auth login

Great! Now, initialize your project locally with the Xata CLI command. In this command, you will need to use the database URL for the database that you just created. You can copy the URL from the Settings page of the database.

xata init --db https://Rishi-Raj-Jain-s-workspace-80514q.us-east-1.xata.sh/db/remix-wysiwyg-litellm-xata-vercel

Answer some quick one-time questions from the CLI to integrate with Remix.

Implementing form actions in Remix

With Remix, Route Actions are the way to process form POST request(s). Here’s how we’ve enabled form actions to process the form submissions and insert records into the Xata database.

import { Form } from '@remix-run/react'
import { ActionFunctionArgs, json, redirect } from '@remix-run/node'

export async function action({ request }: ActionFunctionArgs) {
    // Get the form data
    const body = await request.formData()
}

export default function Index() {
  return (
    <Form navigate={false} method="post" className="mt-8 flex flex-col">
      {% my form elements %}
    </Form>
  )
}

This allows you to colocate the serverless backend and frontend flow for a given page in Remix. Say, you accept a form submission containing the title, slug, and the content’s HTML, process it on the server, and sync it with your Xata serverless database. Here’s how you’d do all of that in a single Remix route (app/routes/_index.tsx).

// app/routes/_index.tsx

import { Editor } from 'novel';
import { Form } from '@remix-run/react';
import { getXataClient } from '@/xata.server';
import Upload from '@/components/Utility/Upload';
import { ActionFunctionArgs, json, redirect } from '@remix-run/node';

export async function action({ request }: ActionFunctionArgs) {
  // Import the Xata Client created by the Xata CLI in app/xata.server.ts
  const xata = getXataClient();
  // Get the form data
  const body = await request.formData();
  const slug = body.get('slug') as string;
  const title = body.get('title') as string;
  const content = body.get('content-html') as string;
  // Sync the attributes to the content table in Xata
  await xata.db.content.create({ slug, title, content });
}

export default function Index() {
  return (
    <Form navigate={false} method="post">
      <span>New Article</span>
      <span>Title</span>
      <input required autoComplete="off" id="title" name="title" placeholder="Title" />
      <span>Content</span>
      <input required id="content-html" name="content-html" />
      <span>Slug</span>
      <input required autoComplete="off" id="slug" name="slug" placeholder="Slug" />
      <button type="submit">Publish &rarr;</button>
    </Form>
  );
}

Handling Client Side Image Uploads with Xata

To let user add their own custom OG Image with the content, we use Xata Upload URLs to handle image uploads on the client side. There are 2 steps to make a successful client side image upload with Xata and Remix:

Create a record with empty photo base64Content and obtain the photo’s uploadUrl.

// app/routes/api_.image.upload.tsx

import { json } from '@remix-run/node';
import { getXataClient } from '@/xata.server';

export async function loader() {
  const xata = getXataClient();
  // Use the Xata client to create a new 'photo' record with an empty base64 content
  const result = await xata.db.uploads.create({ photo: { base64Content: '' } }, ['photo.uploadUrl']);
  return json({ uploadUrl: result?.photo?.uploadUrl });
}

Do a client side PUT request to the uploadUrl with body as image’s buffer.

// app/components/Utility/Upload.tsx

const uploadFile = (e: ChangeEvent<HTMLInputElement>) => {
  // Get the reference to the file uploaded
  const file = e.target.files?.[0];
  if (!file) return;
  const reader = new FileReader();
  reader.onload = async (event) => {
    // Load the file buffer
    const fileData = event.target?.result;
    if (fileData) {
      // Create blob from the file data with the relevant file's type
      const body = new Blob([fileData], { type: file.type });
      // Make a fetch to the get the uploadUrl
      fetch('/api/image/upload')
        .then((res) => res.json())
        .then((res) => {
          // Use the uploadUrl to upload the buffer
          fetch(res.uploadUrl, {
            body,
            method: 'PUT'
          });
        });
    }
  };
  // Read the user uploaded file as buffer
  reader.readAsArrayBuffer(file);
};

Using an AI powered WYSIWYG Editor

For making it easier to write content, users need a reliable and user-friendly AI powered WYSIWYG editor. We’re using Novel, a Notion-Style WYSIWYG Editor providing a seamless experience with intuitive features and real-time preview of the content being written. To get the content being written as HTML, we use Novel’s onUpdate callback and set the HTML string to an input inside the form element.

// app/routes/_index.tsx

import { Editor } from 'novel';
import { Form } from '@remix-run/react';

export default function Index() {
  return (
    <Form navigate={false} method="post">
      <span>Content</span>
      <input required id="content-html" name="content-html" />
      <Editor
        defaultValue={{}}
        storageKey="novel__editor"
        onUpdate={(e) => {
          if (!e) return;
          const tmp = e.getHTML();
          const htmlSelector = document.getElementById('content-html');
          if (tmp && htmlSelector) htmlSelector.setAttribute('value', tmp);
        }}
      />
      <button type="submit">Publish &rarr;</button>
    </Form>
  );
}

Implementing autocompletion using LiteLLM

Under the hood, Novel makes a POST request to /api/generate expecting a stream of tokens from OpenAI API. Well, let’s see how we’ve customised the endpoint to get the flexibility of using any AI API provider with LiteLLM. With LiteLLM, you can call 100+ LLMs with the same OpenAI-like input and output. To implement autocompletion with streaming, we use the completion method with stream flag set to true and further return the response obtained as a ReadableStream.

// app/routes/api_.generate.tsx

import { completion } from 'litellm';
import { ActionFunctionArgs } from '@remix-run/node';

export async function action({ request }: ActionFunctionArgs) {
  const encoder = new TextEncoder();
  const { prompt } = await request.json();
  const response = await completion({
    n: 1,
    top_p: 1,
    stream: true,
    temperature: 0.7,
    presence_penalty: 0,
    model: 'gpt-3.5-turbo',
    messages: [
      {
        role: 'system',
        content:
          'You are an AI writing assistant that continues existing text based on context from prior text. ' +
          'Give more weight/priority to the later characters than the beginning ones. ' +
          'Limit your response to no more than 200 characters, but make sure to construct complete sentences.'
        // we're disabling markdown for now until we can figure out a way to stream markdown text with proper formatting: https://github.com/steven-tey/novel/discussions/7
        // "Use Markdown formatting when appropriate.",
      },
      {
        role: 'user',
        content: prompt
      }
    ]
  });
  // Create a streaming response
  const customReadable = new ReadableStream({
    async start(controller) {
      for await (const part of response) {
        try {
          const tmp = part.choices[0]?.delta?.content;
          if (tmp) controller.enqueue(encoder.encode(tmp));
        } catch (e) {
          console.log(e);
        }
      }
      controller.close();
    }
  });
  // Return the stream response and keep the connection alive
  return new Response(customReadable, {
    // Set the headers for Server-Sent Events (SSE)
    headers: {
      Connection: 'keep-alive',
      'Content-Encoding': 'none',
      'Cache-Control': 'no-cache, no-transform',
      'Content-Type': 'text/event-stream; charset=utf-8'
    }
  });
}

Implementing Content Wide Search with Xata Search

To let user search through the entire collection of the content, we use Remix Route Actions with Xata Search to retrieve relevant records from the database. With Xata Search, you can choose the tables to search through, in this instance, content and set the targets to search on, in this instance, title, slug, content and author_name.

// app/routes/_index.tsx

export async function action({ request }: ActionFunctionArgs) {
  const body = await request.formData();
  const search = body.get('search') as string;
  // If the 'search' parameter is missing, redirect to '/content'
  if (!search) return redirect('/content');
  const xata = getXataClient();

  // Use the Xata client to perform a search across specified tables with fuzziness
  const { records } = await xata.search.all(search, {
    tables: [
      {
        table: 'content',
        target: ['content', 'title', 'slug', 'author_name']
      }
    ],
    fuzziness: 2
  });

  // Extract the 'record' property from each search result containing the content
  const result = records.map((i) => i.record);
  return json({ search, result });
}

Creating Dynamic Routes in Remix

To create a page dynamically for each content, we're gonna use Remix Dynamic Routes and Route Loaders. Creating a page with $ in it, in this instance, content_.$id.tsx specifies a dynamic route where each part of the URL for e.g. for /content/a, /content/b or /content/anything captures the last segment into the id param.

With Remix Loader and Xata Records, we dynamically query the database to give us the content pertaining to a particular id. Once obtained, we process and return the content as HTML string. Finally, we use the loader data to prototype the UI with best practices such as lazy loading non-critical images.

// app/routes/content_.$id.tsx

import { getXataClient } from '@/xata.server';
import Image from '@/components/Utility/Image';
import { useLoaderData } from '@remix-run/react';
import { unescapeHTML } from '@/lib/util.server';
import { getTransformedImage } from '@/lib/ast.server';
import { LoaderFunctionArgs, redirect } from '@remix-run/node';

export async function loader({ params }: LoaderFunctionArgs) {
  if (!params.id) return redirect('/404');
  const xata = getXataClient();
  // Use the Xata client to fetch content from the 'content' table based on the 'slug'
  const content = await xata.db.content
    .filter({
      slug: params.id
    })
    .getFirst();
  if (content) {
    const output = await getTransformedImage(content);
    return { ...content, content: unescapeHTML(output) };
  }
  // If content is not found, redirect to '/404'
  return redirect('/404');
}

export default function Pic() {
  const content = useLoaderData<typeof loader>();
  return (
    <div>
      <span>{content.title}</span>
      <div>
        <Image
          alt={content.author_name}
          url={content.author_image_url}
          width={content.author_image_w}
          height={content.author_image_h}
        />
        <div>
          <span>{content.author_name}</span>
        </div>
      </div>
      <Image
        loading="eager"
        alt={content.title}
        url={content.og_image_url}
        width={content.og_image_w}
        height={content.og_image_h}
      />
      {content.content && <div dangerouslySetInnerHTML={{ __html: content.content }} />}
    </div>
  );
}

Deploy to Vercel

The repository, is now ready to deploy to Vercel. Use the following steps to deploy: 👇🏻

Start by creating a GitHub repository containing your app's code.
Then, navigate to the Vercel Dashboard and create a New Project.
Link the new project to the GitHub repository you just created.
In Settings, update the Environment Variables to match those in your local .env file.
Deploy! 🚀

More Information

For more detailed insights, explore the references cited in this post.

Resource	Link
GitHub Repo	https://github.com/rishi-raj-jain/remix-wysiwyg-litellm-xata-vercel
Remix with Xata	https://xata.io/docs/getting-started/remix
Xata File Attachments	https://xata.io/docs/sdk/file-attachments#upload-a-file-using-file-apis
Remix Route Actions	https://remix.run/docs/en/main/discussion/data-flow#route-action
Remix Route Loaders	https://remix.run/docs/en/main/discussion/data-flow#route-loader