Support multiple databases for querying

[arikfr]

Support multiple databases for querying

[adamghill]

I took a crack at querying MS Sql Server: https://github.com/themotleyfool/redash/tree/sql_server. I kept it pretty separate from the redshift query code, but I am still not sure if you want to merge it in. I can provide a PR if you are interested. It requires installing freetds and pymssql and a new DB_TYPE setting, but the code is pretty straight-forward. Unfortunately, there isn't an easy way to call Sql Server asynchronously 😦.

[arikfr]

It's great to see others find this project useful enough, to write code extending it :-)

I took a crack at querying MS Sql Server: https://github.com/themotleyfool/redash/tree/sql_server. I kept it pretty separate from the redshift query code, but I am still not sure if you want to merge it in. I can provide a PR if you are interested. It requires installing freetds and pymssql and a new DB_TYPE setting, but the code is pretty straight-forward.

My plan of adding support for additional databases was by just moving to using SQLAlchemy. But I'm not sure whether SQLAlchemy supports MSSql, and regardless I've realized that maybe it will be better to have the different databases as separate packages you install (i.e. redash_mssql). This has the benefit that users who don't need a certain database plugin, don't need to install its dependencies.

So DB_TYPE will be the name of a module that redash will import. The different modules will just have to export the query_runner function (or similar class if we make the logic more complicated).

What do you think?

Unfortunately, there isn't an easy way to call Sql Server asynchronously 😦.

Actually the only reason I changed the Redshift query runner to be async, was that psycopg2 ignored the INT signal otherwise. Although I do have plans for switching to async query runners (so we won't need to use threads to execute queries), it's currently postponed as I didn't find a good non-blocking IO library that will have support for most databases.

[adamghill]

plan of adding support for additional databases was by just moving to using SQLAlchemy

Well, if you want to move to SQLAlchemy, it does look like MS Sql Server is supported: http://docs.sqlalchemy.org/en/rel_0_9/dialects/mssql.html. I can also easily make a separate package. I can make my code work either way pretty easily, I think.

DB_TYPE will be the name of a module that redash will import

I was actually just thinking about adding a new model named Database (with fields: connection string, type) which is 1-to-many to Query. This would enable me to to query different databases (Postgres, Sql Server, MySql, etc) specifically for a particular query. I'm weary of over-complicating your project unnecessarily, though, if you don't think it's a valid use-case ;).

[arikfr]

I was actually just thinking about adding a new model named Database (with fields: connection string, type) which is 1-to-many to Query. This would enable me to to query different databases (Postgres, Sql Server, MySql, etc) specifically for a particular query. I'm weary of over-complicating your project unnecessarily, though, if you don't think it's a valid use-case ;).

Actually this is part of what this ticket is about :-) So if you want to take a stab at it, you're more than welcome!

Currently the models are using Django models, and I was thinking of rewriting them as SQLAlchemy models and making use of them in manager.py instead of directly querying the db. But you can implement this with Django models, and worst case we will rewrite this later.

[adamghill]

Sweet, I'll take a look. I'll probably stick with the Django models for now to make the PR a little more managable.

[arikfr]

@adamghill if you like, send me your email address (to arik at everything.me), so if I plan any major changes I will let you know.

[arikfr]

Resolved.