Drizzle Team•11mo ago

Pull schema changes without using drizzle migrations

Hey all, we are currently using Postgres and have migrations running through Flyway. I want to just use Drizzle as a query builder essentially, but to do that I need to keep my schema up-to-date with external migrations. I see the introspect command, but that seems to completely re-write the schema (where there are errors that I have to manually fix). Is there a better way to just "pull" changes if need-be and only alter tables that differ while using external migrations?

1 Reply

zach•11mo ago

Hi, I just recently posted about a similar setup I'm using with flyway in the #kit-discussion channel. I'm currently continuing to use flyway to handle my migrations and have a Makefile that I use to run the db introspection after my flyway migrations run to update the drizzle types. I'm treating the generated introspection files as read-only and only update my sql in the flyway migrations. If you just have one schema in your database like public it should be pretty simple, if you're using multiple database schemas it can be a little more of a setup. I use multiple schemas and had to add a few adjustments to make it work, so for the example below let's say we have two database schemas, public and otherSchema. This setup allows you to have 1 or many schemas under the same database even though drizzle-kit doesn't support that out of the box well. This setup keeps schemas in their own namespace so they can even share same table names. Here's my Makefile that I use to run flyway migrations, then I run the drizzle db introspection to update the models, and then a few commands to clean up the drizzle migrations folders that I don't need as well as fix some of the errors drizzle-kit has on parsing. So with this setup, I just run make migrate-db after I change some sql in the flyway migration files, and it flows down to my app and keeps the drizzle schema types updated. You can add more sed commands to fix any other types of parsing errors that come out of introspection. Makefile

# For starting Postgres and flyway container
start-db:
    docker-compose up --build -d db
    docker-compose up --build flyway

# For stopping Postgres and flyway container
stop-db:
    docker-compose down db
    docker-compose down flyway

# For resetting Postgres and flyway containers and deleting all data/schema
reset-db:
    docker-compose down db -v
    docker-compose down flyway -v

# Runs drizzle introspection which converts flyway .sql files into drizzle typescript models and types
# sed cmd #1 fixes issue drizzle has with introspecting varchar arrays
# sed cmd #2 replaces extra space at the beginning of the second line in the file (trims leading/trailing)
# Note about sed: Must add an extra $ character and only use single quotes for the cmd to work with makefile
introspect-db:
    docker-compose up --build db-introspect
    sed -i '' 's/)\[ \})\.array()/ \})\.array()/g' ./apps/ts-app/src/clients/postgres/**/schema.ts
    sed -i '' '2s/^ *//;s/ *$$//' ./apps/ts-app/src/clients/postgres/**/schema.ts

migrate-db:
    docker-compose up --build flyway
    make introspect-db

# For starting Postgres and flyway container
start-db:
    docker-compose up --build -d db
    docker-compose up --build flyway

# For stopping Postgres and flyway container
stop-db:
    docker-compose down db
    docker-compose down flyway

# For resetting Postgres and flyway containers and deleting all data/schema
reset-db:
    docker-compose down db -v
    docker-compose down flyway -v

# Runs drizzle introspection which converts flyway .sql files into drizzle typescript models and types
# sed cmd #1 fixes issue drizzle has with introspecting varchar arrays
# sed cmd #2 replaces extra space at the beginning of the second line in the file (trims leading/trailing)
# Note about sed: Must add an extra $ character and only use single quotes for the cmd to work with makefile
introspect-db:
    docker-compose up --build db-introspect
    sed -i '' 's/)\[ \})\.array()/ \})\.array()/g' ./apps/ts-app/src/clients/postgres/**/schema.ts
    sed -i '' '2s/^ *//;s/ *$$//' ./apps/ts-app/src/clients/postgres/**/schema.ts

migrate-db:
    docker-compose up --build flyway
    make introspect-db

package.json

