dart_sql_builder

Description:

dart sql builder

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;
copied to clipboard
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();
copied to clipboard
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);

copied to clipboard
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:

SelectQuery 📖
InsertQuery 📝
UpdateQuery 🔄
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});
copied to clipboard
This will generate the following SQL query:
INSERT INTO users (name, age) VALUES ('John Doe', 30);
copied to clipboard
Methods #
into #
The into method is used to specify the table you want to insert into.
query.into('users');
copied to clipboard
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});
copied to clipboard
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}
]);
copied to clipboard
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']);
copied to clipboard
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});
copied to clipboard
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']);
copied to clipboard
returnAll #
The returnAll method is used to return all columns after the insert operation.
query.returnAll();
copied to clipboard
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();
copied to clipboard
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 *;
copied to clipboard
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]);
copied to clipboard
This will generate the following SQL query:
SELECT name, age FROM users WHERE age > 30;
copied to clipboard
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']);
copied to clipboard
selectAll #
The selectAll method is used to select all columns in the query.
query.selectAll();
copied to clipboard
selectDistinct #
The selectDistinct method is used to select distinct values for the specified columns.
query.selectDistinct(['age']);
copied to clipboard
from #
The from method is used to specify the table you want to select from.
query.from('users');
copied to clipboard
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]);
copied to clipboard
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']);
copied to clipboard
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']);
copied to clipboard
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');
copied to clipboard
groupBy #
The groupBy method is used to group the results by one or more columns.
query.groupBy(['country']);
copied to clipboard
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]);
copied to clipboard
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]);
copied to clipboard
limit #
The limit method is used to limit the number of results returned by the query.
query.limit(10);
copied to clipboard
offset #
The offset method is used to specify the starting point of the results returned by the query.
query.offset(20);
copied to clipboard
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);
copied to clipboard
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;
copied to clipboard
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]);
copied to clipboard
This will generate the following SQL query:
UPDATE users SET name = 'John Doe', age = 31 WHERE id = 1;
copied to clipboard
Methods #
update #
The update method is used to specify the table you want to update.
query.update('users');
copied to clipboard
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});
copied to clipboard
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]);
copied to clipboard
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']);
copied to clipboard
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']);
copied to clipboard
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']);
copied to clipboard
returnAll #
The returnAll method is used to return all columns after the update operation.
query.returnAll();
copied to clipboard
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();
copied to clipboard
This will generate the following SQL query:
UPDATE users SET name = 'John Doe', age = 31 WHERE age > 21 AND country = 'USA' RETURNING *;
copied to clipboard
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]);
copied to clipboard
This will generate the following SQL query:
DELETE FROM users WHERE id = 1;
copied to clipboard
Methods #
deleteFrom #
The deleteFrom method is used to specify the table you want to delete from.
query.deleteFrom('users');
copied to clipboard
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]);
copied to clipboard
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']);
copied to clipboard
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']);
copied to clipboard
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']);
copied to clipboard
returnAll #
The returnAll method is used to return all columns after the delete operation.
query.returnAll();
copied to clipboard
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();
copied to clipboard
This will generate the following SQL query:
DELETE FROM users WHERE age > 21 AND country = 'USA' RETURNING *;
copied to clipboard
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! 🚀