database_query_builder

Description:

database query builder

Database: Query Builder



Developed with 💙 by Jean Roldan




Introduction
Insert
Select
Where
Update
Delete
Join
Filter
Table

# Introduction #
The Database Query Builder for Dart provides an easy, efficient, and fluid interface for building or running database queries. It can be used to perform most basic (Temporary) database operations and works perfectly with postgresql we hope to add support for other managers later.
This project will momentarily depend on our source postgres library to connect to this manager and execute queries, although it is true, there are multiple ORM-based libraries, but we want to do something of our own, something CRAZY!
You can perform tests using our files or use your own database, without fear of success
# Data Methods #
We have several methods to bring information from our queries:
get() Returns a list of type map<String,dynamic> depending on our queries
getModel<T>() Returns a list of a strongly typed class to generate direct models
toSql() gets the query generated by the query builder does not require a database connection, you can use this function to generate the required SQL helping you avoid SQL injections
DB.table('products').get();
DB.table('products').getModel<Product>(Product.fromJson);
DB.table('products').toSql();
copied to clipboard
Raw Expressions #
🦖
# INSERT INTO Statements #
Inserts Basic #
Provides an insert method that can be used to insert records using a map(key,value)
final numRowAffected = await DB.table('persons').insert({
'firstName': 'testFirstName',
'lastName': 'testLastName',
'age': 30,
}).save()
copied to clipboard
Inserts Multiple #
You can insert multiple records at once using the insertAll method by passing a list of map(key,value)
final sql03 = await DB.table('persons').insertAll([
{
'firstName': 'testFirstName1',
'lastName': 'testLastName1',
'age': 10,
},
{
'firstName': 'testFirstName2',
'lastName': 'testLastName2',
'age': 20,
},
{
'firstName': 'testFirstName3',
'lastName': 'testLastName3',
'age': 30,
}
]).save();
copied to clipboard
Inserts Get ID #
You can get the id of the affected row using insertGetId, this will perform an internal query to get the primary key
final numRowAffected = await DB.table('persons').insertGetId({
'firstName': 'testFirstName',
'lastName': 'testLastName',
'age': 30,
}).save()
copied to clipboard
If you want to avoid double query or you do not have the permissions to read these attributes and improve the performance of the query, indicate what is your primary key
final numRowAffected = await DB.table('persons').insertGetId({
'firstName': 'testFirstName',
'lastName': 'testLastName',
'age': 30,
},
'id').save()
copied to clipboard
In the same way you can insert multiple records and get their IDs
final numRowAffected = DB.table('people').insertAllGetIds(
[
{
'first_name': 'new first name 1',
'last_name': 'new first last name 1',
'age': 12,
},
{
'first_name': 'new first name 2',
'last_name': 'new first last name 2',
'age': 17,
},
{
'first_name': 'new first last name 3',
'last_name': 'new first last name 3',
'age': 118,
}
],
'id',
);
copied to clipboard
# SELECT Statements #
select #
DB.table('persons').select(['firstName','age']).get();
copied to clipboard
distinct #
🦖
# WHERE Statements #
Where Clauses #
Make simple queries
DB.table('people').where('age', '10').get();
copied to clipboard
You can also use your own conditionals
await DB.table('people').where('age', '=', 10).get();
copied to clipboard
otherwise we offer you a strongly typed conditional
await DB.table('people').where('age', WhereType.equal, '10').get();
await DB.table('people').where('age', WhereType.greaterThan, '10').get();
await DB.table('people').where('age', WhereType.greaterThanOrEqual, '10').get();
await DB.table('people').where('age', WhereType.lessThan, '10').get();
await DB.table('people').where('age', WhereType.lessThanOrEqual, '10').get();
await DB.table('people').where('age', WhereType.like, '10').get();
copied to clipboard
Where Nested #
If you already saw how the assignment of where statements works, you can do it in nested ways at your whim
DB.table('people')
.where('age', 25)
.where('age', '=', 10)
.where('age', WhereType.greaterThan, 80)
.get();
copied to clipboard
Where IN #
DB.table('people').whereIN('age', [10, 20, 30])
copied to clipboard
Where OR #
🦖
Where NULL #
🦖
Where EXIST #
🦖
Where NOT EXIST #
🦖
# UPDATE SET Statements #
update #
DB.table('persons')
.update({'firstName': 'new first name', 'lastName': 'new last name'})
.save();
copied to clipboard
update with conditional #
DB.table('persons')
.update({'firstName': 'new first name', 'lastName': 'new last name'})
.where('id', '1')
.save();
copied to clipboard
Update or Insert #
🦖
# DELETE Statements #
Delete #
You can delete all records from your table
DB.table('people').delete().save()
copied to clipboard
Delete with conditional #
performs a search for the record to delete
DB.table('people').delete().where('id', 5).save();
copied to clipboard
# JOIN Statements #
Join #
🦖
LeftJoin #
🦖
RightJoin #
🦖
CrossJoin #
🦖
# FILTERS #
OrderBy #
🦖
GroupBy #
🦖
Limit #
🦖
# AGGREGATES #
Max #
🦖
Count #
🦖
Increment #
🦖
Decrement #
🦖
# TABLE #
Drop table #
🦖
Truncate table #
🦖