Xata•4mo ago

Migrations with Drizzle

Hi What is the current best practice regarding migrations when I'm using Drizzle with Xata? With Planetscale I just used Drizzle Kit's db push on db feature branches because the actual migration to prod was handled through the Planetscale dashboard.

24 Replies

kostas•4mo ago

I assume you're using a postgres-enabled (Beta) branch with Drizzle, right? Yes you can use drizzle-kit push:pg when using Xata with the postgres driver. Note that if you Adapt your tables in the Xata UI after you push migrations from drizzle, it will introduce internal columns (xata_id/createdat/updatedat) which are necessary for the UI and xata's added features (search, aggregations etc). You'll need to pull those xata-side added migrations back into Drizzle with the introspect call. Drizzle Kit introspection has a bug with our xata_id default value. You need to manually wrap the value with backticks. Note that drizzle relations (one to many, many to many, using foreign key references) do not work yet with the Free or Pro plan because Drizzle's underlying implementation relies on anonymous code blocks, which aren't allowed in Xata. We're working with Drizzle to resolve this. For a list of limitations and known issues: https://xata.io/docs/integrations/drizzle#limitations

Michael Schaufelberger•4mo ago

I assume you're using a postgres-enabled (Beta) branch with Drizzle, right?

Yes, exactly.

You'll need to pull those xata-side added migrations back into Drizzle with the introspect call. Drizzle Kit introspection has a bug with our xata_id default value. You need to manually wrap the value with backticks.

Ok, I'll do that 👍 Can we also let drizzle create the xata fields? Say, I create a new table with pgTable in a schema json, I can just let drizzle create not only the fields the app needs, but also the xata fields, correct? Given that we check first, how the introspection actually interprets the xata fields, then copy those fields over to a new pgTable call.

Note that drizzle relations (one to many, many to many, using foreign key references) do not work yet

Does this mean the constraints cannot be created/managed or that the whole relations features don't work? As we come from Planetscale we are ok with the DB not actually having the constraints.

kostas•4mo ago

You can totally define the xata fields in your Drizzle schema (as long as the definition is accurate, exactly as Xata does it - introspect should show that well) and push them directly from Drizzle when creating tables. They don't necessarily have to be generated by Xata itself 🙂 Regarding relations, it's the constraints creation part which is problematic at the moment. i.e. this Drizzle schema:

export const profileInfo = pgTable('profile_info', {
  id: text('id').primaryKey(),
  userId: integer('user_id').references(() => users.id),
  metadata: jsonb('metadata'),
});

export const profileInfo = pgTable('profile_info', {
  id: text('id').primaryKey(),
  userId: integer('user_id').references(() => users.id),
  metadata: jsonb('metadata'),
});

Generates this DO call, which isn't allowed in Xata:

DO $$ BEGIN
 ALTER TABLE "profile_info" ADD CONSTRAINT "profile_info_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE no action ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

DO $$ BEGIN
 ALTER TABLE "profile_info" ADD CONSTRAINT "profile_info_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE no action ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

Michael Schaufelberger•4mo ago

Great! Thank you! This clears it up.

kostas•4mo ago

This is fully supported in Dedicated Clusters (private Beta), where you get pretty much unrestricted access to Postgres. If you step on any blockers because of anonymous code blocks like above or anything else permission-related, you can consider signing up for Early Access to Dedicated Clusters: https://xata.io/blog/postgres-dedicated-clusters

Introducing Dedicated Clusters for PostgreSQL

We are announcing dedicated clusters, for more scalable and isolated PostgreSQL databases.

Michael Schaufelberger•4mo ago

Quick follow-up questions 🙈 Is it ok, to rename the xata_id column to id, or not? Regarding files array and push: What should we use for the table definition in drizzle to not make drizzle remove the column? Currently it's introspected to

// TODO: failed to parse database type 'xata_file_array'
files: unknown("files").notNull(),

// TODO: failed to parse database type 'xata_file_array'
files: unknown("files").notNull(),

with unknown not existing as an export from drizzle-orm. I thought about https://orm.drizzle.team/docs/custom-types

Drizzle ORM - Custom types

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

kostas•4mo ago

The UI and other internals (Search) require the xata_id column. You could add another column for use as your own id. As for Files and File Arrays, it is a custom type and indeed Drizzle is not aware of it so it can't introspect it. Our team put together an example for defining a custom type (https://orm.drizzle.team/docs/custom-types) here: https://github.com/xataio/client-ts/blob/cb9d0b3e9a39f29d4d5ea07c6ea90ca943c642c8/packages/plugin-client-drizzle/src/types/files.ts#L15-L47 Our plan was originally to merge it into Drizzle, but we're currently reworking the File type to work with SQL and Drizzle. Adding the custom type for now would be just for avoiding Drizzle to drop File columns from your tables when pushing migrations. At the moment xata Files don't work with drizzle, only from the Xata SDK so you'd need to work through that for the files parts. For clarity, File/File Array columns require an additional service in front of Postgres to function, which is handled by Xata's REST API. They won't work with direct PG connectivity. At this point all that can be done is defining them as custom types in Drizzle so that at least migrations from it don't drop these columns.

Michael Schaufelberger•4mo ago

Ok, in that case we will just use the xata_id as the primary key. Better than managing two columns that are IDs, I guess. -- Thank you, that's all we need! 🙏 It's just about not dropping the column for now. We can always still use the sdk for the file operations. Why is the UI offering that option? Is that something that just the postgres dbs have a limitation for?

kostas•4mo ago

The UI shouldn't be offering any option to rename or delete the xata_id. Do you see that somewhere? If so it would be a bug That is, for xata_id columns that are generated by the "Adapt table" feature FYI we're also working on removing the adapt table and functionality dependencies on xata_id. We've seen that having to introspect and add xata-introduced columns is an additional complexity to manage in ORMs, so we're looking to remove this friction point.

Michael Schaufelberger•4mo ago

Hmm. I've checked it's not letting me. Although, we have a database where the UI is not showing any xata_id

Michael Schaufelberger•4mo ago

or xata.id (that's a non-postgres db)

kostas•4mo ago

Ah that output is from a non-postgres enabled db. right With non-postgres enabled db's the id and xata.* internal columns are immutable

Michael Schaufelberger•4mo ago

ah, ok. so with postgres, we just have xata_id instead of id?

kostas•4mo ago

And it's id instead of xata_id for those Yes

Michael Schaufelberger•4mo ago

thanks, that clears it up!

Gaming

Programming

Migrations with Drizzle