[Feature request] Execute raw SQL queries

[cedx]

Some SQL statements are not yet implemented (cf. DISTINCT), and others will probably never be implemented (i.e. SET foreign_key_checks = 0 or ALTER TABLE my_table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci on MySQL).

So we need a way to execute raw SQL queries to be able to escape from TinkSQL, that is probably expose the ident, run and value methods from the underlying driver.

Currently, I use this kind of horror (with Node.js and MySQL):

import tink.sql.Database;
import tink.sql.DatabaseDefinition;
import tink.sql.drivers.node.MySql.MySqlConnection;
import tink.sql.drivers.node.MySql.MySqlConnectionPool;

class Schema extends Database<SchemaTables> {

	var connection(get, never): MySqlConnection<SchemaTables>;
	function get_connection() {
		final cnx: MySqlConnectionPool<SchemaTables> = cast @:privateAccess __cnx;
		return new MySqlConnection(@:privateAccess cnx.info, @:privateAccess cnx.getNativeConnection(), true);
	}

	public inline function ident(identifier: String) return connection.ident(identifier);
	public inline function run(statement: String) return @:privateAccess connection.run({sql: statement});
	public inline function value(value: Any) return connection.value(value);
}

interface SchemaTables extends DatabaseDefinition {
	@:table("table1") final table1: MyTableData1;
	@:table("table2") final table2: MyTableData2;
}

[serjek]

I use that:

final QUERY = "#whatever query you have in mind";
 @:privateAccess (cast db.User.cnx:MySqlConnection<Db>).run({sql:QUERY}); //this returns Promise that you can cast to whatever you expect at function declaration

A bit dirty but fits the need. But your approach is more advanced I give you that :)

[sh-dave]

Thanks both of you 👍 i just made use of this as well.

(I had to handle unsigned BIGINT's and converted them to strings via CAST(seed as NCHAR) as seed as a dirty hack.)

[andyli]

I've added Connection#executeSql() which can fulfil some of the use cases. It cannot properly process the query result though because it needs to understand the input query in order to understand what is returned.

[cedx]

We still don't have easy/obvious access to the connection object and the ident/value methods...