Let's imagine that we have the following SQL Table, from https://www.w3schools.com/postgresql/postgresql_create_demodatabase.php

CREATE TABLE customers (
  customer_id SERIAL NOT NULL PRIMARY KEY,
  customer_name VARCHAR(255),
  contact_name VARCHAR(255),
  address VARCHAR(255),
  city VARCHAR(255),
  postal_code VARCHAR(255),
  country VARCHAR(255)
);

This will require to call the following APIs:

First, let's register a new Entity:

registerEntity("customers", ["customer_id", "customer_name", "contact_name", "address", "city", "postal_code", "country" ]) -> this is to keep it simple without any type information.

Once the entity is registered we can use findAll

findAll("customers")` -> returns an array of columns, or if we can map this to a struct that would be quite awesome.

This results in a simple query:

select "customer_id", "customer_name", "contact_name", "address", "city", "postal_code",  "country" from customers

A similar query will be created to filter by ID.

findById("customers", id)

This results in a simple query: select "customer_id", "customer_name", "contact_name", "address", "city", "postal_code", "country" from customers where customer_id= ?

The mechanism will be the same for delete functions.

If we want to save a new customer:

save("customers", &customer) 

This results in the following query:

INSERT INTO customers (customer_name, contact_name, address, city, postal_code, country)
VALUES (  <values from &customer fields> );

To keep it simple, we should return the errors generated from the specific database to the user for troubleshooting. This API shouldn't do any advanced type mapping.

The implementation of this proposal will require each binding to have a reduced collection of parameterizable SQL statements that will be used to perform the operations described in the APIs, all following the same conventions.

For now I've taken inspiration in the CrudRepository interface from Spring Data -> https://github.com/spring-projects/spring-data-commons/blob/main/src/main/java/org/springframework/data/repository/CrudRepository.java but I will be happy to align with dotNet ORM framework semanticts too @marcduiker can you. help me out with a reference here?

For now I've taken inspiration in the CrudRepository interface from Spring Data -> https://github.com/spring-projects/spring-data-commons/blob/main/src/main/java/org/springframework/data/repository/CrudRepository.java but I will be happy to align with dotNet ORM framework semanticts too @marcduiker can you. help me out with a reference here?

The most well-known ORM in .NET is Entity Framework. It uses a DbContext and DbSets and has similar methods as you mentioned.

Can you explain the scope a bit more @salaboy? Is this proposal to extend the current Bindings API or to only extend the individual Dapr SDKs, which in turn would translate the 'repository' methods to use the existing Bindings API?

The idea is to keep it super simple, for now the only change proposed here is to add more "actions" to existing SQL bindings.

This allows us to have a set of well-defined behaviors for SQL bindings beyond "query" and "exec". The new actions suggested in the issue description align with the idea of Entities, meaning 1 entity <-> 1 table. So for example: findAll("customers") is an action that acts on the customers table, this also introduce the requirement or registering (or creating) tables that has columns with different datatypes, hence I've introduced the registerType that now I can confidently rename to registerEntity.

To clarify, this doesn't require any changes in the SDK, the proposal is now just adding new "actions" to the existing bindings. This is an opt-in feature from the perspective of the user. This is also a backward compatible change as actions such as "query" and "exec" will remain unmodified.

@marcduiker I've added a PR with the in-progress PoC