How to used stored procedure of (SQLSERVER) in strapi?

DMehaffy · October 27, 2020, 4:41pm

Yes you are correct, we do not currently nor plan to support MSSQL

github.com/strapi/strapi

MSSQL support

opened 01:18PM - 14 Apr 20 UTC

closed 05:58PM - 22 Oct 20 UTC

romanmandryk

type: feature request severity: low

**Describe the bug** I want to generate new strapi project or use existing proj…ect 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