A query builder for Dart that simplifies the process of creating complex SQL queries

by Flutter Awesome

Dart SQL Builder 🚀

dart_sql_builder is a powerful and flexible query builder for Dart that simplifies the process of creating complex SQL queries. It is not an ORM, nor is it type-safe, but it provides a more readable and maintainable way to build SQL queries without having to write raw SQL strings.

The package includes support for various query types, including SELECT, INSERT, UPDATE, and DELETE queries. It also provides a convenient API for chaining query components together, making it easy to create complex queries with minimal effort 🛠️.

In addition to the core query-building functionality, dart_sql_builder has plans for future enhancements, such as:

  • A migration tool to manage database tables 📦
  • Support for additional SQL drivers besides PostgreSQL 🔄
  • Builders to help create type-safe queries 🔒

The dart_sql_builder package comes with a built-in PostgreSQL driver, which can be easily integrated into your Dart projects. Here’s an example of how to use the package with the PostgreSQL driver:

final postgreSQL = PostgreSQL();
final selectQuery = postgreSQL.select;
final insertQuery = postgreSQL.insert;
final updateQuery = postgreSQL.update;
final deleteQuery = postgreSQL.delete;

You can run the query with PostgreSQL:

final postgreSQL = PostgreSQL();

await postgreSQL.open();

final query = postgreSQL.select
  ..select(['name', 'age'])
  ..from('users')
  ..where('age > ?', [30]);

await query.query();
await query.queryMapped();
await query.execute();

Or run raw queries:

final postgreSQL = PostgreSQL();
final query = 'SELECT COUNT(*) FROM users';

await postgreSQL.rawQuery(query);
await query.rawQueryMapped(query);
await query.rawExecute(query);

The base Query class serves as the foundation for all queries and can be extended to support additional database drivers and custom query types.

In the following sections, you’ll find detailed documentation for each of the supported query types: SelectQuery, InsertQuery, UpdateQuery, and DeleteQuery. These guides will help you understand how to use the dart_sql_builder package effectively and efficiently to build SQL queries for your Dart applications.

Now, dive into the documentation for each query type to learn how to use the dart_sql_builder package to its full potential:

  1. SelectQuery 📖
  2. InsertQuery 📝
  3. UpdateQuery 🔄
  4. DeleteQuery ❌

InsertQuery

Add new rows to a table in your database. InsertQuery allows you to insert single or multiple rows at once, and even provides conflict handling options for dealing with unique constraints.

Usage

To use InsertQuery, you first need to create an instance of it. You can then chain methods to build the query as needed.

Here’s an example of how to build a simple INSERT query:

final query = InsertQuery()
  ..into('users')
  ..insert({'name': 'John Doe', 'age': 30});

This will generate the following SQL query:

INSERT INTO users (name, age) VALUES ('John Doe', 30);

Methods

into

The into method is used to specify the table you want to insert into.

query.into('users');

insert

The insert method is used to specify the values you want to insert for the columns in the table. You can pass a map of column names and their corresponding values.

query.insert({'name': 'John Doe', 'age': 30});

insertAll

The insertAll method is used to insert multiple rows at once. You can pass a list of maps, where each map contains the column names and their corresponding values.

query.insertAll([
  {'name': 'John Doe', 'age': 30},
  {'name': 'Jane Doe', 'age': 25}
]);

onConflictDoNothing

The onConflictDoNothing method is used to specify that the insert operation should do nothing if there is a conflict with the specified columns.

query.insert({'name': 'John Doe', 'age': 30, 'unq': 'test'}).onConflictDoNothing(['unq']);

onConflictDoUpdate

The onConflictDoUpdate method is used to specify that the insert operation should update the specified columns if there is a conflict with the provided columns.

query.insert({'name': 'John Doe', 'age': 30, 'unq': 'test'}).onConflictDoUpdate(['unq'], {'age': 31});

returning

The returning method is used to specify the columns to return after the insert operation. You can pass an array of strings, where each string represents a column name.

query.returning(['id']);

returnAll

The returnAll method is used to return all columns after the insert operation.

query.returnAll();

Example

Here’s an example of a complex INSERT query using InsertQuery:

final query = InsertQuery()
  ..into('users')
  ..insert({'name': 'John Doe', 'age': 30, 'unq': 'test'})
  ..onConflictDoUpdate(['unq'], {'age': 31})
  ..returnAll();

This will generate the following SQL query:

INSERT INTO users (name, age, unq) VALUES ('John Doe', 30, 'test')
ON CONFLICT (unq) DO UPDATE SET age = 31 RETURNING *;

SelectQuery

Retrieve data from one or more tables in your database. With SelectQuery, you can filter, sort, group, and join data, making it easy to fetch exactly what you need.

Usage

To use SelectQuery, you first need to create an instance of it. You can then chain methods to build the query as needed.

Here’s an example of how to build a simple SELECT query:

final query = SelectQuery()
  ..select(['name', 'age'])
  ..from('users')
  ..where('age > ?', [30]);

This will generate the following SQL query:

SELECT name, age FROM users WHERE age > 30;

Methods

select

The select method is used to specify the columns you want to select in the query. You can pass an array of strings, where each string represents a column name.

query.select(['name', 'age']);

selectAll

The selectAll method is used to select all columns in the query.

query.selectAll();

selectDistinct

The selectDistinct method is used to select distinct values for the specified columns.

query.selectDistinct(['age']);

from

The from method is used to specify the table you want to select from.

query.from('users');

where

The where method is used to add a WHERE condition to the query. You can pass a string representing the condition, and an array of values to replace the placeholders in the condition.

query.where('age > ?', [30]);

and

The and method is used to add an AND operator to the query, allowing you to chain multiple WHERE conditions.

query.where('age > ?', [30]).and().where('country = ?', ['USA']);

