MySql or MongoDb support

[ylahav]

I try both database and it does not work.

In order to connect to these databases I add to the 'server.js' file the following:

if (process.env.TYPEORM_CONNECTION === "mysql") {
connectionSettings.username = process.env.TYPEORM_USERNAME;
connectionSettings.password = process.env.TYPEORM_PASSWORD;
connectionSettings.port = process.env.TYPEORM_PORT;
connectionSettings.database = process.env.TYPEORM_DATABASE;
}
if (process.env.TYPEORM_CONNECTION === "mongodb") {
connectionSettings.port = process.env.TYPEORM_PORT;
connectionSettings.database = process.env.TYPEORM_DATABASE;
}

Than I could connect to the databases and some data was added BUT for each of them I got an error about creating data...
For example - mongoDb case - try to register - http://localhost:3018/register
and got:

TypeORMError: Query Builder is not supported by MongoDB.
at new TypeORMError (E:\projects\sonicjs\node_modules\typeorm\error\TypeORMError.js:9:28)
at MongoRepository.createQueryBuilder (E:\projects\sonicjs\node_modules\typeorm\repository\MongoRepository.js:30:15)
at TypeormStore.createQueryBuilder (E:\projects\sonicjs\node_modules\connect-typeorm\out\app\TypeormStore\TypeormStore.js:180:32)
at TypeormStore.get (E:\projects\sonicjs\node_modules\connect-typeorm\out\app\TypeormStore\TypeormStore.js:30:18)
at session (E:\projects\sonicjs\node_modules\express-session\index.js:485:11)
at Layer.handle [as handle_request] (E:\projects\sonicjs\node_modules\express\lib\router\layer.js:95:5)
at trim_prefix (E:\projects\sonicjs\node_modules\express\lib\router\index.js:317:13)
at E:\projects\sonicjs\node_modules\express\lib\router\index.js:284:7
at Function.process_params (E:\projects\sonicjs\node_modules\express\lib\router\index.js:335:12)
at next (E:\projects\sonicjs\node_modules\express\lib\router\index.js:275:10)
at jsonParser (E:\projects\sonicjs\node_modules\body-parser\lib\types\json.js:110:7)
at Layer.handle [as handle_request] (E:\projects\sonicjs\node_modules\express\lib\router\layer.js:95:5)
at trim_prefix (E:\projects\sonicjs\node_modules\express\lib\router\index.js:317:13)
at E:\projects\sonicjs\node_modules\express\lib\router\index.js:284:7
at Function.process_params (E:\projects\sonicjs\node_modules\express\lib\router\index.js:335:12)
at next (E:\projects\sonicjs\node_modules\express\lib\router\index.js:275:10)

[lane711]

Sorry for the troubles that you experienced in DB setup. We are currently working on a better setup wizard that will streamline the initial DB setup. The db install is triggered when the app is started. If it is successful, you should see a few tables in the database and several records in the content table.

[lane711]

After further investigation, It looks like fully supporting Mongo is going to require some additional effort due to the fact that TypeORM unfortunately only provides "basic support".

MySQL should work without any major issues. What error did you get with MySQL?

FWIW I am currently running a few production SonicJs sites with Postgres if you are open to using that.

I'll add fixing the Mongo support to the backlog

[anjandc]

I am getting the following issue

(node:9280) UnhandledPromiseRejectionWarning: QueryFailedError: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1
    at QueryFailedError.TypeORMError [as constructor] 

[lane711]

Thanks for the details @anjandc , I will look into this asap and get back to you

[lane711]

MySQL has now been fully tested with the latest code from master. Set this in your .env:

# MySQL
TYPEORM_CONNECTION=mysql
TYPEORM_HOST=127.0.0.1
TYPEORM_USERNAME=root
TYPEORM_PASSWORD=root 
TYPEORM_DATABASE=sonicjs
TYPEORM_PORT=3306
TYPEORM_SYNCHRONIZE=true
TYPEORM_LOGGING=error

There is also a new backup/restore feature availale in the admin so you can backup the data from sqlite and then switch your db setup via the .env file, then import (restore) into your MySQL database.

[cfmaniac]

I recieve this error: Running on Node 16
node:internal/process/promises:246
triggerUncaughtException(err, true /* fromPromise */);
^

Error: connect ECONNREFUSED 127.0.0.1:3006
at TCPConnectWrap.afterConnect [as oncomplete] (node:net:1161:16)
--------------------
at Protocol._enqueue (D:\Sites\platforms\nodejs\replicator\node_modules\mysql\lib\protocol\Protocol.js:144:48)
at Protocol.handshake (D:\Sites\platforms\nodejs\replicator\node_modules\mysql\lib\protocol\Protocol.js:51:23)
at PoolConnection.connect (D:\Sites\platforms\nodejs\replicator\node_modules\mysql\lib\Connection.js:116:18)
at Pool.getConnection (D:\Sites\platforms\nodejs\replicator\node_modules\mysql\lib\Pool.js:48:16)
at D:\Sites\platforms\nodejs\replicator\node_modules\typeorm\driver\mysql\MysqlDriver.js:863:18
at new Promise ()
at MysqlDriver.createPool (D:\Sites\platforms\nodejs\replicator\node_modules\typeorm\driver\mysql\MysqlDriver.js:860:16)
at MysqlDriver. (D:\Sites\platforms\nodejs\replicator\node_modules\typeorm\driver\mysql\MysqlDriver.js:288:51)
at step (D:\Sites\platforms\nodejs\replicator\node_modules\tslib\tslib.js:143:27)
at Object.next (D:\Sites\platforms\nodejs\replicator\node_modules\tslib\tslib.js:124:57)
at D:\Sites\platforms\nodejs\replicator\node_modules\tslib\tslib.js:117:75
at new Promise ()
at __awaiter (D:\Sites\platforms\nodejs\replicator\node_modules\tslib\tslib.js:113:16)
at MysqlDriver.connect (D:\Sites\platforms\nodejs\replicator\node_modules\typeorm\driver\mysql\MysqlDriver.js:273:38)
at Connection. (D:\Sites\platforms\nodejs\replicator\node_modules\typeorm\connection\Connection.js:110:58)
at step (D:\Sites\platforms\nodejs\replicator\node_modules\tslib\tslib.js:143:27) {
errno: -4078,
code: 'ECONNREFUSED',
syscall: 'connect',
address: '127.0.0.1',
port: 3006,
fatal: true
}

[lane711]

Are you able to connect to that server/db from another client, like mysql workbench or similar?

SonicJs uses typeorm to connect to MySQL

I did have a similar issue when I first tried to connect to mysql. This might help if its an authentication issues:
https://stackoverflow.com/questions/44946270/er-not-supported-auth-mode-mysql-server

[cfmaniac]

I am able to connect via almost anyhting- I've even had other Node Apps with TypeORM connect with no issue.

[lane711]

I was able to reproduce the same error. Oddly it only happened the first time that I started the application. On all subsequent start ups, it worked as expected. Anyway, I will continue to look into this...