Xata•7mo 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•7mo 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 SchaufelbergerOP•7mo 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•7mo 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 SchaufelbergerOP•7mo ago

Great! Thank you! This clears it up.

kostas•7mo 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 SchaufelbergerOP•7mo 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•7mo 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 SchaufelbergerOP•7mo 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•7mo 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 SchaufelbergerOP•7mo 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 SchaufelbergerOP•7mo ago

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

kostas•7mo 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 SchaufelbergerOP•7mo ago

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

kostas•7mo ago

And it's id instead of xata_id for those Yes

Michael SchaufelbergerOP•7mo ago

thanks, that clears it up!

kostas•7mo ago

It's a lot to absorb, we're trying to simplify things 🙂 Happy to help

Michael SchaufelbergerOP•7mo ago

One more thing: How should we handle composite primary keys in a cross-table for a many-to-many relationship. In a classic design, I would just use a composite primary key to achieve that. But Xata needs to have a primary key being a single xata_id - always, correct?

kostas•7mo ago

Not really! You can use any primary key configuration you need. The xata_id simply guarantees us a unique, non-editable column based on which we enable additional workflows for Search, File attachments and other APIs. But in terms of using your own primary key, there's no restrictions.

Michael SchaufelbergerOP•7mo ago

Okay, so I can just create my cross-table like this?

export const guestsToGuestTags = pgTable(
  'guestsToGuestTags',
  {
    ...XATA_SCHEMA_FIELDS, // the four with ie. xata_*
    guestId: text('guestId'),
    guestTagId: text('guestTagId'),
  },
  (table) => ({
    ...getXataSchemaIdIndex('guestsToGuestTags', table), // unique index `_pgroll_new_${tableName}_xata_id_key` on xata_id
    cpk: primaryKey({ columns: [table.guestId, table.guestTagId] }),
  }),
);

export const guestsToGuestTags = pgTable(
  'guestsToGuestTags',
  {
    ...XATA_SCHEMA_FIELDS, // the four with ie. xata_*
    guestId: text('guestId'),
    guestTagId: text('guestTagId'),
  },
  (table) => ({
    ...getXataSchemaIdIndex('guestsToGuestTags', table), // unique index `_pgroll_new_${tableName}_xata_id_key` on xata_id
    cpk: primaryKey({ columns: [table.guestId, table.guestTagId] }),
  }),
);

-- Thinking the other way around then: We were thinking of using the xata_id for a primary key. Is there any downside to this? Are there benefits? Will the DX with the SDK improve? Our goal is to be as compatible with the xata provided solutions as possible (or rather to not make features unusable, just because we have a slightly different preference for naming things).

kostas•7mo ago

The xata_id is going to go away eventually. We've found that the xata "internal" columns create too many problems to ORM migrations (just like you have to specify them in your schema so Drizzle doesn't delete them). We're refactoring our systems and APIs that rely on xata_id to work on any user-generated column that satisfies certain requirements (namely, unique not null string). For this purpose I wouldn't suggest relying on xata_id for a long term solution, but rather introduce a column of your own that is unique and not null.

Michael SchaufelbergerOP•7mo ago

Okay, cool. That's good to know! In that case, we will use our own id, so we can eventually drop the xata_id column in the future. We can still rely on the xata_createdat/xata_updatedat columns, correct?

kostas•7mo ago

I believe these will all go away altogether (for the same reason: ORMs don't know about them by default and require introspection), and we will provide sample triggers for how to create them if you need them. We won't drop them from exisiting databases though so you can just continue using them as they are.

Michael SchaufelbergerOP•7mo ago

Great! Thank you so much for the good support!

kostas•7mo ago

I am happy to help 🙂

Gaming

Programming

Migrations with Drizzle