sten Posts - Answer Overflow

sten

Explore posts from servers

•Created by sten on 8/7/2024 in #help

[solved] The server does not support SSL connections

I am running Drizzle with Postgres in a devcontainer using Docker WSL 2. In my db file:

import { drizzle } from 'drizzle-orm/node-postgres';
import pg from 'pg';
import * as schema from './schema.ts';
import 'dotenv/config';

const client = new pg.Client({
  host: process.env.DB_HOST,
  port: parseInt(process.env.DB_PORT || '3000'),
  user: process.env.DB_USER,
  password: process.env.DB_PASS,
  database: process.env.DB_NAME,
  ssl: { rejectUnauthorized: false }
});

export const connection = await client.connect();

export const db = drizzle(client, { schema });

import { drizzle } from 'drizzle-orm/node-postgres';
import pg from 'pg';
import * as schema from './schema.ts';
import 'dotenv/config';

const client = new pg.Client({
  host: process.env.DB_HOST,
  port: parseInt(process.env.DB_PORT || '3000'),
  user: process.env.DB_USER,
  password: process.env.DB_PASS,
  database: process.env.DB_NAME,
  ssl: { rejectUnauthorized: false }
});

export const connection = await client.connect();

export const db = drizzle(client, { schema });

In my config:

import { defineConfig } from 'drizzle-kit';
import dotenv from 'dotenv';

dotenv.config();

export default defineConfig({
  schema: './src/db/schema.ts',
  out: './drizzle',
  dialect: 'postgresql',
  dbCredentials: {
    host: process.env.DB_HOST || 'localhost',
    user: process.env.DB_USER || 'postgres',
    password: process.env.DB_PASS || '',
    database: process.env.DB_NAME || 'postgres',
    port: parseInt(process.env.DB_PORT || '5432'),
    ssl: false
  }
});

import { defineConfig } from 'drizzle-kit';
import dotenv from 'dotenv';

dotenv.config();

export default defineConfig({
  schema: './src/db/schema.ts',
  out: './drizzle',
  dialect: 'postgresql',
  dbCredentials: {
    host: process.env.DB_HOST || 'localhost',
    user: process.env.DB_USER || 'postgres',
    password: process.env.DB_PASS || '',
    database: process.env.DB_NAME || 'postgres',
    port: parseInt(process.env.DB_PORT || '5432'),
    ssl: false
  }
});

Despite this, I keep getting the SSL issue. I am starting my container like this: CMD ["sh", "-c", "npm run generate && npm run migrate && npm run dev"] Where the commands correspond to

  "dev": "tsx src/index.ts",
  "generate": "drizzle-kit up",
  "migrate": "drizzle-kit migrate",

  "dev": "tsx src/index.ts",
  "generate": "drizzle-kit up",
  "migrate": "drizzle-kit migrate",

I am calling generate and migrate to assure that if someone would run this on their system, they've would have the correct tables setup. UPDATE: So I removed ssl: { rejectUnauthorized: false } and it worked, however, upon connecting to the db docker exec -it postgres_db psql -U postgres -d postgres, it reveals that I don't have any relations

postgres=# \dt
Did not find any relations.

postgres=# \dt
Did not find any relations.

UPDATE 2: I noticed that my generate command was wrong

"generate": "drizzle-kit generate"

"generate": "drizzle-kit generate"

I was using drizzle-kit up, must have mixed them up somehow

1 replies

DTDrizzle Team

•Created by sten on 12/30/2023 in #help

Limitation in Drizzle? findMany does not work the same as an innerjoin, is it possible?

So I want to get all posts with supporters with a specific user_id. Q1 gives me that but I'd like to do it with Q2 but Q2 gives me all posts instead of just the ones that the user_id is supporting.

Q1:
const result = await db
  .select()
  .from(posts)
  .innerJoin(supporters, eq(posts.id, supporters.idea_id))
  .innerJoin(users, eq(supporters.user_id, users.id))
  .where(eq(users.id, user_id))
  .all();

Q2:
const result = await db.query.posts.findMany({
  with: {
    supporters: {
      where: (supporters, { eq }) => eq(supporters.user_id, user_id)
    }
  }
});

Q1:
const result = await db
  .select()
  .from(posts)
  .innerJoin(supporters, eq(posts.id, supporters.idea_id))
  .innerJoin(users, eq(supporters.user_id, users.id))
  .where(eq(users.id, user_id))
  .all();

Q2:
const result = await db.query.posts.findMany({
  with: {
    supporters: {
      where: (supporters, { eq }) => eq(supporters.user_id, user_id)
    }
  }
});

UPDATE I went this Q3 which works fine.

Q3:
const supporterResult = await db.query.supporters.findMany({
    where: (supporters, { eq }) => eq(supporters.user_id, user_id)
});
const ideaIds = supporterResult.map((supporter) => supporter.idea_id);

const result = await db.query.posts.findMany({
    with: {
        supporters: true
    },
    where: (posts) => inArray(posts.id, ideaIds)
});

Q3:
const supporterResult = await db.query.supporters.findMany({
    where: (supporters, { eq }) => eq(supporters.user_id, user_id)
});
const ideaIds = supporterResult.map((supporter) => supporter.idea_id);

const result = await db.query.posts.findMany({
    with: {
        supporters: true
    },
    where: (posts) => inArray(posts.id, ideaIds)
});

6 replies

Gaming

Programming