One Schema - Many Connections

[minhchu]

Not sure if this question has been asked before

Here is my situation:

• I want to use 1 schema for both mysql and sqlite connections. mysql is for production and sqlite is for testing.
• Not just about testing, I'm quickly prototyping an app with sqlite then decided to migrate to mysql

Because the current schema only supports neither mysql nor sqlite it seems that multi connections is not possible.

Will dizzle supports it in the near future or anything I can do have both connections working with single schema ?

[DenisBessa]

I think you can do it like this:

const yourSqliteConfig = {} // put your sqlite config here
const yourMyqlConfig = {} // put your mysql config here

const databaseConfig =  process.env.NODE_ENV === "development" ? yourSqliteConfig : yourMyqlConfig

const db = drizzle(databaseConfig);

[minhchu]

Thanks. Maybe my post didn't explain it clearly.

Here is my schema for sqlite:

import { sqliteTable, text, integer } from 'drizzle-orm/sqlite-core';

export const featuresTable = sqliteTable('features', {
  id: integer('id').primaryKey(),
  name: text('name'),
});

and when I want to switch to mysql, I have to rewrite that schema:

import { mysqlTable, text, int } from 'drizzle-orm/mysql-core';

export const featuresTable = mysqlTable('features', {
  id: int('id').primaryKey(),
  name: text('name'),
});

I must write 2 schemas for a single table structure.

[DenisBessa]

Yeah, I got it. The point is that the sqliteTable and mysqlTable only are important when you need to create the tables using drizzle-kit. Once they are created, the query builder will generate an SQL statement, and this statement will be executed on the server you configured.

There is a small chance of some incompatibilities between the commands in mysql and sqlite, but I think it will work in almost 100% of the cases, since these two databases are very similar.