Using same schema but different databases

[PaquitoSoft]

Hi there ✋

I'm ussing Drizzle in the application I'm building and the first interation was successful but I'm struggling a bit when trying to setup the necessary resources for my integration tests.

I'm using Supabase (Postgreql) for managing my application data but, when running my integration tests, I would prefer to use a local Sqlite database.
I thought about using drizzle-kit to create a db file, push the schema, seed some data and then run the tests.

I currently have tow config files:

// PRODUCTION CONFIG
import type { Config } from 'drizzle-kit';
import dotenv from 'dotenv';

dotenv.config({
  path: '../../.env.local',
});

export default <Config>{
  dialect: 'postgresql',
  schema: './src/models',
  out: './migrations',
  dbCredentials: {
    url: process.env.IFSECRET_DATABASE_URL,
  },
};

// TESTS CONFIG
import type { Config } from 'drizzle-kit';

export default <Config>{
  dialect: 'sqlite',
  schema: './src/models',
  out: './specs/fixtures/db/migrations',
  dbCredentials: {
    url: './specs/fixtures/db/tests.db',
  },
};

As part of my tests setup, I run the dizzle-kit push command (using my test config) but I'm not getting the schema applied to the database (the database file is actually created). This is the response I get back:

No changed detected

Something similar happens when I try to generate migration files. I get this message back:

No schema changes, nothing to migrate

Does anyone know if I'm using a wrong reasoning?
Am I missing something in this setup?

Thanks a lot!

[djm181]

Have you considered using pglite instead of sqlite? There are some meaningful differences between sqlite and pg dialects, but pglite is basically a drop-in replacement. As long as you aren't using any unsupported PG extensions.

Drizzle has first-class support for pglite too.

[PaquitoSoft]

Hi @djm181 ✋

Thanks for the feedback.

I can use pglite as well (didn't know about it), but the issue here described is independent on the database implementation I use.

[djm181]

What I mean is that you can use the same “out” folder for pglite as you can for Postgres, so your differences between your config is reduced. I think this will help with the issue with migrations you are running into. Although “push” shouldn’t care about this.

Can you post the full commands you are using to switch configs as maybe the problem lies there

[PaquitoSoft]

This is how my production config looks like:

import type { Config } from 'drizzle-kit';
import dotenv from 'dotenv';

dotenv.config({
  path: '../../.env.local',
});

export default <Config>{
  dialect: 'postgresql',
  schema: './src/core/infra/db/models',
  out: './migrations',
  dbCredentials: {
    url: process.env.DATABASE_URL,
  },
};

And this is how it looks the one I want to use when running the tests:

import type { Config } from 'drizzle-kit';

export default <Config>{
  dialect: 'sqlite',
  schema: './src/core/infra/db/models/*',
  out: './specs/fixtures/db/migrations',
  dbCredentials: {
    url: './specs/fixtures/db/tests.db',
  },
  verbose: true,
};

This is what I get in the terminal when trying to push the schema to the tests database:

#  npx drizzle-kit push --config=drizzle.tests.config.ts

Pulling schema from database...
Pulling schema from database...

No changes detected

If I run the generate command, this is what I see:

# npx drizzle-kit generate --config=drizzle.tests.config.ts

No schema changes, nothing to migrate 😴

Does this provide enough information?

Is there any guide I can follow for this kind of setup?

[djm181]

I notice you have separate folders for the migrations for each config. Since you are using drizzle-kit push and not generating sql migrations, there should be no need for a separate migrations folder. If you use the same migrations folder then push will compare your snapshot to the running sqlite db and apply the necessary changes directly.