Feature sequelize sqlite3

[egisz]

Boom! Lots of improvements in both backend and front end. Where to start ? :)

Backend:

• upgraded all current models to sequelize ORM models. This will allow to easily switch between database providers, it supports mysql, mariadb, sequelize, postgresql, mssql. But most of all it maps models to tables and lets avoid writing pure SQL. NOTE: config.json file must be updated in order to upgrade. See notes bellow. NOTE2: all tables will be deleted during migration, so make copy in case you need old data
• DB migrations - sequelize automatically creates new tables based on model definition, so we don't need bot.sql file. Also future DB structure can be organized using migrations files.
• Logging now uses winston-transport-sequelize - no more custom code needed, just configuration.
• Server side data processing for frontend tables

Frontend

• moved (almost) all public css and js included libraries as npm packadges and updated layout.twig to source locally. This will help stay up to date without updating twig pages.
• updated pages to use datatables.net library. Logs page is implemented server side, it has pagination, filtering, sorting, etc. Only visible data is loaded to client. Also updated Trades, Pairs, and partly Orders. More to come.

Upgrading notes

add database objects to conf.json file:

    "development": {
        "storage": "bot.db",
        "dialect": "sqlite"
    },
    "test": {
        "storage": "bot.db",
        "dialect": "sqlite"
    },
    "production": {
        "storage": "bot.db",
        "dialect": "sqlite"
    }

[Haehnchen]

just a quick hint about "sequelize / orm". the main reason why i not took it was performance. In any case its helpful for the database schema and so on.

I am running this mainly on ~100 pairs with 3 time periods. So there is a lot of INSERT happing inside the database. Eg on every minute on most exchange there are a lot of candle informations. Binance provides this candles mainly every seconds, also for not closed one.

This is also for strategies which provides also much of reading on many pairs.

I also needed to took the optimized sqlite3 database package and optimized PRAGMA settings during connecting.

So how ORM is performing here?

[egisz]

First of all, I apologize, I had to raise a ticket and discuss before starting such update. Now I have to take risk that all the work can be thrown to trash bin :) I started from small change and then out of curiosity dig deeper and deeper. From other side I did not want to show semi working code and tried to put everything together before showing it.

Regarding performance, I understand your concern. I now checked several sources, and for sure there is some performance hit, mostly on complex SELECT statements.

But, for candlestick inserts, I used bulkCreate method, so there I believe performance hit is minimal, see resulting query:

Executing (default): INSERT INTO `candlesticks` (`exchange`,`symbol`,`period`,`time`,`open`,`high`,`low`,`close`,`volume`) VALUES
('bitfinex','BTCUSD','1h',1590750000,'9370.5','9416.7','9353.8','9412.4','126.93039908'),
('bitfinex','BTCUSD','1h',1590746400,'9464.81665015','9471.8','9363.7','9370.5','325.68438518'),
('bitfinex','BTCUSD','1h',1590742800,'9459.53903514','9491.1','9434.5','9464.81665015','418.85057192'),
...

I also see than you started works on migrating to typescript. And here sequelize is not perfect solution.

Here typeORM shines, and it's slowly getting more and more popular compared to sequelize.

So let me know what you think.

[AlloryDante]

@egisz Thank you for this update, I had downloaded and working with it right now.

I have some ideeas for the project if you don't mind to suggest.I really see that you are consistent and you make good updates, so it may worth to take a look:

I have the following ideeas for the backtesting: -Show the total profit -Show the nr of trades that the bot made(total /per day) -Show the winrate of the strategy

For the buy and sell part: -I know that you can specify the amount from the ballance that the bot should use/ Coin Pair.A nice thing that the bot should have is the posibility to specify an amount % inside the strategy that the bot should use to trade from the maximum amount per coin. Letme give an example: Consider that we are trading those coins: pair.trade = { capital: 1 }; for BTC ETH pair.trade = { capital: 2 }; for BTC XMR

The strategy used for trading should look like this: return SignalResult.createSignal('long', 'debug', 75); Basically the bot should be able to buy ETH using 75% from the specified capital of the pairs.

Another crucial feature would be to pass a parameter inside the bot settings that would make the bot consider the winning trades in the capital ballance.For example if the bot won a trade, it will update somehow the maximum capital/pair.Without this feature, we have to manually close and update the instances.js to use the maximum balance. I also think that instead specifing a fix ammount of capital, we should also use % like pair.trade = { capital: 25,type: procent};(this will allow trading with 25% from the account ballance) pair.trade = { capital: 1,type: amount};

I had discovered this project yesterday.I still have to learn to use it, but I really hope that I had inspired the future developpment of it with some little suggestions. I really don't want to become a dead project.......

[egisz]

Hi,

Thanks @cezarlacatus for testing and supporting my idea. If you find bugs, let me know. I would like to hear @Haehnchen feedback and to discuss plans regarding ORM and typescript. I'm open to switch to other ORM, it should not bee too complex as we don't have many models/tables.

@cezarlacatus, I suggest to open separate issues for each your ideas. This way we can vote and discuss them separratly. I have my point regarding your ideas, and will happily share my opinion once you create issues.

[egisz]

Tests are green now, @Haehnchen, what's your opinion?

[jorisw]

@Haehnchen Any plans to approve this? It's six months old and the feature is kind of a blocker for production use.

@egisz Bunch of conflicts with master since.

[Haehnchen]

no plans to improve from my side

• orm is a too big performance issue: that was the my reason when i started this projects; all other bots using all complex usages like nosql, mongodb, ... which are not working on lot of pairs at the same time
• auto database schema would be nice, but not needed so from my side; no critcal data inside; database recreated helps for now if there is really at some point a database change needed

[jorisw]

Thanks for your elaboration. Are you saying performance is good with SQLite ?

[Haehnchen]

yes SQLite is every good on my side with "better-sqlite3" module and some connection parameter improvements. ~1000 queries per seconds are no problem on a small vhost with ~500 watching exchange pairs, i guess any other "really database" would have some more issues handle this traffic.

[jorisw]

I understand. FWIW, the reason I am looking for an ORM implementation, is so that I can centralize activities of different bot sessions on different hosts, and manage them.

[scalebig]

@Haehnchen would you consider a lightweight/thin DB adapter pattern? interface so users can plugin whatever storage tech? that wouldn't impact performance but still provide flexibility. Default adapter is the SqlLite code that you already have.. I would put time into this refactor.