How To: Create tables at runtime?

[iamcrisb]

Hey, I have a usecase where I need to create tables directly at runtime for each independent customer, what's the best approach for doing that?

[tonyxiao]

I have ethe same problem. @iamcrisb did you figure out a solution/

[tonyxiao]

I found some relevant code but it is basically not usable. Wish the source could be published for this.

PgCreateTableConvertor = class extends Convertor {
      can(statement, dialect6) {
        return statement.type === "create_table" && dialect6 === "pg";
      }
      convert(st) {
        const { tableName, schema: schema4, columns, compositePKs, uniqueConstraints } = st;
        let statement = "";
        const name = schema4 ? `"${schema4}"."${tableName}"` : `"${tableName}"`;
        statement += `CREATE TABLE IF NOT EXISTS ${name} (
`;
        for (let i = 0; i < columns.length; i++) {
          const column5 = columns[i];
          const primaryKeyStatement = column5.primaryKey ? " PRIMARY KEY" : "";
          const notNullStatement = column5.notNull ? " NOT NULL" : "";
          const defaultStatement = column5.default !== void 0 ? ` DEFAULT ${column5.default}` : "";
          const uniqueConstraint4 = column5.isUnique ? ` CONSTRAINT "${column5.uniqueName}" UNIQUE${column5.nullsNotDistinct ? " NULLS NOT DISTINCT" : ""}` : "";
          const type = isPgNativeType(column5.type) ? column5.type : `"${column5.type}"`;
          statement += `	"${column5.name}" ${type}${primaryKeyStatement}${defaultStatement}${notNullStatement}${uniqueConstraint4}`;
          statement += i === columns.length - 1 ? "" : ",\n";
        }
        if (typeof compositePKs !== "undefined" && compositePKs.length > 0) {
          statement += ",\n";
          const compositePK4 = PgSquasher.unsquashPK(compositePKs[0]);
          statement += `	CONSTRAINT "${st.compositePkName}" PRIMARY KEY("${compositePK4.columns.join(`","`)}")`;
        }
        if (typeof uniqueConstraints !== "undefined" && uniqueConstraints.length > 0) {
          for (const uniqueConstraint4 of uniqueConstraints) {
            statement += ",\n";
            const unsquashedUnique = PgSquasher.unsquashUnique(uniqueConstraint4);
            statement += `	CONSTRAINT "${unsquashedUnique.name}" UNIQUE${unsquashedUnique.nullsNotDistinct ? " NULLS NOT DISTINCT" : ""}("${unsquashedUnique.columns.join(`","`)}")`;
          }
        }
        statement += `
);`;
        statement += `
`;
        return statement;
      }
    };

[QastanFIT]

Bump

[davidsolheim]

We are doing this using SQLite via Turso. Good things to come!

[tonyxiao]

How are you doing it?

[davidsolheim]

We are using Clerk to send a webhook to our API route that creates a Turso database named using the Organization ID that Clerk created, and when the user is authenticated with Clerk, it checks the Org ID & Membership to determine which database to update. We've limited people to being able to create only 1 Organization per paying account.

[davidsolheim]

It's clear that I completely misunderstood the original posters question. We are not creating a table per customer, rather a database per customer.