Kysely•7mo ago

Creating a table within a schema using a column from a table in another schema

I'm using two schemas and I want to create a table in one of them with a column referencing a column that comes from another schema, how would I do this in migrations i'm using Postgresql

  await db.schema
    .createSchema('schemaNameOne')
    .execute()

  await db.schema
    .withSchema("schemaNameOne")
    .createTable("apiClient")
    .addColumn("id", "bigserial", (col) => col.primaryKey())
    .addColumn("username", "varchar", (col) => col.notNull())
    .addColumn("type", "varchar", (col) => col.notNull())
    .addColumn("apiClientId", "integer", (col) => col.notNull())
    .addColumn("webhookUrl", "varchar")
    .addColumn("createdAt", "timestamp", (col) => col.defaultTo(sql`now()`).notNull())
    .addColumn("updatedAt", "timestamp", (col) => col.defaultTo(sql`now()`).notNull())
    .execute();
  
  await db.schema
    .withSchema("schemaNameOne")
    .createTable("apiClientCustomer")
    .addColumn("id", "bigserial", (col) => col.primaryKey())
    .addColumn("customerId", "integer", (col) => col.references('schemaNameTwo.customer.id').onDelete('cascade').notNull())
    .addColumn("apiClientUsername", "varchar", (col) => col.references('apiClient.username').onDelete('cascade').notNull())
    .addColumn("createdAt", "timestamp", (col) => col.defaultTo(sql`now()`).notNull())
    .addColumn("updatedAt", "timestamp", (col) => col.defaultTo(sql`now()`).notNull())
    .execute();

  await db.schema
    .createSchema('schemaNameOne')
    .execute()

  await db.schema
    .withSchema("schemaNameOne")
    .createTable("apiClient")
    .addColumn("id", "bigserial", (col) => col.primaryKey())
    .addColumn("username", "varchar", (col) => col.notNull())
    .addColumn("type", "varchar", (col) => col.notNull())
    .addColumn("apiClientId", "integer", (col) => col.notNull())
    .addColumn("webhookUrl", "varchar")
    .addColumn("createdAt", "timestamp", (col) => col.defaultTo(sql`now()`).notNull())
    .addColumn("updatedAt", "timestamp", (col) => col.defaultTo(sql`now()`).notNull())
    .execute();
  
  await db.schema
    .withSchema("schemaNameOne")
    .createTable("apiClientCustomer")
    .addColumn("id", "bigserial", (col) => col.primaryKey())
    .addColumn("customerId", "integer", (col) => col.references('schemaNameTwo.customer.id').onDelete('cascade').notNull())
    .addColumn("apiClientUsername", "varchar", (col) => col.references('apiClient.username').onDelete('cascade').notNull())
    .addColumn("createdAt", "timestamp", (col) => col.defaultTo(sql`now()`).notNull())
    .addColumn("updatedAt", "timestamp", (col) => col.defaultTo(sql`now()`).notNull())
    .execute();

1 Reply

Igal•7mo ago

Hey 👋 Better rewrite the question. It's unclear what you're asking. What's wrong with the pasted code? typescript errors? runtime e rrors? not sure how to use the following code in a migration file?

Gaming

Programming

Creating a table within a schema using a column from a table in another schema