nd-sql v0.1.7
Nd SQL
SQL Templating tool for postgreSQL, NodeJS and TypeScript collaboration. The library allows you to separate class logic and prepared SQL queries while keeping SQL flexible
With NdSql your table class look like this:
@DbTable({
dependencies: [LocationsDbTable],
init: `./cities.init.sql`,
drop: `./cities.drop.sql`,
initData: `./cities.init-data.sql`,
})
export class CitiesDbTable implements IDbCreateOneRecord {
constructor(private readonly _dbRequester: DbRequester) {}
@GetSql(`./cities.create.sql`)
public async createOne(value: ICityTableParams, sql?: string): Promise<ICityTableParams> {
return this._dbRequester.createOne<ICityTableParams>({
sql,
value,
});
}
}
Your SQL file look like this:
WITH
cities_data(id, title, location) AS (
VALUES
[[(:id::UUID, :title, :location::JSON)]]:list:
),
locations_result AS (
INSERT INTO locations(id, coords, zoom)
SELECT
UUID(cities_data.location->>'id') AS id,
ST_POINT(CAST(cities_data.location->>'latitude' AS FLOAT8), CAST(cities_data.location->>'longitude' AS FLOAT8)) AS coords,
CAST(cities_data.location->>'zoom' AS INTEGER) AS zoom
FROM cities_data
RETURNING
id,
coords,
zoom
),
cities_result AS (
INSERT INTO cities(id, title, location_id)
SELECT
cities_data.id AS id,
cities_data.title AS title,
UUID(cities_data.location->>'id') AS location_id
FROM cities_data
RETURNING
id,
title,
location_id
)
SELECT
cities_result.id AS id,
cities_result.title AS title,
JSON_BUILD_OBJECT(
'id', locations_result.id,
'latitude', ST_X(locations_result.coords),
'longitude', ST_Y(locations_result.coords),
'zoom', locations_result.zoom
) AS location
FROM cities_result
LEFT JOIN locations_result ON cities_result.location_id = locations_result.id;
Docs
Get started:
1) Install the npm package:
npm install nd-sql
2) Create db-config.json
with content:
{
"ignoreDirs": [
"node_modules",
"test",
".git"
],
"match": ".db-table."
}
3) If you use Nest.js add to nest-cli.json:
{
...
"compilerOptions": {
...
"assets": ["**/*.sql"],
"watchAssets": true,
...
},
...
}
Needed to monitor changes to the sql files
and create db.module.ts
contents:
import { DynamicModule, Module } from "@nestjs/common";
import { DbConnector, DbRequester, EDrivers, IDbConnectorConfig } from "nd-sql";
@Module({})
export class DbModule {
static async forRoot(config: Partial<IDbConnectorConfig>): Promise<DynamicModule> {
if (process.env.MODE === `test`) {
return;
}
const connector: DbConnector = await DbConnector.init({
driver: config.driver || (process.env.DB_DRIVER as EDrivers),
host: config.host || process.env.DB_HOST,
port: config.port || Number(process.env.DB_PORT),
user: config.user || process.env.DB_USER,
database: config.database || process.env.DB_DATABASE,
password: config.password || process.env.DB_PASSWORD,
force: config.force || process.env.DB_FORCE.toLowerCase() === `true`,
entities: config.entities,
});
const dbRequester = {
provide: DbRequester,
useValue: new DbRequester(connector),
};
const services = config.entities.map((Repository) => ({
provide: Repository,
useFactory: (dbRequester: DbRequester) => new Repository(dbRequester),
inject: [DbRequester],
}));
return {
module: DbModule,
providers: [dbRequester, ...services],
exports: [dbRequester, ...services],
};
}
}
and connect it to the module where you use db table classes, for example:
@Module({
imports: [
DbModule.forRoot({
entities: [
CitiesDbTable,
CommentsDbTable,
FavoritesDbTable,
FeaturesDbTable,
HotelsImagesDbTable,
HotelTypesDbTable,
HotelsDbTable,
HotelsFeaturesDbTable,
ImagesDbTable,
LocationsDbTable,
RatingsDbTable,
RefreshTokensDbTable,
UserTypesDbTable,
UsersDbTable,
],
}),
],
})
export class CommonAdapterModule {}
where entities
are your db tables
or if you don't use Nest.js you can create file with next content:
import { DbConnector, DbRequester, EDrivers, IDbConnectorConfig } from "nd-sql";
const connector: DbConnector = await DbConnector.init({
driver: config.driver || (process.env.DB_DRIVER as EDrivers),
host: config.host || process.env.DB_HOST,
port: config.port || Number(process.env.DB_PORT),
user: config.user || process.env.DB_USER,
database: config.database || process.env.DB_DATABASE,
password: config.password || process.env.DB_PASSWORD,
force: config.force || process.env.DB_FORCE.toLowerCase() === `true`,
entities: config.entities,
});
export const dbRequester = new DbRequester(connector);
db-config
Config allows optimize searching of database table classes* .sql
ignoreDirs
- ignore dirs when searching database table classesmatch
- sets part of the file name to look for
DbConnector
Database connector allows to connect to DB
init
method with config params:
driver
- tries to connect db (for example"postgreSQL"
)host
- db hostport
- db portuser
- allows you to connect as a DB userdatabase
- DB namepassword
- user passwordforce
- allows synchronize database table classes content with database ( ATTENTION! Don't use in the production or use false value)entities
- database table classes for interaction with database
query
method allows request with object param has properties:
text
- query textvalues
- array of params
DbRequester
Requester allows request to DB via:
createOne
- create one record (hasIDbCreateOneRecord
interface)createList
- creates record array (hasIDbCreateAllRecords
interface)updateOne
- update one record hasIDbUpdateOneRecord
interface)removeOne
- remove one record (hasIDbRemoveOneRecord
interface)findOne
- find one record (hasIDbFindOneRecord
interface)findList
- find list of record (hasIDbFindAllRecords
interface)
Decorators
DbTable
@DbTable
decorator allows create a table in database if not exists, config can contain:
dependencies
- allows build db tables queue for correct table creatinginit
- path to SQL file of the database table to initialize the tableinitSql
- SQL query of the database table to initialize the tabledrop
- path to SQL file of the database table to drop the tabledropSql
- SQL query of the database table to drop the tableinitData
- path to SQL file of the database table to initialize data of the tableinitDataSql
- SQL query of the database table to initialize data of the table
for example:
@DbTable({
dependencies: [LocationsDbTable],
init: `./cities.init.sql`,
drop: `./cities.drop.sql`,
initData: `./cities.init-data.sql`,
})
export class CitiesDbTable implements IDbCreateOneRecord {}
@GetSql
decorator allows use *.sql
file to query the database
for example:
@DbTable({...})
export class CitiesDbTable implements IDbCreateOneRecord {
constructor(private readonly _dbRequester: DbRequester) {}
@GetSql(`./cities.create.sql`)
public async createOne(value: ICityTableParams, sql?: string): Promise<ICityTableParams> {
return this._dbRequester.createOne<ICityTableParams>({
sql,
value,
});
}
}
@SetDefaultParams
decorator allows set default value params, if parameter is undefined
(
IMPORTANT! Always set default parameters for correct working, all used SQL file parameters should be specified
)
for example:
@DbTable({...})
export class CitiesDbTable implements IDbFindOneRecord {
constructor(private readonly _dbRequester: DbRequester) {}
@GetSql(`./cities.find.sql`)
@SetDefaultParams(defaultParams)
public findOne(value?: Partial<ICityTableParams>, sql?: string): Promise<ICityTableParams> {
return this._dbRequester.findOne<ICityTableParams>({
sql,
value,
});
}
}
Additional features for working with SQL
- named params - allows use object properties how values of Requester methods, for example:
favorites.db-table.ts
contains:
@DbTable({...})
export class FavoritesDbTable implements IDbCreateOneRecord {
constructor(private readonly _dbRequester: DbRequester) {}
@GetSql(`./favorites.create.sql`)
public async createOne(value: ICityTableParams, sql?: string): Promise<ICityTableParams> {
return this._dbRequester.createOne<ICityTableParams>({
sql,
value,
});
}
}
favorites.create.sql
contain:
INSERT INTO favorites (hotel_id, user_id) VALUES (:hotel_id::UUID, :user_id::UUID);
createOne
method should be called with:
favoritesDbTable.createOne({
hotel_id: `1008131ec-cb07-499a-86e4-6674afa31532`,
user_id: `008131ec-cb07-499a-86e4-6674afa31532`,
});
- [[]]:list: construction allows pass array of objects, for example:
favorites.db-table.ts
contains:
@DbTable({...})
export class FavoritesDbTable implements IDbCreateAllRecords {
constructor(private readonly _dbRequester: DbRequester) {}
@GetSql(`./favorites.create.sql`)
public createAll(list: IFavoriteTableParams[], sql?: string): Promise<IFavoriteTableParams[]> {
return this._dbRequester.createList({
sql,
list,
});
}
}
favorites.create.sql
contain:
INSERT INTO favorites (hotel_id, user_id) VALUES [[(:hotel_id::UUID, :user_id::UUID)]]:list:;
createAll
method should be called with:
favoritesDbTable.createAll([
{
hotel_id: `1008131ec-cb07-499a-86e4-6674afa31532`,
user_id: `208131ec-cb07-499a-86e4-6674afa31532`,
},
{
hotel_id: `3008131ec-cb07-499a-86e4-6674afa31532`,
user_id: `408131ec-cb07-499a-86e4-6674afa31532`,
}
]);