DT
Drizzle Team•16mo ago
JT

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
bloberenober
bloberenober•16mo 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
bloberenober
bloberenober•16mo 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.
JT
JT•16mo 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?
bloberenober
bloberenober•16mo 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.
JT
JT•16mo 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?
bloberenober
bloberenober•16mo 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).
JT
JT•16mo ago
Sounds good. Thanks for the consideration.
bloberenober
bloberenober•16mo 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!
JT
JT•16mo ago
Sweet, and which level do I need for this project?
bloberenober
bloberenober•16mo ago
I'd estimate it in 1-3 hours. You can start with the silver tier.
JT
JT•16mo 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?
bloberenober
bloberenober•16mo 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.
JT
JT•16mo 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
bloberenober
bloberenober•16mo 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?
JT
JT•16mo 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
bloberenober
bloberenober•16mo 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.
JT
JT•16mo 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
bloberenober
bloberenober•16mo ago
You mean via the codegen?
JT
JT•16mo ago
yeah
bloberenober
bloberenober•16mo 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
JT
JT•16mo 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
bloberenober
bloberenober•16mo 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.
JT
JT•16mo 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
bloberenober
bloberenober•16mo ago
Yes, I can do that. I'll use the file you provided as a reference.
JT
JT•16mo ago
thanks
bloberenober
bloberenober•16mo ago
bloberenober
bloberenober•16mo 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
JT
JT•16mo 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
bloberenober
bloberenober•16mo ago
Same Type syntax in TypeScript is god awful
JT
JT•16mo 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
bloberenober
bloberenober•16mo 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
JT
JT•16mo 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
bloberenober
bloberenober•16mo ago
Thanks! You too
JT
JT•16mo 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
bloberenober
bloberenober•16mo ago
Amazing!
JT
JT•16mo ago
thanks for your help
alexblokh
alexblokh•16mo ago
@plainblackguy looks great! what did you use for code-gen? was it TS server API, or just string concatenations?
JT
JT•16mo ago
Just template strings.
alexblokh
alexblokh•16mo ago
got it
Want results from more Discord servers?
Add your server
More Posts
migration on mysql errorsI've written this migrator: ```ts import { migrate } from 'drizzle-orm/mysql2/migrator'; import { ddrizzle-kit generates mysql 8 syntax, but AWS Aurora uses 5.7 syntax, any way to switch?I think the title says it allerror: there is no transaction in progressI created a drizzle table: ```ts export const passwordTypeEnum = pgEnum('passwordType', ['bcrypt'])Having a problem just connecting with drizzleHowdy, I just found out about drizzle and thought I might give it a try, but unfortunately I'm havin