database 0.3.3

  • Readme
  • Changelog
  • Example
  • Installing
  • 89

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')})

0.3.3 - March 24, 2020 #

  • Improves documentation and fixes a few small issues.

0.3.2 - March 10, 2020 #

  • Various bug fixes (patching, transactions, etc.).
  • Adds vendorData fields.
  • Improves documentation.

0.3.1 - January 16, 2020 #

  • Improves documentation. Fixes some dependencies and deletes unnecessary files.

0.3.0 - January 16, 2020 #

  • Improves the API. Many breaking changes.

0.2.7 - January 16, 2020 #

  • Improves documentation.

0.2.6 - January 15, 2020 #

  • Improves the SQL API a bit.

0.2.5 - January 15, 2020 #

  • Adds initial API for SQL databases.
  • Adds PostgreSQL support.

0.2.4 - January 14, 2020 #

  • Fixes issues spotted during testing.

0.2.3 - January 14, 2020 #

  • Fixes various small issues and improves documentation.

0.2.2 - January 14, 2020 #

  • Fixes various issues.

0.2.1 - January 13, 2020 #

  • Small improvements in documentation.

0.2.0 - January 13, 2020 #

  • Initial release

example/example.dart

import 'package:database/database.dart';

void main() async {
  // Choose a database
  final database = MemoryDatabaseAdapter().database();

  // Search
  final response = await database.collection('people').search(
        query: Query.parse(
          '"software developer" (dart OR javascript)',
          take: 10,
        ),
      );

  // Print results
  for (var snapshot in response.snapshots) {
    print('Employee ID: ${snapshot.document.documentId}');
    print('Employee name: ${snapshot.data['name']}');
    print('');
  }
}

Use this package as a library

1. Depend on it

Add this to your package's pubspec.yaml file:


dependencies:
  database: ^0.3.3

2. Install it

You can install packages from the command line:

with pub:


$ pub get

with Flutter:


$ flutter pub get

Alternatively, your editor might support pub get or flutter pub get. Check the docs for your editor to learn more.

3. Import it

Now in your Dart code, you can use:


import 'package:database/database.dart';
  
Popularity:
Describes how popular the package is relative to other packages. [more]
79
Health:
Code health derived from static analysis. [more]
100
Maintenance:
Reflects how tidy and up-to-date the package is. [more]
100
Overall:
Weighted score of the above. [more]
89
Learn more about scoring.

We analyzed this package on Apr 7, 2020, and provided a score, details, and suggestions below. Analysis was completed with status completed using:

  • Dart: 2.7.1
  • pana: 0.13.7

Dependencies

Package Constraint Resolved Available
Direct dependencies
Dart SDK >=2.6.0 <3.0.0
built_collection ^4.0.0 4.3.2
built_value >=5.0.0 <8.0.0 7.0.9
charcode ^1.1.0 1.1.3
collection ^1.14.0 1.14.12
fixnum ^0.10.0 0.10.11
meta ^1.1.0 1.1.8
protobuf >=0.13.0 <2.0.0 1.0.1
universal_html ^1.1.0 1.2.2
universal_io >=0.8.6 <2.0.0 1.0.1
Transitive dependencies
async 2.4.1
csslib 0.16.1
html 0.14.0+3
matcher 0.12.6
path 1.6.4
quiver 2.1.3
source_span 1.7.0
stack_trace 1.9.3
term_glyph 1.1.0
typed_data 1.1.6
zone_local 0.1.2
Dev dependencies
pedantic ^1.8.0
test ^1.8.0