RDB

The ultimate ORM for Node and Typescript

ORMSQL
alfateam/rdbrdbjs.orgrdb

README

RDB


RDB is the ultimate Object Relational Mapper for Node.js and Typescript, offering seamless integration with popular databases like Postgres, MS SQL, MySQL, Sybase SAP, and SQLite. Whether you're building applications in TypeScript or JavaScript  (including both CommonJS and ECMAScript), RDB has got you covered.  

Key Features


- Rich Querying Model: RDB provides a powerful and intuitive querying model, making it easy to retrieve, filter, and manipulate data from your databases.
- Concise API: With a concise and developer-friendly API, RDB enables you to interact with your database using simple and expressive syntax.
- No Code Generation Required: Enjoy full IntelliSense, even in table mappings, without the need for cumbersome code generation.
- TypeScript and JavaScript Support: RDB fully supports both TypeScript and JavaScript, allowing you to leverage the benefits of static typing and modern ECMAScript features.
- Works in the Browser: You can securely use RDB in the browser by utilizing the Express.js plugin, which serves to safeguard sensitive database credentials from exposure at the client level. This method mirrors a traditional REST API, augmented with advanced TypeScript tooling for enhanced functionality.

This is the _Modern Typescript Documentation_. Are you looking for the _Classic Documentation_ ?

Installation

  1. ```bash
  2. $ npm install rdb
  3. ```  

Example

Here we choose SQLite.  
  1. ```bash
  2. $ npm install sqlite3
  3. ```
📄 map.js
  1. ```javascript
  2. import rdb from 'rdb';

  4. const map = rdb.map(x => ({
  5.   customer: x.table('customer').map(({ column }) => ({
  6.     id: column('id').numeric().primary().notNullExceptInsert(),
  7.     name: column('name').string(),
  8.     balance: column('balance').numeric(),
  9.     isActive: column('isActive').boolean(),
  10.   })),

  12.   order: x.table('_order').map(({ column }) => ({
  13.     id: column('id').numeric().primary().notNullExceptInsert(),
  14.     orderDate: column('orderDate').date().notNull(),
  15.     customerId: column('customerId').numeric().notNullExceptInsert(),
  16.   })),

  18.   orderLine: x.table('orderLine').map(({ column }) => ({
  19.     id: column('id').numeric().primary().notNullExceptInsert(),
  20.     orderId: column('orderId').numeric(),
  21.     product: column('product').string(),
  22.   })),

  24.   deliveryAddress: x.table('deliveryAddress').map(({ column }) => ({
  25.     id: column('id').numeric().primary().notNullExceptInsert(),
  26.     orderId: column('orderId').numeric(),
  27.     name: column('name').string(),
  28.     street: column('street').string(),
  29.     postalCode: column('postalCode').string(),
  30.     postalPlace: column('postalPlace').string(),
  31.     countryCode: column('countryCode').string(),
  32.   }))

  34. })).map(x => ({
  35.   order: x.order.map(v => ({
  36.     customer: v.references(x.customer).by('customerId'),
  37.     lines: v.hasMany(x.orderLine).by('orderId'),
  38.     deliveryAddress: hasOne(x.deliveryAddress).by('orderId'),
  39.   }))
  40. }));

  42. export default map;
  43. ```  
📄 update.js

  1. ```javascript
  2. import map from './map';
  3. const db = map.sqlite('demo.db');

  5. updateRow();

  7. async function updateRow() {
  8.   const order = await db.order.getById(2, {
  9.     lines: true
  10.   });
  11.   order.lines.push({
  12.     product: 'broomstick'
  13.   });

  15.   await order.saveChanges();
  16. }

  18. ```

API


Mapping tablesTo define a mapping, you employ the map() method, linking your tables and columns to corresponding object properties. You provide a callback function that engages with a parameter representing a database table.

Each column within your database table is designated by using the column() method, in which you specify its name. This action generates a reference to a column object that enables you to articulate further column properties like its data type or if it serves as a primary key.