or

The or method is used to add an OR operator to the query, allowing you to chain multiple WHERE conditions.

query.where('age > ?', [30]).or().where('country = ?', ['USA']);

join

The join method is used to join another table to the query. You can pass the table name, the ON condition, and the type of join (default is INNER JOIN).

query.join('orders', 'users.id = orders.user_id');

groupBy

The groupBy method is used to group the results by one or more columns.

query.groupBy(['country']);

having

The having method is used to add a HAVING condition to the query, used with GROUP BY to filter the results.

query.having('COUNT(*) > ?', [1]);

orderBy

The orderBy method is used to order the results by one or more columns. You can pass an array of column names and an array of Order enum values (either Order.asc or Order.desc) to specify the order for each column.

query.orderBy(['age'], [Order.desc]);

limit

The limit method is used to limit the number of results returned by the query.

query.limit(10);

offset

The offset method is used to specify the starting point of the results returned by the query.

query.offset(20);

Example

Here’s an example of a complex SELECT query using SelectQuery:

final query = SelectQuery()
  ..select(['users.name', 'orders.product'])
  ..from('users')
  ..join('orders', 'users.id = orders.user_id', JoinType.left)
  ..where('users.age > ?', [21])
  ..and()
  ..where('users.country = ?', ['USA'])
  ..groupBy(['users.name', 'orders.product'])
  ..orderBy(['users.name'], [Order.desc])
  ..limit(10);

This will generate the following SQL query:

SELECT users.name, orders.product
FROM users
LEFT JOIN orders ON users.id = orders.user_id
WHERE users.age > 21 AND users.country = 'USA'
GROUP BY users.name, orders.product
ORDER BY users.name DESC
LIMIT 10;

UpdateQuery

Modify existing data in your database. UpdateQuery enables you to update specific columns in a table based on a set of conditions, making it easy to apply changes to targeted rows.

Usage

To use UpdateQuery, you first need to create an instance of it. You can then chain methods to build the query as needed.

Here’s an example of how to build a simple UPDATE query:

final query = UpdateQuery()
  ..update('users')
  ..set({'name': 'John Doe', 'age': 31})
  ..where('id = ?', [1]);

This will generate the following SQL query:

UPDATE users SET name = 'John Doe', age = 31 WHERE id = 1;

Methods

update

The update method is used to specify the table you want to update.

query.update('users');

set

The set method is used to specify the values you want to set for the columns in the table. You can pass a map of column names and their corresponding values.

query.set({'name': 'John Doe', 'age': 31});

where

The where method is used to add a WHERE condition to the query. You can pass a string representing the condition, and an array of values to replace the placeholders in the condition.

query.where('id = ?', [1]);

and

The and method is used to add an AND operator to the query, allowing you to chain multiple WHERE conditions.

query
    ..where('age > ?', [30])
    ..and()
    ..where('country = ?', ['USA']);

or

The or method is used to add an OR operator to the query, allowing you to chain multiple WHERE conditions.

query
    ..where('age > ?', [30])
    ..or()
    ..where('country = ?', ['USA']);

returning

The returning method is used to specify the columns to return after the update operation. You can pass an array of strings, where each string represents a column name.

query.returning(['id']);

returnAll

The returnAll method is used to return all columns after the update operation.

query.returnAll();

Example

Here’s an example of a complex UPDATE query using UpdateQuery:

final query = UpdateQuery()
  ..update('users')
  ..set({'name': 'John Doe', 'age': 31})
  ..where('age > ?', [21])
  ..and()
  ..where('country = ?', ['USA'])
  ..returnAll();

This will generate the following SQL query:

UPDATE users SET name = 'John Doe', age = 31 WHERE age > 21 AND country = 'USA' RETURNING *;

DeleteQuery

Remove data from your database. DeleteQuery allows you to delete rows from a table based on specific conditions, ensuring that you only remove the data you intend to.

Usage

To use DeleteQuery, you first need to create an instance of it. You can then chain methods to build the query as needed.

Here’s an example of how to build a simple DELETE query:

final query = DeleteQuery()
  ..deleteFrom('users')
  ..where('id = ?', [1]);

This will generate the following SQL query:

DELETE FROM users WHERE id = 1;

Methods

deleteFrom

The deleteFrom method is used to specify the table you want to delete from.

query.deleteFrom('users');

where

The where method is used to add a WHERE condition to the query. You can pass a string representing the condition, and an array of values to replace the placeholders in the condition.

query.where('id = ?', [1]);

and

The and method is used to add an AND operator to the query, allowing you to chain multiple WHERE conditions.

query.where('age > ?', [30]).and().where('country = ?', ['USA']);

or

The or method is used to add an OR operator to the query, allowing you to chain multiple WHERE conditions.

query.where('age > ?', [30]).or().where('country = ?', ['USA']);

returning

The returning method is used to specify the columns to return after the delete operation. You can pass an array of strings, where each string represents a column name.

query.returning(['id']);

returnAll

The returnAll method is used to return all columns after the delete operation.

query.returnAll();

Example

Here’s an example of a complex DELETE query using DeleteQuery:

final query = DeleteQuery()
  ..deleteFrom('users')
  ..where('age > ?', [21])
  ..and()
  ..where('country = ?', ['USA'])
  ..returnAll();

This will generate the following SQL query:

DELETE FROM users WHERE age > 21 AND country = 'USA' RETURNING *;

Wrapping Up 🎁

Contributions from the community to help improve and expand the package’s features and capabilities are always welcomed 🤝.

dart_sql_builder is released under the MIT license, which means you are free to use, modify, and distribute the code as you see fit.

Remember, dart_sql_builder is made with love ❤️ and we look forward to seeing it grow and evolve with your support.

Happy coding! 🚀

GitHub

View Github