Kysely•6mo ago

How to add array column in migrations?

I am trying to add a column to a table via migrations but cannot find how to do something like this: .addColumn('countyNames', 'varchar', col => col.IsArray() )

Solution:

.addColumn('countyNames', sql`varchar[]`)

.addColumn('countyNames', sql`varchar[]`)

...

Jump to solution

8 Replies

Solution

koskimas•6mo ago

.addColumn('countyNames', sql`varchar[]`)

.addColumn('countyNames', sql`varchar[]`)

mikeOP•6mo ago

thanks @koskimas. Have you been thinking of adding partial typing to migrations? we created our own framework on top of umzug framework where we can easily use typing and still can override existing (present) types in the codebase. For example:

class DeprecatedUser extends User {
    deprecatedColumn: string;
}

export const up: Migration = async ({ context: sequelize }) => {
    const helper = await sequelize.helper();
    const user = await helper.model<User, DeprecatedUser>(USER_ALIAS);
    await user.dropColumn('deprecatedColumn');
    await user.createColumn('avatarId', {
        type: DataType.INTEGER,
        allowNull: true,
    });
};

export const down: Migration = async ({ context: sequelize }) => {
    const helper = await sequelize.helper();
    const user = await helper.model<User, DeprecatedUser>(USER_ALIAS);
    await user.createColumn('deprecatedColumn', {
        type: DataType.STRING,
    });
    await user.dropColumn('avatarId');
};

class DeprecatedUser extends User {
    deprecatedColumn: string;
}

export const up: Migration = async ({ context: sequelize }) => {
    const helper = await sequelize.helper();
    const user = await helper.model<User, DeprecatedUser>(USER_ALIAS);
    await user.dropColumn('deprecatedColumn');
    await user.createColumn('avatarId', {
        type: DataType.INTEGER,
        allowNull: true,
    });
};

export const down: Migration = async ({ context: sequelize }) => {
    const helper = await sequelize.helper();
    const user = await helper.model<User, DeprecatedUser>(USER_ALIAS);
    await user.createColumn('deprecatedColumn', {
        type: DataType.STRING,
    });
    await user.dropColumn('avatarId');
};

koskimas•6mo ago

Types in migrations is a horrible idea. Types evolve, migrations can never change. At least if you're using migrations the way the are meant to be used and don't nuke the db and run all migrations every time something changes

mikeOP•6mo ago

we don't run migrations every time and still evolve the types. The only moment we have to touch migrations (just for fixing type safe not because of runtime) is the deprecated column mentioned in the previous migrations. During last year it happened only twice but as I said - it's just the typing - it doesn't have any impact on the database or codebase. I generally agree with keeping migrations independent from types but in this case I must honestly say it makes our lives easier and happier. 🙂

Igal•6mo ago

I wonder if a frozen-in-time DB interface/s per migration file could work. Could be generated as part of migrate:make command. Would represent the state that the command runner knew about when writing the migration - regardless of other efforts in the team. Is it worth it? not so much... the feedback loop is much quicker in migrations anyway and it's not code that changes - no regression risk

mikeOP•6mo ago

As I said before I generally agree on migration and codebase typing separation. There is one more flow which satisfies both requirements - type safety and separation. You can create temporary types from the current-state types and once the migration is done the sources would be replaced with any.

koskimas•6mo ago

If we added types to migrations, it'd be a footgun. Many (most?) users wouldn't understand that using types there is risky and they'd just default to doing it, leading to all kinds of issues. "Just document the risks" Yeah right, people read docs.

mikeOP•6mo ago

Maybe something has been lost in the translation. I meant to use the types only during the creation process of the migration and remove it right after it's finished (before it's committed). It's the sweet spot for many.

Gaming

Programming

How to add array column in migrations?