Table cloning or inheritance

[Marsup]

Hi,

I'm looking for a way to either clone a table definition (with another table name) or inherit a table from another one.
I tried recomposing a new schema from pieces of getTableConfig but it seems already built, so typescript won't allow that.
Is there any way to do a bit of meta programming around the schemas?

Thanks!

[nmquebb]

You can create chunks normal objects and reuse them in actual table definitions.

This example creates reusable timestamps, but you could certainly create base and use it in any table that would otherwise inherit those columns.

const timestamps = {
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
}

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  ...timestamps
});

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  ...timestamps
});

[Marsup]

That works for columns, but I also have to bring the extra config part. It would be nicer if we could extract this from the model to re-inject it into another instead of composing the parts themselves.

[ghandic]

This also doesnt work for if you modify timestamps.createAt for example

const timestamps = {
  createdAt: timestamp('created_at').notNull(),
  updatedAt: timestamp('updated_at').notNull(),
}

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  ...timestamps,
  createdAt: timestamps.createdAt.defaultNow()
});

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  ...timestamps
});

You instead need to make a function to return timestamps 🤦🏼

[nlynzaad]

been playing with drizzle for the first time and stumbled onto this problem. using example from issue #730 I was able to construct the following:

base.ts

import {AnySQLiteColumnBuilder, integer, SQLiteIntegerBuilder, sqliteTable, SQLiteTableExtraConfig, SQLiteTimestampBuilder} from 'drizzle-orm/sqlite-core';
import { BuildColumns, sql } from 'drizzle-orm';

type BaseColumns = {
	id: SQLiteIntegerBuilder<{
		name: string;
		data: unknown;
		driverParam: unknown;
		notNull: boolean;
		hasDefault: boolean;
	}>;
	createdAt: SQLiteTimestampBuilder<{
		name: string;
		data: unknown;
		driverParam: unknown;
		notNull: boolean;
		hasDefault: boolean;
	}>;
	updatedAt: SQLiteTimestampBuilder<{
		name: string;
		data: unknown;
		driverParam: unknown;
		notNull: boolean;
		hasDefault: boolean;
	}>;
	deletedAt: SQLiteTimestampBuilder<{
		name: string;
		data: unknown;
		driverParam: unknown;
		notNull: boolean;
		hasDefault: boolean;
	}>;
};

export const baseTable = <TTableName extends string, TColumns extends Record<string, AnySQLiteColumnBuilder>>(
	name: TTableName,
	columns: TColumns,
	extraConfig: (self: BuildColumns<TTableName, TColumns & BaseColumns>) => SQLiteTableExtraConfig
) => {
	return sqliteTable(
		name,
		{
			id: integer(`pkiId`).primaryKey({ autoIncrement: true }),
			createdAt: integer('dteCreated', { mode: 'timestamp' })
				.notNull()
				.default(sql`CURRENT_TIMESTAMP`),
			updatedAt: integer('dteUpdated', { mode: 'timestamp' })
				.notNull()
				.default(sql`CURRENT_TIMESTAMP`),
			deletedAt: integer('dteDeleted', { mode: 'timestamp' }),
			...columns,
		},
		extraConfig
	);
};

users.ts

import { text, integer, uniqueIndex } from 'drizzle-orm/sqlite-core';
import { baseTable } from './base';
import { userRole } from './user_role';

export const users = baseTable(
	'Users',
	{
		username: text('txtUsername').notNull(),
		password: text('txtPassword').notNull(),
		role: integer('fkiUserRoleId').references(() => userRole.id),
		locked: integer('blnLocked', { mode: 'boolean' }).default(false).notNull(),
		lockedAt: integer('dteLocked', { mode: 'timestamp' }),
	},
	(users) => ({
		UniqueUsernameIndex: uniqueIndex('ix_UniqueUsername').on(users.username),
	})
);

user_role.ts

import { text, uniqueIndex } from 'drizzle-orm/sqlite-core';
import { baseTable } from './base';

export const userRole = baseTable(
	'tblUserRoles',
	{
		description: text('txtDescription'),
	},
	(UserRoles) => ({ UniqueUserRoleIndex: uniqueIndex('ix_UniqueUserRole').on(UserRoles.description) })
);

[ghandic]

Thought I'd add here - thanks to the above for getting me most of the way! I have a few compositions (baseTable and then a appTable which "inherits" from baseTable) and in Pg

import type {
  BuildExtraConfigColumns,
  HasDefault,
  IsPrimaryKey,
  NotNull,
} from "drizzle-orm";
import type {
  PgColumnBuilderBase,
  PgTableExtraConfigValue,
  PgTimestampBuilderInitial,
  PgUUIDBuilderInitial,
} from "drizzle-orm/pg-core";
import { sql } from "drizzle-orm";
import { pgTable, timestamp, uuid } from "drizzle-orm/pg-core";

export interface BaseColumns {
  id: NotNull<IsPrimaryKey<NotNull<PgUUIDBuilderInitial<"id">>>>;
  createdAt: NotNull<HasDefault<PgTimestampBuilderInitial<"created_at">>>;
  updatedAt: NotNull<HasDefault<PgTimestampBuilderInitial<"updated_at">>>;
}

export const baseTable = <
  TTableName extends string,
  TColumnsMap extends Record<string, PgColumnBuilderBase>,
>(
  name: TTableName,
  columns: TColumnsMap,
  extraConfig?: (
    self: BuildExtraConfigColumns<TTableName, TColumnsMap & BaseColumns, "pg">,
  ) => PgTableExtraConfigValue[],
) => {
  return pgTable(
    name,
    {
      id: uuid(`id`).notNull().primaryKey(),
      createdAt: timestamp("created_at")
        .notNull()
        .default(sql`CURRENT_TIMESTAMP`),
      updatedAt: timestamp("updated_at")
        .notNull()
        .default(sql`CURRENT_TIMESTAMP`),
      ...columns,
    },
    (self) => {
      const baseExtraConfig: PgTableExtraConfigValue[] = [];

      if (extraConfig) {
        const fullyTypedSelf = self as BuildExtraConfigColumns<
          TTableName,
          TColumnsMap & BaseColumns,
          "pg"
        >;
        return [...baseExtraConfig, ...extraConfig(fullyTypedSelf)];
      }
      return baseExtraConfig;
    },
  );
};