Drizzle Team•2y ago

help with column builder types

As I play around with learning drizzle, I wondered if I could generate a drizzle schema from a master schema. I'm very close to being able to do that. As you can see in this repo I created: https://github.com/rizen/drizzle-icing/blob/main/user.ts The code works perfectly, but problem is that when I try to use InferModel to get the column types from my schema, I'm getting

type User {
   [x:string]: unknown;
}

type User {
   [x:string]: unknown;
}

Where the I would get the following on a hand-built table:

type User = {
    id: string;
    createdAt: Date;
    username: string;
    password: string;
    useAsDisplayName: "username" | "email" | "realName";
    admin: boolean;
}

type User = {
    id: string;
    createdAt: Date;
    username: string;
    password: string;
    useAsDisplayName: "username" | "email" | "realName";
    admin: boolean;
}

My guess is that the problem lies in the use of the AnyMySqlColumnBuilder type in this function:

export const makeTable = (schema: icingSchema) => {
    const columns: Record<string, AnyMySqlColumnBuilder> = {};
    for (const prop of schema.props) {
        columns[prop.name] = prop.db(prop);
    }
    return mysqlTable(schema.tableName, columns) 
}

export const makeTable = (schema: icingSchema) => {
    const columns: Record<string, AnyMySqlColumnBuilder> = {};
    for (const prop of schema.props) {
        columns[prop.name] = prop.db(prop);
    }
    return mysqlTable(schema.tableName, columns) 
}

Any help on what I should do to get the appropriate types out for InferModel?

GitHub

drizzle-icing/user.ts at main · rizen/drizzle-icing

A test trying to generate drizzle schemas from a master schema. - drizzle-icing/user.ts at main · rizen/drizzle-icing

39 Replies

Dan•2y ago

You'd need to use generics for makeTable Otherwise there's nothing to infer from You can check out the signature of mysqlTable function in the ORM source for inspiration

Dan•2y ago

https://github.com/drizzle-team/drizzle-orm/blob/main/drizzle-orm/src/mysql-core/table.ts#L184

GitHub

drizzle-orm/table.ts at main · drizzle-team/drizzle-orm

TypeScript ORM for SQL. Contribute to drizzle-team/drizzle-orm development by creating an account on GitHub.

JTOP•2y ago

I know what generics are, but given that I don't pass anything in to mysqlTable< HERE >() I don't see how they could help in this circumstance for my own makeTable function. Any further hints you might be able to provide me?

Dan•2y ago

InferModel needs those generics to infer the actual model from the table. If you're passing just a Record of columns to the table, InferModel won't be able to infer the actual column names and types from it.

JTOP•2y ago

I get that, I just don't know enough about inference to know how to apply that to this code. Is there any way I could hire you or your firm to make this work for me?

Dan•2y ago

It's certainly possible! Let me discuss it with the team and come back tomorrow with more details (it's night in here currently).

JTOP•2y ago

Sounds good. Thanks for the consideration.

Dan•2y ago

Hey @plainblackguy, we've set up the sponsorship page on GitHub, check out Silver and Gold tiers which include consultancy hours. https://github.com/sponsors/drizzle-team

GitHub

Sponsor @drizzle-team on GitHub Sponsors

Make TypeScript SQL great again!

JTOP•2y ago

Sweet, and which level do I need for this project?

Dan•2y ago

I'd estimate it in 1-3 hours. You can start with the silver tier.

JTOP•2y ago

I'm gonna go with the gold, because I'm certain I'll have more questions that I'll need your help with. Ok, paid. How do we get started?

Dan•2y ago

Thank you! You now have access to #gold-sponsors channel. Please let me know: 1. If you want us to shoutout about your sponsorship? In that case, please provide your Twitter handle. 2. If you need this to be live sessions or simply for us to implement your task. For live sessions, I will provide a Calendly link in the #gold-sponsors channel.

JTOP•2y ago

1. feel free. my twitter handle is @plainblackguy, but my company, the one paying for this is @thegamecrafter 2. for right now, i'd just love to have the answer of how to do this, don't need a live session

Dan•2y ago

@plainblackguy could you explain your use case a bit? Do you want to generate schemas in multiple ORMs from a single source? Have you finalized your "master schema" format, or is it a potential subject for change?

JTOP•2y ago

happy to explain my schema will be in a single database: mysql specifically for this project i've finalized my master schema as much as anything is ever final the types look like