{
  ...
  scripts: {
    ...
    "db-introspect": "drizzle-kit introspect:pg --config=src/clients/postgres/public/drizzle.config.ts && drizzle-kit introspect:pg --config=src/clients/postgres/otherSchema/drizzle.config.ts && rm -rf src/clients/postgres/**/meta/ && rm -rf src/clients/postgres/**/*.sql",
    ...
  }
}

{
  ...
  scripts: {
    ...
    "db-introspect": "drizzle-kit introspect:pg --config=src/clients/postgres/public/drizzle.config.ts && drizzle-kit introspect:pg --config=src/clients/postgres/otherSchema/drizzle.config.ts && rm -rf src/clients/postgres/**/meta/ && rm -rf src/clients/postgres/**/*.sql",
    ...
  }
}

docker-compose.yml

db:
    container_name: pg-db
    build:
      context: ./db
      dockerfile: Dockerfile.postgres
    restart: unless-stopped
    environment:
      - POSTGRES_DB=localdb
      - POSTGRES_USER=localuser
      - POSTGRES_PASSWORD=localpassword
    volumes:
      - db:/var/lib/postgresql/data
    ports:
      - '5434:5432'
    networks:
      - app-local-network
    healthcheck:
      test: ['CMD-SHELL', 'pg_isready']

flyway:
    container_name: flyway
    image: flyway/flyway:8.5.7
    command: -connectRetries=60 -url=jdbc:postgresql://db:5432/localdb?useSSL=false -user=localuser -password=localpassword migrate
    volumes:
      - ./db/sql:/flyway/sql
      - ./db/conf:/flyway/conf
    depends_on:
      - db
    networks:
      - app-local-network

db-introspect:
    build:
      context: ./apps/ts-app
      dockerfile: Dockerfile
      target: builder
    depends_on:
      - db
    container_name: db-introspect
    restart: 'no'
    env_file: .env
    environment:
      - NODE_ENV=development
    volumes:
      - ./apps/ts-app/src/:/app/src
    networks:
      - app-local-network
    command: npm run db-introspect

db:
    container_name: pg-db
    build:
      context: ./db
      dockerfile: Dockerfile.postgres
    restart: unless-stopped
    environment:
      - POSTGRES_DB=localdb
      - POSTGRES_USER=localuser
      - POSTGRES_PASSWORD=localpassword
    volumes:
      - db:/var/lib/postgresql/data
    ports:
      - '5434:5432'
    networks:
      - app-local-network
    healthcheck:
      test: ['CMD-SHELL', 'pg_isready']

flyway:
    container_name: flyway
    image: flyway/flyway:8.5.7
    command: -connectRetries=60 -url=jdbc:postgresql://db:5432/localdb?useSSL=false -user=localuser -password=localpassword migrate
    volumes:
      - ./db/sql:/flyway/sql
      - ./db/conf:/flyway/conf
    depends_on:
      - db
    networks:
      - app-local-network

db-introspect:
    build:
      context: ./apps/ts-app
      dockerfile: Dockerfile
      target: builder
    depends_on:
      - db
    container_name: db-introspect
    restart: 'no'
    env_file: .env
    environment:
      - NODE_ENV=development
    volumes:
      - ./apps/ts-app/src/:/app/src
    networks:
      - app-local-network
    command: npm run db-introspect

My app directory structure is the following:

- /repo-name
  - docker-compose.yml
  - Makefile
  - /db
    - /conf
      - flyway.conf
    - /sql
      - /migrations
        - R__001_rep_migration.sql
        - V001__migration.sql
        - V002__migration.sql
  - /apps
    - /ts-app
      - Dockerfile
      - package.json
      - tsconfig.json
      - /src
        - /clients
          - /postgres
            - index.ts // exports drizzle instance with combined schemas
            - /public
              - drizzle.config.ts
              - schema.ts // generated file via introspection, don't edit manually
            - /otherSchema
              - drizzle.config.ts
              - schema.ts // generated file via introspection, don't edit manually

