MSSQL support

[romanmandryk]

Describe the bug
I want to generate new strapi project or use existing project with the MS SQL database. While not officially supported, it should be easy to switch as underlying bookshelf/knex libs support mssql. But after running the project errors occur and admin UI is not started.

Steps to reproduce the behavior

1. install latest strapi with generator (use mysql or postgres for start) or use existing project.
2. npm install mssql --save //tested with latest 6.2.0
3. edit database.json file and change client from mysql or postgres to "mssql" and enter connection details
4. npm run develop
5. see errors in console

Expected behavior
No errors and UI opens in the browser without issues.

System

• Node.js version: v10 or v12
• NPM version: 6.14.4
• Strapi version: v3.0.0-beta.19.5
• Database: mssql
• Operating system: OSX

There are 2 issues which needed to be fixed to support majority of features.

1. Bootstrap function in plugin "users-permissions" failed

[2020-04-13T23:42:56.444Z] error Bootstrap function in plugin "users-permissions" failed
[2020-04-13T23:42:56.445Z] error TransactionError: Can't rollback transaction. There is a request in progress.
    at Transaction._rollback (/project/node_modules/mssql/lib/base/transaction.js:245:37)
    at Transaction._rollback (/project/node_modules/mssql/lib/tedious/transaction.js:77:11)
    at shared.Promise (/project/node_modules/mssql/lib/base/transaction.js:221:19)
    at new Promise (<anonymous>)
    at Transaction.rollback (/project/node_modules/mssql/lib/base/transaction.js:220:12)
    at Transaction_MSSQL.rollback (/project/node_modules/knex/lib/dialects/mssql/transaction.js:29:21)
    at Function.transactor.rollback (/project/node_modules/knex/lib/transaction.js:278:42)
    at result.then.catch (/project/node_modules/knex/lib/transaction.js:194:35)

This is caused by failing transaction on this line (https://github.com/strapi/strapi/blob/master/packages/strapi-connector-bookshelf/lib/queries.js#L86 - also update and delete)
The easiest fix was to make sure in case of mssql we don't use transactions.

2. Any queries with JOINs failing

RequestError: select distinct top (@p0) [users-permissions_permission].* from [users-permissions_permission] left join [tempdb].[users-permissions_role] as [users-permissions_role_1] on [users-permissions_role_1].[id] = [users-permissions_permission].[role] where [users-permissions_role_1].[id] = @p1 and [users-permissions_permission].[type] = @p2 and [users-permissions_permission].[controller] = @p3 and [users-permissions_permission].[action] = @p4 and [users-permissions_permission].[enabled] = @p5 - Invalid object name 'tempdb.users-permissions_role'

Issue is caused by how strapi-connector-bookshelf generates "join" queries. In the query above you can see that it generated the join with the database name prefix. But it misses the schema name. (usually [dbo] in MSSQL)
Fix was removing the db prefix in case of mssql - e.g. https://github.com/strapi/strapi/blob/master/packages/strapi-connector-bookshelf/lib/buildQuery.js#L97 and other 2 occurences

[derrickmehaffy]

@lauriejim @alexandrebodin correct me if I'm wrong but iirc there was no plans to add support for mssql (or accept pull requests for mssql) simply due to the fact of maintaining code, tests (including the issue of automating travisCI testing)

@romanmandryk there is quite a bit of extra stuff that goes into the Strapi packages for more databases even if Knex/Bookshelf support it. It means that Strapi themselves have to maintain all the code that goes along with that, including automated and manually testing for bug fixes and new features. Not to mention the restrictions of using mssql on Linux (IE Strapi is using TravisCI Linux containers for automated testing)

There is also licensing restrictions in running mssql

[alexandrebodin]

Hi, as @derrickmehaffy just said we are not ready to open the database layer to other database connectors without creating random errors.

We do not have enough people right now to start supporting mssql without it becoming a big burden for us. I hope we can make it easy to extend / create custom connectors in the future. For now if you really need to use mssql you might want to fork the bookshelf connector,, do the required updates and use it instead of the official one.

Hope this answers your question.

[derrickmehaffy]

@Aurelsicoko @JabStrapi If you guys want to add this to the product board as "maybe"?

[derrickmehaffy]

Closing as we don't currently plan to support other databases at this time and will be making steps easier to allow for community connectors.

[lauriejim]

This issue has been mentioned on Strapi Community. There might be relevant details there:

https://forum.strapi.io/t/how-to-used-stored-procedure-of-sqlserver-in-strapi/647/3

[derrickmehaffy]

This issue has been mentioned on Strapi Community Forum. There might be relevant details there:

https://forum.strapi.io/t/does-strapi-support-mssql/2100/2