Supports for db clients, such as pg client, mysql client, sqlite client, redis client

[miketang84]

Hi, is there any plan for supports for db clients' handle, such as pg client, mysql client, sqlite client, redis client?

Thank you.

[radu-matei]

Hi, @miketang84! We are in the process of building a Redis client right now, @fibonacci1729 should have a draft PR up soon!

For relational databases we definitely want to have support, we just want to see how that would look like first. Do you have thoughts on how such a client would look like?

[miketang84]

Thanks for your reply.

For now, I'd like to refer the redis client design architecture @fibonacci1729 firstly, and my original thought is that we just provide the basic support for SQL db:

• get the handle of a sql db
• prepare sql statement
• commit a raw sql statement
• process the string rows of results of a sql statement

The attention is that should use sync version or async version of the db connection handle in the wasm context?

[fibonacci1729]

Sounds great @miketang84 ! I should have the PR for the redis client (specifically a publish interface) this afternoon and will reference here.

Would modeling a WIT interface for a db client be a good starting / discussion point?

[fibonacci1729]

@miketang84 Here is the PR for the initial Redis client support. Would love your feedback on anything that can be added or improved here for your use-cases!

https://github.com/fermyon/spin/issues/181

[lann]

SQL databases are tricky to support generically since they each have different APIs/wire protocols. One option might be to build an interface that can wrap ODBC (perhaps modeled after some existing crate like this one). That would still require Spin to run the ODBC drivers, which might be another good use-case for a plugin system like we've started to discuss in #302.

Longer-term I think the WASI sockets proposal would enable database-specific drivers for better performance / features.

[miketang84]

@fibonacci1729 Thank you, I am going to dive in it.

[miketang84]

@lann In my opinion, I think currently we'd better not design an abstract layer for general SQL db, but focus on each concrete sql db client, maybe first is postgresql. If redis client could work in Spin, I presume pg driver could work too.

[jjl]

Hello! Please forgive the essay, I have given this problem a great deal of thought recently because it interests me.

Note: I am a rust developer more than a wasm developer. I do not have a wasm-related job and it's hard to keep up with the state of wasm dev as an interested outsider; please correct any errors!

Much of the API you'd expect from a database adapter is quite amenable. Many things work with integers. There are a couple of things that are slightly more troublesome:

• Strings (interface type?)
• Arrays/Lists (??)
• Connections (reference type?)
  • Should be closed automatically when no longer used.
• Prepared queries:
  • Should return a handle (reference type?)
  • Should not be usable after closing a connection (??)
  • Bind arbitrary parameter values when executed (strings?)
• Result rows could be anything
  • Basically a dependent type and any attempt to pretend otherwise is going to be incredibly language-specific:
    • In rust we might use an iterator over an enum so you can avoid extra allocations etc.
    • In unityped languages you probably just want to inflate everything into e.g. an array of tuples.
  • Probably best to use a buffer and let the user's library parse the results.
  • In which case what are we adding over just giving them a socket and a connection pool with a little database-specific knowledge?

It's also worth considering that there is a lot of waiting for the server to get back to you, for which we'd probably want async support (and honestly i'm not sure what's going on with the async wasm stream at the minute).

Other than this, I'd endorse the approach in the previous comment of doing things on a per-database basis. For example, PostgreSQL supports query pipelining (useful for minimising latency), but not everything does.

I hope some of this was useful!

[lann]

Thanks for the write-up, @jjl ! A few notes:

Note: I am a rust developer more than a wasm developer. I do not have a wasm-related job and it's hard to keep up with the state of wasm dev as an interested outsider; please correct any errors!

The component model design we're working with here is under heavy development; it can be hard for any of us to keep up with everything!

• Strings (interface type?)
• Arrays/Lists (??)

Yep, interface types, and in particular a list of variant or union types for bound parameters.

• Connections (reference type?)
  • Should be closed automatically when no longer used.
• Prepared queries:
  • Should return a handle (reference type?)

If you haven't already, take a look at the resource types proposal, which includes support for reference-counting-based cleanup.

• Result rows could be anything
  • Probably best to use a buffer and let the user's library parse the results.
  • In which case what are we adding over just giving them a socket and a connection pool with a little database-specific knowledge?

Other than this, I'd endorse the approach in the previous comment of doing things on a per-database basis. For example, PostgreSQL supports query pipelining (useful for minimising latency), but not everything does.

I agree that supporting every feature of every database in a single generic interface is effectively impossible, and that database-specific libraries will be necessary. That said, many applications only need a small common set of features and data types, and I think having a common interface with limited functionality would meet a lot of peoples' needs.

[jjl]

Thanks for the response.

Yep, interface types, and in particular a list of variant or union types for bound parameters.

Bound parameters could be of any type supported by postgres or added by a user. I fear this approach might be a bit limiting.

IME most projects have that one weird type they need (different per-project of course!)

If you haven't already, take a look at the resource types proposal, which includes support for reference-counting-based cleanup.

I found this before, but I misunderstood it. With all the talk of copying handles, I hadn't realised they were refcounted!

I agree that supporting every feature of every database in a single generic interface is effectively impossible, and that database-specific libraries will be necessary. That said, many applications only need a small common set of features and data types, and I think having a common interface with limited functionality would meet a lot of peoples' needs.

Okay, that's fair enough. I am probably not in that category :laughing:, so i'll go wander back to my own thing now. Luck!

[ecumene]

PostgREST

In the meantime while WASI Sockets are on the way and proper host component support gets implemented, I made this demo of PostgREST running in Spin so users can insert, select and do most SQL commands you could before.

It's really just a simple HTTP request, but the concept is that this enables you to do things with a Relational Database before that gets properly implemented.

https://github.com/ecumene/spin_pg

[miketang84]

Aha! Also I add the raw support for postgres db, alike the way of outbound redis.

https://github.com/miketang84/spin/tree/outbound-pg/crates/outbound-pg

I must do some clean before I make pull request.

[itowlson]

An initial implementation of Postgres has now landed, and there's also an implementation of MySQL in PR. My suggestion is we close this issue, and open more specific issues for individual databases or for discussion of alternative designs.

[radu-matei]

Opening individual issues to track implementations makes sense to me.

Thank you all for the great suggestions!