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!
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:
sql-schema-generator
sql-schema-control
upsert
, findById
, findByUnique
) and leaving an easily extensible patternsql-code-generator
Powered by:
This enables:
domain-objects
Like an ORM, but without any magic or limitations - the code is in your hands and ready to mod as needed.
npm install --save-dev sql-dao-generator
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
$ npx sql-dao-generator version
$ npx sql-dao-generator generate
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
The sql-dao-generator has many guardrails in place to make sure that you're following best practices and avoiding potential maintainability problems.
Specifically:
domain-literals
may not explicitly specify unique or updatable properties, since these are defined implicitly by the definition of a literaldomain-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 uniqueaddress: Address
homeAddress: Address
home: Address
domain-entities
should not be nested in other domain objects; they should use implicit uuid references instead
database foreign key constraints
without explicitly nesting domain-entities inside of other domain-objects, by using implicit uuid references
user: User
use userUuid: string
uuid <-> id
to and from the database in the data-access-objectsql-dao-generator generate
generate data-access-objects by parsing domain-objects
USAGE
$ sql-dao-generator generate
OPTIONS
-c, --config=config (required) [default: codegen.sql.dao.yml] path to config yml
-h, --help show CLI help
See code: dist/contract/commands/generate.ts
sql-dao-generator help [COMMAND]
display help for sql-dao-generator
USAGE
$ sql-dao-generator help [COMMAND]
ARGUMENTS
COMMAND command to show help for
OPTIONS
--all see all commands in CLI
See code: @oclif/plugin-help
Team work makes the dream work! Please create a ticket for any features you think are missing and, if willing and able, draft a PR for the feature :)