Relationships between tables can also be outlined. By using methods like hasOne, hasMany, and references, you can establish connections that reflect the relationships in your data schema. In the example below, an 'order' is linked to a 'customer' reference, a 'deliveryAddress', and multiple 'lines'. The hasMany and hasOne relations represents ownership - the tables 'deliveryAddress' and 'orderLine' are owned by the 'order' table, and therefore, they contain the 'orderId' column referring to their parent table, which is 'order'. Conversely, the customer table is independent and can exist without any knowledge of the 'order' table. Therefore we say that the order table references the customer table - necessitating the existence of a 'customerId' column in the 'order' table.

📄 map.js
  1. ```javascript
  2. import rdb from 'rdb';

  4. const map = rdb.map(x => ({
  5.   customer: x.table('customer').map(({ column }) => ({
  6.     id: column('id').numeric().primary().notNullExceptInsert(),
  7.     name: column('name').string(),
  8.     balance: column('balance').numeric(),
  9.     isActive: column('isActive').boolean(),
  10.   })),

  12.   order: x.table('_order').map(({ column }) => ({
  13.     id: column('id').numeric().primary().notNullExceptInsert(),
  14.     orderDate: column('orderDate').date().notNull(),
  15.     customerId: column('customerId').numeric().notNullExceptInsert(),
  16.   })),

  18.   orderLine: x.table('orderLine').map(({ column }) => ({
  19.     id: column('id').numeric().primary().notNullExceptInsert(),
  20.     orderId: column('orderId').numeric(),
  21.     product: column('product').string(),
  22.   })),

  24.   deliveryAddress: x.table('deliveryAddress').map(({ column }) => ({
  25.     id: column('id').numeric().primary().notNullExceptInsert(),
  26.     orderId: column('orderId').numeric(),
  27.     name: column('name').string(),
  28.     street: column('street').string(),
  29.     postalCode: column('postalCode').string(),
  30.     postalPlace: column('postalPlace').string(),
  31.     countryCode: column('countryCode').string(),
  32.   }))

  34. })).map(x => ({
  35.   order: x.order.map(({ hasOne, hasMany, references }) => ({
  36.     customer: references(x.customer).by('customerId'),
  37.     deliveryAddress: hasOne(x.deliveryAddress).by('orderId'),
  38.     lines: hasMany(x.orderLine).by('orderId')
  39.   }))
  40. }));

  42. export default map;
  43. ```
The init.js script resets our SQLite database. It's worth noting that SQLite databases are represented as single files, which makes them wonderfully straightforward to manage.

At the start of the script, we import our database mapping from the map.js file. This gives us access to the db object, which we'll use to interact with our SQLite database.

Then, we define a SQL string. This string outlines the structure of our SQLite database. It first specifies to drop existing tables named 'deliveryAddress', 'orderLine', '_order', and 'customer' if they exist. This ensures we have a clean slate. Then, it dictates how to create these tables anew with the necessary columns and constraints.

Because of a peculiarity in SQLite, which only allows one statement execution at a time, we split this SQL string into separate statements. We do this using the split() method, which breaks up the string at every semicolon.  

📄 init.js
  1. ```javascript
  2. import map from './map';
  3. const db = map.sqlite('demo.db');

  5. const sql = `DROP TABLE IF EXISTS deliveryAddress; DROP TABLE IF EXISTS orderLine; DROP TABLE IF EXISTS _order; DROP TABLE IF EXISTS customer;
  6. CREATE TABLE customer (
  7.     id INTEGER PRIMARY KEY,
  8.     name TEXT,
  9.     balance NUMERIC,
  10.     isActive INTEGER
  11. );

  13. CREATE TABLE _order (
  14.     id INTEGER PRIMARY KEY,
  15.     orderDate TEXT,
  16.     customerId INTEGER REFERENCES customer
  17. );

  19. CREATE TABLE orderLine (
  20.     id INTEGER PRIMARY KEY,
  21.     orderId INTEGER REFERENCES _order,
  22.     product TEXT
  23. );

  25. CREATE TABLE deliveryAddress (
  26.     id INTEGER PRIMARY KEY,
  27.     orderId INTEGER REFERENCES _order,
  28.     name TEXT,
  29.     street TEXT,
  30.     postalCode TEXT,
  31.     postalPlace TEXT,
  32.     countryCode TEXT
  33. )
  34. `;


  37. async function init() {
  38.   const statements = sql.split(';');
  39.   for (let i = 0; i < statements.length; i++) {
  40.     await db.query(statements[i]);
  41.   }
  42. }
  43. export default init;
  44. ```
