How to print raw SQL for debugging purpose?

[KiddoV]

After constructing a Query or calling a repository function, how can we print the raw SQL to the console — similar to using .Debug() in GORM?

Is there any built-in logger or debug mode we can enable to see the exact SQL being executed?

If not, what’s the recommended way to inspect the generated SQL for debugging?

[tshedor]

@KiddoV

untested code

class DebugPrintSqlProvider extends SqliteProvider<MyBaseModel> {
  @override
  Future<List<TModel>> get<TModel extends MyBaseModel>({
    Query? query,
    ModelRepository<TProviderModel>? repository,
  }) async {
    final sqlQuery = QuerySqlTransformer<TModel>(
      modelDictionary: modelDictionary,
      query: query,
    );
    debugPrint(sqlQuery.statement);
    // WARNING: sanitize values before printing sensitive data to the console
    // debugPrint(sqlQuery.values);
    return await super.get<TModel>(query: query, repository: repository);
  }
}

[KiddoV]

Would be nice to have something built in like:

Query(...).toSQL() // => SELECT * ....

For debugging the SQL logic.

[tshedor]

@KiddoV I'm sorry, I'm not going to build it in. SQL is discouraged in Brick as it's for advanced use only.

[KiddoV]

Hey @tshedor, is it okay if I message you on Discord when you have time? I just have a few questions about building the Query() — I'm scratching my head over it.

[tshedor]

@KiddoV I'd prefer to keep things public and colocated on Github so that others can find the discussion or I can reference the discussion in documentation. What's your Query question?

[KiddoV]

@tshedor

Says this is my model

@ConnectOfflineFirstWithSupabase(
	sqliteConfig: SqliteSerializable(),
	supabaseConfig: SupabaseSerializable(tableName: "debts")
)
class Debt extends OfflineFirstWithSupabaseModel {
	@Supabase(unique: true)
	@Sqlite(index: true, unique: true)
	final String id;
	//
	final double amount;
	final String currencyCode;	// e.g., "USD", "VND", "EUR"
	final DateTime createdDate;
	// Associations
	@Sqlite(index: true)
	final Person creator;				// User who created the record
	@Sqlite(index: true)
	final Person? lender;				// Person who gave the money
	@Sqlite(index: true)
	final Person? borrower;				// Person who received money
}

I'm trying to construct a query in Dart (using Query and Where objects) to filter the debts table with the following logic:

• The debt must belong to a specific creator (by creator.id).
• Then filter the debt by a searchField, that match firstName of a lender or a borrower.

Query(
    where: [
        Where.exact("creator", Where.exact("id", creatorId)),
        WherePhrase([
            Where.exact("lender", Where("firstName").contains(searchText), isRequired: false),
            Where.exact("borrower", Where("firstName").contains(searchText), isRequired: false)
        ])
    ]
)

My search always return an empty list.

[tshedor]

@KiddoV ok a couple things:

1. If you only search by creator are results returned?
2. Is the search text cased appropriately? If not, you may need to add extra Where statements that are searchText.toLowerCase()
3. You can make your query a little more legible. Consider this test case.

untested

Query(
  where: [
    const Where.exact('creator', Where.exact('id', creatorId)),
    WherePhrase(
      [
        Where.exact('lender', const Or('firstName').contains(searchText)),
        Where.exact('borrower', const Or('firstName').contains(searchText)),
      ],
      isRequired: true,
    ),
  ],
),

You'll want to think about the field that's doing the evaluation as owning the operator. In this case, the comparison on firstName should be an Or instead of Where.exact (which will produce an AND operator). That way, when the query is constructed it will have an OR within the phrase. isRequired on the WherePhrase will be prefixed by an AND when the query is constructed.

[KiddoV]