type basicVingProps = {
    name: string,
    view: string[],
    edit: string[],
    noSetAll?: boolean,
    required: boolean,
    relation?: {
        type: '1:n' | 'n:1' | 'n:n' | '1:1',
        name: string,
    },
    unique?: boolean,
}

export type vingProp =
    | {
        type: 'string',
        length: number,
        default: string | (() => string),
        db: (prop: Extract<vingProp, { type: 'string' }>) => AnyMySqlColumnBuilder,
        zod?: (prop: Extract<vingProp, { type: 'string' }>) => ZodTypeAny,
    } & basicVingProps
    | {
        type: 'enum',
        length: number,
        default: string | (() => string),
        db: (prop: Extract<vingProp, { type: 'enum' }>) => AnyMySqlColumnBuilder,
        zod?: (prop: Extract<vingProp, { type: 'enum' }>) => ZodTypeAny,
        enums: [string, ...string[]],
        enumLabels: [string, ...string[]],
    } & basicVingProps
    | {
        type: 'boolean',
        default: boolean | (() => boolean),
        enums: [boolean, boolean],
        enumLabels: [string, ...string[]],
        db: (prop: Extract<vingProp, { type: 'boolean' }>) => AnyMySqlColumnBuilder,
        zod?: (prop: Extract<vingProp, { type: 'boolean' }>) => ZodTypeAny,
    } & basicVingProps
    | {
        type: 'number',
        default: number | (() => number),
        db: (prop: Extract<vingProp, { type: 'number' }>) => AnyMySqlColumnBuilder,
        zod?: (prop: Extract<vingProp, { type: 'number' }>) => ZodTypeAny,
    } & basicVingProps
    | {
        type: 'date',
        default: (() => Date),
        db: (prop: Extract<vingProp, { type: 'date' }>) => AnyMySqlColumnBuilder,
        zod?: (prop: Extract<vingProp, { type: 'date' }>) => ZodTypeAny,
    } & basicVingProps
    | {
        type: 'id',
        length: 36,
        db: (prop: Extract<vingProp, { type: 'string' }>) => AnyMySqlColumnBuilder,
        zod?: (prop: Extract<vingProp, { type: 'string' }>) => ZodTypeAny,
        default: undefined | (() => string),
    } & basicVingProps;

export type vingSchema = {
    kind: string,
    tableName: string,
    owner: string[]
    props: vingProp[],
}

type basicVingProps = {
    name: string,
    view: string[],
    edit: string[],
    noSetAll?: boolean,
    required: boolean,
    relation?: {
        type: '1:n' | 'n:1' | 'n:n' | '1:1',
        name: string,
    },
    unique?: boolean,
}

export type vingProp =
    | {
        type: 'string',
        length: number,
        default: string | (() => string),
        db: (prop: Extract<vingProp, { type: 'string' }>) => AnyMySqlColumnBuilder,
        zod?: (prop: Extract<vingProp, { type: 'string' }>) => ZodTypeAny,
    } & basicVingProps
    | {
        type: 'enum',
        length: number,
        default: string | (() => string),
        db: (prop: Extract<vingProp, { type: 'enum' }>) => AnyMySqlColumnBuilder,
        zod?: (prop: Extract<vingProp, { type: 'enum' }>) => ZodTypeAny,
        enums: [string, ...string[]],
        enumLabels: [string, ...string[]],
    } & basicVingProps
    | {
        type: 'boolean',
        default: boolean | (() => boolean),
        enums: [boolean, boolean],
        enumLabels: [string, ...string[]],
        db: (prop: Extract<vingProp, { type: 'boolean' }>) => AnyMySqlColumnBuilder,
        zod?: (prop: Extract<vingProp, { type: 'boolean' }>) => ZodTypeAny,
    } & basicVingProps
    | {
        type: 'number',
        default: number | (() => number),
        db: (prop: Extract<vingProp, { type: 'number' }>) => AnyMySqlColumnBuilder,
        zod?: (prop: Extract<vingProp, { type: 'number' }>) => ZodTypeAny,
    } & basicVingProps
    | {
        type: 'date',
        default: (() => Date),
        db: (prop: Extract<vingProp, { type: 'date' }>) => AnyMySqlColumnBuilder,
        zod?: (prop: Extract<vingProp, { type: 'date' }>) => ZodTypeAny,
    } & basicVingProps
    | {
        type: 'id',
        length: 36,
        db: (prop: Extract<vingProp, { type: 'string' }>) => AnyMySqlColumnBuilder,
        zod?: (prop: Extract<vingProp, { type: 'string' }>) => ZodTypeAny,
        default: undefined | (() => string),
    } & basicVingProps;

