Xata•2y 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

db push

on db feature branches because the actual migration to prod was handled through the Planetscale dashboard.

kostas•5/27/24, 7:59 AM

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

Yes you can use 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•5/27/24, 8:10 AM

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 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 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•5/27/24, 8:18 AM

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

kostas•5/27/24, 8:21 AM

Regarding relations, it's the constraints creation part which is problematic at the moment.

i.e. this Drizzle schema:

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

Michael SchaufelbergerOP•5/27/24, 9:05 AM

Great! Thank you! This clears it up.

kostas•5/27/24, 9:20 AM

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•5/30/24, 1:27 PM

Quick follow-up questions

Is it ok, to rename the xata_id column to

id

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

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•5/30/24, 2:04 PM

The UI and other internals (Search) require the xata_id column. You could add another column for use as your own id.

kostas•5/30/24, 2:07 PM

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.

kostas•5/30/24, 2:14 PM

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•5/30/24, 2:27 PM

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.

Kkostas The UI and other internals (Search) require the xata_id column. You could add an...

Michael SchaufelbergerOP•5/30/24, 2:29 PM

Why is the UI offering that option? Is that something that just the postgres dbs have a limitation for?

kostas•5/30/24, 2:32 PM

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

kostas•5/30/24, 2:33 PM

That is, for xata_id columns that are generated by the "Adapt table" feature

kostas•5/30/24, 2:35 PM

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.

Kkostas The UI shouldn't be offering any option to rename or delete the xata_id. Do you ...

Michael SchaufelbergerOP•5/30/24, 2:45 PM

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•5/30/24, 2:45 PM

or xata.id

Michael SchaufelbergerOP•5/30/24, 2:46 PM

(that's a non-postgres db)

kostas•5/30/24, 2:46 PM

Ah that output is from a non-postgres enabled db.

kostas•5/30/24, 2:46 PM

right

kostas•5/30/24, 2:47 PM

With non-postgres enabled db's the id and xata.* internal columns are immutable

Michael SchaufelbergerOP•5/30/24, 2:47 PM

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

kostas•5/30/24, 2:47 PM

And it's id instead of xata_id for those

kostas•5/30/24, 2:47 PM

Yes

Michael SchaufelbergerOP•5/30/24, 2:47 PM

thanks, that clears it up!

kostas•5/30/24, 2:48 PM

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

Happy to help

Michael SchaufelbergerOP•6/3/24, 10:22 AM

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•6/3/24, 10:46 AM

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•6/3/24, 11:15 AM

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

--
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•6/3/24, 11:20 AM

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•6/3/24, 11:23 AM

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•6/3/24, 11:27 AM

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•6/3/24, 11:28 AM

Great! Thank you so much for the good support!

kostas•6/3/24, 11:30 AM

I am happy to help