In SQLite, columns with the INTEGER PRIMARY KEY attribute are designed to autoincrement by default. This means that each time a new record is inserted into the table, SQLite automatically produces a numeric key for the id column that is one greater than the largest existing key. This mechanism is particularly handy when you want to create unique identifiers for your table rows without manually entering each id.

Connecting

__SQLite__
  1. ```bash
  2. $ npm install sqlite3
  3. ```  
  1. ```javascript
  2. import map from './map';
  3. const db = map.sqlite('demo.db');
  4. ```
__With connection pool__
  1. ```bash
  2. $ npm install sqlite3
  3. ```  
  1. ```javascript
  2. import map from './map';
  3. const db = map.sqlite('demo.db', { size: 10 });
  4. ```
__From the browser__  
You can securely use RDB from the browser by utilizing the Express.js plugin, which serves to safeguard sensitive database credentials from exposure at the client level. This technique bypasses the need to transmit raw SQL queries directly from the client to the server. Instead, it logs method calls initiated by the client, which are later replayed and authenticated on the server. This not only reinforces security by preventing the disclosure of raw SQL queries on the client side but also facilitates a smoother operation. Essentially, this method mirrors a traditional REST API, augmented with advanced TypeScript tooling for enhanced functionality. You can read more about it in the section called  In the browser
📄 server.js
  1. ```javascript
  2. import map from './map';
  3. import { json } from 'body-parser';
  4. import express from 'express';
  5. import cors from 'cors';

  7. const db = map.sqlite('demo.db');

  9. express().disable('x-powered-by')
  10.   .use(json({ limit: '100mb' }))
  11.   .use(cors())
  12.   //for demonstrational purposes, authentication middleware is not shown here.
  13.   .use('/rdb', db.express())
  14.   .listen(3000, () => console.log('Example app listening on port 3000!'));
  15. ```

📄 browser.js
  1. ```javascript
  2. import map from './map';

  4. const db = map.http('http://localhost:3000/rdb');
  5. ```
__MySQL__
  1. ```bash
  2. $ npm install mysql2
  3. ```  
  1. ```javascript
  2. import map from './map';
  3. const db = map.mysql('mysql://test:test@mysql/test');
  4. ```


__MS SQL__
  1. ```bash
  2. $ npm install tedious
  3. ```  
  1. ```javascript
  2. import map from './map';
  3. const db = map.mssql({
  4.           server: 'mssql',
  5.           options: {
  6.             encrypt: false,
  7.             database: 'test'
  8.           },
  9.           authentication: {
  10.             type: 'default',
  11.             options: {
  12.               userName: 'sa',
  13.               password: 'P@assword123',
  14.             }
  15.           }
  16.         });
  17. ```

__PostgreSQL__
  1. ```bash
  2. $ npm install pg
  3. ```  
  1. ```javascript
  2. import map from './map';
  3. const db = map.pg('postgres://postgres:postgres@postgres/postgres');
  4. ```
__SAP Adaptive Server__
  1. ```bash
  2. $ npm install msnodesqlv8
  3. ```  
  1. ```javascript
  2. import { fileURLToPath } from 'url';
  3. import { dirname } from 'path';
  4. import map from './map';

  6. const __filename = fileURLToPath(import.meta.url);
  7. const __dirname = dirname(__filename);
  8. //download odbc driver from sap web pages
  9. const db = map.pg(`Driver=${__dirname}/libsybdrvodb.so;SERVER=sapase;Port=5000;UID=sa;PWD=sybase;DATABASE=test`);

  11. ```