sql_query_builder 1.0.4

sql_query_builder: ^1.0.4 copied to clipboard

example/example.md

Dart SQL Query Builder

• Dart SQL Query Builder
  • Example Usage
• Schema (to create database table)
  • create table
  • drop/delete table
• Model
  • Soft Deletes
  • Save
  • Debug
  • Reset query and create new one
• Query
  • insert or create
  • update
  • count
  • find
  • all
  • get
  • toSql
  • delete
  • forceDelete (only with SoftDeletes)
  • withTrash (only with SoftDeletes)
  • select
  • where
  • orWhere
  • whereRaw
  • orWhereRaw
  • chain where and orWhere
  • limit or take
  • offset
  • groupBy
  • orderBy
  • join
  • leftJoin
  • rightJoin
  • joinRaw
  • leftJoinRaw
  • rightJoinRaw
  • debug
  • Custom Query Builder Without Model
• Development

Example Usage

import 'package:postgres/postgres.dart';
import 'package:sql_query_builder/sql_query_builder.dart';

class Actor extends Model {
  @override
  String get primaryKey => 'id';

  @Column()
  int? id;

  @Column(name: 'name')
  String? actorName;

  @Column()
  int? age;

  @Column(name: 'created_at')
  DateTime? createdAt;

  @Column(name: 'updated_at')
  DateTime? updatedAt;
}

void main() async {
  // create database connection and open
  PostgreSQLConnection db = PostgreSQLConnection(
    'localhost',
    5432,
    'postgres',
    username: 'admin',
    password: 'password',
  );
  await db.open();

  // initialize SqlQueryBuilder, only once at main function
  SqlQueryBuilder.initialize(database: db, debug: true);

  // and finally use model from anywhere in the project.
  Actor actor = Actor();
  actor.actorName = 'John Wick';
  actor.age = 60;
  await actor.save();

  actor.actorName = "Tom Cruise";
  await actor.save(); // save again

  print(actor.id);

  Actor result = await Actor().where('name', 'Tom Cruise').get();
  print(result.age);
}

Schema (to create database table)

create table

Schema.create('blog', (Table table) {
  table.id();
  table.string('slug').unique();
  table.string('title').nullable();
  table.char('status').withDefault('active');
  table.text('body');
  table.money('amount').nullable();
  table.softDeletes();
  table.timestamps();
});

drop/delete table

Schema.drop('blog');

Model

class Blog extends Model {
  @override
  String get primaryKey => 'id';

  @Column()
  int? id;

  @Column()
  String? title;

  @Column()
  String? status;

  @Column(name: 'body')
  String? description;

  @Column(name: 'created_at')
  DateTime? createdAt;

  @Column(name: 'updated_at')
  DateTime? updatedAt;
}

• String get primaryKey is optional default is id
• use @Column annotation to declare an attribute as a database column
• @Column support name (database key)

Soft Deletes

class Blog extends Model with SoftDeletes {
 // columns here
}

Save

Actor actor = Actor();
actor.name = 'John Wick';
actor.age = 60;
await actor.save();

Debug

Actor actor = Actor();
actor.name = 'John Wick';
actor.age = 60;
actor.debug(true);
await actor.save();

Reset query and create new one

• If you do not want to create new class and reuse existing class to do new query, use can use newQuery attribute.

List blog = Blog().where('status', 'active').where('user', 'super_user').get();

// reset existing get query and make new one using `newQuery`
List blog = await blog.newQuery.where('status', 'deleted').where('user', 'normal').get();

Query

insert or create

// single entry
await Actor().create(
  {'name': 'John Wick', 'age': 60}
);

await Actor().insert(
  {'name': 'John Wick', 'age': 60}
);

// multiple
await Actor().insertMultiple([
  {'name': 'John Wick', 'age': 60},
  {'name': 'John Doe', 'age': 25},
]);

update

await Actor()
  .where('id', 3)
  .where('status', 'active')
  .update({
    "name": "Updated AJ",
    "age": 120,
  });

count

await Actor().count();

await Actor().where('age', '>=' , 23).count();

find

await Actor().find(id); // find by id

await Actor().find('name', 'John Wick');

all

List actors = await Actor().all();
for(Actor actor in actors) {
  print(actor.id)
}

get

List actors = await Actor().where('name', 'John Wick').get();
for(Actor actor in actors) {
  print(actor.id)
}

toSql

String query = Actor().where('name', 'John Wick').toSql();
print(query)

NOTE:: currently there is an ongoing issue with List type

List<Actor> actors = await Actor().where('name', 'John Wick').get(); // will throw error

delete

await Actor().where('name', 'John Wick').delete();

forceDelete (only with SoftDeletes)

await Actor().where('name', 'John Wick').forceDelete();

withTrash (only with SoftDeletes)

List actors = await Actor().where('name', 'John Wick').withTrash().get();
for(Actor actor in actors) {
  print(actor.id)
}

select

await Actor()
  .select('id')
  .select('name')
  .where('name', 'John Wick').get();

await Actor()
  .select(['id', 'name', 'age']).where('name', 'John Wick').get();

await Actor()
  .select('id, name, age').where('name', 'John Wick').get();

where

// equal condition between column and value
await Actor().where('name', 'John Wick').get();

// custom condition between column and value
await Actor().where('name', '=', 'John Wick').get();
await Actor().where('age', '>=', 23).get();

orWhere

// equal condition between column and value
await Actor().orWhere('name', 'John Wick').get();

// custom condition between column and value
await Actor().orWhere('name', '=', 'John Wick').get();
await Actor().orWhere('age', '>=', 23).get();

whereRaw

await Actor().whereRaw('name = @name', {'name', 'John Wick'}).get();

orWhereRaw

await Actor().orWhereRaw('name = @name', {'name', 'John Wick'}).get();

chain where and orWhere

await Actor()
  .where('name', 'John Doe').orWhere('name', 'John Wick').get();

limit or take

await Actor().limit(10).get();
// or
await Actor().take(10).get();

offset

await Actor().limit(10).offset(10).get();

groupBy

await Actor()
  .select('count(*) as total, name').groupBy('name').get();

orderBy

// default order by with name column
await Actor().orderBy('name').get();

// order by column and type desc
await Actor().orderBy('name', 'desc').get();

// order by column and type asc
await Actor().orderBy('name', 'asc').get();

// multiple order by
await Actor()
  .orderBy('name', 'asc')
  .orderBy('id', 'desc')
  .get();

join

await Actor()
    .join('actor_info', 'actor_info.admin_id', 'admin.id')
    .get();

leftJoin

await Actor()
    .leftJoin('actor_info', 'actor_info.admin_id', 'admin.id')
    .get();

rightJoin

await Actor()
    .rightJoin('actor_info', 'actor_info.admin_id', 'admin.id')
    .get();

joinRaw

await Actor()
    .joinRaw('actor_info', 'actor_info.admin_id', 'admin.id')
    .get();

leftJoinRaw

await Actor()
    .leftJoinRaw('actor_info', 'actor_info.admin_id', 'admin.id')
    .get();

rightJoinRaw

await Actor()
    .rightJoinRaw('actor_info', 'actor_info.admin_id', 'admin.id')
    .get();

debug

await Actor().debug(true).all();

Custom Query Builder Without Model

await QueryBuilder.table('actor').get();

Development

Want to contribute? Great! Fork the repo and create PR to us.