export type vingSchema = {
    kind: string,
    tableName: string,
    owner: string[]
    props: vingProp[],
}

that's the types for creating the schema the implementation of 1 table looks like this:

export const userSchema: vingSchema = {
    kind: 'User',
    tableName: 'users',
    owner: ['$id', 'admin'],
    props: [
        ...baseSchemaProps,
        {
            type: "string",
            name: "username",
            required: true,
            unique: true,
            length: 60,
            default: '',
            db: (prop) => dbString(prop),
            zod: (prop) => zodString(prop),
            view: [],
            edit: ['owner'],
        },
        {
            type: "string",
            name: "email",
            required: true,
            unique: true,
            length: 256,
            default: '',
            db: (prop) => dbString(prop),
            zod: (prop) => zodString(prop).email(),
            view: [],
            edit: ['owner'],
        },
        {
            type: "string",
            name: "realName",
            required: true,
            length: 60,
            default: '',
            db: (prop) => dbString(prop),
            zod: (prop) => zodString(prop),
            view: [],
            edit: ['owner'],
        },
        {
            type: "string",
            name: "password",
            length: 256,
            required: false,
            default: 'no-password-specified',
            db: (prop) => dbString(prop),
            view: [],
            edit: [],
        },
        {
            type: "enum",
            name: "passwordType",
            required: false,
            length: 20,
            default: 'bcrypt',
            db: (prop) => dbEnum(prop),
            enums: ['bcrypt'],
            enumLabels: ['Bcrypt'],
            view: [],
            edit: [],
        },
        {
            type: "enum",
            name: 'useAsDisplayName',
            required: true,
            length: 20,
            default: 'username',
            db: (prop) => dbEnum(prop),
            enums: ['username', 'email', 'realName'],
            enumLabels: ['Username', 'Email Address', 'Real Name'],
            view: [],
            edit: ['owner'],
        },
        {
            type: "boolean",
            name: 'admin',
            required: true,
            default: false,
            db: (prop) => dbBoolean(prop),
            enums: [false, true],
            enumLabels: ['Not Admin', 'Admin'],
            view: ['owner'],
            edit: ['admin'],
        },
        {
            type: "boolean",
            name: 'developer',
            required: true,
            default: false,
            db: (prop) => dbBoolean(prop),
            enums: [false, true],
            enumLabels: ['Not a Software Developer', 'Software Developer'],
            view: [],
            edit: ['owner'],
        },
    ],
};

export const userSchema: vingSchema = {
    kind: 'User',
    tableName: 'users',
    owner: ['$id', 'admin'],
    props: [
        ...baseSchemaProps,
        {
            type: "string",
            name: "username",
            required: true,
            unique: true,
            length: 60,
            default: '',
            db: (prop) => dbString(prop),
            zod: (prop) => zodString(prop),
            view: [],
            edit: ['owner'],
        },
        {
            type: "string",
            name: "email",
            required: true,
            unique: true,
            length: 256,
            default: '',
            db: (prop) => dbString(prop),
            zod: (prop) => zodString(prop).email(),
            view: [],
            edit: ['owner'],
        },
        {
            type: "string",
            name: "realName",
            required: true,
            length: 60,
            default: '',
            db: (prop) => dbString(prop),
            zod: (prop) => zodString(prop),
            view: [],
            edit: ['owner'],
        },
        {
            type: "string",
            name: "password",
            length: 256,
            required: false,
            default: 'no-password-specified',
            db: (prop) => dbString(prop),
            view: [],
            edit: [],
        },
        {
            type: "enum",
            name: "passwordType",
            required: false,
            length: 20,
            default: 'bcrypt',
            db: (prop) => dbEnum(prop),
            enums: ['bcrypt'],
            enumLabels: ['Bcrypt'],
            view: [],
            edit: [],
        },
        {
            type: "enum",
            name: 'useAsDisplayName',
            required: true,
            length: 20,
            default: 'username',
            db: (prop) => dbEnum(prop),
            enums: ['username', 'email', 'realName'],
            enumLabels: ['Username', 'Email Address', 'Real Name'],
            view: [],
            edit: ['owner'],
        },
        {
            type: "boolean",
            name: 'admin',
            required: true,
            default: false,
            db: (prop) => dbBoolean(prop),
            enums: [false, true],
            enumLabels: ['Not Admin', 'Admin'],
            view: ['owner'],
            edit: ['admin'],
        },
        {
            type: "boolean",
            name: 'developer',
            required: true,
            default: false,
            db: (prop) => dbBoolean(prop),
            enums: [false, true],
            enumLabels: ['Not a Software Developer', 'Software Developer'],
            view: [],
            edit: ['owner'],
        },
    ],
};

