DEV Community: Cezzaine Zaher

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 🦋

Summer launch week hackathon with the PXCI stack

Cezzaine Zaher — Mon, 08 Jul 2024 11:35:15 +0000

We’re excited to invite you to join our summer hackathon in collaboration with our friends at Prisma, Clerk and Inngest!

Over the past year, we’ve noticed that many of our customers were using the PXCI (Prisma, Xata, Clerk and Inngest) stack and thought it would be fun to host a hackathon for our joint community. As part of our upcoming Xata Launch Week in July 2024, we’re bringing you a two-week coding challenge like no other. Whether you're a solo developer or part of a duo, this is your chance to showcase your creative skills, collaborate with a vibrant community, and win fantastic prizes!

✨ Hackathon overview

Excited? We are too! Here are the rules ✅

Duration: July 5 - July 17, 2024
Format: Individual or 2-person teams.
Requirements: Use the free tier of the full stack (Xata, Clerk, Inngest, Prisma) to create an app.
Public repositories: Your code must be public.
New submissions only: No recycling old projects.
Submission deadline: July 17, 2024 (midnight local time)

❓ How to enter

Sign up for a free Xata account.
Free install or sign up: Prisma, Inngest, Clerk.
Check out this PXCI starter stack to help get you started.
Submit your project and agree to the terms and conditions.

✅ Judging criteria

Our panel of judges across Prisma, Xata, Clerk and Inngest will evaluate your projects based on:

Use of technology: Effective use of the PXCI stack.
Usability & user experience: How user-friendly and functional your application is.
Creativity: How innovative and original your idea is.

Bonus points

AI Integration: Incorporate AI into your project for extra points.
Showcase: Create content (e.g. videos, social posts, blogs) to showcase your app via online community channels.
Public engagement: Create buzz from your promotional efforts by sharing on your favorite social networks.

Scoring system

Meets application criteria: Up to 100 points
AI integration: Up to 50 points
Content creation: Up to 25 points
Social engagement: Up to 25 points

The more points you earn, the more chance you have at claiming a prize so we encourage you to try aim for bonus points if you can 😎

🎉 Prizes

1st place: $2500 USD + Xata & Partner Swag
2nd place: $1000 USD + Xata & Partner Swag
3rd place: $500 USD + Xata & Partner Swag

📅 Key Dates

Hackathon kickoff: July 5, 2024
Submission deadline: July 17, 2024 (midnight ET)
Winners announcement: July 19, 2024 at 12:00pm EST live on Discord

🆘 Support and resources

