database 0.3.3

A database access API that's vendor-agnostic. Adapters exist for browser APIs, SQLite, Google Cloud Firestore, PostgreSQL, ElasticSearch, Algolia, and others.

Introduction

This is database.dart, a vendor-agnostic database access API for Flutter and other Dart projects.

The package gives you:

• Document database API.
  • Our document database API works with a wide range of products, including document databases, SQL databases, and search engines.
• SQL database API
  • You can use raw SQL when you need to.
• Search engine support
  • The API supports forwarding specific queries to search engines that can, for example, handle natural language queries better than transaction databases.
  • There are already several search engines already supported: Algolia, ElasticSearch, and a simple search engine written in Dart.

Copyright 2020 Gohilla Ltd. Licensed under the Apache License 2.0.

Links

• Github project
• Issue tracker
• Pub package
• API reference

Contributing

• Just create a pull request in Github. :)

Supported products and APIs

Document databases

• Azure Cosmos DB (website)
  • Package (not ready for use): database_adapter_azure (Github
• Google Cloud Firestore (website)
  • Browser package: database_adapter_firestore_browser (Github)
  • Flutter (iOS /Android) package: database_adapter_firestore_flutter (Github)
• Have a package? Add it here!

SQL databases

• PostgreSQL (website)
  • Package: database_adapter_postgre (Github)
• SQLite (website)
  • Package: database_adapter_sqlite (Github)
• Have a package? Add it here!

Search engines

• Algolia (website)
  • Package: database_adapter_algolia (Github)
• Azure Cognitive Search (search)
  • Package (not ready for use): database_adapter_azure (Github
• Elasticsearch (website))
  • Package: database_adapter_elasticsearch (Github)
• Have a package? Add it here!

Other

• Web APIs
  • BrowserDatabaseAdapter (Github) uses the best available web API.
  • LocalStorageDatabaseAdapter (Github).
• Memory
  • MemoryDatabaseAdapter (Github) keeps data in memory. Good for tests and caching.
• Have a package? Add it here!

Middleware

• CachingDatabaseAdapter (Github)
  • Caches data in another database (such as MemoryDatabaseAdapter).
• SchemaEnforcingDatabaseAdapter (Github)
  • Enforces schemas on reads/writes.
• SearchEnginePromotingDatabaseAdapter (Github)
  • Forwards cache-accepting search requests to a search engine.
• SearchingDatabaseAdapter in package search (Github) provides minimalistic search engine for small collections.
• Have a package? Add it here!

Getting started

1.Add dependency

In pubspec.yaml, add:

dependencies:
  database: any

2.Choose adapter

Look at the earlier list of adapters.

For example:

import 'package:database/database.dart';

final database = MemoryDatabaseAdapter().database();

Main API

Overview

If you have used some other document-oriented API (such as Google Firestore), this API will feel familiar to you. A database is made of document collections. A document is an arbitrary tree of values that may contain references to other documents.

See the classes:

• Database
• Collection
• Document
• Query
• QueryResult
• Snapshot

For example, this is how you would store a recipe using MemoryDatabaseAdapter (our in-memory database):

Future<void> main() async {
  // Use an in-memory database
  final database = MemoryDatabase();

  // Our collection
  final collection = database.collection('pizzas');

  // Our document
  final document = collection.newDocument();

  // Insert a pizza
  await document.insert({
    'name': 'Pizza Margherita',
    'rating': 3.5,
     'ingredients': ['dough', 'tomatoes'],
    'similar': [
      database.collection('recipes').document('pizza_funghi'),
    ],
  });

  // ...
}

Supported data types

• null
• bool
• int
• Int64
• double
• Date
• DateTime
• Timestamp
• GeoPoint
• String
• Uint8List
• List
• Map<String,Object>
• Document (a reference to another document)

Inserting documents

Use collection.insert(), which automatically generates a document ID for you:

final document = await database.collection('product').insert({
  'name: 'Coffee mug',
  'price': 8.50,
});

If you want to use a specific document identifier, you can use use collection.document('id').insert(...):

await database.collection('product').document('coffeeMugId').insert({
  'name: 'Coffee mug',
  'price': 8.50,
});

Updating documents

Use document.patch() for updating individual properties:

await product.patch(
  {
    'price': 12.50,
  },
);

If you want to update all properties, use document.update().

If you want to update the document even when it doesn't exist, use document.upsert().

Deleting documents

Use document.delete():

await document.delete();

Reading documents

You can read a snapshot with document.get(). In this example, we read a snapshot from a regional master database. If it's acceptable to have a locally cached version, you should use Reach.local.

final snapshot = await document.get(reach: Reach.regional);

// Use 'exists' to check whether the document exists
if (snapshot.exists) {
  final price = snapshot.data['price'];
  print('price: $price');
}

Watching changes in documents

You can watch document changes with document.watch(). Some databases support this natively. In other databases, the implementation may use polling.

final stream = await document.watch(
  pollingInterval: Duration(seconds:2),
);

Transactions

Use database.runInTransaction():

await database.runInTransaction((transaction) async {
  final document = database.collection('products').document('coffeeMugId');
  final snapshot = await transaction.get(document);
  final price = snapshot.data['price'] as double;
  await transaction.patch(document, {
    'price': price + 1.50,
  });
), timeout: Duration(seconds:3);

Searching documents

You can search documents with collection.search(), which takes a Query.

For example:

// Define what we are searching
final query = Query(
  filter: MapFilter({
    'category': OrFilter([
      ValueFilter('computer'),
      ValueFilter('tablet'),
    ]),
    'price': RangeFilter(min:0, max:1000),
  }),
  skip: 0, // Start from the first result item
  take: 10, // Return 10 result items
);

// Send query to the database
final result = await database.collection('product').search(
  query: query,
  reach: Reach.server,
);

The result is QueryResult, which contains a Snapshot for each item:

// For each snapshots
for (var snapshot in result.snapshots) {
  // Get price
  final price = snapshot.data['price'] as double;
  print('price: $price');
}

Supported logical filters

• AndFilter
  • AndFilter([ValueFilter('f0'), ValueFilter('f1')])
• OrFilter
  • OrFilter([ValueFilter('f0'), ValueFilter('f1')])
• NotFilter
  • NotFilter(ValueFilter('example'))

Supported structural filters

• MapFilter
  • MapFilter({'key': ValueFilter('value')})
• ListFilter
  • ListFilter(items: ValueFilter('value'))

Supported primitive filters

• ValueFilter
  • ValueFilter(3.14)
• RangeFilter
  • RangeFilter(min:3)
  • RangeFilter(min: Date(2020,01,01), max: Date(2020,06,01))
  • RangeFilter(min:0.0, max:1.0, isExclusiveMax:true)
• GeoPointFilter
  • GeoPointFilter(near:GeoPoint(1.23, 3.45), maxDistanceInMeters:1000)

Using SQL

By using SqlClient, you can interact with the database using SQL:

import 'package:database/sql.dart';
import 'package:database_adapter_postgre/database_adapter_postgre.dart';

Future main() async {
    // In this example, we use PostgreSQL adapter
    final database = Postgre(
      host:         'localhost',
      user:         'database user',
      password:     'database password',
      databaseName: 'example',
    ).database();

    // Get SQL client.
    final sqlClient = database.sqlClient;

    // Select all pizza products with price less than 10.
    //
    // This will return a value of type:
    //   Iterable<Map<String,Object>>
    final pizzas = await sqlClient.query(
      'SELECT * FROM product WHERE type = ?, price < ?',
      ['pizza', 10],
    ).toMaps();

    for (var pizza in pizzas) {
      print(pizza['name']);
    }
}

Selecting rows

Use SQL selection helper:

final pizzas = await sqlClient
  .table('Product')
  .whereColumn('category', 'pizza')
  .descending('price')
  .select(columnNames:['name', 'price'])
  .toMaps();

The above is just another way to execute:

final pizzas = await sqlClient.query(
  'SELECT FROM Product (name, price) WHERE category = ? ORDER BY DESCENDING price,
  ['pizza'],
).toMaps();;

Inserting rows

Use SQL table helper:

await sqlClient.table('Product').insert({
  'name': 'Pizza Hawaii',
  'category': 'pizza',
  'price': 8.50,
});

The above is just another way to execute:

await sqlClient.execute(
  'INSERT INTO Product (name, price) VALUES (?, ?)',
  ['Pizza Hawaii', 8.50],
);

Deleting rows

Use SQL selection helper:

await sqlClient.table('Product').where('price < ?', [5.0]).deleteAll();

The above is just another way to execute:

await sqlClient.execute('DELETE FROM Product WHERE price < ?', [5.0]);

Transactions

Use sqlClient.runInTransaction():

await sqlClient.runInTransaction((transaction) async {
  final values = await transaction.query('...').toMaps();
  // ...

  await transaction.execute('...');
  await transaction.execute('...');
  // ...
), timeout: Duration(seconds:3));

Migrations

await sqlClient.createTable('TableName');
await sqlClient.dropTable('TableName');

await sqlClient.table('TableName').createColumn('ColumnName', 'TypeName');
await sqlClient.table('TableName').renameColumn(oldName:'OldName', newName:'NewName');
await sqlClient.table('TableName').dropColumn('ColumnName');

await sqlClient.table('TableName').createForeignKeyConstraint(
  constraintName: 'ConstraintName',
  localColumnNames: ['Column0', 'Column1', 'Column2'],
  foreignTable: 'ForeignTableName',
  foreignColumnNames: ['Column0', 'Column1', 'Column2']
);
await sqlClient.table('TableName').dropConstraint('ConstraintName');

await sqlClient.table('TableName').createIndex('IndexName', ['Column0', 'Column1', 'Column2']);
await sqlClient.table('TableName').dropIndex('IndexName');

Parsing natural language queries

Query.parse enables parsing search queries from strings.

The supported syntax is almost identical to syntax used by Apache Lucene, a popular search engine written in Java. Lucene syntax itself is similar to syntax used by search engines such as Google or Bing. Keywords are parsed into KeywordFilter instances. Note that most database adapters do not support keywords. If you use keywords, make sure you configure a specialized text search engine.

Example

final query = Query.parse(
  'Coffee Mug price:<=10',
  skip: 0,
  take: 10,
);

...returns the following query:

final query = Query(
  filter: AndFilter([
    KeywordFilter('Coffee),
    KeywordFilter('Mug'),
    MapFilter({
      'price': RangeFilter(max:10),
    }),
  ]),
  skip: 0,
  take: 10,
);

Supported query syntax

Examples:

• norwegian forest cat
  • Matches keywords "norwegian", "forest", and "cat".
• "norwegian forest cat"
  • A quoted keyword ensures that the words must appear as a sequence.
• cat AND dog
  • Matches keywords "cat" and "dog" (in any order).
• cat OR dog
  • Matches keyword "cat", "dog", or both.
• pet -cat
  • Matches keyword "pet", but excludes documents that match keyword "cat".
• color:brown
  • Color matches keyword "brown".
• color:="brown"
  • Color is equal to "brown".
• weight:>=10
  • Weight is greater than or equal to 10.
• weight:[10 TO 20]
  • Weight is between 10 and 20, inclusive.
• weight:{10 TO 20}
  • Weight is between 10 and 20, exclusive.
• (cat OR dog) AND weight:>=10
  • An example of grouping filters.

In equality and range expressions, the parser recognizes:

• null
• false, true
• 3
• 3.14
• 2020-12-31 (Date)
• 2020-12-31T00:00:00Z (DateTime)

For example:

• weight:=10 --> MapFilter({'weight':ValueFilter(10)})
• weight:="10" --> MapFilter({'weight':ValueFilter('10')})
• weight:=10kg --> MapFilter({'weight':ValueFilter('10kg')})
• weight:10 --> MapFilter({'weight':KeywordFilter('10')})