P
Prisma•4mo ago
XenTobias

Using Prisma with Both SQLite and PostgreSQL

Hi community ✌️ is there a way to support both SQLite and PostgreSQL with Prisma at the same time? In other words, the user would have the option to use either SQLite or PostgreSQL, with Prisma acting as an abstraction layer. Specifically, I want to implement the following: 
const prisma = (() => {
  switch (process.env.DATABASE_PROVIDER) {
    case 'postgresql':
      return new PrismaClientPostgresql({
        adapter: new PrismaPg({
          connectionString: process.env.DATABASE_URL,
        }),
      });

    case 'sqlite':
      return new PrismaClientSqlite({
        adapter: new PrismaBetterSQLite3({
          url: process.env.DATABASE_URL,
        }),
      });

    default:
      throw new Error(
        `Unsupported DATABASE_PROVIDER: ${process.env.DATABASE_PROVIDER}`
      );
  }
})();

// Works fine
await prisma.user.create({ data: { name: 'test', email: '' } });

// Type error:
/*
This expression is not callable.
  Each member of the union type '(<T extends UserFindUniqueArgs>(args: SelectSubset<T, UserFindUniqueArgs<DefaultArgs>>) => Prisma__UserClient<GetFindResult<$UserPayload<DefaultArgs>, T, { ...; }> | null, null, DefaultArgs, { ...; }>) | (<T extends UserFindUniqueArgs>(args: SelectSubset<...>) => Prisma__UserClient<...>)' has signatures, but none of those signatures are compatible with each other.ts(2349)*/
await prisma.user.findUnique({});
const prisma = (() => {
  switch (process.env.DATABASE_PROVIDER) {
    case 'postgresql':
      return new PrismaClientPostgresql({
        adapter: new PrismaPg({
          connectionString: process.env.DATABASE_URL,
        }),
      });

    case 'sqlite':
      return new PrismaClientSqlite({
        adapter: new PrismaBetterSQLite3({
          url: process.env.DATABASE_URL,
        }),
      });

    default:
      throw new Error(
        `Unsupported DATABASE_PROVIDER: ${process.env.DATABASE_PROVIDER}`
      );
  }
})();

// Works fine
await prisma.user.create({ data: { name: 'test', email: '' } });

// Type error:
/*
This expression is not callable.
  Each member of the union type '(<T extends UserFindUniqueArgs>(args: SelectSubset<T, UserFindUniqueArgs<DefaultArgs>>) => Prisma__UserClient<GetFindResult<$UserPayload<DefaultArgs>, T, { ...; }> | null, null, DefaultArgs, { ...; }>) | (<T extends UserFindUniqueArgs>(args: SelectSubset<...>) => Prisma__UserClient<...>)' has signatures, but none of those signatures are compatible with each other.ts(2349)*/
await prisma.user.findUnique({});
However, the problem here is that prisma.user.findUnique() does not provide type safety for the arguments, presumably because the types differ between SQLite and PostgreSQL. Is there a workaround for this? Best regards, Tobi
9 Replies
Prisma AI Help
Prisma AI Help•4mo ago
You chose to debug with a human. They'll tinker with your query soon. If you get curious meanwhile, hop into #ask-ai for a quick spin!
jonfanz
jonfanz•4mo ago
There’s really not a workaround for this. Having a different db engine depending on environment is something that we want you to avoid 😅 Is there a reason for the SQLite/PSQL split in your case? Ease of development or similar?
XenTobias
XenTobiasOP•4mo ago
Ok, that's a pity. On the one hand, it's the simplicity of development (in-memory), but primarily it's easier for the user of my application to start with an SQLite database without having to provide a database right away.
jonfanz
jonfanz•4mo ago
That makes sense! Have you looked into PGLite at all? I think that will give you the ease of a in-mem db during dev while still sticking to Postgres.
XenTobias
XenTobiasOP•4mo ago
Oh wow, how cool! No, I didn’t know about that before. I’ll definitely give it a try today. Is it also supported by Prisma?
jonfanz
jonfanz•4mo ago
Yup easy to integrate. We also have a recently released prisma dev command that sets it up. Check out #announcements for more info on that. But you don’t have to use that command. It’s just a convenience.
XenTobias
XenTobiasOP•4mo ago
Thank you so much! You can't imagine how much that brightened my day. I have one more small question, although it deviates from the initial one. I'm planning to compile Prisma into a single binary using Bun (which has worked so far, with the modification of __internal.configOverride(...)). I'd also like to embed the migration files and then automatically apply them to the database at binary startup if they haven't been applied yet (I still need to implement the logic). What do you think about that? Is there anything I might have overlooked?
jonfanz
jonfanz•4mo ago
I think that sounds reasonable, though I would challenge the need to package migration files in the first place and instead use the db push functionality to update the local db to the latest schema. But that might not be possible in your situation which led to the migration approach
XenTobias
XenTobiasOP•4mo ago
Unfortunately, that's not possible because the binary is being run on a different device, so prisma and the migrations folder aren't available. Thank you so much for your help 🙂

Did you find this page helpful?