-
RDP2.0-Aurora
- Query Builder
-
DBModel System
- Declaring Class
- Creating new instances
- Setting a column value
- Setting multiple column values by passing in an object
- Getting a column value from an instance
- Saving the changes.
- Deleting the object in DB
- Declaring special column types
- Declaring default values
- Declaring read only classes
- Declaring dummy objects
Table of contents generated with markdown-toc
RDP2.0-Aurora
Aurora npm package for RDP2.0 products
Install
npm install @reddotpay/rdp2.0-aurora mysql lodash moment
npm install aws-sdk --save-dev
Requirements
AWS Role can refer to Policy below.
Policy
BackendFunctionRole
Type: AWS::IAM::Role
Properties:
AssumeRolePolicyDocument:
Version: '2012-10-17'
Statement:
- Effect: Allow
Action:
- 'sts:AssumeRole'
Principal:
Service:
- lambda.amazonaws.com
Policies:
- PolicyName: {Product}BackendFunctionRole
PolicyDocument:
Version: '2012-10-17'
Statement:
- Effect: Allow
Action: 'secretsmanager:GetSecretValue'
Resource: '*'
Usage
Primary Function
Examples
const AWSXRAY = require('aws-xray-sdk');
const AWS = AWSXRAY.captureAWS(require('aws-sdk'));
const { aurora, auroraConfig } = require('aurora');
auroraConfig
.setDefaultDb('db_name')
.registerDb('db_name', {
// you can provide an AWS secret ARN that stores the DB configuration information
secretArn: 'secretArn',
// alternatively, you can provide the following information SQL information to connect to MySQL
host: 'url',
port: 3306,
username: 'user',
password: 'password',
})
// you can provide a custom log handler below. if not provided, logs will be outputted on console.log
.setLogger({
log: console.log,
error: console.error,
debug: console.log, // if provided, every SQL command will be logged
})
// you can provide an AWS object to use when getting secrets manager.
// if not provided, it will use the base aws-sdk package
.setAWS(AWS);
// fetch tableOne row given a tableOneId, and get the last update
// if tableOne is not found, return null.
const fetchTableOneLastUpdate = async (testTableId) => {
try {
const sql = "select * from ?? where ?? = ?"; // use ?? for column or table names, use ? for values
const ret = await aurora.query(sql, ['tableOne', 'tableOneId', testTableId]);
if (ret.length > 0) {
const row = ret[0];
const updatedAt = row.updated_at; // this will be a Date object
return updatedAt;
}
return null;
} catch (err) {
throw err;
}
};
// set the selected tableTwo and tableOne row's updated_at field to current timestamp
const updateInTransaction = async (testTableTwoId, testTableId) => {
try {
await aurora.begin();
const now = new Date();
const updateSql = "update ?? set ?? = ? where ?? = ?";
const args = ['tableOne', 'updated_at', now, 'tableOneId', testTableId];
await aurora.query(sql, args);
const updateTable2 = "update ?? set ?? = ? where ?? = ?";
await aurora.query(sql, ['tableTwo', 'updated_at', now, 'tableTwoId', testTableTwoId]);
await aurora.commit();
} catch (err) {
await aurora.rollback();
throw err;
}
};
Query Builder
Creating a new Query Builder. NOTE: you will need to await to create the builder.
const { QueryBuilder } = require('aurora');
// creating a query builder that will select from "test" table
const testQueryBuilder = await QueryBuilder.CreateBuilder('test');
// select * from test
const ret = await testQueryBuilder.exect();
Selecting specific columns
const { QueryBuilder } = require('aurora');
const testQueryBuilder = await QueryBuilder.CreateBuilder('test');
// to only select specific columns.
testQueryBuilder.selectColumn('id');
// select id, column_a, desc, asc from test
const ret = await testQueryBuilder.exect();
Selecting aggregate columns
// by default, this will select all columns. To only select 1 column, e.g count(*)
testQueryBuilder.select('count(*)');
Conditions
Comparators, and what they get translated into
// condition (third argument, by default is '=')
// do not use this to compare strings. use conditionStrCmp instead (see below)
testQueryBuilder.condition('id', 5); // WHERE id = 5
testQueryBuilder.condition('id', 5, '<>'); // WHERE id <> 5
testQueryBuilder.condition('id', 5, '>'); // WHERE id > 5
// string comparison
// this is used to check if the column value is the same as a provided string
// use this instead of using condition() function for strings
testQueryBuilder.conditionStrCmp('name', 'bob'); // WHERE STRCMP('name', 'bobby') = 0
testQueryBuilder.conditionStrCmp('name', 'bob', '<>'); // WHERE STRCMP('name', 'bobby') <> 0
// check if column value is between 2 values
testQueryBuilder.conditionBetween('id', 1, 100); // WHERE id BETWEEN 1 AND 100
// check if column value is between 2 values. if only 1 value is provided, do a direct comparison
conditionRange.conditionRange('date', { // WHERE date BETWEEN '2020-01-01 00:00:00' AND '2020-01-15 23:59:59'
start: '2020-01-01 00:00:00',
end: '2020-01-15 23:59:59',
});
testQueryBuilder.conditionRange('date', { // WHERE date >= '2020-01-01 00:00:00'
start: '2020-01-01 00:00:00',
});
testQueryBuilder.conditionRange('id', { // WHERE date <= '2020-01-15 23:59:59'
end: '2020-01-15 23:59:59',
});
// check if column value exists inside an array of values.
// if the array is empty, will be automatically evaluated to FALSE
testQueryBuilder.conditionRange('id', [1, 2, 3]); // WHERE id IN (1, 2, 3);
testQueryBuilder.conditionRange('id', []); // WHERE FALSE.
// check if column value contains provided substring
testQueryBuilder.conditionLike('name', '%bob%'); // WHERE name LIKE '%bob%'
testQueryBuilder.conditionBeginsWithStr('name', 'bob'); // WHERE name LIKE 'bob%'
testQueryBuilder.conditionEndsWithStr('name', 'bob'); // WHERE name LIKE '%bob'
testQueryBuilder.conditionContainsStr('name', 'bob'); // WHERE name LIKE '%bob%'
// check if column value is null
testQueryBuilder.conditionIsNull('description'); // WHERE description IS NULL
// custom expression (for complex conditions)
testQueryBuilder.customExpr('DATE_ADD(??, INTERVAL 5 HOURS) <= ?', [ 'created_date', '2020-01-01 00:00:00' ]); // WHERE DATE_ADD(`created_date`, INTERVAL 5 HOURS) <= '2020-01-01 00:00:00'
Negation
To negate, use the not
function, followed by the condition expression.
// WHERE NOT(`id` IS NULL)
testQueryBuilder.not();
testQueryBuilder.conditionIsNull('id');
// WHERE NOT(`created_at` between '2020-01-01' and '2020-01-02')
testQueryBuilder.not();
testQueryBuilder.conditionBetween('created_at', '2020-01-01', '2020-01-02');
Block Expressions
By default, multiple where expressions are grouped together using the 'AND' clause.
// WHERE id >= 10 AND created_at < '2020-01-01'
queryBuilder.condition('id', 10, '>=');
queryBuilder.condition('created_at', '2020-01-01', '<');
You can also explicitly declare a AND
block.
// WHERE id >= 10 AND created_at < '2020-01-01'
queryBuilder.startAndBlock()
queryBuilder.condition('id', 10, '>=');
queryBuilder.condition('created_at', '2020-01-01', '<');
queryBuilder.endBlock();
To create a block where expressions are grouped together using the 'OR' clause, use startOrBlock
// WHERE id < 10 OR id > 20
queryBuilder.startOrBlock()
queryBuilder.condition('id', 10, '<');
queryBuilder.condition('id', 20, '>');
queryBuilder.endBlock();
You can also negate a whole block expression
// WHERE NOT ( id >= 10 AND created_at < '2020-01-01' )
queryBuilder.not();
queryBuilder.startAndBlock()
queryBuilder.condition('id', 10, '>=');
queryBuilder.condition('created_at', '2020-01-01', '<');
queryBuilder.endBlock();
// WHERE NOT (id < 10 OR id > 20)
queryBuilder.not();
queryBuilder.startOrBlock()
queryBuilder.condition('id', 10, '<');
queryBuilder.condition('id', 20, '>');
queryBuilder.endBlock();
Complex Block Expressions
From block expressions, you can create sub block expressions, and create complex query expressions
// WHERE
// ( id >= 10 AND created_at < '2020-01-01' )
// OR
// NOT (id < 10 OR id > 20)
queryBuilder.startOrBlock();
queryBuilder.startAndBlock()
queryBuilder.condition('id', 10, '>=');
queryBuilder.condition('created_at', '2020-01-01', '<');
queryBuilder.endBlock();
queryBuilder.not();
queryBuilder.startOrBlock()
queryBuilder.condition('id', 10, '<');
queryBuilder.condition('id', 20, '>');
queryBuilder.endBlock();
querBuilder.endBlock();
Locking query results. (for handling in transaction)
// SELECT * FROM test FOR UPDATE
queryBuilder.lock();
Limit Results
// SELECT * FROM test LIMIT 100
queryBuilder.limit(100);
Pagination
// SELECT * FROM test LIMIT 0, 100
const pageSize = 100;
const pageNum = 1; // get pagea 1
queryBuilder.page(pageSize, pageNum);
// SELECT * FROM test LIMIT 100, 100
const pageSize = 100;
const pageNum = 2; // get page 2 (the 101st entry onwards)
queryBuilder.page(pageSize, pageNum);
Pagination Optimization
When the page number is very huge, executing the query results in a very huge query time.
When this happens, use the useOffsetOptimization
function.
For this to work, you will need to pass in the primary key for the table due to the nature of the optimization.
// Generated statement:
// SELECT * FROM test
// INNER JOIN ( SELECT pk_col from test ORDER BY date_created DESC LIMIT 300000, 100 ) t2
// ON test.pk_col = t2.pk_col
// ORDER BY date_created DESC
const pageSize = 100;
const pageNum = 3000; // get page 3000 (the 300,001 st entry onwards)
queryBuilder.page(pageSize, pageNum);
queryBuilder.orderByDesc('date_created');
queryBuilder.useOffsetOptimization(['pk_col']);
ORDER BY
queryBuilder.orderBy('id'); // ORDER BY id DESC
queryBuilder.orderBy('id', true); // ORDER BY id ASC
queryBuilder.orderBy('id', false); // ORDER BY id DESC
queryBuilder.orderByDesc('id'); // ORDER BY id DESC
queryBuilder.orderByAsc('id'); // ORDER BY id ASC
Executing the Query
After your query builder has finished building the query, use exec
function to execute the query.
const builder = await QueryBuilder.CreateBuilder('test');
...
// create the rest of the query here
...
const ret = await builder.exec();
You can also just get the resultant SQL statement and arguments without executing the query.
const queryBuilder = await QueryBuilder.CreateBuilder('test');
// create the rest of the query here
const queryInfo = queryBuilder.getParamSql();
const { sql, varList } = queryInfo; // get the SQL here.
const ret = await aurora.query(sql, varList);
Chaining Queries
You can chain the queries together to quickly form the SQL expression
const queryBuilder = await QueryBuilder.CreateBuilder('test');
// SELECT `id`, `cost` from `test`
// WHERE
// ( `id` < 5 or `id` > 10 ) AND `description` IS NOT NULL
// LIMIT 20, 10
// ORDER BY `status` DESC, `id` ASC
const ret = await queryBuilder
.select('id')
.select('cost')
.startOrBlock()
.condition('id', 5, '<')
.condition('id', 10, '>')
.endBlock()
.not().conditionIsNull('description')
.page(10, 3) // limit by 10, get page 3
.orderByDesc('status')
.orderByAsc('id')
.exec();
DBModel System
DBModel system is a simple ORM for MySQL. Each DBModel class, should correspond to 1 DB table.
Declaring Class
For example, if you have a class test
, you first have to create a file test.js
, and declare it to extend DBModelBase
Example test.js contents:
// test.js
const { DBModelBase } = require('@reddotpay/rdp2.0-aurora');
class Test extends DBModelBase {
// (OPTIONAL) declare the database name here.
// if this is not provided, will default to the value that is set using setDefaultDb
// when setting aurora config.
static GetDBName() {
return 'test_db';
}
// declare the table name here
static GetTableName() {
return 'test';
}
// declare the primary key columns in the table here.
static GetPrimaryKeys() {
return ['pk_col_1', 'pk_col_2', 'pk_col_3'];
}
}
Creating new instances
There are 2 main ways to create a new instance.
- from DB fetch
- when creating new instance. (e.g when you want to prepare to insert into DB)
Creating instances from DB query fetch.
To create instances from DB query fetch, use query builder.
const Test = require('./test');
// select * from test where pk_col_1 = 1 and pk_col2 = 2;
const builder = await Test.GetBuilder();
builder
.condition('pk_col_1', 1)
.condition('pk_col_2', 2);
const testList = await Test.QueryFetch(builder);
testList.forEach((test) => {
console.log(test instanceOf Test); // true
});
You can also request to lock the rows for the next query if you are in a transaction
const Test = require('./test');
// select * from test where pk_col_1 = 1 and pk_col2 = 2 FOR UPDATE;
const builder = await Test.GetBuilder();
builder
.condition('pk_col_1', 1)
.condition('pk_col_2', 2);
Test.LockNext();
const testList = await Test.QueryFetch(builder);
Creating new instances
Use NewFromObject function to create a new instance, and pass in an object with the column values. Note that if the declared primary keys' values are not provided, an error will be thrown.
const Test = require('./test');
const test = await Test.NewFromObject({
pk_col_1: 1,
pk_col_2: 2,
pk_col_3: 3,
col_4: 'column_4_value',
col_5: 'column_5_value',
});
Setting a column value
NOTE: DO NOT OVERWRITE A PRIMARY KEY VALUE
test.set('column_name', 'new_column_value');
Setting multiple column values by passing in an object
NOTE: DO NOT OVERWRITE PRIMARY KEY VALUES
test.setFromObject({
column_1: 'new_value1',
column_2: 'new_value2'
});
Getting a column value from an instance
const value = test.get('column_name');
You can also get the original value when it is fetched from DB.
Assume there is a row on DB with the following values: pk_col_1: 1 pk_col_2: 2 pk_col_3: 3 column_name: 'original_value'
const Test = require('./test');
const builder = await Test.GetBuilder();
builder
.condition('pk_col_1', 1)
.condition('pk_col_2', 2)
.condition('pk_col_3', 3);
const testList = await Test.QueryFetch(builder);
const test = testList[0];
const value = test.get('column_name'); // original_value
test.set('column_name', 'new_value');
const newValue = test.get('column_name'); // new_value
const originalValue = test.get('column_name', true); // original_value
Saving the changes.
After you made the changes, you can request to save the changes into DB
await test.save();
If the object is created using NewFromObject
, the row will be inserted to the DB.
If the object is created using QueryFetch
, the row in the the DB will be updated.
Deleting the object in DB
test.delete();
await test.save();
There is also a saveAll
function in aurora, which allows saving of all aurora objects.
const { aurora } = require('@reddotpay/rdp2.0-aurora');
const Test = require('./test');
const test1 = // create test 1 instance
const test2 = // create test 2 instance
const test3 = // create test 3 instance
await aurora.saveAll();
The above is equivalent to
await test1.save();
await test2.save();
await test3.save();
Declaring special column types
You can declare special column types in your class, that will have special handling.
class Test extends DBModelBase {
... // other sfuff
// declare this optional function
static GetSpecialColumnType() {
return {
// column name : type
primary_key_column: 'uuid'
column_1: 'created-date',
column_2: 'update-date',
column_3: 'bool',
column_4: 'json'
};
}
}
Types are as below
- uuid
- Only for primary key columns
- When creating a new object, a uuid v4 will be automatically generated and set into this column.
- created-date
- When creating a new object, the current timestamp will be automatically assigned and set into this column.
- update-date
- When creating a new object, the current timestamp will be automatically assigned and set into this column.
- When saving into DB, this column's value will automatically be assigned to the current timestamp
- bool
- On MySQL, boolean types are stored as TINYINT. (1 or 0 values)
- When getting the column's value using the
get
method, will automatically cast the value into a true/false boolean value. - When setting the column's value using the
set
method, allows passing in a 'true/false' boolean value.
- json
- When getting the column's value using the
get
method, will automatically parse the string value and convert into a js object. - When setting the column's value using the
set
method, allows passing in a js object, will automatically do a stringify before saving.
- When getting the column's value using the
// example
class Test extends DBModelBase {
... // other sfuff
// declare this optional function
static GetSpecialColumnType() {
return {
// column name : type
primary_key_column: 'uuid'
column_1: 'created-date',
column_2: 'update-date',
column_3: 'bool',
column_4: 'json'
};
}
}
// assume test object has the following values:
// column_3: 1
// column_3_not_declared: 1
// column_4: "{\"key1\":\"value\"}"
// column_4_not_declared: "{\"key1\":\"value\"}"
// a = true
const a = test.get('column_3');
// b = 1
const b = test.get('column_3_not_declared');
// c = { key: 'value' }
// typeof c = 'object'
const c = test.get('column_4');
// d = "{\"key1\":\"value\"}"
// typeof d = 'string'
const d = test.get('column_4_not_declared');
const test2 = await Test.newFromObject({});
test2.get('primary_key_column'); // will be an automatically generated uuid
test2.get('created_date'); // will be the current date time
test2.get('update_date'); // will be the current date time
Getting all the column and values in an object
const values = test.getDisplayValues();
class Test extends DBModelBase {
... // other sfuff
// declare this optional function
static GetSpecialColumnType() {
return {
column_2: 'bool',
column_3: 'json'
};
}
}
Example
// assume test object has the following values:
// primary_key: "key_value"
// column_1: 1
// column_2: 0
// column_4: "{\"key1\":\"value\"}"
const values = test.getDisplayValues();
// values = {
// primary_key: 'key_value',
// column_1: 1,
// column_2: false,
// column_3: {
// key1: 'value'
// },
// }
Declaring default values
You can also declare default values which will be set automatically when creating new object.
class Test extends DBModelBase {
...// other declarations here
// declare this optional function
static DefaultValues() {
return {
// column name : default value
column_1: {},
column_2: 'value',
column_3: 12345,
};
}
static GetSpecialColumnType() {
return {
// column name : type
column_1: 'json',
};
}
}
const test = await Test.newFromObject({ primary_key_column: 1 });
test.get('column_1'); // {}
test.get('column_2'); // "value"
test.get('column_3'); // 12345
Declaring read only classes
You can declare read-only classes. All object instances will not be saved into DB.
class Test extends DBModelBase {
// rest of the declarations
// declare this function
static IsReadOnly() {
return true;
}
}
Declaring dummy objects
You can declare dummy objects. These will not be saved into DB.
const { aurora } = require('@reddotpay/rdp2.0-aurora');
const Test = require('./test');
const test1 = // create test 1 instance
const test2 = // create test 2 instance
const test3 = // create test 3 instance
test1.setIsDummy();
await test1.save(); // NOTHING HAPPENS
await aurora.saveAll(); // ONLY test2 and test3 are saved