DT
Drizzle Teamā€¢2mo ago
Myst

Dynamic Table Names

I am new to drizzle (started today) and am wondering if it is possible to have a dynamic table name. I have a database that has a new table created for each month. The schema of all these tables is the same. Is it possible to just have 1 schema definition instead of needing to have this repeated? I was able to accomplish this in the past using sqlalchemy using the blow config. Sorry if I have asked this in a confusing manner. Current Drizzle: 
export const sqltData1202401 = pgTable(
  "sqlt_data_1_2024_01",
  {
    tagid: integer().notNull(),
    // You can use { mode: "bigint" } if numbers are exceeding js number limitations
    intvalue: bigint({ mode: "number" }),
    /// other variables
  },
  (table) => [
    index("sqlt_data_1_2024_01t_stampndx").using(
      "btree",
      table.tStamp.asc().nullsLast().op("int8_ops")
    ),
    primaryKey({
      columns: [table.tagid, table.tStamp],
      name: "sqlt_data_1_2024_01_pkey",
    }),
  ]
);

export const sqltData1202402 = pgTable(
  "sqlt_data_1_2024_02",
  {
    tagid: integer().notNull(),
    // You can use { mode: "bigint" } if numbers are exceeding js number limitations
    intvalue: bigint({ mode: "number" }),
    /// other variables
  },
  (table) => [
    index("sqlt_data_1_2024_02t_stampndx").using(
      "btree",
      table.tStamp.asc().nullsLast().op("int8_ops")
    ),
    primaryKey({
      columns: [table.tagid, table.tStamp],
      name: "sqlt_data_1_2024_02_pkey",
    }),
  ]
);
export const sqltData1202401 = pgTable(
  "sqlt_data_1_2024_01",
  {
    tagid: integer().notNull(),
    // You can use { mode: "bigint" } if numbers are exceeding js number limitations
    intvalue: bigint({ mode: "number" }),
    /// other variables
  },
  (table) => [
    index("sqlt_data_1_2024_01t_stampndx").using(
      "btree",
      table.tStamp.asc().nullsLast().op("int8_ops")
    ),
    primaryKey({
      columns: [table.tagid, table.tStamp],
      name: "sqlt_data_1_2024_01_pkey",
    }),
  ]
);

export const sqltData1202402 = pgTable(
  "sqlt_data_1_2024_02",
  {
    tagid: integer().notNull(),
    // You can use { mode: "bigint" } if numbers are exceeding js number limitations
    intvalue: bigint({ mode: "number" }),
    /// other variables
  },
  (table) => [
    index("sqlt_data_1_2024_02t_stampndx").using(
      "btree",
      table.tStamp.asc().nullsLast().op("int8_ops")
    ),
    primaryKey({
      columns: [table.tagid, table.tStamp],
      name: "sqlt_data_1_2024_02_pkey",
    }),
  ]
);
SQLAlchemy: 
def get_data_table(table_name: str | Column[str]):
    DynamicBase = declarative_base(class_registry=dict())

    class DataTable(DynamicBase):
        __tablename__ = table_name

        tagid = Column(Integer, primary_key=True)
        intvalue = Column(BigInteger)
        #other variables

    return DataTable
def get_data_table(table_name: str | Column[str]):
    DynamicBase = declarative_base(class_registry=dict())

    class DataTable(DynamicBase):
        __tablename__ = table_name

        tagid = Column(Integer, primary_key=True)
        intvalue = Column(BigInteger)
        #other variables

    return DataTable
1 Reply
AlexDaniel
AlexDanielā€¢2mo ago
Pseudo code: 
export const sqltData = (month) => pgTable(
  `sqlt_data_${month}`,
  {
    id: ā€¦,
    ā€¦
  }
);

export const sqlData01 = sqltData('01');
export const sqlData02 = sqltData('02');
ā€¦
export const sqltData = (month) => pgTable(
  `sqlt_data_${month}`,
  {
    id: ā€¦,
    ā€¦
  }
);

export const sqlData01 = sqltData('01');
export const sqlData02 = sqltData('02');
ā€¦
We use it, works great. Basically, you just define a function that defines your table, then you just call it. No magic, just regular js šŸ™‚

Did you find this page helpful?