Is it possible to use MS SQL?

bowdoinj · August 25, 2022, 3:08pm

Is it possible to use MS SQL in Observable cells? Is there an example showing how to connect?

tomlarkworthy · August 25, 2022, 6:08pm

mootari · August 25, 2022, 6:13pm

@bowdoinj MS SQL is not currently supported. You can subscribe to the following issue to be notified when it lands:

github.com/observablehq/feedback

Support Microsoft SQL Server

opened 01:15PM - 29 Dec 21 UTC

jtheisen

Feature Request Data Access

Hi Observable folks, after a short mail exchange with Toph Tucker, he encoura…ged 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](https://talk.observablehq.com/t/poor-database-client-support/3107) 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.

Topic		Replies	Views
Do I need to proxy connexion from local notebooks to local DB? Community Help	2	435	May 7, 2020
Export + SQL Cells Site Feedback	3	576	November 14, 2022
Backend data stores? Site Feedback	2	1019	February 9, 2018
SQL cells broken? Community Help	7	246	July 12, 2022
DatabaseClient not working Community Help	6	947	February 9, 2021

Is it possible to use MS SQL?

Related Topics