Security News
tea.xyz Spam Plagues npm and RubyGems Package Registries
Tea.xyz, a crypto project aimed at rewarding open source contributions, is once again facing backlash due to an influx of spam packages flooding public package registries.
ucast-sql
Advanced tools
Readme
This package is a part of ucast ecosystem. It provides an interpreter that can translates ucast conditions into SQL query.
npm i @ucast/sql
# or
yarn add @ucast/sql
# or
pnpm add @ucast/sql
In order to interpret something, we need 2 things: interpreter and AST. It's really easy to create interpreter just pick operators you want to use or pass all of them:
import {
createSqlInterpreter,
eq,
lt,
lte,
allInterpreters
} from '@ucast/sql';
const interpret = createSqlInterpreter({ eq, lt, lte });
// or
const interpret = createSqlInterpreter(allInterpreters);
interpret
is a function that takes up to 3 parameters:
Condition
, condition to interpretoptions
, SQL dialect specific options that tells how to escape field, create placeholders and join related tables. @ucast/sql
provides options for the most popular SQL dialects.targetQuery
, optional, this is the parameter that @ucast/sql
passes as the 2nd one to joinRelation
function. This is useful when integrating with ORMs and their query builders.For the sake of an example, we will create AST manually using Condition
from @ucast/core
:
import { CompoundCondition, FieldCondition } from '@ucast/core';
// x > 5 && y < 10
const condition = new CompoundCondition('and', [
new FieldCondition('gt', 'x', 5),
new FieldCondition('lt', 'y', 10),
]);
Now, we can combine these 2 together to get SQL condition:
import { CompoundCondition, FieldCondition } from '@ucast/core';
import { createSqlInterpreter, allInterpreters, pg } from '@ucast/sql';
// x > 5 && y < 10
const condition = new CompoundCondition('and', [
new FieldCondition('gt', 'x', 5),
new FieldCondition('lt', 'y', 10),
]);
const interpret = createSqlInterpreter(allInterpreters);
const [sql, replacements] = interpret(condition, {
...pg,
joinRelation: () => false
})
console.log(sql) // ("x" > $1 and "y < $2)
console.log(params) // [5, 10]
Interpreter automatically detects fields with dot (.
) inside and interprets them as fields of a relation. It's possible to automatically inner join table using options.joinRelation
function. That function accepts 2 parameters: relation name and targetQuery (3rd argument of interpret
function). For example:
const condition = new FieldCondition('eq', 'address.street', 'some street');
const relations = { address: '"address"."id" = "address_id"' };
const [sql, params, joins] = interpret(condition, {
...pg,
joinRelation: relationName => relations.hasOwnProperty(relationName)
});
console.log(sql); // "address"."street" = $1
console.log(params); // ['some street']
console.log(joins); // ['address']
Sometimes you may want to add custom operator or restrict supported operators. To do this, just pass desired operators manually:
import { createSqlInterpreter, eq, lt, gt, pg } from '@ucast/sql';
const interpret = createSqlInterpreter({ eq, lt, gt });
const condition = new FieldCondition('eq', 'x', true);
interpret(condition, pg);
To add a custom operator, all you need to do is to create a function that applies Condition
to instance of Query
object. Let's create an operator, that adds condition on publishedAt
field:
import { DocumentCondition } from '@ucast/core';
import {
SqlOperator,
createSqlInterpreter,
allInterpreters,
pg,
} from '@ucast/sql';
const isActive: SqlOperator<DocumentCondition<boolean>> = (node, query) => {
const operator = node.value ? '>=' : '<';
return query.where('publishedAt', operator, new Date());
};
const interpret = createSqlInterpreter({
...allInterpreters,
isActive,
});
const condition = new DocumentCondition('isActive', true);
const [sql, params] = interpret(condition, pg);
console.log(sql) // "publishedAt" >= $1
console.log(params) // [new Date()]
This library provides sub-modules that allows quickly integrate SQL interpreter with popular ORMs:
import { interpret } from '@ucast/sql/sequelize';
import { CompoundCondition, FieldCondition } from '@ucast/core';
import { Model, Sequelize, DataTypes } from 'sequelize';
const sequelize = new Sequelize('sqlite::memory:');
class User extends Model {}
User.init({
name: { type: DataTypes.STRING },
blocked: { type: DataTypes.BOOLEAN },
lastLoggedIn: { type: DataTypes.DATETIME },
});
const condition = new CompoundCondition('and', [
new FieldCondition('eq', 'blocked', false),
new FieldCondition('lt', 'lastLoggedIn', Date.now() - 24 * 3600 * 1000),
]);
// {
// include: [],
// where: literal('(`blocked` = 0 and lastLoggedIn < 1597594415354)')
// }
const query = interpret(condition, User)
import { interpret } from '@ucast/sql/objection';
import { CompoundCondition, FieldCondition } from '@ucast/core';
import { Model } from 'objection';
import Knex from 'knex';
Model.knex(Knex({ client: 'pg' }));
class User extends Model {}
const condition = new CompoundCondition('and', [
new FieldCondition('eq', 'blocked', false),
new FieldCondition('lt', 'lastLoggedIn', Date.now() - 24 * 3600 * 1000),
]);
// the next code produces:
// User.query()
// .where('blocked', false)
// .where('lastLoggedIn', Date.now() - 24 * 3600 * 1000)
const query = interpret(condition, User.query())
import { interpret } from '@ucast/sql/mikro-orm';
import { CompoundCondition, FieldCondition } from '@ucast/core';
import { MikroORM, Entity, PrimaryKey, Property } from 'mikro-orm';
@Entity()
class User {
@PrimaryKey()
id!: number;
@Property()
blocked: boolean;
@Property()
name!: string;
@Property()
lastLoggedIn = new Date();
}
const condition = new CompoundCondition('and', [
new FieldCondition('eq', 'blocked', false),
new FieldCondition('lt', 'lastLoggedIn', Date.now() - 24 * 3600 * 1000),
]);
async function main() {
const orm = await MikroORM.init({
entities: [User],
dbName: ':memory:',
type: 'sqlite',
});
// the next code produces:
// orm.em.createQueryBuilder(User)
// .where('blocked = ?', [false])
// .andWhere('lastLoggedIn = ?', [Date.now() - 24 * 3600 * 1000])
const qb = interpret(condition, orm.em.createQueryBuilder(User));
}
main().catch(console.error);
import { interpret } from '@ucast/sql/typeorm';
import { CompoundCondition, FieldCondition } from '@ucast/core';
import {
Entity,
PrimaryGeneratedColumn,
Column,
createConnection
} from 'typeorm';
@Entity()
class User {
@PrimaryGeneratedColumn()
id!: number;
@Column()
blocked: boolean;
@Column()
name!: string;
@Column()
lastLoggedIn = new Date();
}
const condition = new CompoundCondition('and', [
new FieldCondition('eq', 'blocked', false),
new FieldCondition('lt', 'lastLoggedIn', Date.now() - 24 * 3600 * 1000),
]);
async function main() {
const conn = await createConnection({
type: 'sqlite',
database: ':memory:',
entities: [User]
});
// the next code produces:
// conn.createQueryBuilder(User, 'u')
// .where('blocked = ?', [false])
// .andWhere('lastLoggedIn = ?', [Date.now() - 24 * 3600 * 1000])
const qb = interpret(condition, conn.createQueryBuilder(User, 'u'));
}
main().catch(console.error);
Written in TypeScript and supports type inference for supported ORMs.
Want to file a bug, contribute some code, or improve documentation? Excellent! Read up on guidelines for contributing
FAQs
git@github.com:stalniy/ucast.git
The npm package ucast-sql receives a total of 588 weekly downloads. As such, ucast-sql popularity was classified as not popular.
We found that ucast-sql demonstrated a healthy version release cadence and project activity because the last version was released less than a year ago. It has 1 open source maintainer collaborating on the project.
Did you know?
Socket for GitHub automatically highlights issues in each pull request and monitors the health of all your open source dependencies. Discover the contents of your packages and block harmful activity before you install or update your dependencies.
Security News
Tea.xyz, a crypto project aimed at rewarding open source contributions, is once again facing backlash due to an influx of spam packages flooding public package registries.
Security News
As cyber threats become more autonomous, AI-powered defenses are crucial for businesses to stay ahead of attackers who can exploit software vulnerabilities at scale.
Security News
UnitedHealth Group disclosed that the ransomware attack on Change Healthcare compromised protected health information for millions in the U.S., with estimated costs to the company expected to reach $1 billion.