After researching, there are two libraries to be considered which are Monaco editor and CodeMirror. According to this npmtrends, those editors are the top ranked
Monaco editor:
Advantages

• Well maintained and developed by Microsoft
• Rich features, the one that powered Visual studio code which may attract users who are familiar with Vs Code
• Support theme, allow to customize color theme and styles through either by javascript object or css code.
• Having playground examples to show features
• Has mysql language (https://github.com/microsoft/monaco-languages/blob/main/src/mysql/mysql.ts). Has ability to add new language.
• Has all built-in necessary functionalities of an editor.
• Robust and modern library using TypeScript

Disadvantages:

• Currently, it doesn't provide option to add custom icon for suggestion items ( intellisense suggestion). It comes with pre-defined item label such as Class, Constant, Enum, Event, Field, Keyword, Function.... ( may not be a big deal as workaround can be applied and future release may support this)
• API documentation is long and overly technical make it a bit difficult to use in the first place.

Code mirror: (Snowflake is using code mirror)
Advantages

• Well maintained by the community
• It's the editor used in the dev tool of many browsers
• Has built-in sql hint for auto completion feature
• Already has mariadb as sql dialects
• Support theme, allow to customize colors and styles through class name selectors
• Simple to use and customize
• Has been used by the community for more than a decade which stable release.

Disadvantages:

• Many companies using Code mirror have been switching to Monaco editor due to its rich features.
• Needs to import and customize add-on feature such as searching functionality, key maps configurations.

In brief, after trying to use both libraries in Vue.js, Monaco editor offers better user experience with its built-in editor features. However, the auto-generated API documentation is a bit difficult to understand.
On the other hand, CodeMirror offers with less built-in features but it supports for add-on features and the documentation is written by human. Though, this brings more room to customize, it also slows down the process of adding basic functionalities of an editor.
As a result, Monaco editor will be chosen.
Here are the links to the prototype to compare:
https://mariadb-thienly.github.io/vue-codemirror-sql-editor/
https://mariadb-thienly.github.io/vue-monaco-sql-editor/