Layer authoring schema generation

[BlueBazze]

Is your feature request related to a problem? Please describe.
When authoring nuxt layers with nuxthub, and creating schemas for multiple db variants. It is "somewhat" cumbersome to generate migrations for the different variants.

Describe the solution you'd like
An arg on the generate command that if specified generates the migrations for the specified db variant.

--variant sqlite
--variant mysql
--variant postgresql
--variant all

Describe alternatives you've considered
Considered creating a bun script that automates the generation by looping through each variant by changing the env variable for the db type:
bun i -> nuxt prepare -> nuxt db generate -> delete node_modules -> loop next variant

[atinux]

I think this makes sense, I would go with --dialect <sqlite|mysql|postgresql|all>

Could you be happy to open a PR?

[BlueBazze]

@atinux Sure, ill create the PR.
Looking at the drizzle-kit generate command there are a few other options that potentially could be useful to someone.
Now that im at it anyway, want me to set it up to be modifiable by the end user?

Flags:
  --config string                                                         Path to drizzle config file
  --dialect [ postgresql | mysql | sqlite | turso | singlestore | gel ]   Database dialect: 'gel', 'postgresql', 'mysql', 'sqlite', 'turso' or 'singlestore'
  --driver [ d1-http | expo | aws-data-api | pglite | durable-sqlite ]    Database driver
  --casing [ camelCase | snake_case ]                                     Casing for serialization
  --schema string                                                         Path to a schema file or folder
  --out string                                                            Output folder, 'drizzle' by default
  --name string                                                           Migration file name
  --breakpoints                                                           Prepare SQL statements with breakpoints
  --custom                                                                Prepare empty migration file for custom SQL (default: false)
  --prefix [ index | timestamp | supabase | unix | none ]                 default: "index"

My thoughts about the implementation

But for the --dialect, the migration generation depends on nuxt prepare to build the schema.
So in order to switch dialect, we would have to set hub.db | hub.db.dialect
We would have to check if either the `NUXT_HUB_DB` or `NUXT_HUB_DB_DIALECT` exists, before the guard clause.
Possibly even overriding the existing hub.db from the user' nuxt.config.ts if the env is defined.
https://github.com/nuxt-hub/core/blob/main/src/db/setup.ts#L41C23-L44
The solution i came up with is `if(process.env.NUXT_HUB_DB) hub.db = process.env.NUXT_HUB_DB` before the guard clause, which seems to work, but i havent looked into what problems this could cause.

I dont think the generation of migrations would care about any specific driver for each dialect.

The line https://github.com/nuxt-hub/core/blob/main/src/db/setup.ts#L133 breaks mysql migration generation if the connection uri is not set.

[atinux]

now that I am thunking about it more, I am wondering if we should honestly give the option to generate migrations for multiple dialects.

 

Could you explain me more about your specific use case here?

[BlueBazze]

TLDR - It eases the pain of generating migrations for layers needing to support multiple dialects.

I have an auth layer that provides user authentication including components, database schemas, api endpoints etc..
And because of how nuxthub handles the schema generation and schema import, the same server code can be used no matter the dialect.

In my opinion, i would want to store the migrations needed for a layer within that layer repository rather than in the app project.
Because it would be a hazzle for upstream to handle the migrations after updating to a new layer version, and figuring out what and why changed, for instance if a column name changes drizzle-kit is going to prompt if the column name changed, or if an old one was deleted and new created.
Plus, the same migrations would be needed across multiple different apps anyway.

So this would allow ease the proccess of generating migrations for a layer.
The other option is to create a script that automates this, by setting hub.db to an env var and running the nuxt db generate three times with the different env vars.

---

Here is the script i made to automate this process, incase this feature is decided not to be implemented.

import { $ } from "bun";

async function Cleanup() {
  await $`rm -drf ./node_modules .data .nuxt`;
  await $`bun remove ${Object.values(variantPackage).join(" ")}`;

  console.log("---Cleaned up---");
}

enum DBVariant {
  "postgresql" = "postgresql",
  "mysql" = "mysql",
  "sqlite" = "sqlite",
}

const variantPackage: Record<DBVariant, string> = {
  postgresql: "@electric-sql/pglite",
  mysql: "mysql2",
  sqlite: "@libsql/client",
};

function createEnv(variant: DBVariant) {
  return {
    NUXT_HUB_DB: variant,
    ...(variant === DBVariant.mysql
      ? {
          DATABASE_URL: "localhost:3306",
        }
      : {}),
  };
}

async function generateForVariant(variant: DBVariant = DBVariant.postgresql) {
  await Cleanup();

  const env = variant ? createEnv(variant) : {};

  await $`bun i`;
  variantPackage[variant].length > 0 &&
    (await $`bun add -D ${variantPackage[variant]}`.env(env));

  await $`bunx nuxt prepare`.env(env);

  await $`bunx nuxt db generate`.env(env);

  await $`bun remove ${variantPackage[variant]}`;
}

await generateForVariant(DBVariant.mysql);
await generateForVariant(DBVariant.sqlite);
await generateForVariant(DBVariant.postgresql);

await Cleanup();
await $`bun i && bunx nuxt prepare`

[BlueBazze]

Just realized...
Drizzle-kit doesnt take into account the existing migrations in other layers.

Which means drizzle-kit will try to generate migrations for all schema available in the schema.entry.ts
Even if they were filtered to only include the current layer, relations would import pull everything else.

Example: One nuxt layer with schema and relating migrations -> in the layer playground without any additional schemas when running db generate it will create an identical migration as the layer itself.

This is a problem that i cant spend any more time on at the moment.

---

Edit: Nvm, i misunderstood the tablesFilter.. But i've comitted the changes regardless to the PR.
This adds the ability to modify the drizzle.config extra keys like the filters & entities with easy adding of other keys - https://github.com/drizzle-team/drizzle-orm/blob/main/drizzle-kit/src/index.ts#L109-L131
Through hub.db.drizzle: {...} to be compatible with #789
Then using defu before json stringify at https://github.com/nuxt-hub/core/blob/main/src/db/setup.ts#L641