how-to-build-a-graph-database-with-flutter

What this skill does

# How to build a graph database with Flutter


In this article I will go over how to create and use a graph database with [Flutter](https://flutter.dev/).

**TLDR** The final source [here](https://github.com/rodydavis/flutter_graph_database) and an online [demo](https://rodydavis.github.io/flutter_graph_database/).

## Prerequisites 

Flutter installed and setup (Refer to this [article](https://rodydavis.com/posts/first-flutter-project/) if you need help).

Basic knowledge of [SQLite](https://www.sqlite.org/index.html).

Basic knowledge of Graph Databases (Refer to this [video](https://www.youtube.com/watch?v=GekQqFZm7mA) if you need to learn more).

## Overview 

First of all, why do we need a graph database when other storage options exist?

Why not use key value stores, document stores, or relational databases?

Well, the answer is that it depends on the problem you are trying to solve.

Graph databases are great for modeling relationships between data.

A couple examples:

*   A social network app can model the relationships between users and posts
*   A game can model the relationships between players and items
*   A blog can model the relationships between posts and comments

The possibilities are endless.

Instead of storing data in a table for each collection we store the data as a graph in a nodes and edges table with some additional extensions in SQLite to make it easier.

Here is a [page](https://www.hytradboi.com/2022/simple-graph-sqlite-as-probably-the-only-graph-database-youll-ever-need) that goes in to detail about it and showcases what we are trying to build.

## Getting Started 

First we need to create a new Flutter project.

```
mkdir flutter_graph_database
cd flutter_graph_database
flutter create .
```

After the project is created open it in your favorite code editor.

```
code .
```

## Creating the Database 

We are going to use the [drift](hhttps://pub.dev/packages/drift) package to create the database.

Update the **pubspec.yaml** file with the following:

```
name: flutter_graph_database
description: A new Flutter package project.
version: 0.0.1
publish_to: none

environment:
  sdk: ">=2.19.0-238.0.dev <3.0.0"
  flutter: ">=1.17.0"

dependencies:
  flutter:
    sdk: flutter
  drift: ^2.1.0
  sqlite3_flutter_libs: ^0.5.5
  http: ^0.13.5
  path_provider: ^2.0.0
  path: ^1.8.2
  sqlite3: ^1.7.0

dev_dependencies:
  flutter_test:
    sdk: flutter
  flutter_lints: ^2.0.0
  build_runner: ^2.2.0
  drift_dev: ^2.1.0

flutter:
```

### Database Connection 

Next we need to create the database.

Create a new file at **lib/database/connection/unsupported.dart** and update it with the following:

```
import 'package:drift/drift.dart';
import 'package:drift/native.dart';

DatabaseConnection connect(
  String dbName, {
  bool useWebWorker = false,
  bool logStatements = false,
}) {
  return DatabaseConnection(NativeDatabase.memory(
    logStatements: logStatements,
  ));
}
```

Create a new file at **lib/database/connection/native.dart** and update it with the following:

```
import 'dart:io';
import 'dart:isolate';

import 'package:drift/drift.dart';
import 'package:drift/isolate.dart';
import 'package:drift/native.dart';
import 'package:path_provider/path_provider.dart';
import 'package:path/path.dart' as p;

DatabaseConnection connect(
  String dbName, {
  bool useWebWorker = false,
  bool logStatements = false,
}) {
  return DatabaseConnection.delayed(Future.sync(() async {
    final appDir = await getApplicationDocumentsDirectory();
    final dbPath = p.join(appDir.path, dbName);

    final receiveDriftIsolate = ReceivePort();
    await Isolate.spawn(_entrypointForDriftIsolate,
        _IsolateStartRequest(receiveDriftIsolate.sendPort, dbPath));

    final driftIsolate = await receiveDriftIsolate.first as DriftIsolate;
    return driftIsolate.connect();
  }));
}

class _IsolateStartRequest {
  final SendPort talkToMain;
  final String databasePath;

  _IsolateStartRequest(this.talkToMain, this.databasePath);
}

void _entrypointForDriftIsolate(_IsolateStartRequest request) {
  final databaseImpl = NativeDatabase(
    File(request.databasePath),
    logStatements: false,
  );

  final driftServer = DriftIsolate.inCurrent(
    () => DatabaseConnection(databaseImpl),
  );

  request.talkToMain.send(driftServer);
}
```

Create a new file at **lib/database/connection/web.dart** and update it with the following:

```
import 'dart:async';

// ignore: avoid_web_libraries_in_flutter
import 'dart:html';

import 'package:drift/drift.dart';
import 'package:drift/remote.dart';
import 'package:drift/web.dart';
import 'package:drift/wasm.dart';
import 'package:http/http.dart' as http;
import 'package:sqlite3/wasm.dart';

DatabaseConnection connect(
  String dbName, {
  bool useWebWorker = false,
  bool logStatements = false,
}) {
  if (useWebWorker) {
    final worker = SharedWorker('shared_worker.dart.js');
    return remote(worker.port!.channel());
  } else {
    return DatabaseConnection.delayed(Future.sync(() async {
      final response = await http.get(Uri.parse('sqlite3.wasm'));
      final fs = await IndexedDbFileSystem.open(dbName: '/db/');
      final path = '/drift/db/$dbName';
      final sqlite3 = await WasmSqlite3.load(
        response.bodyBytes,
        SqliteEnvironment(fileSystem: fs),
      );
      final databaseImpl = WasmDatabase(
        sqlite3: sqlite3,
        path: path,
        fileSystem: fs, // <- this is required but not documented
        logStatements: logStatements,
      );
      return DatabaseConnection(databaseImpl);
    }));
  }
}
```

Create a new file at **lib/database/connection/connection.dart** and update it with the following:

```
export 'unsupported.dart'
    if (dart.library.js) 'web.dart'
    if (dart.library.ffi) 'native.dart';
```

### Database SQL Files 

#### Schema 

Create a new file at **lib/database/sql/schema.drift** and update it with the following:

```
CREATE TABLE IF NOT EXISTS nodes (
    body TEXT,
    id   TEXT GENERATED ALWAYS AS (json_extract(body, '$.id')) VIRTUAL NOT NULL UNIQUE
);

CREATE INDEX IF NOT EXISTS id_idx ON nodes(id);

CREATE TABLE IF NOT EXISTS edges (
    source     TEXT,
    target     TEXT,
    properties TEXT,
    UNIQUE(source, target, properties) ON CONFLICT REPLACE,
    FOREIGN KEY(source) REFERENCES nodes(id),
    FOREIGN KEY(target) REFERENCES nodes(id)
);

CREATE INDEX IF NOT EXISTS source_idx ON edges(source);
CREATE INDEX IF NOT EXISTS target_idx ON edges(target);
```

> The ID column is a virtual column that is generated from the body column. This is done so that we can query the database by ID without having to parse the JSON body column.

#### Queries 

Create a new file at **lib/database/sql/queries.drift** and update it with the following:

```
import 'schema.drift';

getAllNodes: 
    SELECT * FROM nodes;
getAllEdges: 
    SELECT * FROM edges;
```

#### Delete Edge 

Create a new file at **lib/database/sql/delete-edge.drift** and update it with the following:

```
import 'schema.drift';

deleteEdge:
    DELETE FROM edges 
    WHERE source = ? OR target = ?;
```

#### Delete Node 

Create a new file at **lib/database/sql/delete-node.drift** and update it with the following:

```
import 'schema.drift';

deleteNode: 
    DELETE FROM nodes 
    WHERE id = ?;
```

#### Insert Edge 

Create a new file at **lib/database/sql/insert-edge.drift** and update it with the following:

```
import 'schema.drift';

insertEdge(:source as TEXT, :target as TEXT, :body as TEXT): 
    INSERT INTO edges VALUES(:source, :target, json(:body));
```

#### Search Edges Inbound 

Create a new file at **lib/database/sql/search-edges-inbound.drift** and update it with the following:

```
import 'schema.drift';

searchEdgesInbound: 
    SELECT * FROM edges 
    WHERE source = ?;
```

#### Search Edges Outbound 

Create a new file at **lib/database/sql/search-edges-outbound.drift** and update it with the following:

```
import 'schema.drift';

searchEdgesOutbound: 
    SELECT * FR