and the makeTable function looks like this:

export const makeTable = (schema: vingSchema) => {
    const columns: Record<string, AnyMySqlColumnBuilder> = {};
    const uniqueIndexes: Record<string, any> = {};
    for (const prop of schema.props) {
        columns[prop.name] = prop.db(prop as never);
        if (prop.unique) {
            const key = prop.name + 'Index';
            uniqueIndexes[key] = (table: Record<string, AnyMySqlColumn>) => uniqueIndex(key).on(table[prop.name]);
        }
    }
    const extras = (table: Record<string, any>) => {
        const out: Record<string, any> = {};
        for (const key in uniqueIndexes) {
            out[key] = uniqueIndexes[key](table);
        }
        return out;
    }
    return mysqlTable(schema.tableName, columns, extras)
}

export const makeTable = (schema: vingSchema) => {
    const columns: Record<string, AnyMySqlColumnBuilder> = {};
    const uniqueIndexes: Record<string, any> = {};
    for (const prop of schema.props) {
        columns[prop.name] = prop.db(prop as never);
        if (prop.unique) {
            const key = prop.name + 'Index';
            uniqueIndexes[key] = (table: Record<string, AnyMySqlColumn>) => uniqueIndex(key).on(table[prop.name]);
        }
    }
    const extras = (table: Record<string, any>) => {
        const out: Record<string, any> = {};
        for (const key in uniqueIndexes) {
            out[key] = uniqueIndexes[key](table);
        }
        return out;
    }
    return mysqlTable(schema.tableName, columns, extras)
}

all of that is "a lot" so that's why i simplified it for you at the start of this thread here's an example of one of the "db" helper functions that is generating your schema for an individual field type:

export const dbString = (prop: Extract<vingProp, { type: "string" }>) => {
    return varchar(prop.name, { length: prop.length }).notNull().default(stringDefault(prop, true));
}

export const dbString = (prop: Extract<vingProp, { type: "string" }>) => {
    return varchar(prop.name, { length: prop.length }).notNull().default(stringDefault(prop, true));
}

let me know if you need more, or if that was already too much and you need something simplified

Dan•2y ago

OK, I see. The thing is, the types you've created don't have enough type information to be correctly inferred into a Drizzle table. Your tables don't type have information about specific columns, only the type for all the columns in general. To make it clearer, what currently can be inferred from your schema is "in a userSchema, every column is either a string, an enum, a boolean etc. and has a name which is a string", but you cannot infer information like "userSchema is a table with a 'username' column of type string, an 'email' column of type string etc.", and that's the information that's required to create a Drizzle table. In other words, you have runtime data but no type data in your schemas. If you want to be able to infer type information about specific columns and other things (like the table name), all that information need to be stored on type level, i.e. in generics. That's why I asked if your schema format is final.

JTOP•2y ago

ok...so reading between the lines a bit, i think you are saying what i want is impossible i'm not opposed to changing my schema if needed another option is that i could potentially use my schema to generate your hand written table.ts files

Dan•2y ago

You mean via the codegen?

JTOP•2y ago

yeah

Dan•2y ago

That's also an option, yes If that's fine for you, it will be actually easier than building all the generic types required for this to work on type level

JTOP•2y ago

i don't like having the extra step, but if it means that i can write 1 schema to rule them all, and still use drizzle, then i'm fine doing that i really like drizzle, even though it might not seem like it sometimes when i'm grilling you for answers and again, i spent the past 6 weeks trying out over a dozen ORMs, so i know what's on the market you've got something really cool here

Dan•2y ago

All good, happy to help! Thank you for your support 💪 So as a summary: you'd either need your schema types augmented with generics to store the type information about the columns and the table name, or use codegen to generate the Drizzle tables using its native syntax.

JTOP•2y ago

let me ask 1 question, before i go off and write the code gen step can you give me a brief example of what the generics might look like. obviously i'd have to apply that through-out, but is it possible to give me a peek behind the curtain? i just want to make sure that code gen is the best way to go, and that i'm not missing something

Dan•2y ago

Yes, I can do that. I'll use the file you provided as a reference.

JTOP•2y ago

thanks

Dan•2y ago

@plainblackguy there you go: https://github.com/rizen/drizzle-icing/pull/1

