node-sql-dao
Data access objects providing an abstract interface to persistence. So you can use them as usual Objects (in OOP) and simply call the "save" method to store them, without messing around with database specific things like SQL statements.
Table of Contents
- Features
- Installation
- Example DatabaseAccessObject
- Methods (CRUD & Validate)
- Relations
- Transactions
- Generator
- Contributing & Development
Features
- The abstract
DatabaseAccessObject
class providing easy methods for CRUD (create, read, update, delete) - Also create, read, update, delete relations
- Model validation (required, length, numeric, ...)
- Extendible: add own validators, databases, etc.
- support transactions (rollback)
- Generate the DAO directly from your database schema
TODOs
- Generate relations
- Date-Validator
- Add more databases (for now only MySQL)
Installation
npm install sql-dao
Example DatabaseAccessObject
Just extends the DatabaseAccessObject
const DatabaseAccessObject = DatabaseAccessObject// ... more includes ... // override the abstact methods
Take a look on a complete file: ./Example.js
Methods (CRUD & Validate)
The DatabaseAccessObject provides easy methods for CRUD.
Create
let example = examplename = 'Test'await exampleconsole
Read
// find alllet examples = await Example // find somelet whereClause = '?? = ?' 'name''Test' // will prepare paramslet examples2 = await Example
Update
let example = exampleid = 1 // PrivateKeyexamplename = 'Test2'await example /* * create or on duplicate update * e.g. when name is unique constraint in db */ let example = examplename = 'Test2'await example
Delete
let example = exampleid = 1 // PrivateKeyawait example
Validate
let example = examplename = 'Test'if example example else console
Relations
For defining relations you can override the getRelations
method.
Example:
// ... /** * @returns */ static { return 'shop' 'shopId' Shop 'id' 'customer' 'customerId' Customer 'id' 'remarks' 'id' Remark 'orderId' 'items' 'id' Item 'id' 'item_order' 'orderId' 'itemId' } // ...
Complete file: ./example/Order.js
Notices about relations
⚠️ Don't create recursive relations (e.g. belongsTo in Order & hasOne in Shop)
find
- will fetch any referenced objects
insert
- will insert any new referenced objects and relations (ManyMany)
- will update existing referenced objects
update
- will insert referenced objects with undefined primary key
- will update referenced objects with defined primary key
- will delete "hasMany" referenced objects (when removed from array)
- will delete "ManyMany" relations (when removed from array)
- will not delete missing referenced objects on "hasOne" or "belongsTo"
delete
- only deletes "hasMany" and relations from "ManyMany", rest could be used somewhere else
- for other you can override the beforeDelete/afterDelete methods
save
- will save (insert on duplicate update) referenced objects
- will delete "hasMany" referenced objects (when removed from array)
- will delete "ManyMany" relations (when removed from array)
- will not delete missing referenced objects on "hasOne" or "belongsTo"
Transactions
/* * When an statement fails, rollback previous statements */let dbConn = Examplelet transaction = dbConnlet example1 = let example2 = try await example1 await example2 await dbConn catch e await dbConn
Generator
First create a config file for database (see https://www.npmjs.com/package/mysql):
// just an examplemoduleexports = host: '127.0.0.1' user: 'root' password: '' database: 'dao_example'
Then call the generator script.
Usage:
Usage: gen-mysql [options] Options: -c, --config <path> path to db config file -t, --table <name> table name -d, --destination <path> path where files should be created -h, --help output usage information
Example:
$ node ./node_modules/sql-dao/gen-mysql.js -c ./config/db.config.js -t example -d ./lib
For an example output see ./Example.js
Contributing & Development
Style
https://github.com/standard/standard
Testing
Run mocha tests:
npm test
Check code coverage (creates "./coverage/index.html"):
npm run-script cover
Release
Using: https://github.com/conventional-changelog/standard-version
On master branch:
npm run release