For many years, we’ve accepted a painful compromise: for those who wished logic contained in the database, you needed to write SQL/PSM (Persistent Saved Modules). It’s clunky, exhausting to debug, and declarative by nature, making it horrible for algorithmic duties.
That ends with Percona Server 8.4.7-7.
We’re introducing JS Saved Applications as a Tech Preview. In contrast to Oracle’s implementation, which is restricted to the Enterprise Version or Oracle Cloud, we’ve built-in the open-source Google V8 engine (the identical engine powering Chrome and Node.js). This implies you get trendy programmability in a really open-source atmosphere.
Whereas we’re making ready to launch this as a tech preview, I wish to spotlight the three most quick, high-impact use instances the place JS blows SQL out of the water.
1. Complicated Information Validation (“The Information Firewall”)
SQL CHECK constraints are helpful however restricted. If it’s essential to validate advanced strings (like emails, URLs, or worldwide telephone numbers) SQL forces you into a large number of SUBSTRING and REPLACE chains.
With the V8 engine, you might have entry to native JS Common Expressions. You possibly can create a “Information Firewall” that ensures rubbish by no means hits your disk.
The Use Case: Validating an electronic mail deal with contained in the DB to stop unhealthy knowledge ingestion.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
CREATE FUNCTION validate_email_js(electronic mail VARCHAR(255)) RETURNS BOOLEAN LANGUAGE JS AS $$ // Native JS Regex is highly effective and readable const emailRegex = /^[^s@]+@[^s@]+.[^s@]+$/; return emailRegex.check(electronic mail); $$;
+——————————————————–+ +——————————————————–+ | 1 | +——————————————————–+ 1 row in set (0.01 sec)
mysql> SELECT validate_email_js(“unhealthy.electronic mail.com”); +——————————————————+ | validate_email_js(“unhealthy.electronic mail.com”) | +——————————————————+ | 0 | +——————————————————+ 1 row in set (0.02 sec)
|
2. Native JSON Processing
MySQL has supported JSON varieties for years, however manipulating that JSON utilizing SQL features (JSON_EXTRACT, JSON_SET) is verbose and unnatural. JS is the native language of JSON.
In Percona Server 8.4.7-7, we mechanically marshall MySQL JSON varieties into JS Objects. You don’t have to parse string blobs; you simply work with the item.
The Use Case: API Response Shaping. As a substitute of fetching a large JSON blob to your app simply to filter it, do it within the database and save the community price.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
CREATE FUNCTION normalize_order(raw_json JSON) RETURNS JSON LANGUAGE JS AS $$ // raw_json is already a JS object right here return { id: raw_json.id, whole: raw_json.objects.scale back((acc, merchandise) => acc + merchandise.worth, 0), tags: raw_json.metadata.tags.map(t => t.toLowerCase()) }; $$;
mysql> SELECT normalize_order(‘{“id”: 101, “objects”: [{“price”: 10}, {“price”: 20}], “metadata”: {“tags”: [“SALE”]}}’); +——————————————————————————————–+ | normalize_order(...) | +——————————————————————————————–+ | {“id”: 101, “tags”: [“sale”], “whole”: 30} | +——————————————————————————————–+ 1 row in set (0.01 sec)
|
3. Computational Offloading (Orders of Magnitude Sooner)
SQL engines are optimized for knowledge retrieval (IO), not heavy computation (CPU). Once you pressure SQL to do math, loops, or cryptographic hashing, it struggles with overhead.
As a result of V8 makes use of Simply-In-Time (JIT) compilation, heavy computational duties can run orders of magnitude sooner than normal SQL saved procedures.
The Use Case: Fuzzy Matching utilizing Levenshtein distance. Implementing this algorithm in SQL is sluggish and sophisticated. In JS, it’s extremely environment friendly.
|
CREATE FUNCTION levenshtein_js(s1 VARCHAR(255), s2 VARCHAR(255)) RETURNS INT LANGUAGE JS AS $$ // Environment friendly algorithms run natively in V8 // … normal JS implementation of Levenshtein … $$;
|
You possibly can then name this instantly in your SELECT statements to rank search outcomes immediately.
Present Standing & The Roadmap
You will need to handle expectations: that is presently a Tech Preview.
The largest distinction between our implementation and Oracle’s proper now could be that you can not but execute SQL queries (like SELECT or UPDATE) from inside the JS routine. At the moment, the Tech Preview helps JS Saved Routines for pure knowledge processing (Enter → Calculation → Output).
We’re actively engaged on bridging the SQL execution hole and including module assist. Nonetheless, for knowledge validation, JSON manipulation, and heavy math, the characteristic is prepared so that you can experiment with at this time.
Our mission, as all the time, is to shut the hole between Group and Enterprise editions. This characteristic brings JS functionality to the open-source model, eradicating the necessity for proprietary licenses. I invite you to put in the element bundle from our foremost repository, allow it, and inform us what you assume.
Be aware: Exterior module loading (e.g., import) isn’t supported on this preview; library code have to be included instantly within the routine physique.
Be taught Extra
For a deep dive into the structure, kind mapping, and particular limitations, learn the official documentation.
Getting Began This characteristic is accessible in the principle repository for Percona Server 8.4.7-7.
- Set up the Bundle: For RPM/Deb customers, set up the separate element bundle:
sudo apt-get set up percona-server-js(oryum set up ...) (Be aware: Tarball customers will discover this included in the principle bundle.) - Allow the Part: Log into MySQL and run:
INSTALL COMPONENT 'file://component_js_lang';
