Drizzle with Xata

Drizzle is a TypeScript ORM that is focused on type safety and closely follows the SQL syntax.

Drizzle currently only works with Xata databases that are set up with the ability to make direct Postgres connections. Follow the instructions below to learn how to configure Drizzle to work with Xata in this method.

You can use the following package managers:

npm install drizzle-orm @xata.io/client@next

pnpm add drizzle-orm @xata.io/client@next

yarn add drizzle-orm @xata.io/client@next

If you want to use the TCP client, you need to install the pg package as well:

npm install pg

pnpm add pg

yarn add pg

Drizzle works with Xata as a HTTP client or a TCP client. The HTTP client doesn't create a persistent connection to the Xata server, while the TCP client does and can be used for better performance with a higher number of requests.

To create a drizzle instance with the HTTP client, use the following code:

import { drizzle } from 'drizzle-orm/xata-http';
import { getXataClient } from './xata'; // Generated client

const xata = getXataClient();
const db = drizzle(xata);

To create a drizzle instance with the TCP client, use the following code:

import { drizzle } from 'drizzle-orm/node-postgres';
import { getXataClient } from './xata'; // Generated client
import { Client } from 'pg';

const xata = getXataClient();
const client = new Client({ connectionString: xata.sql.connectionString });
await client.connect();
const db = drizzle(client);

For production use cases, you can create a pool of connections with the TCP client:

import { drizzle } from 'drizzle-orm/node-postgres';
import { getXataClient } from './xata'; // Generated client
import { Pool } from 'pg';

const xata = getXataClient();
const pool = new Pool({ connectionString: xata.sql.connectionString, max: 20 });
const db = drizzle(pool);

Depending on your current plan, you may need to adjust the max value to match your plan's connection limit.

A full example of using Drizzle with Xata is shown below:

import { drizzle } from 'drizzle-orm/node-postgres';
import { pgTable, text } from 'drizzle-orm/pg-core';
import { eq } from 'drizzle-orm';
import { getXataClient } from './xata'; // Generated client

const xata = getXataClient();

const actors = pgTable('Actors', {
  id: text('id').primaryKey(),
  name: text('name'),
  city: text('city')
});

const db = drizzle(xata);

const result = await db
  .select({ name: actors.name, city: actors.city })
  .from(actors)
  .where(eq(actors.city, 'New York'))
  .execute();

The code imports required modules from the drizzle-orm libraries. It also brings in the eq function from drizzle-orm for value comparison and the getXataClient function from a local xata module. The getXataClient function is used to acquire an instance of the Xata client.

Currently, we recommend manually defining the schema like in the example above. Alternatively, you can try to use drizzle-kit experimental feature to generate the schema from the database.

• Transactions are not supported in the HTTP client.
• Drizzle Kit introspection has a bug with our xata_id default value. You need to manually wrap the value with backticks.
• Xata File attachments columns are read-only and only shows file metadata. You will need to use the SDK to upload and download files.