SQL store configuration beginners question - DataSource could not be resolved

[svhtw]

I'm trying to add sql persistence to my connector for assets, policies, contracts and transfer processes. I've set up postgres databases following the schemas discribed in the corresponding sql store extension documentations. My asset configuration looks like:

edc.datasource.asset.name=asset
edc.datasource.asset.url=jdbc:postgresql://myIP:5432/edc_asset
edc.datasource.asset.user=user
edc.datasource.asset.password=pass

Unfortunatly I'm getting the following exception when I try to add an asset:
org.eclipse.edc.spi.persistence.EdcPersistenceException: java.lang.NullPointerException: DataSource asset could not be resolved
The same goes for policies etc.
I've also checked that the databases are accessable using standard java code.
What am I missing? What would be the correct configuration?

[svhtw]

Fixed the problem by adding the following dependencies:

• org.eclipse.edc:sql-pool-apache-commons
• org.eclipse.edc:transaction-local
• org.eclipse.edc:transaction-datasource-spi
  in addition to
• org.eclipse.edc:control-plane-sql
• org.postgresql:postgresql

[stratosEft]

Hi @svhtw, I run on issues when using policy-definition-store-sql and transfer-process-store-sql. Other sql modules (i.e assets, contract definition etc..) worked fine. I suspect I may initialise those modules the wrong way. Could you please share your setup, with the exact locations of the build configs? Thank you in advance

[Julian-Marco]

Thank you, that definitely saved me a lot of time! After a little bit of testing I found that while you do indeed need org.eclipse.edc:sql-pool-apache-commons and org.postgresql:postgresql as dependencies, the rest doesn't seem to be necessary. So saving an Asset in a PostgreSQL works perfectly fine when combining the aforementioned depdencies with the org.eclipse.edc.asset-index-sql dependency. At least that's true for our setup, maybe you could tell whether that works on your side as well or not. Furthermore, it probably shouldn't be "/edc_asset" at the end of the Url but simply "/".

Besides that, I'm wondering what's the correct configuration if you want to use several different sql schemas, say if you want to save the Policy as well for example. Then you'd have to use this module https://github.com/eclipse-edc/Connector/tree/main/extensions/control-plane/store/sql/policy-definition-store-sql too, but then the configuration would have to look like this:

edc.datasource.asset.name=asset
edc.datasource.asset.user=postgres
edc.datasource.asset.password=postgres
edc.datasource.asset.url=jdbc:postgresql://hostname:5438/

edc.datasource.policy.name=policy
edc.datasource.policy.user=postgres
edc.datasource.policy.password=postgres
edc.datasource.policy.url=jdbc:postgresql://hostname:5438/

That does seem to be a little bit redundant. Does anyone have an idea why the modules are designed like this? At least there should be the possibility to set a database url only once if you want to use it for all of the available schemas.

[Julian-Marco]

Upon further investigation, using org.eclipse.edc:transaction-local and transaction-datasource-spi seems to become necessary once you also want to use the contract-negotiation-store-sql and the transfer-process-store-sql modules since the EDC throws some errors once you start a negotiation otherwise. Saving the assets, policies and contract definitions via their respective sql modules seems to work without it though.

[matthiasdg]

In case anyone else ends up here using the search: there is now https://github.com/eclipse-edc/Connector/blob/39089d12268be753667f56c68028d428bc26a8a9/docs/developer/decision-records/2023-08-01-default-datasource/README.md, so if you don't specify separate datasource.xxx.names, you can just use edc.datasource.default.user/url/password

[avlopez01]

I’m trying to do it but I’m having quite a few errors. When I initialize the connector, it gives me errors that it cannot find tables "edc_transfer_process", "edc_contract_negotiation", that are indeed in the database. Later, when I try to insert an asset, policy,… I receive this message:curl -d @transfer/transfer-01-negotiation/resources/create-asset.json -H 'content-type: application/json' http://localhost:19193/management/v3/assets -s | jq
parse error: Invalid numeric literal at line 2, column 0
whitout the jq:

<title>Error 500 Internal Server Error</title>

HTTP ERROR 500 Internal Server Error

URI:	/management/v3/assets
STATUS:	500
MESSAGE:	Internal Server Error
SERVLET:	EDC-management

This is the configuration I’m doing in the build.gradle.kts and this is what I’m doing in provider-configuration.properties.
gradle:
implementation("org.eclipse.edc:sql-pool-apache-commons:0.6.0")
implementation("org.eclipse.edc:transaction-local:0.6.0")
implementation("org.eclipse.edc:transaction-datasource-spi:0.6.0")
implementation("org.eclipse.edc:control-plane-sql:0.6.0")
implementation("org.postgresql:postgresql:42.7.3")
provider-configuration.properties:

edc.datasource.default.user=postgres
edc.datasource.default.password=pass
edc.datasource.default.url=jdbc:postgresql://Myip:5555/

edc.datasource.asset.name=asset
edc.datasource.asset.url=jdbc:postgresql://Myip:5555/
edc.datasource.asset.user=postgres
edc.datasource.asset.password=pass

edc.datasource.contractdefinition.name=contractdefinition
edc.datasource.contractdefinition.url=jdbc:postgresql://Myip:5555/
edc.datasource.contractdefinition.user=postgres
edc.datasource.contractdefinition.password=pass

edc.datasource.transferprocess.name=transferprocess
edc.datasource.transferprocess.url=jdbc:postgresql://Myip:5555/
edc.datasource.transferprocess.user=postgres
edc.datasource.transferprocess.password=pass

edc.datasource.policy.name=policy
edc.datasource.policy.user=postgres
edc.datasource.policy.password=pass
edc.datasource.policy.url=jdbc:postgresql://Myip:5555/