Accessing Data

The @DriftDatabase annotation specifies a class that provides CRUD methods for database.

Basic CRUD Operations

Drift facilitates interaction with database tables through predefined methods.

Insert Operations

Basic Insert: Add new rows to a table.

// INSERT INTO users (id, name) VALUES (1, 'John Doe')
await database.into(database.users).insert(
  UsersCompanion.insert(id: Value(1), name: 'John Doe'),
);

Upsert Operation: Insert or update existing rows.

// INSERT INTO users (id, name) VALUES (1, 'John Doe')
// ON CONFLICT (id) DO UPDATE SET name = 'John Doe'
await database.into(database.users).insertOnConflictUpdate(
  UsersCompanion.insert(id: Value(1), name: 'John Doe'),
);

Batch Insert: Insert multiple rows simultaneously.

// Batch insert for users
await database.batch((batch) => batch.insertAll(
  database.users,
  [UsersCompanion.insert(id: Value(2), name: 'John Doe'), ...],
));

Update Operations

Single Row Update: Modify an existing row.

// Update user with id 1
await database.update(database.users).replace(
  UsersCompanion(id: Value(1), name: Value('Jane Doe')),
);

Conditional Update: Update multiple rows based on conditions.

// Conditional update for users
await (database.update(database.users)..where((tbl) => tbl.id.equals(1)))
  .write(UsersCompanion(name: Value('Jane Doe')));

Delete Operations

Delete Row: Remove rows from a table.

// Delete user with id 1
await (database.delete(database.users)..where((tbl) => tbl.id.equals(1))).go();

Select Operations

Retrieve Data: Fetch rows from a table.

// Select all users
await database.select(database.users).get();

Retrieve with Conditions: Apply clauses like WHERE, ORDER BY, OFFSET and LIMIT.

// Conditional data retrieval
await (database.select(database.users)
      ..where((tbl) => tbl.id.isBiggerThan(Constant(1)))
      // limit is often used with offset to implement pagination
      ..limit(20, offset: 20)
      ..orderBy([(tbl) => OrderingTerm(expression: tbl.name)]))
    .get();

Clauses

Drift supports common SQL clauses like WHERE, ORDER BY, OFFSET and LIMIT.

WHERE

Filter rows based on conditions.

// Select all users with id > 1
await (database.select(database.users)..where((tbl) => tbl.id.isBiggerThan(Constant(1)))).get();

ORDER BY

Sort rows based on columns.

// Select all users and sort them by name
await (database.select(database.users)..orderBy([(tbl) => OrderingTerm(expression: tbl.name)])).get();

OFFSET and LIMIT

Implement pagination.

// Select all users and limit the result to 20 rows
await (database.select(database.users)..limit(20)).get();

// Select all users and limit the result to 20 rows, starting from row 20
await (database.select(database.users)..limit(20, offset: 20)).get();

Like operator

Filter rows based on patterns.

// Select all users with name starting with 'John'
await (database.select(database.users)..where((tbl) => tbl.name.like('John%'))).get();

Logical Operators

Combine multiple conditions.

// Select all users with id > 1 and name starting with 'John'
await (database.select(database.users)
      ..where((tbl) => tbl.id.isBiggerThan(Constant(1)) & tbl.name.like('John%')))
    .get();

Complex Queries

Drift supports advanced SQL features like joins, subqueries, and transactions.

Joins

Combine data from multiple tables using INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER joins.

final result = await (database.select(database.users)
      .join([innerJoin(database.posts, database.posts.userId.equalsExp(database.users.id))]))
    .get();

final usersWithPosts = result.map((row) {
  return (row.readTable(database.users), row.readTable(database.posts));
}).toList();

Group By

Group rows based on columns.

// Select all users and group them by name
await (database.select(database.users)..groupBy([database.users.name])).get();

Subqueries

Nested queries for complex conditions.

await (database.select(database.users)
  ..where((tbl) => tbl.id.isInQuery(database.selectOnly(database.posts)..addColumns([database.posuserId]))))
.get();

Transactions

Ensures atomic operations.

await database.transaction(() async {
  final userId = await database.into(database.users).insert(
        UsersCompanion.insert(id: Value(1), name: 'John Doe'),
      );
  await database.into(database.posts).insert(
        PostsCompanion.insert(userId: userId, title: 'Hello'),
      );
});

Drift Files ✨

Define complex queries in .drift files.

-- Drift file example
postsPerUser:
  SELECT users.name, COUNT(posts.id) AS post_count
  FROM users
  LEFT JOIN posts ON posts.user_id = users.id
  GROUP BY users.id;

The biggest advantage of drift files is that Drift can generate type-safe code for them. For example, the above drift file would generate the following code:

class PostsPerUserResult {
  final String name;
  final int postCount;
  PostsPerUserResult({required this.name, required this.postCount});
}

We can use it like this:

// List<PostsPerUserResult>
final result = await database.postsPerUser().get();

// Let's get the first result
final firstPair = result.first;

// firstPair is of type PostsPerUserResult
print(firstPair.name); // John Doe
print(firstPair.postCount); // 2

For detailed documentation, visit Drift Documentation.

DAOs (Data Access Objects)

DAOs are classes that provide methods to perform database operations. Their purpose is to abstract the database layer from the rest of the application, much like data sources do for network requests in Sizzle architecture.

class SimpleDao {
  SimpleDao(this.db);
  final Database db;

  Future<void> addUser(String name) async {
    await db.into(db.users).insert(UsersCompanion.insert(name: name));
  }

  Future<List<User>> getUsers() async {
    return await db.select(db.users).get();
  }
}

Drift also provides a way to generate DAOs. For more information, visit Drift Documentation.