sql-dao-generator

Generate data-access-objects from your domain-objects.

Generates sql-schema, sql-control, type definitions, query functions, tests, and bundles it all up into daos with a single command!

Goals
Installation
Usage
Commands
- sql-dao-generator generate
- sql-dao-generator help [COMMAND]
Contribution

Goals

The goal of sql-dao-generator is to use the domain-objects you've already defined in order to speed up development and eliminate errors. This is done by composing several libraries that already do the individual steps and bundling them up into a fully functional data-access-object.

This includes:

generating sql schema resources with sql-schema-generator
generating sql control config for use with sql-schema-control
generating standard queries for each domain object (e.g., upsert, findById, findByUnique) and leaving an easily extensible pattern
generating typescript type definitions for each sql resource and query with sql-code-generator

extracting the domain information you've already encoded in your domain-objects using domain-objects-metadata.

This enables:

creating a fully functional data-access-object, using best practices, simply by defining your domain-objects
easily extending your generated data-access-objects, because you control the code completely
instantly leveraging the best practices and safety features implemented in the libraries that this library composes

Like an ORM, but without any magic or limitations - the code is in your hands and ready to mod as needed.

Installation

1. Save the package as a dev dependency

npm install --save-dev sql-dao-generator

2. Define a config yml

This file will define which domain-objects you want to generate data-access-objects for - as well as where we can find the config for the libraries this one composes.

For example:

# codegen.sql.dao.yml

language: postgres
dialect: 10.7
for:
  objects:
    search:
      - 'src/domain/objects/*.ts'
    exclude:
      - 'TrainLocatedEvent' # we track this one in dynamodb, so no sql dao needed
generates:
  daos:
    to: src/data/dao
    using:
      log: src/util/log#log
      DatabaseConnection: src/util/database/getDbConnection#DatabaseConnection
  schema:
    config: codegen.sql.schema.yml
  control:
    config: provision/schema/control.yml
  code:
    config: codegen.sql.types.yml

3. Test it out!

  $ npx sql-dao-generator version
  $ npx sql-dao-generator generate

Examples

a simple literal's dao

Input: Say you have the following domain object

export interface Geocode {
  id?: number;
  latitude: number;
  longitude: number;
}
export class Geocode extends DomainLiteral<Geocode> implements Geocode {}

Output: Running this sql-dao-generator on this domain object will:

generate the sql-schema-generator sql-schema-definition-object

import { prop, Literal } from 'sql-schema-generator';

export const geocode = new Literal({
  name: 'geocode',
  properties: {
    latitude: prop.NUMERIC(),
    longitude: prop.NUMERIC(),
  }
})

run sql-schema-generator on the generated sql-schema-definition-object to generate the sql-schema-resources

generate the sql-schema-control control file, domain-objects.ts, to control the generated sql-schema-resources

# geocode
- type: resource
  path: ./tables/geocode.sql
- type: resource
  path: ./functions/upsert_geocode.sql

generate the dao files

geocodeDao/index.ts

import { findById } from './findById';
import { findByUnique } from './findByUnique'
import { upsert } from './upsert';

export const geocodeDao = {
  findById,
  findByUnique,
  upsert,
}

geocodeDao/findById.query.ts

export const sql = `
-- query_name = find_geocode_by_id
select
  g.id,
  g.latitude,
  g.longitude
from geocode g
where g.id = :id
`.trim();

export const findById = async ({
  dbConnection,
  id,
}: {
  dbConnection: DatabaseConnection;
  id: number;
}) => {
  const results = await sqlQueryFindGeocodeById({
    dbExecute: dbConnection.query,
    logDebug: log.debug,
    input: { id },
  });
  if (results.length > 1) throw new Error('should only be one');
  if (!results.length) return null;
  return fromDatabaseObject({ dbObject: results[0] });
};

geocodeDao/findByUnique.query.ts

 export const sql = `
 -- query_name = find_geocode_by_unique
 select
   g.id,
   g.latitude,
   g.longitude
 from geocode g
 where 1=1
   and g.latitude = :latitude
   and g.longitude = :longitude
 `.trim();

 export const findByUnique = async ({
   dbConnection,
   latitude,
   longitude,
 }: {
   dbConnection: DatabaseConnection;
   latitude: string;
   longitude: string;
 }) => {
   const results = await sqlQueryFindGeocodeByUnique({
     dbExecute: dbConnection.query,
     logDebug: log.debug,
     input: { latitude, longitude },
   });
   if (results.length > 1) throw new Error('should only be one');
   if (!results.length) return null;
   return fromDatabaseObject({ dbObject: results[0] });
 };

geocodeDao/upsert.query.ts

export const upsert = async ({
  dbConnection,
  geocode,
 }: {
   dbConnection: DatabaseConnection;
   geocode: Geocode;
 }) => {
   const result = await sqlQueryUpsertGeocode({
     dbExecute: dbConnection.query,
     logDebug: log.debug,
     input: {
       latitude: geocode.latitude,
       longitude: geocode.longitude,
     },
   });
   const id = result[0].id;
   return new Geocode({ ...geocode, id }) as HasId<Geocode>;
 };

geocodeDao/utils/fromDatabaseObject.ts

export const fromDatabaseObject = async ({
   dbConnection,
   dbObject,
 }: {
   dbConnection: DatabaseConnection;
   dbObject: SqlQueryFindGeocodeByIdOutput;
 }) => {
   return new Geocode({
     id: dbObject.id,
     latitude: dbObject.latitude,
     longitude: dbObject.longitude,
   });
 };

run sql-code-generator on the generated sql-schema-resources and dao query-files to output the typescript sql-type-definitions and sql-query-functions

Features

Guard Rails

The sql-dao-generator has many guardrails in place to make sure that you're following best practices and avoiding potential maintainability problems.

Specifically:

unique + updatable properties need to be specified or not depending on the domain-object variant
- domain-literals may not explicitly specify unique or updatable properties, since these are defined implicitly by the definition of a literal
  - i.e., nothing is updatable
  - i.e., unique on all natural properties
- domain-entities must specify at least one key on which they are unique and must explicitly specify the list of updatable properties (even if the list is empty)
- domain-events must specify at least one key on which they are unique
properties which reference domain-objects must be named after the domain-object they reference
- this makes sure that properties are easy to understand when reading code -> increasing maintainability
  - makes it easier to understand relationships for new folks looking at your project
  - makes it easier to understand relationships for yourself when you come back to your project after a while and don't remember everything
- for example:
  - allowed:
    - address: Address
    - homeAddress: Address
  - not allowed:
    - home: Address
domain-entities should not be nested in other domain objects; they should use implicit uuid references instead
- experience has shown that nesting domain-entities inside of other domain-objects results in maintainability issues and complexity
  - this is because, in the backend
    - we typically do not have the state of the nested domain-entity in memory already when dealing with the domain-object that references it
    - the domain-entity being referenced has its own lifecycle and it's state typically needs to be explicitly managed with its own logic
  - note, in the frontend, the opposite is typically true. nesting domain-entities inside of other domain-objects is a common way to simplify your code in the frontend. (just not in the backend)
- instead, this library allows you to achieve the same database foreign key constraints without explicitly nesting domain-entities inside of other domain-objects, by using implicit uuid references
  - e.g., instead of user: User use userUuid: string
  - this library takes care of creating the foreign key in the db and mapping uuid <-> id to and from the database in the data-access-object

Commands

sql-dao-generator generate
sql-dao-generator help [COMMAND]