Support Microsoft SQL Server

[jtheisen]

Hi Observable folks,

after a short mail exchange with Toph Tucker, he encouraged me to give some input about the perspective of a Microsoft Shop guy.

I found Observable to be extremely promising in filling the niche between deployed applications and what a Microsoft Shop would probably use Excel for.

I've introduced it in one company that is more open-source leaning and uses MySQL as its primary database of choice. That seems to work great.

What I develop myself in terms of apps uses SQL Server (MSSQL) though, and I really prefer that one to MySQL. So, a connector to SQL Server is quite critical to me. There already is one ticket moping about that on the talk site.

Since I really like relational databases (yes, that happens), I have some strong opinions that may be helpful to you.

Which databases

First, I think SQL Server is really different from the other commercial database offerings (IBM and Oracle) in that it's still the kind of database which self-serving tinkerers use. DB2 and Oracle folks are perhaps less inclined to connect observable than SQL Server folk just because they are farer away from the open-source roots of observable in terms of technical culture.

Proxies

Proxies are really the obvious way to connect to data sources from a browser, as no relational database wants to talk HTTP.

You guys already have a NodeJS proxy for security reasons, but it doesn't support SQL Server.

Even though I have come to like the idea of a pure JavaScript stack, it's lack of connectivity to SQL Server (and the lack of good ORMs like Entity Framework in .NET) is mostly what so far kept me from going to NodeJS for server-side work. So, it's not a surprise to me that a NodeJS proxy wouldn't support my favorite database.

That brings me to the idea that instead of you guys supporting more databases in your existing proxy, you standardize the protocol and allow third-party proxies to be hosted by your users.

Writing a proxy that accepts SQL queries and returns the result is straight-forward, but should probably be done with the tools most commonly used for a particular database, i.e. .NET for SQL Server - otherwise you may get issues.

Here's an example for such an issue: SQL Server can not only return tabular results but also XML, which is really cool since you can design a query to give a tree-like results. However, the way the server protocol returns this XML is through a one-columned table with the rows containing partial chunks of the XML. It's the .NET client-side library that then recombines them to the complete result. When you use NodeJS for that, even if you get it to work for more common things, it's never going to be as good as the tools native to the database.

This is a minor thing as XML queries are rarely used, but these kinds of weird issues are all over the place with databases if you don't use the officially provided client code. And in the case of SQL Server, I think that official client code is limited to C and .NET. There's also Java code that I suppose is good enough (it works well enough for JetBrains' DataGrip and some other clients).

Going forward

So, if I had a minimalistic protocol definition, I would be willing to set up a proof-of-concept .NET proxy for SQL Server if that allows me to use observable with SQL Server. (Use it naturally that is: I could of course always use it without using the database connectivity feature and simply do custom requests against a custom proxy.)

I'm not the guy that would then maintain things going forward, but maybe I can help find the ones that are.

[visnup]

Wondering if tedious is a usable node package for this.

[visnup]

Also, wanted to link to https://hub.docker.com/_/microsoft-mssql-server for later too, which works well for amd64, but doesn't work on arm64.

[CobusT]

Hi @jtheisen would you be interested in being a beta tester for a SQLServer database connector?

[jtheisen]

@CobusT Hell yeah!

[CobusT]

@CobusT Hell yeah!

Please email me at support@observablehq.com so we can work on timing and other details.

[CobusT]

Done! See https://observablehq.com/@observablehq/release-notes#cell-2906