Metarhia SQL builder and Postgres utilities 🐘
Home Page: https://metarhia.com
License: MIT License
Metahria technology stack relational database access layer library for DDL generation from metaschema, data validation, migrations auto generation, queries and introspection.
Copyright (c) 2019-2023 Metarhia contributors.
Metasql is MIT licensed.
Metasql is a part of Metarhia technology stack.
![dependabot[bot] avatar](https://avatars.githubusercontent.com/in/29110?v=4 "dependabot[bot]")
name: { many: 'EntityName' }
It will be nice to have sth like this .select('Table', ['*'], { id: ['1', '2'] }) => id IN ('1', '2')
Possible Solution:
// database.js if (value instanceof Array) { return ['IN', value]; }
We need a custom create function. that will allocate an Identifier` and then use the allocated id to insert a new record into the table.
At the moment it's impossible to insert a new value because you need to provide an id from the Identifier table.
We may need support for queries that include OR.
For example:
Database.select(table, fields, conditions)Database.select(table, conditions)Database.select(table, fields)Query.prototype.toObject()
Query.from(object)
metasql c // create database creation script from current schema
metasql g // generate migration from current schema
metasql m // apply all generated migrations
metasql m <number> // apply or rollback to the specified version
metasql l // list with statuses: generated, applied, current
We need need to autogenerate insert statements for categories and theirs IDs.
We may have sub-projects with extension to the base schema:
Following are restricted:
({
price: { type: 'number', default: 0 },
});name: { index: 'custom index definition' }
({
name: string,
fullName: { include: 'FullName' }, // Will include entity schema: ../FullName.js
});Use this separate repo from metascmema, metasql, Example, etc.
| Case | Default |
|---|---|
| no directives | { scope: 'system', kind: 'entity' } |
struct |
{ scope: 'memory', kind: 'struct' } |
dictionary, registry, entity, details, relation |
{ scope: 'system', kind: '<K>' } |
form, view, projection |
{ scope: 'system', kind: '<K>' } |
log |
{ scope: 'local', kind: 'log' } |
Related PR: #14
name: { index: ['field1', 'field2'] }
Use postgres in CI and fix CI errors
Don't mixin { required: true } to Schema indexes
At the moment, when operator has been found the loop continues its work. Please, take a look at the following piece of code
Line 20 in d606878
const { clause } = where({ age: '>=<=18' });
console.log(clause); // 'age <='Maybe in this case it makes sense to use the break statement.
Is your feature request related to a problem? Please describe.
This feature provides the ability to create a query using the ON CONFLICT construct for INSERT, as it is not currently possible to use metasql in places - where this construct is needed.
The postgres syntax is as follows:
ON CONFLICT target action;The action can be one of the following:
Describe the solution you'd like
for example, there is a record
const record = {
id:100,
subId:32,
title:'Title text',
price:12.2,
description:'Details about product',
delivery:'pickup'
}// I would like to have the freedom to choose the style of code, for example:
domain.db.insert('Products', record).onConflict(['id','subId']).doNothing()
//or
domain.db.insert('Products', record).onConflict(['id','subId'])equals:
INSERT INTO...
... ON CONFLICT (id,subId)
DO NOTHINGdomain.db.insert('Products', record).onConflict(['id','subId']).doUpdate()
//or
domain.db.insert('Products', record).onConflict(['id','subId']).update()equals:
INSERT INTO...
... ON CONFLICT (id,subId)
DO UPDATE
SET
title= EXCLUDED.title,
price= EXCLUDED.price,
description= EXCLUDED.description,
delivery= EXCLUDED.delivery;That is, we are updating non-conflicting fields by default.
domain.db.insert("Products", record).onConflict(["id","subId"]).doUpdate(["!price"])
//or
domain.db.insert("Products", record).onConflict("id","subId").update("!price")
//or
domain.db.insert("Products", record).onConflict("id","subId").doUpdate().exclude("price")equals:
INSERT INTO...
... ON CONFLICT (id,subId)
DO UPDATE
SET
title= EXCLUDED.title,
description= EXCLUDED.description,
delivery= EXCLUDED.delivery;That is, we are updating non-conflicting fields by default, minus the specified fields with an exclamation mark (price)
domain.db.insert('Products', record).onConflict(['id','subId']).doUpdate('price',{delivery:'cashOnDelivery'})
/**
Inside, doUpdate you can check -
if the value type is an array or string - these are the field names
otherwise if it is an object, then it is a "Where" condition
**/
//or
domain.db.insert('Products', record).onConflict(['id','subId']).update(['price'],{delivery:'cashOnDelivery'})equals:
INSERT INTO...
... ON CONFLICT (id,subId)
DO UPDATE
SET
price= EXCLUDED.price
WHERE delivery='cashOnDelivery';updates only the specified field (price) and only according to the "where" condition
Describe alternatives you've considered
I'm using this code right now
const fields = ['id','subId','title','price','description','delivery']
function setExcluded(fields){
return fields.map( (field, index, fields_) => `${field} = EXCLUDED.${field} ${(fields_.length - 1) == index ? ';' : ','}`).join('')
}
var updateList = fields.filter(field => field !== 'id' && field !== 'subId');
const query = `INSERT INTO...
ON CONFLICT (id,subId)
DO UPDATE
SET
${setExcluded(updateList)}
`Additional context
also implement the builder when there is a conflict on CONSTRAINT:
INSERT INTO customers (user, email)
VALUES('user','[email protected]')
ON CONFLICT ON CONSTRAINT usersname_name_key
DO NOTHING;We have simple query builder with parameterized queries but we need DML generation as a string too.
Metaschema supports nested structures, but for database we need to unroll grouped fields into plain structure.
Refs: metarhia/metaschema#291
Example:
({
login: { type: 'string', unique: true, length: 30 },
fullName: {
givenName: { type: 'string', required: false },
middleName: { type: 'string', required: false },
surname: { type: 'string', required: false },
},
birth: {
birthDate: 'string',
birthPlace: 'string',
},
});({
price: {
type: 'number',
set(value) {
this.value = typeof value === 'number' ? value : parseInt(value, 10);
}
get() {
return Math.round(this.value);
}
},
});Cross-reference entities should have composite primary key consists of two foreign keys.
({
company: 'Company',
city: 'City',
companyCity: { primary: ['Company', 'City'] },
});Filenames for migration files
2020-09-11-v1-dn.sql
2020-09-11-v1-up.sql
2020-09-11-v2-dn.sql
2020-09-11-v2-up.sql
2020-09-12-v3-dn.sql
2020-09-12-v3-up.sql
Example:
ALTER TABLE "City" ADD CONSTRAINT "fkCityCountryId"
FOREIGN KEY ("CountryId")
REFERENCES "Country" ("CountryId") ON DELETE CASCADE;
It will be great to have db.select('City', ['*'], { cityId: 3 }, not: boolean);, to select all except sth.
We need to add support for schema-aware flattening of field groups when we insert a new value, and the reverse "unflattening" mapping for selectors.
Is your feature request related to a problem? Please describe.
insert, update, delete methods from class Database are not very useful without returning an auto-generated ID. I am using mostly query method and writing SQL queries by myself with RETURNING clause.
Describe the solution you'd like
Add an opportunity to use RETURNING clause with insert, update, delete methods.
Describe alternatives you've considered
Maybe add support to other useful clauses too. For example, ON CONFLICT for 'INSERT' operation.
Additional context
Implementation may look something like that:
returning(fields, sql) {
if (fields.length > 0) {
const data = fields.toString();
sql += ` RETURNING ${data}`;
}
return;
};
insert(table, record, output = []) {
const keys = Object.keys(record);
const nums = new Array(keys.length);
const data = new Array(keys.length);
let i = 0;
for (const key of keys) {
data[i] = record[key];
nums[i] = `$${++i}`;
}
const fields = '"' + keys.join('", "') + '"';
const params = nums.join(', ');
let sql = `INSERT INTO "${table}" (${fields}) VALUES (${params})`;
returning(output, sql);
return this.query(sql, data);
};There is a small problem with this approach. pg.pool.query will return not just fields, that are given with clause RETURNING. It will return an object from pg. Data is available by data.rows. It will be better to simplify returning object and return just data.rows
Returning object looks like this:
result: "success",
data: {
command: "INSERT",
rowCount: 1,
oid: 0,
rows: [
{
countryId: "8",
name: "Kekistans"
}
],
fields: [
{
name: "countryId",
tableID: 16438,
columnID: 1,
dataTypeID: 20,
dataTypeSize: 8,
dataTypeModifier: -1,
format: "text"
},
{
name: "name",
tableID: 16438,
columnID: 2,
dataTypeID: 1043,
dataTypeSize: -1,
dataTypeModifier: -1,
format: "text"
}
],
_parsers: [
null,
null
],
_types: {...},
RowCtor: null,
rowAsArray: false
}Current method:
/database.js
async select(table, fields = ['*'], conditions = null) {
const keys = fields[0] === '*' ? '*' : '"' + fields.join('", "') + '"';
const sql = `SELECT ${keys} FROM "${table}"`;
let whereClause = '';
let args = [];
if (conditions) {
const whereData = where(conditions);
whereClause = ' WHERE ' + whereData.clause;
args = whereData.args;
}
const res = await this.query(sql + whereClause, args);
return res.rows;
}Propose:
async select(table, fields = ['*'], conditions = null, otherParams = null) {
const keys = fields[0] === '*' ? '*' : '"' + fields.join('", "') + '"';
const sql = `SELECT ${keys} FROM "${table}"`;
let whereClause = '';
let args = [];
if (conditions) {
const whereData = where(conditions);
whereClause = ' WHERE ' + whereData.clause;
args = whereData.args;
}
// ------------ add this ---------
let additional = '';
if (otherParams) {
Object.keys(otherParams).map(d => {
additional += ` ${d} ${otherParams[d]}`;
});
}
// -------------------
const res = await this.query(sql + whereClause + additional, args);
return res.rows;
}for example:
db.select('some_table', ['*'], { price: 5 }, { 'order by': 'id desc', limit: 15 })
.then(d => { console.log({ d }) })
.catch(e => console.error(e));Now we can just load it from file: new Schema(filePath)
({
name: { type: 'string', length: 30 },
fullName: { type: 'string', length: { min: 20 } },
address: { type: 'string', length: [20, 60] },
});cascade, restrict, update, set null, nothing
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
Django
The Web framework for perfectionists with deadlines.
Laravel
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
Microsoft
Open source projects and samples from Microsoft.
Google
Google ❤️ Open Source for everyone.
Alibaba
Alibaba Open Source for everyone
D3
Data-Driven Documents codes.
Tencent
China tencent open source team.