npm package discovery and stats viewer.

Discover Tips

  • General search

    [free text search, go nuts!]

  • Package details

    pkg:[package-name]

  • User packages

    @[username]

Sponsor

Optimize Toolset

I’ve always been into building performant and accessible sites, but lately I’ve been taking it extremely seriously. So much so that I’ve been building a tool to help me optimize and monitor the sites that I build to make sure that I’m making an attempt to offer the best experience to those who visit them. If you’re into performant, accessible and SEO friendly sites, you might like it too! You can check it out at Optimize Toolset.

About

Hi, 👋, I’m Ryan Hefner  and I built this site for me, and you! The goal of this site was to provide an easy way for me to check the stats on my npm packages, both for prioritizing issues and updates, and to give me a little kick in the pants to keep up on stuff.

As I was building it, I realized that I was actually using the tool to build the tool, and figured I might as well put this out there and hopefully others will find it to be a fast and useful way to search and browse npm packages as I have.

If you’re interested in other things I’m working on, follow me on Twitter or check out the open source projects I’ve been publishing on GitHub.

I am also working on a Twitter bot for this site to tweet the most popular, newest, random packages from npm. Please follow that account now and it will start sending out packages soon–ish.

Open Software & Tools

This site wouldn’t be possible without the immense generosity and tireless efforts from the people who make contributions to the world and share their work via open source initiatives. Thank you 🙏

© 2024 – Pkg Stats / Ryan Hefner

sql-dao

v1.4.1

Published

database access objects for sql databases

Downloads

13

Readme

npm version Dependencies devDependencies Status

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

  1. Features
  2. Installation
  3. Example DatabaseAccessObject
  4. Methods (CRUD & Validate)
  5. Custom Queries
  6. Relations
  7. Transactions
  8. Generator
  9. 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, ...)
  • Extensible: add own validators, databases, etc.
  • Supports transactions (rollback)
  • Generate the DAO directly from your database schema

TODOs

  • Generate relations
  • Add more validators (e.g. date)
  • Add more databases (for now only MySQL)

Installation

npm install sql-dao

Example DatabaseAccessObject

Just extends the DatabaseAccessObject

const DatabaseAccessObject = require('sql-dao').DatabaseAccessObject
// ... more includes ...

class Example extends DatabaseAccessObject {
  // 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 = new Example()
example.name = 'Test'
await example.insert()
console.log('inserted with PK: ' + example.id)

Read

// find all
let examples = await Example.find()

// find some
let whereClause = new WhereClause('?? = ?', ['name','Test']) // will prepare params
let examples2 = await Example.find(whereClause)

// use model as search template
let example = new Example()
example.name = 'Test'
let examples3 = await example.search()

Update

let example = new Example()
example.id = 1 // PrimaryKey
example.name = 'Test2'
await example.update()

/*
 * create or on duplicate update
 * e.g. when name is unique constraint in db
 */ 
let example = new Example()
example.name = 'Test2'
await example.save()

Delete

let example = new Example()
example.id = 1 // PrimaryKey
await example.delete()

Validate

let example = new Example()
example.name = 'Test'
if (example.validate()) {
  example.save()
} else {
  console.error(example.errors.join("\n"))
}

Custom Queries

You could also send a custom query in the model like this:

class Order extends DatabaseAccessObject {
  findLastId () {
    const db = this.getDatabaseConnection()
    let query = 'SELECT MAX(id) FROM ?? WHERE active = ?'
    query = db.prepareQuery(query, ['order', 1])
    const result = await db.sendQuery(query)
    // parse query
  }

  // ...
}

Relations

For defining relations you can override the getRelations method.

Example:

erm-image

// ...
class Order extends DatabaseAccessObject {
  /**
   * @returns {Relation[]}
   */
  static getRelations () {
    return [
      new RelationBelongsTo('shop', 'shopId', Shop, 'id'),
      new RelationHasOne('customer', 'customerId', Customer, 'id'),
      new RelationHasMany('remarks', 'id', Remark, 'orderId'),
      new RelationManyMany('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 = Example.getDatabaseConnection()
let transaction = dbConn.createTransaction()
let example1 = new Example()
let example2 = new Example()
try {
  await example1.insert(transaction)
  await example2.insert(transaction)
  await dbConn.commitTransaction(transaction)
} catch (e) {
  await dbConn.rollbackTransaction(transaction)
}

Generator

First create a config file for database (see https://www.npmjs.com/package/mysql):

// just an example
module.exports = {
  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 (otherwise all)
  -d, --destination <path>  path where files should be created (default: ".")
  -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

Set up test database:

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