Schema issues with split files, relational queries

I'm having issues getting db queries to work with split schema files. My config (drizzle.config.ts) looks like:

export default {
    out: './drizzle',
    schema: './src/lib/db/schema/*'
} satisfies Config;

export default {
    out: './drizzle',
    schema: './src/lib/db/schema/*'
} satisfies Config;

and my folder structure:

lib/db/schema:
  - users.ts
  - games.ts

lib/db/schema:
  - users.ts
  - games.ts

and when I do stuff like (auth user is in users.ts)

await db.query.auth_user.findFirst

await db.query.auth_user.findFirst

I get the error

Property 'auth_user' does not exist on type 'DrizzleTypeError<"Seems like the schema generic is missing - did you forget to add it to your DB type?">'

Property 'auth_user' does not exist on type 'DrizzleTypeError<"Seems like the schema generic is missing - did you forget to add it to your DB type?">'

So it seems like it can't load my table structure. When I had my schema in a single file, it was easy just do do

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

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

But I can't figure out how to do that when I have separate schema files?

7 Replies

Faruq Alli-Balogun•15mo ago

@zicho

import * as postgres from 'postgres';
import { drizzle, PostgresJsDatabase } from 'drizzle-orm/postgres-js';
import { FactoryProvider } from '@nestjs/common';
import { ConfigService } from '@nestjs/config';
import * as otp from 'src/database/schemas/otp';
import * as users from 'src/database/schemas/users';

const schema = {
  ...otp,
  ...users,
};

export const Database = Symbol('DATABASE_SERVICE');
export type DatabaseType = PostgresJsDatabase<typeof schema>;

export const DatabaseProvider: FactoryProvider = {
  provide: Database,
  useFactory: async (configService: ConfigService) => {
    const connection = postgres(configService.get('DATABASE_URL'));

    return drizzle(connection, {
      schema,
    });
  },
  inject: [ConfigService],
};

import * as postgres from 'postgres';
import { drizzle, PostgresJsDatabase } from 'drizzle-orm/postgres-js';
import { FactoryProvider } from '@nestjs/common';
import { ConfigService } from '@nestjs/config';
import * as otp from 'src/database/schemas/otp';
import * as users from 'src/database/schemas/users';

const schema = {
  ...otp,
  ...users,
};

export const Database = Symbol('DATABASE_SERVICE');
export type DatabaseType = PostgresJsDatabase<typeof schema>;

export const DatabaseProvider: FactoryProvider = {
  provide: Database,
  useFactory: async (configService: ConfigService) => {
    const connection = postgres(configService.get('DATABASE_URL'));

    return drizzle(connection, {
      schema,
    });
  },
  inject: [ConfigService],
};

This works with schemas in different files

xamarot•15mo ago

thanks! I did manage to fix it in another way though:

// index.ts
export * from './schema';
export * from './schema2';
export * from './schema3';

// client.ts
import postgres from 'postgres';
import { drizzle } from 'drizzle-orm/postgres-js'; 
import * as schema from './index';

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

// index.ts
export * from './schema';
export * from './schema2';
export * from './schema3';

// client.ts
import postgres from 'postgres';
import { drizzle } from 'drizzle-orm/postgres-js'; 
import * as schema from './index';

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

It would be nice to have some examples in the docs though, so I submitted a suggestion. Let's hope it comes through

Faruq Alli-Balogun•15mo ago

Alright. Thank you! @zicho Do you have any idea how to query one-to-many from the many side and check where on the one side?

  return this.database.query.otp.findMany({
      where: and(eq(otp.code, code), eq(otp.status, 'pending')),
      with: {
        user: {
          where: eq(users.email, email),
        },
      },
    });

  return this.database.query.otp.findMany({
      where: and(eq(otp.code, code), eq(otp.status, 'pending')),
      with: {
        user: {
          where: eq(users.email, email),
        },
      },
    });

xamarot•15mo ago

Hm, what data is is that you want? All "otp" where the email is: - equal to user email and - equal to code and equal to status: pending? how does the table structure look?

Faruq Alli-Balogun•15mo ago

Figured it out but from the user's table not otp

    return this.database.query.users.findMany({
      where: eq(users.email, email),
      with: {
        otp: {
          where: and(eq(otp.code, code), eq(otp.status, 'pending')),
        },
      },
    });

    return this.database.query.users.findMany({
      where: eq(users.email, email),
      with: {
        otp: {
          where: and(eq(otp.code, code), eq(otp.status, 'pending')),
        },
      },
    });

So I want to do that inversely from the OTP table not users table I'm trying to get otp where code = '' and status = '' and where the related user email = ''

fatribz•13mo ago

import { createRequire } from 'module';
import fs from 'fs';

const require = createRequire(import.meta.url);

export default fs.readdirSync(__dirname).reduce((schema: any, file: string) => {
  if (file == "index.ts") return schema;
  Object.assign(schema, require(/* @vite-ignore */ __dirname + '/' + file));
  return schema;
}, {});

import { createRequire } from 'module';
import fs from 'fs';

const require = createRequire(import.meta.url);

export default fs.readdirSync(__dirname).reduce((schema: any, file: string) => {
  if (file == "index.ts") return schema;
  Object.assign(schema, require(/* @vite-ignore */ __dirname + '/' + file));
  return schema;
}, {});

dynamic import of schema files in a directory, exported into one object

Angelelz•13mo ago

They removed the ability to filter by nested relations in an earlier release: https://github.com/drizzle-team/drizzle-orm/releases/tag/0.28.0

GitHub

Release 0.28.0 · drizzle-team/drizzle-orm

Breaking changes Removed support for filtering by nested relations Current example won't work in 0.28.0: const usersWithPosts = await db.query.users.findMany({ where: (table, { sql }) => (...

Gaming

Programming

Schema issues with split files, relational queries