@tshedor Thanks for taking time on this.
I have tried your code, it doesn't work, it still return an empty list...

	static Query querySearch(String searchText, String creatorId, {int limit = 10, int? offset}) {
		final searchStr = searchText.toLowerCase().trim();
		//
		return Query(
			limit: limit,
			offset: offset,
			orderBy: [OrderBy("createdDate", ascending: false)],
			where: searchStr.isEmpty ? null : [ // Condition when have `searchText` only
				Where.exact("creator", Where.exact("id", creatorId)),
				WherePhrase([
					Where.exact("lender", Or("firstName").contains(searchStr)),
					Where.exact("borrower", Or("firstName").contains(searchStr))
				], isRequired: true),
			]
		);
	}

1. If you only search by creator are results returned?

Yes if I just use

Where.exact("creator", Where.exact("id", creatorId)),

... all items are returned.

[tshedor]

@KiddoV Can you keep trying different variations to see what would work? For example, removing the wrapping of the WherePhrase, trying just lender, trying just borrower, etc.

You can also try printing the SQL with an extension:

untested

import 'package:my_package/brick/brick.g.dart';
import 'package:brick_sqlite/src/models/sqlite_model.dart';

extension QuerySql on Query {
  String toSql<TModel extends SqliteModel>() {
    final sqlQuery = QuerySqlTransformer<TModel>(
      modelDictionary: sqliteModelDictionary,
      query: this,
    );

    return sqlQuery.statement;
  }
}

[KiddoV]

@tshedor
I have tried to separate them and test each query by itself (each Where clause), all works, only when combine them, it doesn't work.

---

The Query.toSql() work nicely...
I can translate to SQL string now:

print(Debt.querySearch(_searchCtr.text, "123", limit: _limitItems, offset: nextPageKey * _limitItems).toSql<Debt>());
// SELECT DISTINCT `Debt`.* FROM `Debt` INNER JOIN `Person` ON `Debt`.creator_Person_brick_id = `Person`._brick_id WHERE `Person`.id = ? AND (`Person`.first_name LIKE ? OR `Person`.first_name LIKE ?) ORDER BY datetime(`Debt`.created_date) DESC LIMIT 10 OFFSET 0

Only 1 thing when import the QuerySqlTransformer, my vscode warns:

Import of a library in the 'lib/src' directory of another package.
Try importing a public library that exports this library, or removing the import.dart[implementation_imports](https://dart.dev/diagnostics/implementation_imports)
library package:brick_sqlite/src/helpers/query_sql_transformer.dart
package:brick_sqlite/src/helpers/query_sql_transformer.dart

So I have to import like:

import "package:brick_sqlite/src/helpers/query_sql_transformer.dart"; // ignore: implementation_imports

[KiddoV]

In some rows, the lender or borrower can be NULL. Could this cause the INNER JOIN to silently fail and exclude those rows from the result?

How do we use LEFT JOIN or RIGHT JOIN?, I only see you are using INNER JOIN.

[tshedor]

Wouldn't you want null rows excluded? Can you prove that a LEFT JOIN would succeed in this case by modifying that query and replacing INNER JOIN?

[KiddoV]

@tshedor I mean if I have to write a raw SQL, this is how the logic works:

final rs = await repo.sqliteProvider.rawQuery(
	"""
	SELECT DISTINCT Debt.id FROM Debt
	JOIN Person AS creator ON Debt.creator_Person_brick_id = creator._brick_id
	LEFT JOIN Person AS lender ON Debt.lender_Person_brick_id = lender._brick_id
	LEFT JOIN Person AS borrower ON Debt.borrower_Person_brick_id = borrower._brick_id
	WHERE creator.id = ? AND (
	    LOWER(lender.first_name) LIKE ? OR LOWER(borrower.first_name) LIKE ?
	)
	ORDER BY datetime(Debt.created_date) DESC
	LIMIT ? OFFSET ?
	""",
	[creatorId, "%$searchStr%", "%$searchStr%", limit, offset]
);

As you can see when construct with Query(), we are missing 2 JOINs for lender and borrower association tables.

[tshedor]

@KiddoV youre right; I didn't anticipate searching and joining different columns on the same model. This is on my list to do now.