database 0.3.3

Dart native js
Flutter Android iOS web

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

Pub Package Github Actions CI PRs Welcome

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.

Contributing #

  • Just create a pull request in Github. :)

Supported products and APIs #

Document databases #

SQL databases #

Search engines #

Other #

Middleware #

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:

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 #

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')})
51
likes
110
pub points
85%
popularity

Publisher

dint.dev

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

Repository (GitHub)
View/report issues

Documentation

API reference

License

Apache 2.0 (LICENSE)

Dependencies

built_collection, built_value, charcode, collection, fixnum, meta, protobuf, universal_html, universal_io

More

Packages that depend on database