GitHub

Implement converter to Drizzle schema by dankochetov · Pull Request...

Dan•2y ago

Also had to change the props from array to a map, because I wasn't able to easily figure out how to properly infer the array as a tuple

JTOP•2y ago

thank you also....wow yeah, code gen is going to be way cleaner and easier this pretty much exposes everything i hate about typescript

Dan•2y ago

Same Type syntax in TypeScript is god awful

JTOP•2y ago

probably code gen will be easier for people to understand also, cuz ultimately the file they get will look like what you show them in your docs not to mention, when i need to come bug you for a a solution to a problem, i can just give you my table definition without having to unwrap it from my schema 🙂 thanks again for all your help

Dan•2y ago

I mean, it's always good to learn advanced TypeScript patterns But yeah, in a long run it will be easier We made the ORM without the codegen not because it's simple, but because ~~we thought it's simple~~ we know TypeScript to the point when we can implement what we need without codegen The types we had to build are crazy hard

JTOP•2y ago

i'm certain of that...just using types is hard in a lot of cases, i can't even imagine trying to build something as complex as this as you have sleep well, and have a good weekend. from my point of view you've earned it

Dan•2y ago

Thanks! You too

JTOP•2y ago

and code generation complete!

import type { InferModel } from 'drizzle-orm/mysql-core';
import { boolean, mysqlEnum, mysqlTable, timestamp, uniqueIndex, varchar, text } from 'drizzle-orm/mysql-core';


export const UserTable = mysqlTable('users',
    {
        id: varchar('id', { length: 36 }).notNull().primaryKey(),
        createdAt: timestamp('createdAt').defaultNow().notNull(),
        updatedAt: timestamp('updatedAt').defaultNow().notNull(),
        username: varchar('username', { length: 60 }).notNull().default(''),
        email: varchar('email', { length: 256 }).notNull().default(''),
        realName: varchar('realName', { length: 60 }).notNull().default(''),
        password: varchar('password', { length: 256 }).notNull().default('no-password-specified'),
        passwordType: mysqlEnum('passwordType', ['bcrypt']).notNull().default('bcrypt'),
        useAsDisplayName: mysqlEnum('useAsDisplayName', ['username','email','realName']).notNull().default('username'),
        admin: boolean('admin').notNull().default(false),
        developer: boolean('developer').notNull().default(false)
    }, 
    (table) => ({
        usernameIndex: uniqueIndex('usernameIndex').on(table.username),
        emailIndex: uniqueIndex('emailIndex').on(table.email)
    })
);


export type UserModel = typeof UserTable;
export type UserSelect = InferModel<UserModel, 'select'>
export type UserInsert = InferModel<UserModel, 'insert'>

import type { InferModel } from 'drizzle-orm/mysql-core';
import { boolean, mysqlEnum, mysqlTable, timestamp, uniqueIndex, varchar, text } from 'drizzle-orm/mysql-core';


export const UserTable = mysqlTable('users',
    {
        id: varchar('id', { length: 36 }).notNull().primaryKey(),
        createdAt: timestamp('createdAt').defaultNow().notNull(),
        updatedAt: timestamp('updatedAt').defaultNow().notNull(),
        username: varchar('username', { length: 60 }).notNull().default(''),
        email: varchar('email', { length: 256 }).notNull().default(''),
        realName: varchar('realName', { length: 60 }).notNull().default(''),
        password: varchar('password', { length: 256 }).notNull().default('no-password-specified'),
        passwordType: mysqlEnum('passwordType', ['bcrypt']).notNull().default('bcrypt'),
        useAsDisplayName: mysqlEnum('useAsDisplayName', ['username','email','realName']).notNull().default('username'),
        admin: boolean('admin').notNull().default(false),
        developer: boolean('developer').notNull().default(false)
    }, 
    (table) => ({
        usernameIndex: uniqueIndex('usernameIndex').on(table.username),
        emailIndex: uniqueIndex('emailIndex').on(table.email)
    })
);


export type UserModel = typeof UserTable;
export type UserSelect = InferModel<UserModel, 'select'>
export type UserInsert = InferModel<UserModel, 'insert'>

looks like it was hand written

Dan•2y ago

Amazing!

JTOP•2y ago

thanks for your help

alexblokh•2y ago

@plainblackguy looks great! what did you use for code-gen? was it TS server API, or just string concatenations?

JTOP•2y ago

Just template strings.

alexblokh•2y ago

got it

Gaming

Programming

help with column builder types