Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Implement RETURNING clause #155

Closed
georgolden opened this issue Jun 17, 2021 · 1 comment
Closed

Implement RETURNING clause #155

georgolden opened this issue Jun 17, 2021 · 1 comment

Comments

@georgolden
Copy link
Member

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
}
@tshemsedinov
Copy link
Member

tshemsedinov commented Aug 6, 2021

Partially implemented in #197 but need this issue and tests to be completely done: #185

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

Successfully merging a pull request may close this issue.

2 participants