- /repo-name
  - docker-compose.yml
  - Makefile
  - /db
    - /conf
      - flyway.conf
    - /sql
      - /migrations
        - R__001_rep_migration.sql
        - V001__migration.sql
        - V002__migration.sql
  - /apps
    - /ts-app
      - Dockerfile
      - package.json
      - tsconfig.json
      - /src
        - /clients
          - /postgres
            - index.ts // exports drizzle instance with combined schemas
            - /public
              - drizzle.config.ts
              - schema.ts // generated file via introspection, don't edit manually
            - /otherSchema
              - drizzle.config.ts
              - schema.ts // generated file via introspection, don't edit manually

/repo-name/apps/ts-app/src/clients/postgres/public/drizzle.config.ts

import { defineConfig } from "drizzle-kit";
import * as dotenv from "dotenv";

const isDev = process.env.NODE_ENV !== 'production';

if (isDev) {
  dotenv.config();
}

export default defineConfig({
  schema: "./src/clients/postgres/public/schema.ts", // This doesn't seem to work for introspection, only works for creating migrations
  driver: "pg",
  dbCredentials: {
    connectionString: process.env.POSTGRES_DB_URL!
  },
  out: "./src/clients/postgres/public",
  schemaFilter: ["public"],
  introspect: { casing: 'preserve' }
});

import { defineConfig } from "drizzle-kit";
import * as dotenv from "dotenv";

const isDev = process.env.NODE_ENV !== 'production';

if (isDev) {
  dotenv.config();
}

export default defineConfig({
  schema: "./src/clients/postgres/public/schema.ts", // This doesn't seem to work for introspection, only works for creating migrations
  driver: "pg",
  dbCredentials: {
    connectionString: process.env.POSTGRES_DB_URL!
  },
  out: "./src/clients/postgres/public",
  schemaFilter: ["public"],
  introspect: { casing: 'preserve' }
});

/repo-name/apps/ts-app/src/clients/postgres/otherSchema/drizzle.config.ts

import { defineConfig } from "drizzle-kit";
import * as dotenv from "dotenv";

const isDev = process.env.NODE_ENV !== 'production';

if (isDev) {
  dotenv.config();
}

export default defineConfig({
  schema: "./src/clients/postgres/otherSchema/schema.ts", // This doesn't seem to work for introspection, only works for creating migrations
  driver: "pg",
  dbCredentials: {
    connectionString: process.env.POSTGRES_DB_URL!
  },
  out: "./src/clients/postgres/otherSchema",
  schemaFilter: ["otherSchema"],
  introspect: { casing: 'preserve' }
});

import { defineConfig } from "drizzle-kit";
import * as dotenv from "dotenv";

const isDev = process.env.NODE_ENV !== 'production';

if (isDev) {
  dotenv.config();
}

export default defineConfig({
  schema: "./src/clients/postgres/otherSchema/schema.ts", // This doesn't seem to work for introspection, only works for creating migrations
  driver: "pg",
  dbCredentials: {
    connectionString: process.env.POSTGRES_DB_URL!
  },
  out: "./src/clients/postgres/otherSchema",
  schemaFilter: ["otherSchema"],
  introspect: { casing: 'preserve' }
});

/repo-name/apps/ts-app/src/clients/postgres/index.ts

import postgres from 'postgres';
import { drizzle } from 'drizzle-orm/postgres-js';

import * as publicSchema from './public/schema';
import * as otherSchema from './otherSchema/schema';

export const schema = {
  public: publicSchema,
  otherSchema
};

const client = postgres(process.env.POSTGRES_DB_URL);
export const db = drizzle(client, { schema, logger: false });

import postgres from 'postgres';
import { drizzle } from 'drizzle-orm/postgres-js';

import * as publicSchema from './public/schema';
import * as otherSchema from './otherSchema/schema';

export const schema = {
  public: publicSchema,
  otherSchema
};

const client = postgres(process.env.POSTGRES_DB_URL);
export const db = drizzle(client, { schema, logger: false });

Gaming

Programming

Pull schema changes without using drizzle migrations