Building a Local-First Flutter App: Seamless SQLite Export/Import to Google Drive

To honor this principle, I decided to build a local-first application using SQLite. No third-party servers, no hidden databases. Everything lives on the user’s device. However, absolute data privacy brings up a critical UX challenge: How do we ensure users don’t lose their data when they switch or lose their phones?

The solution: Seamless Cloud Export & Import via Google Drive.

In this article, I will share the architecture, the OAuth 2.0 nuances, and the performance bottlenecks (and solutions) I encountered while building this feature.

1. Introduction & Background

The Cloud Export/Import feature is highly essential for offline-first applications. Users want peace of mind knowing their data has a safe backup.

In this implementation, we will use the Google Drive API and Google Sign-In. The workflow is quite straightforward:

1. Export: The app reads all tables from the local SQLite database, converts them into JSON format, temporarily saves the JSON file to a local directory, and uploads it to the user’s Google Drive account.
2. Import: The app looks for the latest JSON backup file in Google Drive, downloads it, clears the old local data, and then inserts the data from the JSON back into SQLite.

For this guide, we are utilizing the googleapis, google_sign_in, and drift (as our SQLite ORM) packages.

2. Step-by-Step Implementation

Step 1: Google Cloud Console & OAuth Setup

Before writing any Flutter code, you must configure your project in the Google Cloud Console. Without this, your app won’t be authorized to use Google Sign-In or access Drive.

1. Enable Google Drive API: Go to the Google Cloud Console, select your project, navigate to APIs & Services > Library, search for “Google Drive API”, and enable it.
2. Configure OAuth Consent Screen: Go to OAuth consent screen.

• Choose External (unless you are a Google Workspace user restricting the app to your organization).
• Fill in the required app information.
• Crucial Step for Development: While your app’s publishing status is set to “Testing”, Google strictly blocks logins. You must add your testing email addresses under the Test users section. Otherwise, you will encounter a 403 access_denied error when trying to log in.

3. Create Credentials:

• If you are using Firebase, simply enable Google Sign-In in Firebase Authentication, add your Android SHA-1 fingerprint, and download the google-services.json (and GoogleService-Info.plist for iOS). Firebase automatically configures the OAuth Client ID for you behind the scenes!

• If not using Firebase, go to Credentials > Create Credentials > OAuth client ID and set it up manually for Android and iOS.

How to Get SHA-1 Fingerprint for Firebase (Google Sign-In Android)

To use Google Sign-In with Firebase on Android, you must add your app’s SHA-1 fingerprint to Firebase.

Method 1: Gradle (Recommended)

1. Open terminal in your Flutter project
2. Navigate to Android folder:

cd android

1. Run:

• Mac/Linux:

./gradlew signingReport

• Windows:

gradlew signingReport

1. Copy the SHA-1 from:

Variant: debug
SHA1: XX:XX:XX:...

Method 2: Keytool (Alternative)

• Windows:

keytool -list -v -keystore "%USERPROFILE%\.android\debug.keystore" -alias androiddebugkey -storepass android -keypass android

• Mac/Linux:

keytool -list -v -keystore ~/.android/debug.keystore -alias androiddebugkey -storepass android -keypass android

Copy the SHA1 value from the output.

Step 2: Google API Client Configuration

The Google Drive API requires an HTTP client injected with Auth Headers (OAuth Token) from the currently logged-in user. Let’s create a custom HTTP Client first:

import 'package:http/http.dart' as http;

class GoogleAuthClient extends http.BaseClient {
  final Map<String, String> _headers;
  final http.Client _client = http.Client();

  GoogleAuthClient(this._headers);

  @override
  Future<http.StreamedResponse> send(http.BaseRequest request) {
    return _client.send(request..headers.addAll(_headers));
  }
}

The OAuth 2.0 Flow: Firebase vs. Google Workspace

One of the most confusing parts of integrating Google Drive in a Flutter app is navigating the authentication flow, especially if you are also using Firebase.

The Golden Rule: Firebase Authentication tokens cannot be used to access Google Workspace APIs (like Google Drive or Google Sheets).

When users click “Sign in with Google,” you must explicitly request the Drive API scope before authenticating with Firebase.

import 'package:google_sign_in/google_sign_in.dart';
import 'package:googleapis/drive/v3.dart' as drive;

final GoogleSignIn _googleSignIn = GoogleSignIn(scopes: [drive.DriveApi.driveFileScope]);

When you want to export or import data, do not use FirebaseAuth.instance.currentUser. Instead, extract the raw Auth Headers from the GoogleSignInAccount:

final account = _googleSignIn.currentUser ?? await _googleSignIn.signIn();
if (account == null) throw Exception("User not authenticated");
final authHeaders = await account.authHeaders;
final client = GoogleAuthClient(authHeaders);
final driveApi = drive.DriveApi(client);

Step 3: Initialize Google Sign-In with Drive Scope

