“This is the 20th day of my participation in the November Gwen Challenge. See details of the event: The Last Gwen Challenge 2021”.

This article is translated from the official documentation of Drift Getting Started with SQL (Simonbinder.eu).

Meat turn more deficiencies, generous advice.

Important notice: MOOR has been renamed drift. More information [英 文].

Using SQL

Learn how to start using the SQL version of Drift or how to migrate from an existing project to Drift.

The general usage guide (In Chinese) explains how to start using Drift in DART by declaring tables and queries. The current (SQL) version focuses on using Drift through SQL.

Add the dependent

This part contains information about Flutter Persistence library Drift-Introduction to Flutter Excavation (juejin. Cn) (juejin.cn/post/703020…) In the relevant content of the same, not repeated translation.

Declare tables and queries

To declare tables and queries in SQL, create a tables.drift file and place it in the same directory as the DART file (e.g. Lib /database/table.drift).

You can place the CREATE TABLE statement for the query in this file. The following example creates two tables to model todo-app. If you are migrating an existing project to Drift, you simply copy the existing CREATE TABLE statement into this file.

tables.drift:

-- tables. Drift file
CREATE TABLE todos (
    id INT NOT NULL PRIMARY KEY AUTOINCREMENT,
    title TEXT,
    body TEXT,
    category INT REFERENCES categories (id)
);

CREATE TABLE categories (
    id INT NOT NULL PRIMARY KEY AUTOINCREMENT,
    description TEXT
) AS Category; -- "AS Category" is explained below

/* After the table is created, you can put the query in it. Just write out the query name, (colon), and SQL. * /
todosInCategory: SELECT * FROM todos WHERE category =? ;/Here is a more complex query. It's going to count the entities for each category, and the values that it counts are in those entities, not in any category.*/
countEntries:     
  SELECT
    c.description,
    (SELECT COUNT(*) FROM todos WHERE category = c.id) AS amount
  FROM categories c
  UNION ALL
  SELECT null, (SELECT COUNT(*) FROM todos WHERE category IS NULL)
Copy the code

About the AS a Category

Drift creates DART classes for tables whose class names are based on the table names. By default, drift simply removes the s after the table name. This works in most cases, but in some cases (like the Categories table above) it doesn’t work properly. We want to generate the Category class, not Categorie, so we need to add an AS declaration at the end of the statement to tell drift to generate a different table name.

Generate matching code

With the table declared, let’s generate some actual DART code to execute. Drift needs to know which tables are used in the database, so we need to write a small DART class that Drift will then read. Create a file called database.dart under the same directory as the tables.drift you created in the previous step.

import 'dart:io';

import 'package:drift/drift.dart';
// The following imports are only used to open the database.
import 'package:drift/native.dart';
import 'package:path_provider/path_provider.dart';
import 'package:path/path.dart' as p;

part 'database.g.dart';

@DriftDatabase(
  //.drift file related import. Drift also supports import from 'package:'
  include: {'tables.drift'},)class AppDb extends _$AppDb {
  AppDb() : super(_openConnection());

  @override
  int get schemaVersion => 1;
}

LazyDatabase _openConnection() {
  // The LazyDatabase utility class lets us find a suitable place to store asynchronous files.
  return LazyDatabase(() async {
    // Store a database file named db.sqlite in the Documents directory.
    final dbFolder = await getApplicationDocumentsDirectory();
    final file = File(p.join(dbFolder.path, 'db.sqlite'));
    return NativeDatabase(file);
  });
}
Copy the code

Execute the flutter pub run build_runner build command on the command line to generate the database.g.art file containing the _$AppDb parent.

What does drift produce

Take a look at what the Drift generated during the build:

  • Generate data classes (Todo 和 Category) – These classes hold a single row of data for their respective tables.
  • Sister versions of these classes. These are only relevant when using dirft’s DART API. More can be found here.
  • aCountEntriesResultClass, which keeps runningcountEntriesThe resulting data row after the query.
  • a_$AppDbThe parent class. This class is responsible for creating tables when the database file is first opened. It’s also included intables.driftThe type-safe method for the query declared in the file:
    • aSelectable todosInCategory(int)Method to execute the above declarationtodosInCategoryThe query. Drift has set the variable type of the query tointBecause this is used for (where) comparisonscategoryColumn type.

      Method returns aSelectableTo indicate that it can be used for general queries (Selectable.getreturnFuture), or as an automatically updated stream (with.watchInstead of.get()).
    • aSelectable countEntries()Method when used to perform other queries.

By the way, you can also put insert, update, and delete statements in your.drift file. Drift also generates matching code for them.

To learn more

Now you know how to use SQL in Drift. Here’s a further guide to help you learn more.

  • The SQL IDE provides feedback for SQL queries in the editor.
  • The transaction
  • Schema migration
  • Write queries and expressions in Dart
  • A aboutdriftOf the fileIn-depth guide, explains the impport statement and darT-SQL interaction.