Introduction
The Oracle Database 23c supports MLE JavaScript modules. MLE modules are standard ECMAScript 2022 modules. The easiest way to develop such modules is outside the database. This allows us to take advantage of the extensive JavaScript ecosystem and develop MLE modules in TypeScript instead of JavaScript.
In this blog post, I demonstrate how to develop and test an MLE module in TypeScript and deploy it into the database. I will use Node and VS Code.
TL;DR
See the conclusion and explore the code on GitHub.
Requirements
The idea is to provide a public stored procedure in the database that creates and populates the well-known tables dept and emp in the current schema. The function accepts different table names. The tables are not re-created if they already exist. However, the original data should be reset to their initial state while other rows should be left unchanged. Problems are reported via exceptions.
Nothing fancy. However, we will have to deal with SQL and address potential SQL injection vulnerabilities. Furthermore, it allows us to demonstrate how to test an MLE module outside the database.
Design
The following image visualizes the solution design.
We create an Oracle Database user demotab and deploy an npm and a self-made module as MLE modules into this schema, along with an MLE environment and a PL/SQL package as an interface. We grant the package to public and create a public synonym for it. As a result, any user in the database instance (e.g. the user otheruser) can execute the following code to install and populate the tables dept and emp within their schema.
begin
demo.create_tabs;
end;
/We can also pass alternative table names like this:
begin
demo.create_tabs('departments', 'employees');
end;
/Prerequisites
You need the following to build this MLE module yourself:
- Full access to an Oracle Database 23c Free (>=23.3). This means you know how to connect as
sysdba. - A machine with VS Code (>=1.83.1), Node (>=20.9.0) and SQLcl (>=23.3.0, must be found in the OS path).
- Internet access and the rights to install npm modules and VS Code extensions.
Prepare Node Project
Open a folder in VS Code where you want to develop the MLE module. And create the files package.json, tsconfig.json, .eslintrc and .prettier. The content of each file is shown below.
{
"name": "demotab",
"version": "1.0.0",
"description": "Create and populate demo tables.",
"type": "module",
"scripts": {
"build": "npm run format && npm run lint && npm run tsc && npm run coverage",
"tsc": "tsc --project tsconfig.json",
"lint": "eslint . --ext .ts",
"format": "prettier --write './**/*{.ts,.eslintrc,.prettierrc,.json}'",
"test": "vitest --no-threads --reporter=verbose --dir ./test",
"coverage": "vitest --no-threads --dir ./test run --coverage"
},
"devDependencies": {
"@types/oracledb": "^6.0.3",
"@typescript-eslint/eslint-plugin": "^6.9.1",
"@typescript-eslint/parser": "^6.9.1",
"@vitest/coverage-v8": "^0.34.6",
"eslint": "^8.52.0",
"eslint-config-prettier": "^9.0.0",
"oracledb": "^6.2.0",
"prettier": "^3.0.3",
"typescript": "^5.2.2",
"vitest": "^0.34.6"
},
"dependencies": {
"sql-assert": "^1.0.3"
}
}Node uses some of these JSON fields. However, most of the fields are required by npm and its command-line interface. The type on line 5 defines that we build an ECMAScript module. Important are the dependencies. Our module needs the sql-assert module at runtime. Other dependencies are for developing purposes only.
{
"compilerOptions": {
"rootDir": "./src",
"target": "ES2017",
"module": "ES2022",
"moduleResolution": "node",
"esModuleInterop": true,
"forceConsistentCasingInFileNames": true,
"strict": true,
"skipLibCheck": true,
"sourceMap": true,
"outDir": "esm"
},
"include": ["./src/**/*"]
}This is the configuration for the TypeScript compiler. On lines 5 and 6 we define the ECMAScript versions to be used. We develop in TypeScript with ECMAScript 2022 features and generate a JavaScript file using ECMAScript 2017, the version in which the async/await feature was introduced. This makes the generated JavaScript module a bit more readable. However, for MLE we could also use ECMAScript 2022. Using older ECMAScript targets makes sense when you want to use it in environments with an older JavaScript engine, for example, old browsers.
{
"root": true,
"parser": "@typescript-eslint/parser",
"plugins": ["@typescript-eslint"],
"extends": [
"eslint:recommended",
"plugin:@typescript-eslint/eslint-recommended",
"plugin:@typescript-eslint/recommended",
"prettier"
],
"rules": {
"no-console": "error"
}
}Here we define the configuration for ESlint. We configure the linter for TypeScript with a recommended rule set. However, we do not want console.log statements in our code. Therefore we treat them as errors.
{
"semi": true,
"printWidth": 120,
"singleQuote": false,
"tabWidth": 4,
"trailingComma": "none",
"arrowParens": "always"
}The last configuration file is for Prettier, a popular formatter for various languages.
Initialize Node Project
Now we are ready to initialize the Node project. Open a terminal window in VS Code and execute the following command:
npm installThis will create a file named package-lock.json and a node_modules folder. package-lock.json is the table of contents for the node_modules folder. It contains the recursively resolved dependencies with their versions. Dependencies can be defined with version ranges. Therefore, they are not unambiguous and can lead to different results depending on the time of analysis.
When you delete the node_modules folder and re-run npm install, it will produce the same content based on the module versions registered in package-lock.json. As a result, it might be useful to add this file to your version control system. To make things reproducible.
Original TypeScript Module
Let’s create a file named demotab.ts in a new folder src with the following content:
import { simpleSqlName } from "sql-assert";
import oracledb from "oracledb";
// global variable for default connection in the database
declare const session: oracledb.Connection;
/**
* Creates demo tables with initial data for the well-known tables `dept` and `emp`.
* Alternative table names can be passed to this function. The tables are not re-created
* if they already exist. However, the rows for the 4 departments and the 14 employees
* should be reset to their initial state while other rows are left unchanged.
* Problems are reported via exceptions.
*
* @param [deptName="dept"] name of the dept table.
* @param [empName="emp"] name of the emp table.
* @returns {Promise<void>}.
*/
export async function create(deptName: string = "dept", empName: string = "emp"): Promise<void> {
const dept = simpleSqlName(deptName);
const emp = simpleSqlName(empName);
await session.execute(`
create table if not exists ${dept} (
deptno number(2, 0) not null constraint ${dept}_pk primary key,
dname varchar2(14 char) not null,
loc varchar2(13 char) not null
)
`);
await session.execute(`
merge into ${dept} t
using (values
(10, 'ACCOUNTING', 'NEW YORK'),
(20, 'RESEARCH', 'DALLAS'),
(30, 'SALES', 'CHICAGO'),
(40, 'OPERATIONS', 'BOSTON')
) s (deptno, dname, loc)
on (t.deptno = s.deptno)
when matched then
update
set t.dname = s.dname,
t.loc = s.loc
when not matched then
insert (t.deptno, t.dname, t.loc)
values (s.deptno, s.dname, s.loc)
`);
await session.execute(`
create table if not exists ${emp} (
empno number(4, 0) not null constraint ${emp}_pk primary key,
ename varchar2(10 char) not null,
job varchar2(9 char) not null,
mgr number(4, 0) constraint ${emp}_mgr_fk references ${emp},
hiredate date not null,
sal number(7, 2),
comm number(7, 2),
deptno number(2, 0) not null constraint ${emp}_deptno_fk references ${dept}
)
`);
await session.execute(`create index if not exists ${emp}_mgr_fk_i on ${emp} (mgr)`);
await session.execute(`create index if not exists ${emp}_deptno_fk_i on ${emp} (deptno)`);
await session.execute(`alter table ${emp} disable constraint ${emp}_mgr_fk`);
await session.execute(`
merge into ${emp} t
using (values
(7839, 'KING', 'PRESIDENT', null, date '1981-11-17', 5000, null, 10),
(7566, 'JONES', 'MANAGER', 7839, date '1981-04-02', 2975, null, 20),
(7698, 'BLAKE', 'MANAGER', 7839, date '1981-05-01', 2850, null, 30),
(7782, 'CLARK', 'MANAGER', 7839, date '1981-06-09', 2450, null, 10),
(7788, 'SCOTT', 'ANALYST', 7566, date '1987-04-19', 3000, null, 20),
(7902, 'FORD', 'ANALYST', 7566, date '1981-12-03', 3000, null, 20),
(7499, 'ALLEN', 'SALESMAN', 7698, date '1981-02-20', 1600, 300, 30),
(7521, 'WARD', 'SALESMAN', 7698, date '1981-02-22', 1250, 500, 30),
(7654, 'MARTIN', 'SALESMAN', 7698, date '1981-09-28', 1250, 1400, 30),
(7844, 'TURNER', 'SALESMAN', 7698, date '1981-09-08', 1500, 0, 30),
(7900, 'JAMES', 'CLERK', 7698, date '1981-12-03', 950, null, 30),
(7934, 'MILLER', 'CLERK', 7782, date '1982-01-23', 1300, null, 10),
(7369, 'SMITH', 'CLERK', 7902, date '1980-12-17', 800, null, 20),
(7876, 'ADAMS', 'CLERK', 7788, date '1987-05-23', 1100, null, 20)
) s (empno, ename, job, mgr, hiredate, sal, comm, deptno)
on (t.empno = s.empno)
when matched then
update
set t.ename = s.ename,
t.job = s.job,
t.mgr = s.mgr,
t.hiredate = s.hiredate,
t.sal = s.sal,
t.comm = s.comm,
t.deptno = s.deptno
when not matched then
insert (t.empno, t.ename, t.job, t.mgr, t.hiredate, t.sal, t.comm, t.deptno)
values (s.empno, s.ename, s.job, s.mgr, s.hiredate, s.sal, s.comm, s.deptno)
`);
await session.execute(`alter table ${emp} enable constraint ${emp}_mgr_fk`);
} Global session Variable
On line 5 we declare a constant named session. This way we tell the TypeScript compiler that a read-only session variable of type oracledb.Connection is available. As a result, we have code completion enabled for session.
Asynchronous Function
On line 18 we define the signature of the create function. Please note that this is an asynchronous function. We need that to use await in Node. However, functions in MLE modules run always synchronously within the Oracle Database, even if a function is declared as async. So, async would not be necessary if the code runs exclusively in the database.
Preventing SQL Injection
We call simpleSqlName on lines 19 and 20 to ensure that no SQL injection is possible. This makes the variables dept and emp in the template literals safe. The function simpleSqlName has the advantage that it runs outside of the database. It has the same logic as its sibling dbms_assert.simple_sql_name.
Generated JavaScript Module
We run the TypeScript compiler as follows in a terminal window within VS Code:
npm run tscThis will execute tsc --project tsconfig.json as defined in package.json and produce a demotab.js file in the esm folder.
import { simpleSqlName } from "sql-assert";
/**
* Creates demo tables with initial data for the well-known tables `dept` and `emp`.
* Alternative table names can be passed to this function. The tables are not re-created
* if they already exist. However, the rows for the 4 departments and the 14 employees
* should be reset to their initial state while other rows are left unchanged.
* Problems are reported via exceptions.
*
* @param [deptName="dept"] name of the dept table.
* @param [empName="emp"] name of the emp table.
* @returns {Promise<void>}.
*/
export async function create(deptName = "dept", empName = "emp") {
const dept = simpleSqlName(deptName);
const emp = simpleSqlName(empName);
await session.execute(`
create table if not exists ${dept} (
deptno number(2, 0) not null constraint ${dept}_pk primary key,
dname varchar2(14 char) not null,
loc varchar2(13 char) not null
)
`);
await session.execute(`
merge into ${dept} t
using (values
(10, 'ACCOUNTING', 'NEW YORK'),
(20, 'RESEARCH', 'DALLAS'),
(30, 'SALES', 'CHICAGO'),
(40, 'OPERATIONS', 'BOSTON')
) s (deptno, dname, loc)
on (t.deptno = s.deptno)
when matched then
update
set t.dname = s.dname,
t.loc = s.loc
when not matched then
insert (t.deptno, t.dname, t.loc)
values (s.deptno, s.dname, s.loc)
`);
await session.execute(`
create table if not exists ${emp} (
empno number(4, 0) not null constraint ${emp}_pk primary key,
ename varchar2(10 char) not null,
job varchar2(9 char) not null,
mgr number(4, 0) constraint ${emp}_mgr_fk references ${emp},
hiredate date not null,
sal number(7, 2),
comm number(7, 2),
deptno number(2, 0) not null constraint ${emp}_deptno_fk references ${dept}
)
`);
await session.execute(`create index if not exists ${emp}_mgr_fk_i on ${emp} (mgr)`);
await session.execute(`create index if not exists ${emp}_deptno_fk_i on ${emp} (deptno)`);
await session.execute(`alter table ${emp} disable constraint ${emp}_mgr_fk`);
await session.execute(`
merge into ${emp} t
using (values
(7839, 'KING', 'PRESIDENT', null, date '1981-11-17', 5000, null, 10),
(7566, 'JONES', 'MANAGER', 7839, date '1981-04-02', 2975, null, 20),
(7698, 'BLAKE', 'MANAGER', 7839, date '1981-05-01', 2850, null, 30),
(7782, 'CLARK', 'MANAGER', 7839, date '1981-06-09', 2450, null, 10),
(7788, 'SCOTT', 'ANALYST', 7566, date '1987-04-19', 3000, null, 20),
(7902, 'FORD', 'ANALYST', 7566, date '1981-12-03', 3000, null, 20),
(7499, 'ALLEN', 'SALESMAN', 7698, date '1981-02-20', 1600, 300, 30),
(7521, 'WARD', 'SALESMAN', 7698, date '1981-02-22', 1250, 500, 30),
(7654, 'MARTIN', 'SALESMAN', 7698, date '1981-09-28', 1250, 1400, 30),
(7844, 'TURNER', 'SALESMAN', 7698, date '1981-09-08', 1500, 0, 30),
(7900, 'JAMES', 'CLERK', 7698, date '1981-12-03', 950, null, 30),
(7934, 'MILLER', 'CLERK', 7782, date '1982-01-23', 1300, null, 10),
(7369, 'SMITH', 'CLERK', 7902, date '1980-12-17', 800, null, 20),
(7876, 'ADAMS', 'CLERK', 7788, date '1987-05-23', 1100, null, 20)
) s (empno, ename, job, mgr, hiredate, sal, comm, deptno)
on (t.empno = s.empno)
when matched then
update
set t.ename = s.ename,
t.job = s.job,
t.mgr = s.mgr,
t.hiredate = s.hiredate,
t.sal = s.sal,
t.comm = s.comm,
t.deptno = s.deptno
when not matched then
insert (t.empno, t.ename, t.job, t.mgr, t.hiredate, t.sal, t.comm, t.deptno)
values (s.empno, s.ename, s.job, s.mgr, s.hiredate, s.sal, s.comm, s.deptno)
`);
await session.execute(`alter table ${emp} enable constraint ${emp}_mgr_fk`);
}
//# sourceMappingURL=demotab.js.mapAs you see on line 13, all type definitions are gone including the oracledb import. So, the JavaScript file has no dependencies on a database driver. As a result, we can use node-oracledb outside the database and mle-js-oracledb within the database.
Besides the removed types, the file looks very much like its TypeScript pendant.
On line 89 there’s a comment mentioning a map file. This map file was also generated by the TypeScript compiler. It improves the developer experience during a debugging session so that the developer can work on the original TypeScript files. The JavaScript files are only used behind the scenes.
Testing
1. Framework
I decided to use Vitest for this project. Why not Jest or Mocha?
I tried Mocha with a plain JavaScript project. It felt a bit outdated and I did not like the fact that I had to opt-in for an assertion library. IMO this should be part of the framework. It’s too much freedom. Too many unnecessary variants when googling for solutions.
Jest is a full-fletched and very popular testing framework. It would have been a natural choice. However, I stumbled over Vitest with a Jest-compatible API which claims to be faster and easier to use with TypeScript. So I thought to give it a try.
2. Database Configuration
We create a file named dbconfig.ts in a new folder test with the following content:
import oracledb from "oracledb";
let sysSession: oracledb.Connection;
export let demotabSession: oracledb.Connection;
export let otheruserSession: oracledb.Connection;
const connectString = "192.168.1.8:51007/freepdb1";
const sysConfig: oracledb.ConnectionAttributes = {
user: "sys",
password: "oracle",
connectString: connectString,
privilege: oracledb.SYSDBA
};
export const demotabConfig: oracledb.ConnectionAttributes = {
user: "demotab",
password: "demotab",
connectString: connectString
};
export const otheruserConfig: oracledb.ConnectionAttributes = {
user: "otheruser",
password: "otheruser",
connectString: connectString
};
export async function createSessions(): Promise<void> {
sysSession = await oracledb.getConnection(sysConfig);
await createUser(demotabConfig);
await createUser(otheruserConfig);
await sysSession.execute("grant create public synonym to demotab");
await sysSession.execute("grant execute on javascript to public");
sysSession.close();
demotabSession = await oracledb.getConnection(demotabConfig);
otheruserSession = await oracledb.getConnection(otheruserConfig);
}
async function createUser(config: oracledb.ConnectionAttributes): Promise<void> {
await sysSession.execute(`drop user if exists ${config.user} cascade`);
await sysSession.execute(`
create user ${config.user} identified by ${config.password}
default tablespace users
temporary tablespace temp
quota 1m on users
`);
await sysSession.execute(`grant db_developer_role to ${config.user}`);
}
export async function closeSessions(): Promise<void> {
await demotabSession?.close();
await otheruserSession?.close();
}To make the configuration work in your environment, you need to change the lines 7 and 11. The connect string and the password of the Oracle user sys. Everything else could be left “as is”.
This module creates the database users demotab and otheruser and manages database sessions.
3. Test TypeScript Module Outside of the Database
We create a file named demotab.test.ts in the folder test with the following content:
import { beforeAll, afterAll, describe, it, expect } from "vitest";
import { createSessions, closeSessions, demotabSession } from "./dbconfig";
import { create } from "../src/demotab";
describe("TypeScript outside of the database", () => {
const timeout = 10000;
beforeAll(async () => {
await createSessions();
global.session = demotabSession;
});
describe("invalid input causing 'Invalid SQL name.'", () => {
// error is thrown in JavaScript (no ORA-04161 outside of the database)
it("should throw an error with invalid deptName", () => {
expect(async () => await create("a-dept-table")).rejects.toThrowError(/invalid sql/i);
});
it("should throw an error with invalid empName", () => {
expect(async () => await create("dept", "a-emp-table")).rejects.toThrowError(/invalid sql/i);
});
});
describe("invalid input causing 'ORA-00911: _: invalid character after <identifier>'", () => {
// error is thrown by the Oracle Database while trying to execute a SQL statement
it("should throw an error with quoted deptName", () => {
expect(async () => await create('"dept"')).rejects.toThrowError(/ORA-00911.+invalid/);
});
it("should throw an error with quoted empName", () => {
expect(async () => await create("dept", '"emp"')).rejects.toThrowError(/ORA-00911.+invalid/);
});
});
describe(
"valid input",
() => {
it("should create 'dept' and 'emp' without parameters)", async () => {
await create();
const dept = await demotabSession.execute("select * from dept order by deptno");
expect(dept.rows).toEqual([
[10, "ACCOUNTING", "NEW YORK"],
[20, "RESEARCH", "DALLAS"],
[30, "SALES", "CHICAGO"],
[40, "OPERATIONS", "BOSTON"]
]);
const emp = await demotabSession.execute(`
select empno, ename, job, mgr, to_char(hiredate,'YYYY-MM-DD'), sal, comm, deptno
from emp
order by empno
`);
expect(emp.rows).toEqual([
[7369, "SMITH", "CLERK", 7902, "1980-12-17", 800, null, 20],
[7499, "ALLEN", "SALESMAN", 7698, "1981-02-20", 1600, 300, 30],
[7521, "WARD", "SALESMAN", 7698, "1981-02-22", 1250, 500, 30],
[7566, "JONES", "MANAGER", 7839, "1981-04-02", 2975, null, 20],
[7654, "MARTIN", "SALESMAN", 7698, "1981-09-28", 1250, 1400, 30],
[7698, "BLAKE", "MANAGER", 7839, "1981-05-01", 2850, null, 30],
[7782, "CLARK", "MANAGER", 7839, "1981-06-09", 2450, null, 10],
[7788, "SCOTT", "ANALYST", 7566, "1987-04-19", 3000, null, 20],
[7839, "KING", "PRESIDENT", null, "1981-11-17", 5000, null, 10],
[7844, "TURNER", "SALESMAN", 7698, "1981-09-08", 1500, 0, 30],
[7876, "ADAMS", "CLERK", 7788, "1987-05-23", 1100, null, 20],
[7900, "JAMES", "CLERK", 7698, "1981-12-03", 950, null, 30],
[7902, "FORD", "ANALYST", 7566, "1981-12-03", 3000, null, 20],
[7934, "MILLER", "CLERK", 7782, "1982-01-23", 1300, null, 10]
]);
});
it("should create 'dept2' and 'emp2' with both parameters)", async () => {
await create("dept2", "emp2");
const dept = await demotabSession.execute("select * from dept minus select * from dept2");
expect(dept.rows).toEqual([]);
const emp = await demotabSession.execute("select * from emp minus select * from emp2");
expect(emp.rows).toEqual([]);
});
it("should fix data in 'dept' and 'emp' after changing data and using default parameters", async () => {
await demotabSession.execute(`
begin
delete dept where deptno = 40;
update dept set loc = initcap(loc);
insert into dept(deptno, dname, loc) values(50, 'utPLSQL', 'Winterthur');
delete emp where empno = 7876;
update emp set sal = sal * 2;
insert into emp(empno, ename, job, hiredate, sal, deptno)
values (4242, 'Salvisberg', 'Tester', date '2000-01-01', 9999, '50');
end;
`);
await create();
const dept = await demotabSession.execute("select * from dept minus select * from dept2");
expect(dept.rows).toEqual([[50, "utPLSQL", "Winterthur"]]);
const emp = await demotabSession.execute(`
select empno, ename, job, mgr, to_char(hiredate,'YYYY-MM-DD'), sal, comm, deptno
from emp
minus
select empno, ename, job, mgr, to_char(hiredate,'YYYY-MM-DD'), sal, comm, deptno
from emp2
`);
expect(emp.rows).toEqual([[4242, "Salvisberg", "Tester", null, "2000-01-01", 9999, null, 50]]);
});
},
timeout
);
afterAll(async () => {
await closeSessions();
});
});Test Suite
The main test suite starts on line 5 and ends on line 105. The Vitest configuration enforces serial execution. As a result, the tests are executed according to their order in the file.
Global session Variable
On line 11 we initialize the global variable session with a database session to the Oracle user demotab. We use this global variable in the function create. See demotab.ts.
Test Case – Assertions
Look at line 36. It looks similar to the English sentence “it should create ‘dept’ and ’emp’ without parameters”. That’s why the testing framework provides the alias it for the function test. This notation leads to test names that are easier to understand in the code and other contexts where the it is not shown, during test execution, for example.
On line 37 we call the create function without parameters. We read the content of the table dept into a variable dept on line 38. And finally on lines 39 to 44 we assert if the 4 expected rows are stored in the table dept.
A difference between the expected and actual results would be reported like this. I changed the expected output in the code to produce this result:
4. Run All Tests
To run all tests open a terminal window in VS Code and execute the following command:
npm run testThis will produce an output similar to this:
5. Build with Test Coverage
Open a terminal window in VS Code and execute the following to format, lint and compile the code, and run all tests with a code coverage report:
npm run buildThis will produce an output similar to this:
Deployment
We tested the module successfully outside of the database. Now it’s time to deploy it into the database. For that, we create the SQL script deploy.sql in the root folder of our project with the following content:
set define off
script
var url = new java.net.URL("https://esm.run/sql-assert@1.0.3");
var content = new java.lang.String(url.openStream().readAllBytes(),
java.nio.charset.StandardCharsets.UTF_8);
var script = 'create or replace mle module sql_assert_mod '
+ 'language javascript as ' + '\n'
+ content + "\n"
+ '/' + "\n";
sqlcl.setStmt(script);
sqlcl.run();
/
script
var path = java.nio.file.Path.of("./esm/demotab.js");
var content = java.nio.file.Files.readString(path);
var script = 'create or replace mle module demotab_mod '
+ 'language javascript as ' + '\n'
+ content + "\n"
+ '/' + "\n";
sqlcl.setStmt(script);
sqlcl.run();
/
create or replace mle env demotab_env
imports('sql-assert' module sql_assert_mod)
language options 'js.strict=true, js.console=false, js.polyglot-builtin=true';
create or replace package demo authid current_user is
procedure create_tabs as
mle module demotab_mod env demotab_env signature 'create()';
procedure create_tabs(
in_dept_table_name in varchar2
) as mle module demotab_mod env demotab_env signature 'create(string)';
procedure create_tabs(
in_dept_table_name in varchar2,
in_emp_table_name in varchar2
) as mle module demotab_mod env demotab_env signature 'create(string, string)';
end demo;
/
-- required "execute on javascript" was granted to public in test
grant execute on demo to public;
create or replace public synonym demo for demotab.demo;
exitnpm Module sql-assert (MLE Module sql_assert_mod)
On lines 2-12, we load version 1.0.3 of the npm module sql-assert as MLE module sql_assert_mod into the database. We dynamically build a create or replace mle module statement and execute it with the help of SQLcl’s script command.
The URL https://esm.run/sql-assert@1.0.3 provides a minimized file of the npm module. In other words, it is optimized for use in browsers where the modules are loaded over the network at runtime.
Minimized code works in the database, of course. However, it might make it a bit harder to understand the error stack.
No Template Literals?
You might wonder why we do not use ECMAScript template literals to populate the script variable. The reason is, that SQLcl does not provide a JavaScript engine. It relies on the JDK’s JavaScript engine. Unfortunately, the Nashorn JavaScript engine is decommissioned in current JDKs. The last JDK with a JavaScript engine is JDK 11, based on ECMAScript 2011 (5.1), which does not support template literals.
The GraalVM JDK is an exception. Versions 17 and 21 come with a current GraalVM JavaScript engine that supports template literals. And this JDK can be used with SQLcl.
However, there is an additional reason to avoid JavaScript features introduced after ECMAScript 2011 and that’s SQL Developer. You can run the SQL script deploy.sql also in an SQL Developer worksheet. SQL Developer requires a JDK 11. You cannot use a newer JDK in SQL Developer, because you would lose some important features such as Real Time SQL Monitor which requires JavaFX. Another decommissioned component in the JDK. And the GraalVM JDK does not provide JavaFX.
So for compatibility reasons, we have to stick to old JavaScript features available in ECMAScript 2011 when using the script command in SQLcl or SQL Developer.
Local Module demotab (MLE Module demotab_mod)
On lines 14-23, we load the JavaScript MLE module demotab from our local file system into the database. The process is similar to the npm module. The only difference is that we get the module from the local disk and not over the network.
MLE Environment demotab_env
On lines 25-27, we create an MLE environment. Besides configuring compiler options, we tell the JavaScript compiler what modules are available and where to find them.
PL/SQL Call Interface
On lines 29-42, we create a PL/SQL package demo. It contains three procedures with call specifications for the function create in the MLE module demotab_mod. Why three procedures and not just one? Because the MLE call specifications do not support default values for parameters. However, we can work around it by providing three procedures. One without parameters, one with a single parameter and another one with two parameters.
Test JavaScript MLE Module within the Database
To test if the deployed code works we create the file mle-demotab.test.ts in the folder test with the following content:
import { beforeAll, afterAll, describe, it, expect, beforeEach } from "vitest";
import { createSessions, closeSessions, otheruserSession, demotabSession, demotabConfig } from "./dbconfig";
import oracledb from "oracledb";
import { exec } from "child_process";
import util from "node:util";
describe("MLE JavaScript module within the database", () => {
const timeout = 15000;
async function userTables(): Promise<oracledb.Result<unknown>> {
return await otheruserSession.execute(`
with
function num_rows(in_table_name in varchar2) return integer is
l_rows integer;
begin
execute immediate 'select count(*) from ' || in_table_name
into l_rows;
return l_rows;
end;
select table_name, num_rows(table_name) as num_rows
from user_tables
order by table_name
`);
}
beforeAll(async () => {
await createSessions();
const execAsync = util.promisify(exec);
await execAsync(
`sql -S ${demotabConfig.user}/${demotabConfig.password}@${demotabConfig.connectString} @deploy.sql`
);
}, timeout);
beforeEach(async () => {
await otheruserSession.execute(`
begin
for r in (select table_name from user_tables) loop
execute immediate 'drop table '
|| r.table_name
|| ' cascade constraints purge';
end loop;
end;
`);
});
describe("deployment", () => {
it("should have valid database objects in demotab user", async () => {
const mods = await demotabSession.execute(`
select object_type, object_name, status
from user_objects
order by object_type, object_name
`);
expect(mods.rows).toEqual([
["MLE ENVIRONMENT", "DEMOTAB_ENV", "VALID"],
["MLE MODULE", "DEMOTAB_MOD", "VALID"],
["MLE MODULE", "SQL_ASSERT_MOD", "VALID"],
["PACKAGE", "DEMO", "VALID"]
]);
});
});
describe("run MLE module from otheruser", () => {
it("should create 'dept' and 'emp' without parameters", async () => {
await otheruserSession.execute("begin demo.create_tabs; end;");
expect((await userTables()).rows).toEqual([
["DEPT", 4],
["EMP", 14]
]);
});
it("should create 'd' and 'emp' with first parameter only", async () => {
await otheruserSession.execute("begin demo.create_tabs('d'); end;");
expect((await userTables()).rows).toEqual([
["D", 4],
["EMP", 14]
]);
});
it("should create 'd' and 'e' with both parameters", async () => {
await otheruserSession.execute("begin demo.create_tabs('d', 'e'); end;");
expect((await userTables()).rows).toEqual([
["D", 4],
["E", 14]
]);
});
});
afterAll(async () => {
await closeSessions();
});
});On line 30 we run the SQL script deploy.sql with SQLcl. We connect as demotab with the credentials and connect string configured in dbconfig.ts.
We test the default PL/SQL call interface on lines 63-69 by executing begin demo.create_tabs; end;. Then we check the number of rows in the tables dept and emp. That’s enough. We do not need to repeat the tests of the demotab module since the module was already successfully tested.
Re-Run All Tests
To re-run all tests open a terminal window in VS Code and execute the following command:
npm run testThis will produce an output similar to this:
Conclusion
For years I’ve been advocating file-based database development. With moderate success. All of my customers are using a version control system and automated deployments. However, the way the files in the version control system are maintained is suboptimal. In most cases, the developers use an IDE such as SQL Developer or PL/SQL Developer to read the source code from the database, change it in the editor of the IDE and then save (=deploy) it in the database. Updating the files in the version control system is a postponed, sometimes half-automated task. This leads to all kinds of bugs detected in the CI (or in later stages) which should have been detected during development. Sometimes code changes are lost, for example, when the underlying database instance has been replaced by a newer clone.
Why is it so hard to change the behaviour of the database developers? Changing the files first and then deploying them into the database? One reason is that the IDEs do not support the file-based development process well enough. They favour the let-us-read-everything-from-the-database approach, which makes sense for application data but is not ideal for code.
The MLE is not supported by the current IDEs. Oracle Database Actions (SQL Developer Web) is an exception, it provides basic support for MLE. However, I guess it will take years until a reasonable functionality is provided, if at all.
So when we want to develop MLE modules efficiently we have to use the currently available IDEs for TypeScript or JavaScript. They are great. Excellent editor, VCS integration, testing tools, debugger, formatter, linter and packaging system. The ecosystem is mature and is constantly improving. I very much like the fact that we have a global module registry npm which supports also private modules. As a result, being forced to use this ecosystem is not a bad thing. Quite the contrary. It’s the best that could have happened to database development.
When I look at the code of this MLE demo module, I’m quite happy with it. I’m confident that this approach can be used on a larger scale.
IMO the MLE is the best thing that happened to the Oracle Database since version 7, which brought us PL/SQL.
Let’s find out what works and what should be improved.
Updated on 2023-11-13, using npm install to initialize the Node project; using var instead of const in deploy.sql to make it compatible with ECMAScript 2011 (ES 5.1).
Updated on 2023-11-14, using declare const session: oracledb.Connection; on line 5 of demotab.ts (instead of any) and amended all related parts (removed "@typescript-eslint/no-explicit-any": "off" in .eslintrc; changed description of .eslintrc; changed description of “Global session variable”; changed description of demotab.js)
1 Comment
[…] my previous blog post, I’ve shown how you can deploy an npm module from a URL and a custom ESM module from a local […]