Lucid: connect to azure sql

[Karnith]

Does anyone know the correct way to configure azure sql connection? I've spent a long time trying to find any examples of this and many variations of:

mssql: {
client: 'mssql',
connection: {
host: Env.get('DB_HOST', 'localhost'),
port: Env.get('DB_PORT', ''),
user: Env.get('DB_USER', 'root'),
password: Env.get('DB_PASSWORD', ''),
database: Env.get('DB_DATABASE', 'adonis')
},
options: {
port: Env.get('DB_PORT', '1433'),
database : Env.get('DB_DATABASE', 'adonis'),
encrypt: Env.get('DB_ENCRYPT', true),
//debug: Env.get('DEBUG', true)
},
debug: Env.get('DEBUG', true)
}

trying to run the migrations as a test of connectivity.. It's a new project, mssql 4.1.0 is installed and Adonis 4.0.28. The error I get is:

Users-MacBook-Pro-2:apiprojectadon4 mmarino$ adonis migration:run
[ { sql: 'select object_id from sys.tables where object_id = object_id(?)',
output: [Function: output],
bindings: [ '[adonis_schema]' ] } ]
TypeError: (connection.tx_ || connection).request is not a function
at /Users/mmarino/development/apiprojectAdon4/node_modules/knex/lib/dialects/mssql/index.js:213:48
at Client_MSSQL._query (/Users/mmarino/development/apiprojectAdon4/node_modules/knex/lib/dialects/mssql/index.js:208:12)
at Client_MSSQL.query (/Users/mmarino/development/apiprojectAdon4/node_modules/knex/lib/client.js:211:17)
at Runner. (/Users/mmarino/development/apiprojectAdon4/node_modules/knex/lib/runner.js:149:36)
From previous event:
at Runner.queryArray (/Users/mmarino/development/apiprojectAdon4/node_modules/knex/lib/runner.js:199:40)
at /Users/mmarino/development/apiprojectAdon4/node_modules/knex/lib/runner.js:59:23
at runCallback (timers.js:789:20)
at tryOnImmediate (timers.js:751:5)
From previous event:
at Runner.run (/Users/mmarino/development/apiprojectAdon4/node_modules/knex/lib/runner.js:47:31)
at SchemaBuilder.Target.then (/Users/mmarino/development/apiprojectAdon4/node_modules/knex/lib/interface.js:35:43)
at process._tickCallback (internal/process/next_tick.js:188:7)
at Function.Module.runMain (module.js:678:11)
at startup (bootstrap_node.js:187:16)
at bootstrap_node.js:608:3

[thetutlage]

How about trying this config? https://github.com/tgriesser/knex/issues/1480#issuecomment-231009712

[Karnith]

that's what i was looking at when I created the config in database.js of adonis. I even bypassed the .env file and entered this in directly. Nothing seems to work..

[thetutlage]

Can u connect to the DB without using Adonis or knex? Let's say connecting it from command line?

[Karnith]

yes, I was using sails with sequelize earlier that also uses mssql driver. I've gotten a bit farther, I'm getting:

Knex:warning - Connection Error: ConnectionError: Server requires encryption, set 'encrypt' config option to true.
Knex:warning - Connection Error: ConnectionError: Server requires encryption, set 'encrypt' config option to true.

I've set this in multiple places to try to find out exactly where it's expecting this. in sequalize, I had to add an options section to my model in order to get it to work along with the connection config..

[Karnith]

I finally got it to work. Don't know why it wouldn't work earlier..

```
mssql: {
client: 'mssql',
connection: {
host: Env.get('DB_HOST', 'localhost'), // azure database
user: Env.get('DB_USER', 'root'), // azure user
password: Env.get('DB_PASSWORD', ''), //azure password
database: Env.get('DB_DATABASE', 'adonis'), //azure database
options: {
encrypt: Env.get('DB_ENCRYPT', true) // use this for Azure database encryption
}
}
}