PostgreSQL support

[coratgerl]

Add PostgreSQL as a valid Database provider. Copy all tests of MongoAdapter and replace with postgre. All tests need to pass without change to be sure of global support.

[stawuah]

Hi @coratgerl,

I’d like to take on the challenge of adding PostgreSQL support as a database provider to Wabe. I’ve already mirrored the MongoDB adapter and created a PostgreSQL version. My goal is to ensure global compatibility by copying the MongoDB tests and adapting them for PostgreSQL.

Before moving forward, I have a few questions:

1. Adapter Design: Is there a preferred pattern or interface you’d like the PostgreSQL adapter to follow, similar to the MongoAdapter? Should I strictly adhere to the existing DatabaseAdapter interface, or can I introduce necessary deviations (e.g., for handling relations, transactions, or advanced SQL features)?

2. Testing Approach: Should I duplicate all MongoDB tests for PostgreSQL to verify equivalent functionality, or do you recommend a different approach to ensure robust testing across both adapters?

3. ORM vs Raw SQL: For the PostgreSQL implementation, would you prefer using raw SQL for performance and control, or would you consider an ORM like Prisma, Drizzle or TypeORM to abstract away database operations and ease future maintainability?

4. Edge Cases: Are there any edge cases or existing compatibility concerns between MongoDB and PostgreSQL that I should watch out for while adapting the adapter and tests?

I want to ensure my contributions align with Wabe’s vision and architecture, so I’d appreciate your insights before I proceed further.

Looking forward to hearing your thoughts

[coratgerl]

Hi @stawuah,

First of all, thank you very much for your interest in the Wabe project; I'm delighted to see that it is appreciated.

1. Regarding the adapter, you should follow the same adapter as Mongo, the database adapter available here: https://github.com/palixir/wabe/blob/main/packages/wabe/src/database/adapters/adaptersInterface.ts#L175 . For now, we are not going to add any additional fields, although this might evolve in the future. The goal is to maintain compatibility between Mongo and Postgres for links with the GraphQL API and the future REST API.

2. Copying the Mongo adapter tests while just changing the adapter seems like the right approach, as it ensures full compatibility, especially for certain tests that cover specific cases. Also ensure using a postgres database in memory to avoid launch docker during test, like with mongodb-memory-server (this will reduce loop feedback during test and improve DX). You can create new package in the monorepo to launch a postgres database. I can propose to use https://github.com/oguimbal/pg-mem :). For the new package, you can create a separate PR if you want (I will review it faster :)).

3. For Wabe, we pay special attention to performance. It would be preferable to use raw SQL. But you can use pg (https://www.npmjs.com/package/pg) package to interact with postgres database. pg-mem proposes an adapter to interact with pg (https://github.com/oguimbal/pg-mem/wiki/Libraries-adapters). Special attention will be given to queries and their optimization; we can look into this at a later stage. :)

4. I don’t see any particular cases for now since we are supporting relatively simple cases, and not yet transactions or more complex scenarios.

Concerning the Wabe philosophy, the development of Wabe places great importance on code quality and testing.

Thank you again for your interest, and if you have any questions or anything else, feel free to open the PR and ask your questions directly there. I'd be happy to help you. :)

[coratgerl]

Need to investigate https://pglite.dev/

[stawuah]

PGlite is a WASM Postgres build packaged into a TypeScript/JavaScript client library, that enables you to run Postgres in the browser, Node.js and Bun, with no need to install any other dependencies.

this means no need for pgas in the package because, PGlite gives full Postgres if i am correct ?

[coratgerl]

PgLite allow you to run an in-memory database in local, but it doesn't allow to connect to remote database without ElectricSQL. For production use with hosted database we will need pg. It replace pg-mem, just for local, but pg can't use a PgLite database so I am not sure if we need to use it. We need something like that in wabe:

 database: {
      type: DatabaseEnum.Postgres,
      connection : process.env.NODE === 'production' ? {... connection database} : { ... local in memory database},
      name: "WabeApp",
}

[stawuah]

@coratgerl ,

i would like you to run In dev wabe-postgres-support to test is the in-memory is working before i open a pr on my fork , having issues with my db 🙏

cd wabe-postgres-launcher and bun test:wabe-pg-mem