Join our dedicated Hackathon channel (#hackathon) on the Xata Community Discord for help and support. Xata and Partner teams will be available Monday to Friday, from 5am to 5pm ET. Any queries outside these hours will be addressed to the best of our ability or the next working day.

Here's a list of resources you may find useful

🎬 Live stream winners announcement

Winners will be announced on the Xata Community Discord via live stream on July 19, 2024, at 12:00pm EST. Hosted by Alex Francoeur (Head of Product @ Xata) and featuring Monica Sarbu (CEO @ Xata), Søren Bramer Schmidt (CEO @ Prisma), Dan Farrelly (CTO @ Inngest), and Braden Sidoti (CTO @ Clerk).

🚀 Ready to enter?

Don't miss this opportunity to showcase your talent and win amazing prizes! Join the Xata Community Discord for more information about the Summer Hackathon.

We can't wait to see what you create! 🦋

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

What’s next?

Building a Retrieval-Augmented Generation Chatbot with SvelteKit and Xata Vector Search

Cezzaine Zaher — Wed, 15 May 2024 08:29:38 +0000

In this post, you'll create a Retrieval-Augmented Generation Chatbot using Xata, SvelteKit, LiteLLM, TailwindCSS and Vercel. You'll learn how to:

Set up Xata
Create a schema with different column types
Using LiteLLM to Create Vector Embeddings
Using Xata to Store Vector Embeddings with Metadata
Using Xata Vector Search to Create Relevant Context
Using Replicate Predictions to Prompt LLAMA 2 70B Chat Model
Wiring Up Chatbot UI in SvelteKit

Before you begin

Prerequisites

You'll need the following:

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

Tech Stack

Technology	Description
Xata	Serverless database platform for scalable, real-time applications
SvelteKit	UI framework that uses a compiler to let you write breathtakingly concise components
LiteLLM	Call all LLM APIs using the OpenAI format
Replicate	Platform to run and fine-tune open-source models
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 vectors, that will contain all the vector embeddings along with their metadata.

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

Creating the Schema

In the vectors table, you want to store all the vector embeddings along with metadata (such as string content) so that you can use metadata of the relevant vector(s) to create context for prompting the LLAMA 2 70B Chat model.

First, click + Add column and select String.

Proceed with adding the column name as contents. This column is responsible for storing the metadata as String type.

Next, proceed to add another column name as embedding. This column is responsible for storing the vector embedding generated via OpenAI as vector type.

Among multiple embedding models offered by OpenAI, the text-embedding-3-small model is one of the newest and performant embedding models. The length of the vector embedding generated via the text-embedding-3-small model is 1536.

Enter the default value of the length of the vector embedding equal to 1536.

Lovely! With all that done, the final schema shall be shown 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/xata-rag-chatbot
cd xata-rag-chatbot
pnpm install

Configure Xata with SvelteKit

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

npm install -g @xata.io/cli

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.us-east-1.xata.sh/db/rag-chatbot

Use the following answers to the Xata CLI one-time setup question prompts to integrate Xata with SvelteKit:

Yes when prompted to add .env to .gitignore.
TypeScript when prompted to select the language to generate code and types from your Xata database.
src/xata.server.ts when prompted to enter the output path for the generated code by the Xata CLI.

Using LiteLLM to Create Vector Embeddings

To use OpenAI Embedding models using LiteLLM, we need to make sure that the OPENAI_API_KEY exists as an environment variable. Refer to the following OpenAI doc on how to find your API Key: Where do I find my OpenAI API Key?

# OpenAI API Key
OPENAI_API_KEY="sk-..."

With LiteLLM, you can call 100+ LLMs with the same OpenAI-like input and output. To create an embedding vector from a string content, we use the embedding method from litellm with text-embedding-3-small as the model. By default, the length of the embedding vector will be 1536 for text-embedding-3-small. Using the OpenAI output format, we obtain the embedding vector from the first object in the data array returned.

// File: src/routes/api/chat/+server.ts

import { embedding } from 'litellm';

export async function POST({ request }: RequestEvent) {
  // Generate embeddings of a message using OpenAI via LiteLLM
  const embeddingData = await embedding({
    model: 'text-embedding-3-small',
    input: 'Rishi is enjoying using LiteLLM'
  });

  // Using the OpenAI output format, obtain the embedding vector stored in
  // the first object of the data array
  const getEmbeddingVector = embeddingData.data[0].embedding;
}

Using Xata to Store Vector Embeddings with Metadata

We’ll use the Xata Serverless database platform (powered by PostgreSQL) to store the content from which the embedding vector was created, and the embedding vector itself. It’s as easy as inserting a record in your database. Here’s the code on how to add each vector with it’s content to the vectors table in your Xata database.

// Upsert the vector with description to be used further
// as the context to upcoming questions
xata.db.vectors.create({
  contents: message,
  embedding: embeddingData.data[0].embedding,
}),

Storing vectors in a database helps us create context when generating chatbot responses on the fly including the latest content added. To add embedding vectors along with metadata dynamically, we create a Server Side Rendered SvelteKit endpoint to accept messages (a string array) in the POST request. Using messages, we create embeddings and insert them into our Xata database.

// File: src/routes/api/context/warm/+server.ts

import { embedding } from 'litellm';
import { getXataClient } from '@/xata.server';

// Load the in-memory Xata client
const xata = getXataClient();

export async function POST({ request }: RequestEvent) {
  // Set of messages to create vector embeddings on
  const { messages = [] } = await request.json();

  // Call the OpenAI API to get emebeddings on the messages
  const generatedEmbeddings = await Promise.all(
    messages.map((input: string) => embedding({ input, model: 'text-embedding-3-small' }))
  );

  // Insert all of them into Xata Embedding with the content
  await Promise.all(
    generatedEmbeddings.map((embeddingData, index) =>
      // Upsert the vector with description to be further as the context to upcoming questions
      xata.db.vectors.create({
        contents: messages[index],
        embedding: embeddingData.data[0].embedding
      })
    )
  );
}

Using Xata Vector Search to Create Relevant Context

We’ll use the Xata Vector Search to perform similarity search on your data based on the latest query’s dynamically generated embedding vector. It’s as easy as retrieving a record in your database. Here’s the code on how to find the 5 most similar embedding vectors to the latest user query vector in your Xata database.

// Fetch the relevant set of records based on the embedding
const relevantRecords = await xata.db.vectors.vectorSearch(
  // Column Name
  'embedding',
  // Embedding Vector
  embeddingData.data[0].embedding,
  // Count of relevant vector embeddings
  { size: 5 }
);

With each relevant embedding vector, each record of your Xata database is returned. This allows us to obtain the metadata associated with these vectors and use them to create the context for the AI to answer with. In the code below, we simply create a string containing all the metadata from the relevant vectors returned in our search.

// File: src/routes/api/context/warm/+server.ts

import { embedding } from 'litellm';
import { getXataClient } from '@/xata.server';

// Load the in-memory Xata client
const xata = getXataClient();

export async function POST({ request }: RequestEvent) {
  // Set of messages to create vector embeddings on
  const { messages = [] } = await request.json();

  // Get the latest question stored in the last message of the chat array
  const userMessages = messages.filter((i: any) => i.role === 'user');
  const input = userMessages[userMessages.length - 1].content;

  // Generate embeddings of the latest question using OpenAI
  const embeddingData = await embedding({ input, model: 'text-embedding-3-small' });

  // Fetch the relevant set of records based on the embedding
  const relevantRecords = await xata.db.vectors.vectorSearch('embedding', embeddingData.data[0].embedding, { size: 5 });

  // Combine all the metadata of the relevant vectors
  const systemContext = relevantRecords.records.map((i) => i.contents).join('\n');
}

Using Replicate Predictions to Prompt LLAMA 2 70B Chat Model

To prompt Meta’s LLAMA 2 70B Chat model, we’ll use Replicate which allows us to run the model with just an API call. In our case, we make use of the replicate package, saving us from the need to write a fetch api by ourselves and handle streaming responses natively. We need to make sure that the REPLICATE_API_TOKEN is set as an environment variable. Go to your Replicate’s Account Settings > API tokens to obtain your Replicate API token.

import Replicate from 'replicate'

// Instantiate the Replicate API
const replicate = new Replicate({
  auth: process.env.REPLICATE_API_TOKEN,
}

Using Vercel’s AI SDK baked-in experimental_buildLlama2Prompt function, we’re able to:

Pass instructions so that the code blocks in the markdown-like responses generated by AI are prepended with \. This'll help us highlight them in our frontend setup via a syntax highlighter. This is done via creating a message with the role ofassistantand passing our relevant instructions in thecontent` field.
Pass the dynamically generated context (as string) for the AI to consider before generating responses. This is done via creating a message with the role of system and passing our relevant context string in the content field.
Pass the whole conversation between user and chatbot as messages, which contains each message with its relevant content and role field.

import { experimental_buildLlama2Prompt } from 'ai/prompts';

// Now use Replicate LLAMA 70B streaming to perform the autocompletion with context
const response = await replicate.predictions.create({
  // You must enable streaming.
  stream: true,
  // The model must support streaming. See https://replicate.com/docs/streaming
  model: 'meta/llama-2-70b-chat',
  // Format the message list into the format expected by Llama 2
  // @see https://github.com/vercel/ai/blob/99cf16edf0a09405d15d3867f997c96a8da869c6/packages/core/prompts/huggingface.ts#L53C1-L78C2
  input: {
    prompt: experimental_buildLlama2Prompt([
      {
        // create a system content message to be added as
        // the llama2prompt generator will supply it as the context with the API
        role: 'system',
        content: systemContext
      },
      {
        // create a system instruction
        // make sure to wrap code blocks with ```
{% endraw %}
 so that the svelte markdown picks it up correctly
        role: 'assistant',
        content: {% raw %}`When creating repsonses sure to wrap any code blocks that you output as code blocks and not text so that they can be rendered beautifully.`{% endraw %}
      },
      // also, pass the whole conversation!
      ...messages
    ])
  }
});
{% raw %}

Wiring Up Chatbot UI in SvelteKit

Using Vercel's AI SDK we’re able to save tremendous time in setting up a reactive chatbot UI in SvelteKit. The useChat utility from ai/svelte encapsulates the frontend logic to maintaining the array of messages exchanged between the user and the chatbot, submitting form responses to the relevant API endpoint with the messages exchanged, and stream content right into the UI from the streaming API response.



<script lang="ts">
  // File: src/routes/+page.svelte
  import { useChat } from 'ai/svelte';
  import MemoizedMD from '@/markdown/MemoizedMD.svelte';
  import ChevronRight from '@/icons/ChevronRight.svelte';
  import InputElement from '@/lib/components/ui/input/input.svelte';

  const { input, handleSubmit, messages } = useChat();

  function capitalizeFirstLetter(input: string) {
    return input.charAt(0).toUpperCase() + input.slice(1);
  }
</script>

<div class="flex flex-col items-center">
  <div class="relative flex w-full max-w-lg flex-col items-start overflow-hidden px-5">
    <form on:submit="{handleSubmit}" class="fixed bottom-4 flex w-[75vw] max-w-[500px] flex-row items-center space-x-2">
      <InputElement
        id="message"
        type="message"
        autocomplete="off"
        bind:value="{$input}"
        placeholder="What's your next question?"
        class="rounded border-black/25 placeholder:text-black/75 hover:border-black"
      />
      <button
        class="absolute right-3 flex size-6 flex-col items-center justify-center rounded-full border border-black/50 hover:bg-black hover:text-white"
        type="submit"
      >
        <ChevronRight />
      </button>
    </form>
    <div class="flex max-h-[90vh] w-full flex-col overflow-y-scroll">
      {#each $messages as message}
      <div class="mt-3 h-[1px] w-full bg-white" />
      <span class="max-w-max rounded border px-2 py-1 text-xs"> {capitalizeFirstLetter(message.role)} </span>
      <div class="mt-2 h-[1px] w-full bg-white" />
      <MemoizedMD message="{message.content}" />
      <div class="mt-3 h-[1px] bg-black/10" />
      {/each}
    </div>
  </div>
</div>

Using the svelte-markdown package, we’re able to visually format the responses returned by the chatbot API route for the user. Breaking HTML into components of their own tags (such as <code> and <a>), we’re able to selectively pass our own Svelte components containing their own processing logic. In this example, we create our custom Code svelte component to touch up the <code> element’s visual appearance.



<script lang="ts">
  // File: src/markdown/MemomizedMD.svelte
  import Code from './Code.svelte';
  import SvelteMarkdown from 'svelte-markdown';

  export let message: string;
</script>

<SvelteMarkdown source={message} renderers={{ code: Code }} />

Using svelte-highlight package, we’ll specifically highlight the code blocks in the chatbot responses to make them more visually appealing. Within just a few lines, we’re able to create HTML classes for each code block specifically based on its language (by default, we’ve set it to typescript).



<script lang="ts">
  // File: src/markdown/Code.svelte
  import Highlight from 'svelte-highlight';
  import typescript from 'svelte-highlight/languages/typescript';

  export let lang: string;
  export let text: any;
</script>

<Highlight language="{typescript}" code="{text}" />

To make sure the specific HTML classes have their respective CSS styles present, we use the GitHub Light Theme (exported by svelte-highlight) in the app’s parent layout file.



<script lang="ts">
  // File: src/routes/+layout.svelte
  import github from 'svelte-highlight/styles/github';
</script>

<svelte:head>
  {@html github}
</svelte:head>

<slot />

Here's a preview of what we've succesfully created with SvelteKit and Xata 👇🏻

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/xata-rag-chatbot
SvelteKit with Xata	https://xata.io/docs/getting-started/sveltekit
Xata Vector Search	https://xata.io/docs/sdk/vector-search
How to Prompt Llama 2	https://huggingface.co/blog/llama2#how-to-prompt-llama-2
SvelteKit AI SDK	https://sdk.vercel.ai/docs/guides/frameworks/sveltekit

What’s next?

Build authenticated and paywall pages with Stripe and Xata

Cezzaine Zaher — Wed, 08 May 2024 17:10:53 +0000

In this post, you'll learn how to authenticate users using Xata, create Stripe checkouts and respond to Stripe webhooks in an Astro application.

Set up Xata
Create a schema with different column types
Programmatically create Stripe checkout sessions
Respond to Stripe webhooks
Authenticate users with Lucia Auth

Before you begin

Prerequisites

You'll need the following:

Node.js 18 or later
pnpm package manager
A Xata account
A Stripe account

Tech Stack

The following technologies are used in this guide:

Technology	Description
Xata	Serverless Postgres database platform for scalable, real-time applications.
Astro	Framework for building fast, modern websites with serverless backend support.
Stripe	A platform to accept payments globally.
Lucia Auth	An authentication library that abstracts away the complexity of handling sessions.

Steps

To complete this guide, you will need to follow these steps:

Create a new Astro application
Add Tailwind CSS to the application
Enable Server Side Rendering in Astro
Set up a Xata Database
- Install the Xata CLI
Create Stripe Checkout Sessions
Respond to Stripe Webhooks
Authenticate users with Lucia Auth and Xata
- Build a Xata Adapter for Lucia Auth
- Initialize Lucia with Xata
- Create Utilities for User Authentication Status
- Build Authentication Routes with Lucia Auth
- Build the Sign Up HTML and API Route
- Build the Sign In HTML and API Route
- Build the Sign Out API Route
- Create Protected and Paid Routes
Deploy to Vercel

Create a new Astro application

Let’s get started by creating a new Astro project. Open your terminal and run the following command:

pnpm create astro@latest protected-and-paid-stripe-xata

pnpm create astro is the recommended way to scaffold an Astro project quickly.

When prompted, choose:

Empty when prompted on how to start the new project.
Yes when prompted if you plan to write using Typescript.
Strict when prompted how strict Typescript should be.
Yes when prompted to install dependencies.
Yes when prompted to initialize a git repository.

Once that’s done, you can move into the project directory and start the app:

cd protected-and-paid-stripe-xata
pnpm dev

The app should be running on localhost:4321. Now, let's close the development server as we move on to integrating Tailwind CSS in the application.

Add Tailwind CSS to the application

For styling the app, you will use Tailwind CSS. Install and set up Tailwind at the root of your project's directory by running:

pnpm astro add tailwind

When prompted, choose:

Yes when prompted to install the Tailwind dependencies.
Yes when prompted to generate a minimal tailwind.config.mjs file.
Yes when prompted to make changes to the Astro configuration file.

The command then finishes integrating TailwindCSS into your Astro project. It installed the following dependency:

tailwindcss: TailwindCSS as a package to scan your project files to generate corresponding styles.
@astrojs/tailwind: The adapter that brings Tailwind's utility CSS classes to every .astro file and framework component in your project.

Now, let's move on to enabling server side rendering in the application.

Enabling Server Side Rendering in Astro

To create checkouts using the Stripe API dynamically, you will need to server-side render your Astro application. Enable server side rendering in your Astro project by executing the following command in your terminal:

pnpm add @astrojs/vercel

When prompted, choose:

Yes when prompted to install the Vercel dependencies.
Yes when prompted to make changes to the Astro configuration file.

The command above installed the following dependency:

@astrojs/vercel: The adapter that allows you to server-side render your Astro application on Vercel.

Now, let's move on to integrating Xata in the application.

Setting up a Xata Database

After you've created a Xata account and are logged in, create a database by clicking on + Add database.

Install the Xata CLI

Obtain the Xata initialization command by clicking on a table (say, user), and then on the Get code snippet button.

A modal titled Code snippets is presented which contains a set of commands to integrate Xata into your project, locally.

For interacting with your Xata database using the TypeScript SDK, install the Xata CLI globally by executing the following command in your terminal:

# Installs the CLI globally
npm install -g @xata.io/cli

Then, link your Xata project to your Astro application by executing the following command in your terminal window:

# Initialize your project locally with the Xata CLI
xata init --db https://Rishi-Raj-Jain-s-workspace-80514q.us-east-1.xata.sh/db/astro-stripe-protected-and-paid

Use the following answers to the Xata CLI one-time setup question prompts to integrate Xata with Astro:

Yes when prompted to add .env to .gitignore.
src/xata.ts when prompted to enter the output path for the generated code by the Xata CLI.

Now, let’s move onto creating API endpoints that create checkouts dynamically using Stripe.

Create Stripe Checkout Sessions

A Stripe checkout session, in this case, would refer to a payment link hosted by Stripe where users can pay for a given product.

Let's use the index route to display a buy button that would take users to the Stripe hosted checkout. Create an index route (src/pages/index.astro) with the following code:

---
// File: src/pages/index.astro

## export const prerender = true

<html lang="en">
 <head>
   <meta charset="utf-8" />
   <meta name="viewport" content="width=device-width" />
 </head>
 <body class="font-display overflow-x-hidden">
   <form class="p-12" method="post" action="/api/stripe/checkout">
     <button class="rounded border border-black px-4 py-2 duration-150 hover:scale-105">Get My Product &rarr;</button>
   </form>
 </body>
</html>

The code above creates an index route in the Astro application containing a form that POSTs to /api/stripe/checkout. To handle the form submission, create a file src/pages/api/stripe/checkout.ts with the following code:

// File: src/pages/api/stripe/checkout.ts

import Stripe from 'stripe';
import type { APIContext } from 'astro';

export async function POST({ redirect }: APIContext) {
  const STRIPE_SECRET_KEY = import.meta.env.STRIPE_SECRET_KEY;
  if (!STRIPE_SECRET_KEY) return new Response(null, { status: 500 });
  const stripe = new Stripe(STRIPE_SECRET_KEY, { apiVersion: '2023-10-16' });
  const session = await stripe.checkout.sessions.create({
    mode: 'payment',
    payment_method_configuration: 'pmc_1O2qH3SE9voLRYpuz5FLmkvn',
    line_items: [
      {
        quantity: 1,
        price_data: {
          unit_amount: 0,
          currency: 'usd',
          product: 'prod_OqWkk7Rz9Yw18f'
        }
      }
    ],
    success_url: 'http://localhost:4321'
  });
  return redirect(session.url);
}

The code above does the following:

Imports the Stripe SDK and the type APIContext by Astro.
Exports a POST HTTP request handler.
Validates the presence of STRIPE_SECRET_KEY environment variable.
Creates a new stripe object using the secret key and the latest api version.
Creates a checkout with a given item adjusted to be for free. Having a checkout containing free items will help you quickly test a purchase flow manually.
Redirects user to the newly created checkout's URL.

Let's move on to handling the webhook requests triggered by Stripe if there's a successful purchase on the payment link.

Respond to Stripe Webhooks

Upon a successful purchase, Stripe fires a webhook POST request. To handle the webhook requests in Astro via server-side code, create a src/pages/api/stripe/webhook.ts file with the following code:

// File: src/pages/api/stripe/webhook.ts

import Stripe from 'stripe';
import { getXataClient } from '@/xata';
import type { APIContext } from 'astro';

// Process rawBody from the request Object
async function getRawBody(request: Request) {
  let chunks = [];
  let done = false;
  const reader = request.body.getReader();
  while (!done) {
    const { value, done: isDone } = await reader.read();
    if (value) {
      chunks.push(value);
    }
    done = isDone;
  }
  const bodyData = new Uint8Array(chunks.reduce((acc, chunk) => acc + chunk.length, 0));
  let offset = 0;
  for (const chunk of chunks) {
    bodyData.set(chunk, offset);
    offset += chunk.length;
  }
  return Buffer.from(bodyData);
}

// Stripe API Reference
// https://stripe.com/docs/webhooks#webhook-endpoint-def
export async function POST({ request }: APIContext) {
  try {
    const STRIPE_SECRET_KEY = import.meta.env.STRIPE_SECRET_KEY;
    const STRIPE_WEBHOOK_SIG = import.meta.env.STRIPE_WEBHOOK_SIG;
    if (!STRIPE_SECRET_KEY || !STRIPE_WEBHOOK_SIG) return new Response(null, { status: 500 });
    const stripe = new Stripe(STRIPE_SECRET_KEY, { apiVersion: '2023-10-16' });
    const rawBody = await getRawBody(request);
    let event = JSON.parse(rawBody.toString());
    const sig = request.headers.get('stripe-signature');
    try {
      event = stripe.webhooks.constructEvent(rawBody, sig, STRIPE_WEBHOOK_SIG);
    } catch (err) {
      console.log(err.message);
      return new Response(`Webhook Error: ${err.message}`, { status: 400 });
    }
    if (event.type === 'checkout.session.completed' || event.type === 'payment_intent.succeeded') {
      const email = event.data.object?.customer_details?.email;
      if (email) {
        const xata = getXataClient();
        const existingRecord = await xata.db.user.filter({ email }).getFirst();
        if (existingRecord) {
          await xata.db.user.update(existingRecord.id, { paid: true });
        } else {
          await xata.db.user.create({ email, paid: true });
        }
        return new Response('marked the user as paid', { status: 200 });
      }
      return new Response('no email of the user is found', { status: 200 });
    }
    return new Response(JSON.stringify(event), { status: 404 });
  } catch (e) {
    return new Response(e.message || e.toString(), { status: 500 });
  }
}

The code above does the following:

Imports the Stripe class from its SDK, APIContext type by Astro and getXataClient to access the in-memory Xata instance.
Exports a POST HTTP handler to only respond to incoming POST request(s).
Validates the presence of STRIPE_SECRET_KEY and STRIPE_WEBHOOK_SIG environment variables.
Creates a Stripe webhook event from the incoming Stripe webhook request body.
Checks for an existing record with the email entered during the checkout.
If a record is found, the paid attribute is set to true.
Otherwise, it creates a record with the email and paid attribute as true.

Let's move on to authenticating users with your Xata database and a Lucia library.

Authenticating users with Lucia Auth and Xata

To start authenticating users and managing their sessions, install Lucia and Oslo, for various authentication utilities by executing the following command in your terminal:

pnpm add lucia oslo dotenv

The above command installs the packages passed to the install command. The libraries installed include:

lucia: An open source authentication library that abstracts away the complexity of handling sessions.
dotenv: A library for handling environment variables.
oslo: A collection of authentication related utilities.

// File: astro.config.mjs

+ import 'dotenv/config'
import node from '@astrojs/node'
import tailwind from '@astrojs/tailwind'
import { defineConfig } from 'astro/config'

export default defineConfig({
  output: 'server',
  adapter: node({
    mode: 'standalone',
  }),
  integrations: [tailwind()],
+  vite: {
+    optimizeDeps: {
+      exclude: ['oslo'],
+    },
+  },
})

The code additions above do the following:

Imports dotenv which makes all the environment variables accessible via the process.env object.
Uses vite's optimizeDeps to exclude the oslo library from being pre-bundled, effectively, using the library as is.

Now, create a Lucia directory in the src directory by running the following command:

mkdir src/lucia

Let's now move on to creating a Xata adapter for our Lucia library.

Building a Xata Adapter for Lucia Auth

Create a src/lucia/xata.ts file with the following code:

// File: src/lucia/xata.ts

import { getXataClient } from '@/xata';
import type {
  Adapter,
  DatabaseSession,
  RegisteredDatabaseSessionAttributes,
  DatabaseUser,
  RegisteredDatabaseUserAttributes,
  UserId
} from 'lucia';

The code above imports the getXataClient utility to retrieve the in-memory Xata instance. It then imports the relevant types from the lucia module. Append the following code:

// File: src/lucia/xata.ts

// ...

interface UserSchema extends RegisteredDatabaseUserAttributes {
  id: string;
}

interface SessionSchema extends RegisteredDatabaseSessionAttributes {
  id: string;
  user_id: string;
  expires_at: Date;
}

The code above defines the following types:

UserSchema: A type created with RegisteredDatabaseUserAttributes type definition alongwith id attribute as a string.
SessionSchema: A type created with RegisteredDatabaseSessionAttributes type definition along with id attribute as a string, user_id attribute as a string and expires_at attribute as a Date.

Append the following code to handle data transformations to Lucia's expected structure:

// File: src/lucia/xata.ts

// ...

function transformIntoDatabaseSession(raw: SessionSchema): DatabaseSession {
  const { id, user_id: userId, expires_at: expiresAt, ...attributes } = raw;
  return {
    id,
    userId,
    expiresAt,
    attributes
  };
}

function transformIntoDatabaseUser(raw: UserSchema): DatabaseUser {
  const { id, ...attributes } = raw;
  return {
    id,
    attributes
  };
}

The code above defines the following methods:

transformIntoDatabaseSession: returns the SessionSchema object in the shape of DatabaseSession type.
transformIntoDatabaseUser: returns the UserSchema object in the shape of DatabaseUser type.

Append the following code to create the XataAdapter class:

// File: src/lucia/xata.ts

// ...

export class XataAdapter implements Adapter {
  private controller = getXataClient();

  // ...
}

The code above defines a class XataAdapter based on Lucia's Adapter class. Then, it sets a controller private class variable equivalent to the Xata instance obtained from the getXataClient method. Define a method to delete a session by appending the following code:

// File: src/lucia/xata.ts

// ...

export class XataAdapter implements Adapter {
  // ...

  public async deleteSession(sessionId: string): Promise<void> {
    await this.controller.sql`DELETE FROM "session" WHERE id=${sessionId}`;
  }
}

The code above defines a deleteSession method that uses Xata SQL queries to delete a particular session from your database. Define a method to delete all the sessions for a user by appending the following code:

// File: src/lucia/xata.ts

// ...

export class XataAdapter implements Adapter {
  // ...

  public async deleteUserSessions(userId: UserId): Promise<void> {
    await this.controller.sql`DELETE FROM "session" WHERE user_id=${userId}`;
  }
}

The code above defines a deleteUserSessions method that uses Xata SQL queries to delete all sessions pertaining to a particular user in your database. Define a method to retrieve all the sessions for a user by appending the following code:

// File: src/lucia/xata.ts

// ...

export class XataAdapter implements Adapter {
  // ...

  public async getUserSessions(user_id: UserId): Promise<DatabaseSession[]> {
    const records = await this.controller.db.session.filter({ user_id }).getAll();
    return records.map((val) => {
      return transformIntoDatabaseSession(val);
    });
  }
}

The code above defines a getUserSessions method that uses Xata SDK methods to retrieve all sessions pertaining to a particular user in your database. Define a method to create a session by appending the following code:

// File: src/lucia/xata.ts

// ...

export class XataAdapter implements Adapter {
  // ...

  public async setSession(databaseSession: DatabaseSession): Promise<void> {
    await this.controller.db.session.create({
      id: databaseSession.id,
      user_id: databaseSession.userId,
      expires_at: databaseSession.expiresAt,
      ...databaseSession.attributes
    });
  }
}

The code above defines a setSession method that uses Xata SDK methods to insert a record in a session table in your database. Define a method to update a session by appending the following code:

// File: src/lucia/xata.ts

// ...

export class XataAdapter implements Adapter {
  // ...

  public async updateSessionExpiration(sessionId: string, expiresAt: Date): Promise<void> {
    await this.controller.sql`UPDATE "session" SET expires_at=${expiresAt} WHERE id=${sessionId}`;
  }
}

The code above defines an updateSessionExpiration method that uses a Xata SQL query to update the expires_at attribute in a particular session in your database. Define a method to delete all expired sessions by appending the following code:

// File: src/lucia/xata.ts

// ...

export class XataAdapter implements Adapter {
  // ...

  public async deleteExpiredSessions(): Promise<void> {
    await this.controller.sql`DELETE FROM "session" WHERE expires_at <=${new Date()}`;
  }
}

The code above defines a deleteExpiredSessions method that uses a Xata SQL query to delete all the sessions whose expires_at attribute have expired. Define a method to obtain a session by appending the following code:

// File: src/lucia/xata.ts

// ...

export class XataAdapter implements Adapter {
  // ...

  private async getSession(session_id: string): Promise<DatabaseSession | null> {
    const records = await this.controller.db.session.filter({ session_id }).getFirst();
    return transformIntoDatabaseSession(records);
  }
}

The code above defines a getSession method that uses Xata SDK methods to retrieve a single matching record for a given session from your database. Define a method to obtain a user associated with a session by appending the following code:

// File: src/lucia/xata.ts

// ...

export class XataAdapter implements Adapter {
  // ...

  private async getUserFromSessionId(session_id: string): Promise<DatabaseUser | null> {
    const theSession = await this.controller.db.session.filter({ session_id }).getFirst();
    if (theSession) {
      const { user_id } = theSession;
      const getUser = await this.controller.db.user.filter({ user_id }).getFirst();
      if (getUser) return transformIntoDatabaseUser(getUser);
    }
    return null;
  }
}

The code above defines a getUserFromSessionId method that uses Xata SDK methods to retrieve a single matching record for a given session from your database. If a session is found, it further queries to obtain the user associated with the session. Otherwise, it returns null.

// File: src/lucia/xata.ts

// ...

export class XataAdapter implements Adapter {
  // ...

  public async getSessionAndUser(
    sessionId: string
  ): Promise<[session: DatabaseSession | null, user: DatabaseUser | null]> {
    const [databaseSession, databaseUser] = await Promise.all([
      this.getSession(sessionId),
      this.getUserFromSessionId(sessionId)
    ]);
    return [databaseSession, databaseUser];
  }
}

The code above defines a getSessionAndUser method that uses the getSession and getUserFromSessionId methods to obtain both the user and session information associated with the particular session id.

The XataAdapter class is now ready to be used as Lucia's adapter.

Initialize Lucia with Xata

To use Xata with Lucia, create a file index.ts inside the lucia directory with the following code:

// File: src/lucia/index.ts

import { Lucia } from 'lucia';
import { XataAdapter } from './xata';

const adapter = new XataAdapter();

export const lucia = new Lucia(adapter, {
  sessionCookie: {
    attributes: {
      secure: import.meta.env.PROD
    }
  },
  getUserAttributes: (attributes) => {
    return {
      paid: attributes.paid,
      email: attributes.email
    };
  }
});

The code above does the following:

Imports the Lucia and XataAdapter classes.
Creates a new instance of XataAdatper as adapter.
Uses the adapter to create a new Lucia instance wherein the session cookie is set to Secure if the application is running in the production environment.
The lucia instance defines the getUserAttributes function to only fetch paid and email attributes from the user record(s).

To create type definitions for the associated user and session, append the following code:

// File: src/lucia/index.ts

// ...

interface DatabaseUserAttributes {
  email: string;
  paid: boolean | null;
}

declare module 'lucia' {
  interface Register {
    Lucia: typeof lucia;
    DatabaseUserAttributes: DatabaseUserAttributes;
  }
}

The code above defines the following types:

DatabaseUserAttributes: An object that would contain an email attribute as a string, and paid attribute as boolean or null.
Lucia: As the type of the lucia instance created.

Create Utilities for User Authentication Status

To retrieve the authentication status of the session, you will need to obtain the user information associated with the request. Create a file user.ts with the following code:

// File: src/lucia/user.ts

import { lucia } from '.';
import type { User } from 'lucia';
import type { AstroCookies } from 'astro';

export function getSessionID(cookies: AstroCookies): string | null {
  const auth_session = cookies.get('auth_session');
  if (!auth_session) return null;
  return lucia.readSessionCookie(`auth_session=${auth_session.value}`);
}

export async function getUser(cookies: AstroCookies): Promise<User | null> {
  const session_id = getSessionID(cookies);
  if (!session_id) return null;
  const { user } = await lucia.validateSession(session_id);
  return user;
}

The code above does the following:

Imports the lucia instance and the User type by Lucia.
Exports a getSessionID function which uses Lucia's readSessionCookie utility to obtain the session ID.
Exports a getUser function which uses the getSessionID function to obtain the session id. Then, it uses Lucia's validateSession utility to obtain the user data information with the session.

Let's move on to creating simple user authentication forms and the API routes to handle the authentication logic.

Building Authentication Routes with Lucia Auth

In this section, you will learn how to create simple forms in Astro, and use the form data in Astro endpoints to sign up, sign out and sign in the users in your application.

Build the Sign Up HTML and API Route

To serve responses to a /signup route in Astro, create a src/pages/signup.astro file with the following code:

---
// File: src/pages/signup.astro

export const prerender = true
---

<html lang="en">
 <head>
   <meta charset="utf-8" />
   <meta name="viewport" content="width=device-width" />
 </head>
 <body class="font-display overflow-x-hidden">
   <h1>Sign Up</h1>
   <form method="post" action="/api/sign/up">
     <label for="email">Email</label>
     <input id="email" name="email" />
     <label for="password">Password</label>
     <input id="password" name="password" />
     <button>Continue</button>
   </form>
 </body>
</html>

The code above creates a sign up route in the Astro application containing a form that POSTs to /api/sign/up with the user's email and password. Create a src/pages/api/sign/up.ts file with the following code to handle the form submission:

// File: src/pages/api/sign/up.ts

import { generateId } from 'lucia';
import { lucia } from '@/lucia/index';
import { getXataClient } from '@/xata';
import type { APIContext } from 'astro';
import { Argon2id } from 'oslo/password';

export async function POST({ request, redirect, cookies }: APIContext): Promise<Response> {
  const xata = getXataClient();
  const user_id = generateId(15);
  const formData = await request.formData();
  const email = (formData.get('email') as string).trim();
  const password = formData.get('password') as string;
  const hashed_password = await new Argon2id().hash(password);
  const existingRecord = await xata.db.user.filter({ email }).getFirst();
  if (existingRecord) {
    if (existingRecord.hashed_password !== null) return redirect('/signin');
    await xata.db.user.update(existingRecord.id, { user_id, hashed_password });
  } else {
    await xata.db.user.create({ email, user_id, hashed_password });
  }
  const session = await lucia.createSession(user_id, {});
  const sessionCookie = lucia.createSessionCookie(session.id);
  cookies.set(sessionCookie.name, sessionCookie.value, sessionCookie.attributes);
  return redirect('/');
}

The code above does the following:

Imports a utility from the Lucia SDK to create random ID(s), the getXataClient utility to obtain the in-memory Xata instance, Argon2id class by oslo to hash given password(s).
Exports a POST HTTP handler to only respond to incoming POST request(s).
Obtains the email and password from the request's form data.
Hashes the password using Argon2id's hash method.
Retrieves a single matching record using the email from form data.
If no such record is found, it creates a record in the user table with the email, user id and hashed password.
Otherwise, if the hashed password is present in the matching record, it redirects to /signin route. Otherwise, it updates the existing record with the newly signed up user.
Creates a session using createSession helper utility by Lucia and sets the cookie.
Redirects to the index route.

Build the Sign In HTML and API Route

To serve responses to a /signin route in Astro, create a src/pages/signin.astro file with the following code:

---
// File: src/pages/signin.astro

export const prerender = true
---

<html lang="en">
 <head>
   <meta charset="utf-8" />
   <meta name="viewport" content="width=device-width" />
 </head>
 <body class="font-display overflow-x-hidden">
   <h1>Sign In</h1>
   <form method="post" action="/api/sign/in">
     <label for="email">Email</label>
     <input id="email" name="email" />
     <label for="password">Password</label>
     <input id="password" name="password" />
     <button>Continue</button>
   </form>
 </body>
</html>

The code above creates a sign in route in the Astro application containing a form that POSTs to /api/sign/in with the user's email and password. Create a src/pages/api/sign/in.ts file with the following code to handle the form submission:

// File: src/pages/api/sign/in.ts

import { lucia } from '@/lucia/index';
import { getXataClient } from '@/xata';
import type { APIContext } from 'astro';
import { Argon2id } from 'oslo/password';

export async function POST({ request, cookies, redirect }: APIContext): Promise<Response> {
  const xata = getXataClient();
  const formData = await request.formData();
  const email = (formData.get('email') as string).trim();
  const password = formData.get('password') as string;
  const existingRecord = await xata.db.user.filter({ email }).getFirst();
  if (!existingRecord) return new Response('Incorrect email or password', { status: 400 });
  const validPassword = await new Argon2id().verify(existingRecord.hashed_password, password);
  if (!validPassword) return new Response('Incorrect email or password', { status: 400 });
  const session = await lucia.createSession(existingRecord.user_id, {});
  const sessionCookie = lucia.createSessionCookie(session.id);
  cookies.set(sessionCookie.name, sessionCookie.value, sessionCookie.attributes);
  return redirect('/');
}

The code above does the following:

Imports the initialized lucia instance and getXataClient helper utility.
Exports a POST HTTP handler to only respond to incoming POST request(s).
Obtains the email and password from the request's form data.
Looks for an existing record with the email in user table of your database.
If not found, it returns a 400 Bad Request response.
Otherwise, it uses Argon2id to verify the hashed password.
If the hashed password does not match, it returns a 400 Bad Request response.
Otherwise, it creates a session using createSession helper utility by Lucia and sets the cookie.
Redirects to the index route.

Build the Sign Out API Route

To sign out users in your Astro application, create a src/pages/api/sign/out.ts file with the following code:

// File: src/pages/api/sign/out.ts

import { lucia } from '@/lucia/index';
import type { APIContext } from 'astro';
import { getSessionID } from '@/lucia/user';

export async function GET({ cookies, redirect }: APIContext): Promise<Response> {
  await lucia.invalidateSession(getSessionID(cookies));
  const sessionCookie = lucia.createBlankSessionCookie();
  cookies.set(sessionCookie.name, sessionCookie.value, sessionCookie.attributes);
  return redirect('/');
}

The code above does the following:

Imports the initialized lucia instance and getSessionID helper utility.
Exports a GET HTTP handler to only respond to incoming GET request(s).
Invalidates the current user session using the invalidateSession utility by Lucia.
Creates an empty user session cookie using createBlankSessionCookie utility by Lucia.
Sets the blank user session cookie.
Redirects to the index route.

Create Protected and Paid Routes

To restrict access to a page for only paid and authenticated users, you will obtain the user information using the utilities created earlier. Create a file src/pages/protected_and_paid.astro with the following code:

---
// File: src/pages/protected_and_paid.astro

import { getUser } from '@/lucia/user'

const user = await getUser(Astro.cookies)

if (!user || user.paid !== true) return new Response(null, { status: 403 })
---

<html lang="en">
 <head>
   <meta charset="utf-8" />
   <meta name="viewport" content="width=device-width" />
 </head>
 <body class="font-display overflow-x-hidden">
   <span> Protected and Paid Content </span>
 </body>
</html>

The code above creates a /protected_and_paid route in the Astro application that does the following:

Checks if there's a user associated with the session and if they have paid.
If the above is not true, it returns a 403 Unauthorized response. Otherwise, it returns an HTML page containing the paid and protected information.

To restrict access to a page for only authenticated users, you will obtain the user information using the utilities created earlier. Create a file src/pages/protected.astro with the following code:

---
import { getUser } from '@/lucia/user'

const user = await getUser(Astro.cookies)

if (!user) return new Response(null, { status: 403 })
---

<html lang="en">
 <head>
   <meta charset="utf-8" />
   <meta name="viewport" content="width=device-width" />
 </head>
 <body class="font-display overflow-x-hidden">
   <span> Protected Content </span>
 </body>
</html>

The code above creates a /protected route in the Astro application that does the following:

Checks if there's a user associated with the session.
If the above is not true, it returns a 403 Unauthorized response. Otherwise, it returns an HTML page containing the protected information.

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've 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.

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 would like to contribute a community blog or tutorial. Reach out to us on Discord or join us on X | Twitter. Happy building 🦋