There are currently three ways to write custom code:
Extending the engine with C++ is the most versatile and efficient. With C++ you have full access to the entire engine, giving you all the power you need. Any serious project will have to use C++ code for some parts. For things like game logic you can start with script code instead, and migrate critical parts to C++ on demand. Extending the renderer and access to third party integrations is mostly only possible from C++ code. Using the C++ Project Generation feature, it is very quick and easy to set up a custom C++ plugin.
C++ obviously has the downsides of longer compilation times, and live updating code is not possible. There is a way for game plugins to be modifiable while the editor runs, and the editor can fully shut down and reload its engine process, which does enable a form of live reloading of C++ code.
TypeScript code is updated every time you run a scene, which allows for quick iteration times.
Visual script code is the most limited both in functionality and tooling. It can be used for simple if-then scripts. The visual script system is not bad, but it is very far away from what can be done with TypeScript, and due to auto-completion and type checking provided by Visual Studio Code, using TypeScript is even more convenient. Without serious investment into the visual scripting system and tools (for which the core team has no time), there is no reason to ever prefer it over the other options.