zach Comments - Answer Overflow

zach

•Created by lordbinbash on 6/21/2024 in #help

Introspect poor accuracy (SQLite/Turso)

There’s probably a few more issues I can’t remember right now, but yeah I’m basically just fixing them with a few clean up commands after running introspection

4 replies

DTDrizzle Team

•Created by lordbinbash on 6/21/2024 in #help

Introspect poor accuracy (SQLite/Turso)

Yeah, my issues are with using Postgres though. The schemas generated with introspection contains javascript errors with unclosed brackets for varchar arrays, views still not getting generated, and foreign keys not added when connecting to remote databases

4 replies

DTDrizzle Team

•Created by Happer64Bit on 6/21/2024 in #help

is there a way to use Redis in drizzle

I don't believe so, at least according to their docs. What are you using Redis for that would require something like Drizzle? Are you just trying to cache sql query results while using Redis as middleware? I use the redis npm library with a few simple get, set and delete functions that cover my use cases, using Redis as a caching database. I would imagine there's ways to set it up to cache your drizzle sql queries with helper functions, but you would lose all of the drizzle type-safe benefits.

4 replies

DTDrizzle Team

•Created by sockthedev on 6/19/2024 in #help

Drizzle Kit: How to introspect Postgres DB without creating relations in generated schema?

I'd be interested in this as well, since the relations files generate with errors for large db's. Currently I just added the following to my package.json script to remove the relations files after introspection: package.json

scripts: {
  db-introspect: drizzle-kit introspect --config=src/clients/postgres/public/drizzle.config.ts && rm -rf src/clients/postgres/**/relations.ts
}

scripts: {
  db-introspect: drizzle-kit introspect --config=src/clients/postgres/public/drizzle.config.ts && rm -rf src/clients/postgres/**/relations.ts
}

Add and modify && rm -rf src/clients/postgres/**/relations.ts to target the directory you want to clean up after introspection

2 replies

DTDrizzle Team

•Created by Cole on 2/13/2024 in #help

Pull schema changes without using drizzle migrations

/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 });

9 replies

DTDrizzle Team

•Created by Cole on 2/13/2024 in #help

Pull schema changes without using drizzle migrations

/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' }
});

9 replies

DTDrizzle Team

•Created by Cole on 2/13/2024 in #help

Pull schema changes without using drizzle migrations

/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' }
});

9 replies

DTDrizzle Team

•Created by Cole on 2/13/2024 in #help

Pull schema changes without using drizzle migrations

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

9 replies

DTDrizzle Team

•Created by Cole on 2/13/2024 in #help

Pull schema changes without using drizzle migrations

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

9 replies

DTDrizzle Team

•Created by Cole on 2/13/2024 in #help

Pull schema changes without using drizzle migrations

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",
    ...
  }
}

9 replies

DTDrizzle Team

•Created by Cole on 2/13/2024 in #help

Pull schema changes without using drizzle migrations

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

9 replies

DTDrizzle Team

•Created by Cole on 2/13/2024 in #help

Pull schema changes without using drizzle migrations

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.

9 replies

DTDrizzle Team

•Created by zach on 2/3/2024 in #help

How can I read the table name and schema name of a created drizzle table instance?

Perfect, thank you!

4 replies

Gaming

Programming