When triggering Google Sign-In, we must include the drive.file scope. This scope is highly secure and recommended because it only gives our app access to files created by our app itself (meaning the app cannot see the user's personal photos, documents, or other Drive files).

import 'package:google_sign_in/google_sign_in.dart';
import 'package:googleapis/drive/v3.dart' as drive;

class AuthBackupDataSourceImpl implements AuthBackupDataSource {
  final DatabaseImpl databaseImpl;
  final GoogleSignIn _googleSignIn = GoogleSignIn(
    scopes: [drive.DriveApi.driveFileScope]
  );

  AuthBackupDataSourceImpl({required this.databaseImpl});

  // ... authentication methods
}

Step 4: Export Logic (Data Backup)

The export process involves three main stages: Database Query -> JSON Conversion -> Upload to Drive.

@override
Future<void> exportAndUploadToDrive() async {
  // 1. Authenticate User
  final account = _googleSignIn.currentUser ?? await _googleSignIn.signIn();
  if (account == null) throw Exception("User not authenticated");

  final authHeaders = await account.authHeaders;
  final client = GoogleAuthClient(authHeaders);
  final driveApi = drive.DriveApi(client);

  // 2. Fetch all data from SQLite (using Drift)
  final users = await databaseImpl.select(databaseImpl.user).get();
  final banks = await databaseImpl.select(databaseImpl.banks).get();
  final wallets = await databaseImpl.select(databaseImpl.wallets).get();
  final categories = await databaseImpl.select(databaseImpl.categories).get();
  final budget = await databaseImpl.select(databaseImpl.budget).get();
  final transactions = await databaseImpl.select(databaseImpl.transactions).get();

  // 3. Convert to Map and Encode to JSON
  final backupData = {
    "user": users.map((e) => e.toJson()).toList(),
    "banks": banks.map((e) => e.toJson()).toList(),
    "wallets": wallets.map((e) => e.toJson()).toList(),
    "categories": categories.map((e) => e.toJson()).toList(),
    "budget": budget.map((e) => e.toJson()).toList(),
    "transactions": transactions.map((e) => e.toJson()).toList(),
  };

  String jsonData = jsonEncode(backupData);

  // 4. Save temporarily to the device's local storage
  final directory = await getTemporaryDirectory();
  final file = File("${directory.path}/backup.json");
  await file.writeAsString(jsonData);

  // 5. Upload to Google Drive
  var driveFile = drive.File();
  // Assign a unique name using a timestamp
  driveFile.name = "app_backup_${DateTime.now().millisecondsSinceEpoch}.json";
  driveFile.mimeType = "application/json";

  var media = drive.Media(file.openRead(), file.lengthSync());
  await driveApi.files.create(driveFile, uploadMedia: media);
}

Step 5: Import Logic (Data Restore)

The import process acts in reverse: Search for the File in Drive -> Download -> Parse JSON -> Insert into SQLite.

@override
Future<void> importAndRestoreFromDrive() async {
  // 1. Authenticate & Initialize Client
  final account = _googleSignIn.currentUser ?? await _googleSignIn.signIn();
  if (account == null) throw Exception("User not authenticated");

  final authHeaders = await account.authHeaders;
  final client = GoogleAuthClient(authHeaders);
  final driveApi = drive.DriveApi(client);

  // 2. Search for the latest JSON backup file in Drive
  var fileList = await driveApi.files.list(
    q: "name contains 'app_backup' and mimeType = 'application/json' and trashed = false",
    orderBy: "createdTime desc",
  );

  if (fileList.files == null || fileList.files!.isEmpty) return;
  String fileId = fileList.files!.first.id!;

  // 3. Clear old data to prevent duplicates or conflicts
  await databaseImpl.delete(databaseImpl.transactions).go();
  await databaseImpl.delete(databaseImpl.wallets).go();
  await databaseImpl.delete(databaseImpl.budget).go();
  // Delete only custom categories (keep default ones)
  await (databaseImpl.delete(databaseImpl.categories)
    ..where((tbl) => tbl.isDefault.equals(false)))
      .go();

  // 4. Download the file from Drive
  drive.Media media = await driveApi.files.get(
    fileId,
    downloadOptions: drive.DownloadOptions.fullMedia,
  ) as drive.Media;

  List<int> bytes = [];
  await for (var data in media.stream) {
    bytes.addAll(data);
  }

  // 5. Parse JSON
  String jsonString = utf8.decode(bytes);
  Map<String, dynamic> backupData = jsonDecode(jsonString);

  // 6. Insert data into the Database (Wrap in a transaction for safety & speed)
  await databaseImpl.transaction(() async {
    if (backupData.containsKey('user')) {
      for (var item in backupData['user']) {
        await databaseImpl.into(databaseImpl.user).insert(UserData.fromJson(item), mode: InsertMode.insertOrReplace);
      }
    }
    // Repeat the insertion loop for banks, wallets, categories, budget, and transactions
    if (backupData.containsKey('transactions')) {
      for (var item in backupData['transactions']) {
        await databaseImpl.into(databaseImpl.transactions).insert(Transaction.fromJson(item), mode: InsertMode.insertOrReplace);
      }
    }
  });
}

https://youtu.be/kqwZQ49DZ2w

3. Problems and Bottlenecks

Although the architecture above runs perfectly for initial use and smaller datasets, I quickly realized there are performance bottlenecks if the app starts holding tens of thousands of transaction records:

1. Out of Memory (OOM): Pulling 100,000 rows of data from SQLite into the device’s RAM (as a List<Map>) all at once can cause the app to crash (Force Close), especially on low-end Android devices.
2. UI Freeze (Jank): The jsonEncode() and jsonDecode() processes run synchronously on the Dart Main Thread. Converting a massive JSON payload will freeze your app's UI and animations for several seconds.

Optimization Plans:

• To overcome the UI freeze, the JSON encoding/decoding process should be moved to a background thread using Flutter’s compute() function or an Isolate.
• However, the ultimate best practice is to directly back up the raw .db SQLite file to Google Drive, rather than converting it to JSON. This entirely bypasses the CPU-heavy conversion process and results in a much smaller backup file size.

4. Conclusion

Building a local-first application doesn’t mean you have to sacrifice the convenience of cloud backups. By leveraging the Google Drive API, we can provide total peace of mind to our users without incurring backend database server costs.

The approach of converting SQLite to JSON is great for cross-platform data readability, but it must be heavily optimized as user data grows.

Do you have a different approach or experience handling local data backups in Flutter? I’d love to hear your thoughts — let’s discuss in the comments section!