DrizzleORM v0.27.2 release
Jul 12, 2023

๐ŸŽ‰ Added support for UNIQUE constraints in PostgreSQL, MySQL, SQLite

For PostgreSQL, unique constraints can be defined at the column level for single-column constraints, and in the third parameter for multi-column constraints. In both cases, it will be possible to define a custom name for the constraint. Additionally, PostgreSQL will receive the NULLS NOT DISTINCT option to restrict having more than one NULL value in a table. Reference

Examples that just shows a different unique usage. Please donโ€™t search a real usage for those tables

// single column
const table = pgTable('table', {
  id: serial('id').primaryKey(),
  name: text('name').notNull().unique(),
  state: char('state', { length: 2 }).unique('custom'),
  field: char('field', { length: 2 }).unique('custom_field', { nulls: 'not distinct' }),
});
// multiple columns
const table = pgTable('table', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
  state: char('state', { length: 2 }),
}, (t) => ({
  first: unique('custom_name').on(t.name, t.state).nullsNotDistinct(),
  second: unique('custom_name1').on(t.name, t.state),
}));

For MySQL, everything will be the same except for the NULLS NOT DISTINCT option. It appears that MySQL does not support it

Examples that just shows a different unique usage. Please donโ€™t search a real usage for those tables

// single column
const table = mysqlTable('table', {
    id: serial('id').primaryKey(),
    name: text('name').notNull().unique(),
    state: text('state').unique('custom'),
    field: text('field').unique('custom_field'),
});
// multiple columns
const table = mysqlTable('cities1', {
    id: serial('id').primaryKey(),
    name: text('name').notNull(),
    state: text('state'),
}, (t) => ({
    first: unique().on(t.name, t.state),
    second: unique('custom_name1').on(t.name, t.state),
}));

In SQLite unique constraints are the same as unique indexes. As long as you can specify a name for the unique index in SQLite - we will treat all unique constraints as unique indexes in internal implementation

// single column
const table = sqliteTable('table', {
    id: int('id').primaryKey(),
    name: text('name').notNull().unique(),
    state: text('state').unique('custom'),
    field: text('field').unique(),
});
// multiple columns
const table = sqliteTable('table', {
    id: int('id').primaryKey(),
    name: text('name').notNull(),
    state: text('state'),
}, (t) => ({
    first: unique().on(t.name, t.state),
    second: unique('custom').on(t.name, t.state),
}));