Drizzle Team•16mo ago

Are postgres CONSTRAINTS supported when creating a table schema?

In the column builder types I see there's a general assumption of name, dataType, columnType. In contrast the actual Postgres CREATE TABLE syntax is very rich: https://www.postgresql.org/docs/current/sql-createtable.html Are there ways to support column constraints, table constraints, etc? If a dev has requirements not yet supported by Drizzle, it appears from the docs the use-case is understood (much appreciated!) and the sql template exists for that. However I don't think that sql is supported for generating table schemas or that we can use it with custom type definitions? Is there a way I can still use drizzle schemas and migrations if I need to override some of the generated output to meet the requirements of my schema? If custom types enabled adding constraints that would be really helpful. Its like I can almost do it: if I make a customType() definition return anything other than a one-word string when specifying dataType the generated SQL encapsulates the entire value in double quotes. If it didn't do that, I would be able to return a string that included my type and constraints and be off to the races. That's probably a bit of a hack but it shows a potential path to supporting column constraints: as a property that can be specified when defining a custom type (if they're not already supported and I just don't know how and can't find any docs on it).

PostgreSQL Documentation

CREATE TABLE

CREATE TABLE CREATE TABLE — define a new table Synopsis CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | …

1 Reply

firxworxOP•16mo ago

Funnily enough if I start on the not-desired type

export const user = pgTable(
  'user',
  {
    id: integer('id').primaryKey(),

export const user = pgTable(
  'user',
  {
    id: integer('id').primaryKey(),

and generate migrations, and then go back, revise to the following and generate using a custom type:

export const user = pgTable(
  'user',
  {
    id: customIdentity('id').primaryKey(),

export const user = pgTable(
  'user',
  {
    id: customIdentity('id').primaryKey(),

then a migration will be generated that does not wrap the returned value of the custom type in quotation marks:

ALTER TABLE "user" ALTER COLUMN "id" SET DATA TYPE int GENERATED BY DEFAULT AS IDENTITY;

ALTER TABLE "user" ALTER COLUMN "id" SET DATA TYPE int GENERATED BY DEFAULT AS IDENTITY;

meanwhile if I defined my schema with id using the custom type out of the gate it gets generated with quotation marks:

CREATE TABLE IF NOT EXISTS "user" (
    "id" "int GENERATED BY DEFAULT AS IDENTITY",

CREATE TABLE IF NOT EXISTS "user" (
    "id" "int GENERATED BY DEFAULT AS IDENTITY",

Gaming

Programming

Are postgres CONSTRAINTS supported when creating a table schema?