https://github.com/craigmichaelmartin/pure-orm Skip to content Sign up * Why GitHub? Features - + Mobile - + Actions - + Codespaces - + Packages - + Security - + Code review - + Issues - + Integrations - + GitHub Sponsors - + Customer stories- * Team * Enterprise * Explore + Explore GitHub - Learn and contribute + Topics - + Collections - + Trending - + Learning Lab - + Open source guides - Connect with others + The ReadME Project - + Events - + Community forum - + GitHub Education - + GitHub Stars program - * Marketplace * Pricing Plans - + Compare plans - + Contact Sales - + Education - [ ] * # In this repository All GitHub | Jump to | * No suggested jump to results * # In this repository All GitHub | Jump to | * # In this user All GitHub | Jump to | * # In this repository All GitHub | Jump to | Sign in Sign up {{ message }} craigmichaelmartin / pure-orm * Notifications * Star 110 * Fork 31 A Node.js SQL toolkit for writing native SQL queries yielding pure business objects MIT License 110 stars 31 forks Star Notifications * Code * Issues 0 * Pull requests 1 * Discussions * Actions * Projects 0 * Wiki * Security * Insights More * Code * Issues * Pull requests * Discussions * Actions * Projects * Wiki * Security * Insights master Switch branches/tags [ ] Branches Tags Could not load branches Nothing to show {{ refName }} default View all branches Could not load tags Nothing to show {{ refName }} default View all tags 7 branches 34 tags Code Clone HTTPS GitHub CLI [https://github.com/c] Use Git or checkout with SVN using the web URL. [gh repo clone craigm] Work fast with our official CLI. Learn more. * Open with GitHub Desktop * Download ZIP Launching GitHub Desktop If nothing happens, download GitHub Desktop and try again. Go back Launching GitHub Desktop If nothing happens, download GitHub Desktop and try again. Go back Launching Xcode If nothing happens, download Xcode and try again. Go back Launching Visual Studio Code Your codespace will open once ready. There was a problem preparing your codespace, please try again. Latest commit @craigmichaelmartin craigmichaelmartin Update README.md ... ccdcdfa Aug 10, 2021 Update README.md ccdcdfa Git stats * 114 commits Files Permalink Failed to load latest commit information. Type Name Latest commit message Commit time examples don't re-create already seen nodes (so the initial will be re-used in... Oct 23, 2019 src nest into highest parent, not lowest Aug 9, 2021 test-utils nest into highest parent, not lowest Aug 9, 2021 .eslintrc.json resolve circular deps; make api more flexible; some error handling Jan 8, 2019 .gitignore extract "sql-toolkit" into this repo Jan 7, 2019 .travis.yml update npm run script reference Jan 22, 2019 CODEOWNERS extract "sql-toolkit" into this repo Jan 7, 2019 LICENSE extract "sql-toolkit" into this repo Jan 7, 2019 README.md Update README.md Aug 10, 2021 package-lock.json 1.3.0 Aug 9, 2021 package.json 1.3.0 Aug 9, 2021 View code [ ] PureORM * Installation What is PureORM? Philosophy Concepts Examples Data Access Object Business Object Usage Comparisons API Classes BaseBo BaseBoCollection BaseDAO Methods createBaseBO({ getBusinessObjects }): BaseBo createBaseDAO({ db, logError }): BaseDAO Current Status Current Limitations (PRs welcome!) Current Todos (PRs welcome!): Is it production ready? README.md PureORM * Build Status codecov Installation npm install --save pure-orm What is PureORM? PureORM is a pure ORM sql toolkit library for node (on top of pg-promise). It allows you to write regular native SQL and receive back properly structured (nested) pure business objects. This contrasts against traditional ("stateful") ORMs which use query builders (rather than raw SQL) to return database-aware (rather than pure) objects. The name pureORM reflects both that it is pure ORM (there is no query builder dimension) as well as the purity of the mapped Objects. Philosophy * Write native, unobstructed SQL in a "data access layer" which returns pure "business objects" to be used in the app's business logic. * Have database-connected "data access objects" which allow the unobstructed writing of normal SQL. * Have the "data access objects" returning the pure business objects. Concepts A Business Object (BO) is a pure javascript object corresponding to a table. * They represent a row of the table data, but as pure javascript objects. * They are not connected to the database. * They are the subject of the app's business logic. * They will be full of userland business logic methods. * Their purity allows them to be easy to test/use. A Data Access Object (DAO) is a database-aware abstraction layer where native SQL is written. * This is not an "expresion language" or "query builder". There are not hundreds of methods mapping the complexity, expressiveness, and nuance of SQL to class objects. * Rather, is a data access layer in which native SQL is written, and which returns business objects (properly nested and structured). * By convention, they may also accept business objects as inputs (to get, create, or update records) - but this is just a convention (necessary input data can be passed as separate arguments, or however). --------------------------------------------------------------------- Examples Data Access Object Our data access layer where SQL is written. class PersonDAO extends BaseDAO { Bo = Person; // example code from below... } Lets start with a basic example which just uses the BaseBO.createOneFromDatabase method to map the column names to our desired javascript properties. getRandom() { const query = ` SELECT person.id, person.first_name, person.last_name, person.created_date, person.employer_id FROM person ORDER BY random() LIMIT 1; `; return db.one(query).then(Person.createOneFromDatabase) } // OUTPUT: Person {id, firstName, lastName, createdDate, employerId} We can use BaseDAO.one to create our business object for us. getRandom() { const query = ` SELECT person.id, person.first_name, person.last_name, person.created_date, person.employer_id FROM person ORDER BY random() LIMIT 1; `; - return db.one(query).then(Person.createOneFromDatabase) + return this.one(query); } // OUTPUT: Person {id, firstName, lastName, createdDate, employerId} Specifying all the columns is tedious; lets use BaseBo.getSQLSelectClause() to get them for free. getRandom() { const query = ` - SELECT person.id, person.first_name, person.last_name, person.created_date, person.employer_id + SELECT ${Person.getSQLSelectClause()} FROM person ORDER BY random() LIMIT 1; `; return this.one(query); } // OUTPUT: Person {id, firstName, lastName, createdDate, employerId} More important than saving the tedium, though, is how BaseBo.getSQLSelectClause() namespaces each select expression name under the hood, and which BaseBo.createOneFromDatabase knows how to handle. This means that when joining, not only is the select expression easy, select expression names won't collide: getRandom() { const query = ` - SELECT ${Person.getSQLSelectClause()} + SELECT ${Person.getSQLSelectClause()}, ${Employer.getSQLSelectClause()} FROM person + JOIN employer on person.employer_id = employer.id ORDER BY random() LIMIT 1; `; return this.one(query); } // OUTPUT: Person {id, firstName, lastName, createdDate, employer: Employer} Rather than being flat, with the employer id and createdDate colliding with person's id and createDate, the result is a nice Person BO with a nested Employer BO. Lets move to a different example to show off another aspect of BaseBo.createOneFromDatabase: how it handles flattening data. Lets say there are three tags for article being retrieved, rather than the data being an array of 3 results with article repeated, the result is a nice Article BO with the tags nested in it. getBySlug(slug) { const query = ` SELECT ${Article.getSQLSelectClause()}, ${Person.getSQLSelectClause()}, ${ArticleTag.getSQLSelectClause()}, ${Tag.getSQLSelectClause()} FROM article JOIN person ON article.author_id = person.id LEFT JOIN article_tags ON article.id = article_tags.article_id LEFT JOIN tag ON article_tags.tag_id = tag.id WHERE article.slug = $(slug); `; return this.one(query, { slug }); } // OUTPUT: Article { person: Person, articleTags: Array } Notice that we're using this.one, which is what we want. The DAO methods for one, oneOrNone, many, any ensure their count against the number of generated top level business objects - not the number of rows the sql expression returns! Lets say we want to get more than one article. We can make slug an array, and BaseBo.createFromDatabase handles it seemlessly, giving us an Articles collections -getBySlug(slug) { +getBySlugs(slugs) { const query = ` SELECT ${Article.getSQLSelectClause()}, ${Person.getSQLSelectClause()}, ${ArticleTag.getSQLSelectClause()}, ${Tag.getSQLSelectClause()} FROM article JOIN person ON article.author_id = person.id LEFT JOIN article_tags ON article.id = article_tags.article_id LEFT JOIN tag ON article_tags.tag_id = tag.id - WHERE article.slug = $(slug); + WHERE article.slug in ($(slugs:csv)); `; - return this.one(query, { slugs }); + return this.many(query, { slugs }); } -// OUTPUT: Article { person: Person, articleTags: Array } +// OUTPUT: Articles[ +// Article { person: Person, articleTags: Array } +// Article { person: Person, articleTags: Array } +// ] Lastly, lets switch gears one more time to see how meta data can be intertwined. Prefix the value as meta_ and it will be passed through to the business object. getBloggerPayout(id, startDate, endDate) { const query = ` SELECT ${Person.getSQLSelectClause()}, COALESCE(SUM(article.blogger_payout), 0) as meta_amount, FROM person LEFT JOIN article ON article.author_id = person.id AND (article.created_date BETWEEN $(startDate) AND $(endDate)) WHERE person.id = $(id) GROUP BY person.id, person.slug, person.email, person.first_name, person.last_name, person.last_paid_date, person.pay_frequency ORDER BY meta_amount DESC NULLS LAST; `; return this.one(query, { id, startDate, endDate }); } Business Object Usage Now lets look at our business logic layer where we use the DAO to get /persist pure data. (This example uses the few included common DAO methods in order to show something. However, in practice you'll mainly be using your own custom functions with your own SQL to do your own interesting things; vs this contrived and basic example.) let raw = new Person({ email: 'foobar@gmail.com', firstName: 'craig', lastName: 'martin' }); const personDAO = new PersonDAO({ db }); // Returns a person business object with the persisted data let person = await personDAO.create(raw); person.email = 'craigmartin@gmail.com'; // Returns a person business object with the updated persisted data person = await personDAO.update(person); // Gets or creates a person business object same = await personDAO.getOrCreate(raw); same.id === person.id; // true // Returns the person business object which matches this data same = await personDAO.getMatching( new Person({ email: 'craigmartin@gmail.com' }) ); same.id === person.id; // true // Deletes the person data form the database await personDAO.delete(person); To see everything in action, check out the examples directory and the tests. --------------------------------------------------------------------- Comparisons Low Level Abstractions * Database Drivers (eg node-postgres, mysql, node-sqlite3) - These are powerful low level libraries that handle connecting to a database, executing raw SQL, and returning raw rows. All the higher level abstractions are built on these. PureORM like "stateful ORMs" are built on these. Stateful ORMs (comprised of two portions) * Query Builders (eg knex) - These (built on database drivers) offer a dialetic-generic, chainable object api for expressing underlying SQL - thus solving for database "lock-in" as well the inability to compose SQL queriers as strings. pure-orm takes the approach that the tradeoff of developers having to learn the huge surface area of dialetic-generic api, and having to map the complexity and nuance of SQL to it, are simply not worth the cost, and so does not use a query building library. With pure-orm you just write SQL. The tradeoff on pure-orms side that is indeed being tied to a sql dialect and in the inability to compose sql expressions (strings don't compose nicely). Yet all this considered, pure-orm sees writing straight SQL heaviliy as a feature, not a defect needing solved, and not eclipsed by the composibility of a query builder. * Stateful, Database Aware Objects (eg sequelize, waterline, bookshelf, typeorm) - These stateful, database-aware object libraries are the full embrace of "Stateful ORMs". Contrary to this these is pure-orm which yields pure, un-attached, structured objects. PureORM * pure-orm is more than just the preference against the query builder portion of Stateful ORMs * pure-orm is the preference against stateful, db-connected objects: pure-orm resolves result rows to pure business objects. This purity in business objects fosters a clean layer of the business layer from the data access layer, as well as ensuring the very best in performance (eg, the N+1 problem can't exist with pure objects). --------------------------------------------------------------------- API Classes BaseBo An abstract class which is the base class your BO classes to extend. Abstract Methods to be implemented * get BoCollection(): BoCollection - Returns the business object collection class constructor. * static get tableName(): string - Returns the string table name which the business object associates with from the database. * static get sqlColumnsData(): Array - Returns an array of the database column data. The type is either: + ColumnData {column, property?, references?, primaryKey?, transform?} o column: string - The sql column name o propery: string - The javascript property name for this column (defaults to camelCase of column) o references: Bo - The relationship to another Bo (defaults to null) o primaryKey: boolean - Is this column (part of) the primary key (defaults to false) o transform: fn - When this data is pulled, a transform that runs on it; eg, creating a momentjs object for dates (defaults to () => {}) + string - If a string, it is applied as the column value, with all others defaulted. + (Note: if there is no primary key, id is defaulted) Optional * static get displayName(): string - Returns the string display name of the business object (defaults to camelcase of tableName) Public Methods * constructor(props: object) * and more BaseBoCollection An abstract class which is the base class your Bo Collection classes extend. Abstract Methods to be implemented * static get Bo(): BO - Returns the individual (singular) business object class constructor. Optional * get displayName(): BO - Returns the string display name of the business object collection (defaults to bo displayName with an "s") Public Methods * constructor(props: object) * and more BaseDAO The base class your DAO classes extend. Abstract Methods to be implemented * get Bo(): BO - Returns the business object class constructor. * get BoCollection(): BO - Returns the collection business object class constructor. Public Methods * constructor({ db }}) Abstractions over pg-promise's query methods: * one(query: string, params: object) - executes a query and returns a Bo, or throws. * oneOrNone(query: string, params: object) - executes a query and returns a Bo or undefined, or throws. * many(query: string, params: object) - executes a query and returns a BoCollection with at least one model, or throws. * any(query: string, params: object) - executes a query and returns a BoCollection. * none(query: string, params: object) - executes a query and returns null. (Note, these methods assert the correct number on the created BO's - not the raw postgres sql result. Thus, for example, one understands that there may be multiple result rows (which pg-promise's one would throw at) but which could correctly nest into one BO.) Built-in "basic" / generic functions which your extending DAO class instance gets for free * getMatching(bo: BaseBO) * getOneOrNoneMatching(bo: BaseBO) * getAnyMatching(bo: BaseBO) * getAllMatching(bo: BaseBO) * create(bo: BaseBO) * update(bo: BaseBO) * delete(bo: BaseBO) * deleteMatching(bo: BaseBO) These are just provided because they are so common and straight-forward. However, the point of this library specifically contrasts against having a large surface area of pre-built functions to learn. The idea is to add a DAO class, and add your own custom functions with your own SQL. Methods createBaseBO({ getBusinessObjects }): BaseBo Parameters * getBusinessObjects: () => Array - A function which returns an array of all the business objects, used to construct joined row data in the business object. Return Value * The BaseBo class to extend for your business objects. createBaseDAO({ db, logError }): BaseDAO Parameters * logError: function * db: pg-promise database Return Value * The BaseDAO class to extend for your business objects. --------------------------------------------------------------------- Current Status Current Limitations (PRs welcome!) * pg-promise/node-postgres is the only database driver supported. There is not technical reason for this, other than that the project I'm using has a postgres database and so I only had node-postgres in mind. It would be great if pure-orm was database driver agnostic. * the dao you are writing your sql in must always be in the "select" and must be the one you want as your root(s) return objects + the query can start from some other table, and join a bunch of times to get there, though * there must be a clear path in the "select" to your leaf joined-to-entities (eg, (Good): Article, ArticleTag, Tag, TagModerator, Moderator; not (Bad): Article, Moderator). * the result of the select must always be a non-circular tree (eg, (Bad): Article, Person, Group, GroupArticle, Article) Current Todos (PRs welcome!): * Performance. While the API has been somewhat thought through and iterated on to this point, the implementation details have been secondary, knowing that they can be perfected in time. Probably about time now. * Add more tests * Known Bug: if a table references the same table twice, the first one is found as the nodePointingToIt and so ends up throwing. + ideally the fix to this will change the behavior of when a table points to another table by another name (author_id -> person) * Think about how to handle the none case of oneOrNone, any, and none Is it production ready? It is in production at www.kujo.com - powering the marketing pages and blog, as well as the customer, affiliate, and admin platforms (behind login). When considering for your case, note the Current Limitations and TODOs sections above. About A Node.js SQL toolkit for writing native SQL queries yielding pure business objects Topics nodejs sql orm toolkit postgresql pg-promise node-postgres data-access-object-pattern results-resolver Resources Readme License MIT License Releases 34 tags Packages 0 No packages published Contributors 3 * @craigmichaelmartin craigmichaelmartin Craig Martin * @greenkeeper[bot] greenkeeper[bot] * @byted byted Stefan Selent Languages * JavaScript 100.0% * (c) 2021 GitHub, Inc. * Terms * Privacy * Security * Status * Docs * Contact GitHub * Pricing * API * Training * Blog * About You can't perform that action at this time. You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session.