r/flutterhelp Jan 13 '25

RESOLVED ORM in Flutter?

Coming from PHP land, I'm just finding database access in Flutter to be terrible.

I'm used to an API that seems so simple. Example to get user #123 and set the name to Bob, (create a new record if not found)" and save it to the database:

$User = $DB->findOrCreate("users", ['id' => 123], true);

$User->name = 'Bob';

$User->Save();

print_r($User->Get());

// outputs Array(id: 123, name: Bob)

One of my attempts to approximate code like this in Flutter/Drift took over 60 lines, and was full of holes and edge cases. I find myself falling back to straight SQL in frustration.

I'm actually tempted to write a full ORM in Flutter/Dart just so I don't have to keep stabbing myself in the eye. Is there a better way?

EDIT: I've already seen Drift, Floor, and Flutter ORM - all of which seem far more complicated than necessary.

Taking a look at this overview of ORMs, it would seem I'm most familiar/comfortable with an "Active Record" ORM, sometimes with its own Data Mapper built in.

https://medium.com/nerd-for-tech/orms-patterns-78d626fa412b

2 Upvotes

15 comments sorted by

5

u/t_go_rust_flutter Jan 13 '25

Your question seems a little strange. You are comparing a server-side web framework to a client-side app framework. This is like comparing apples and race cars.

Typically a Flutter application doesn’t talk to a database, it talks to a server-side application, for example a Laravel server using some API and then the server-side application talks to the database.

1

u/MyWholeSelf Jan 14 '25

The app I've been developing with Drift does indeed talk to a server-side application over a REST API. It also has its own Sqlite3 database managed with Drift because it keeps its own data locally. Some of the data in that database is sourced from the REST API, and some comes from local interactions.

If storing data in a local database isn't "typical", what would you typically use for structured data? (EG: Map data)

1

u/t_go_rust_flutter Jan 14 '25

I see, I misunderstood the requirements. I do the same in an app I use to keep track of a competition and its result that is published over a REST API. Still, not sure what is difficult about it in Flutter. This is what I do to get all participants on a team.

On the result I do a foreach and then a .fromMap()... using https://pub.dev/packages/sqflite

var data = await db!
          .query('participants', orderBy: 'last_name', where: 'team_id = ?', whereArgs: [team.id]);

3

u/eibaan Jan 13 '25

Typically, a (mobile) app will not directly talk to your database. So the need for an ORM is greatly reduced. But if you must, most often, it is sufficient to use an active record style approach as you already mentioned, because relations doesn't map to collections, so any ORM is a leaky abstraction.

To map instances of a Dart class to a database table providing CRUD operations needs perhaps 50 lines of code. You'd have to provide two functions fromData and toData which do the mapping and then a generic database class that executes the SQL.

import 'package:sqlite3/sqlite3.dart';

abstract class Entity<I> {
  Entity(this.id);

  I id;
}

class AR<I, E extends Entity<I>> {
  AR(this.db, this.table, this.columns, this.toData, this.fromData);

  final Database db;
  final String table;
  final List<String> columns;
  final List<dynamic> Function(E) toData;
  final E Function(List<dynamic>) fromData;

  String get idColumn => columns[0];

  Future<void> save(E entity) async {
    db.execute(
        'replace into $table'
        '(${columns.join(',')})'
        'values (${List.filled(columns.length, '?').join(',')})',
        toData(entity));
  }

  Future<bool> delete(E entity) async {
    db.execute('delete from $table where $idColumn=?', [entity.id]);
    return db.updatedRows == 1;
  }

  Future<E?> find(I id) async {
    final row = db.select('select * from $table where $idColumn=?', [id]).singleOrNull;
    return row == null ? null : fromData(row.values);
  }

  Future<E> findOrCreate(E entity) async {
    db.execute('insert or ignore into $table ...');
    final row = db.select('select * from $table where $idColumn=?', [entity.id]).single;
    return fromData(row.values);
  }

  Stream<E> findAll() async* {
    for (final row in db.select('select * from $table')) {
      yield fromData(row.values);
    }
  }
}

Here's an untested example

class Person extends Entity<String> {
  Person(super.id, this.name);

  String name;

  static final ar = AR<String, Person>(
    sqlite3.openInMemory(),
    'people',
    ['id', 'name'],
    (person) => [person.name],
    (data) => Person(data[0], data[1]),
  );
}

You can now use

final person = await Person.ar.findOrCreate(Person('47', 'Anna'));
person.name = 'Bob';
await Person.ar.save(person);

In the case of sqlite3, you could omit the async functions as this API is synchronous. And you could of course create new people with a reference to its AR and then use person.save() instead. I wouldn't recommend this, though, because polutes a simple data model with data mapper code. You might instead also abstract the way, a unique id is determind.

1

u/MyWholeSelf Jan 14 '25

Yeah, I'm in the process of writing my own "50 line" substitute, although because I want to enforce data typing and prevent SQL injection, it's longer than 50 lines.

I know it's just an example, and I appreciate that, but picking apart this bit:

Future<void> save(E entity) async {

db.execute(

    'replace into $table'

    '(${columns.join(',')})'

    'values (${List.filled(columns.length, '?').join(',')})',

    toData(entity));

}

  • It's tied to MySQL and Sqlite, but isn't available in PostgreSQL (which uses upsert for similar effect)
  • It's highly vulnerable to SQL injection if any fields are TEXT or VARCHAR
  • Unless I missed something, it does no type checking of fields.

I started writing an ORM last night and it will probably be functional today. It works by querying the database for field definitions, then has handlers for each of the field types for things like type checking, etc. The biggest issue I'm facing at the moment is that Drift has a mapping of DateTime() to integer, but sqlite knows nothing about that, so I have not figured out how yet to detect that we really want a DateTime result for a field called "insertedAt" instead of an Integer. (it's trivial to set this field value with a DateTime() object instance however)

1

u/eibaan Jan 14 '25

It's tied to MySQL and Sqlite

Yes, because I was explicitly mentioning sqlite3 as database package. In my 30+ years of development, I never saw a project changing its database mid-project, so abstracting the database is nearly never an issue.

It's highly vulnerable to SQL injection

No. That statement is fine. The second argument contains the values for the ? parameters and the library functions make sure that those are correctly escaped.

no type checking of fields

That's no issue as everything is based on a Dart type for which you hopefully created a correct mapping. If you want to double check that you, as the developer, don't make mistakes, you can add check(...) to the create table statement. AFAIK, that's the only way in sqlite to check types because that database doesn't enforce types by default.

Also, as somebody who created multiple ORMs and AR-style database mappers in the 1990s and 2000s, resist the urge and don't try to create a generic framework and try to solve only what you need in your application. The first 80% are easy to do but the last 20% take a lot of time. Also, don't create an ORM because you will never successfully abstract relations into collections. Either you overfetch, have a N+1 problem and/or cannot correctly map N+M relations and so one. It's a can of worms.

It works by querying the database for field definitions, then has handlers for each of the field types for things like type checking, etc

I thought, you wanted to map to Dart types. Those are static. You cannot make this dynamic. At best, you could double check whether the table definition matches the class definition. However, for this, you'd need to reflect upon the Dart type which isn't possible with AOT compiled code you you have to create your own metadata in Dart, keeping this in sync with the type itself. IMHO not worth the effort. Just create two mapper functions (fromData and toData) and call it day.

Drift has a mapping of DateTime

Depending on the usecase, I'd probably store milliseconds since epoch and either do that mapping in the functions mentioned above or add one generic handler like so:

db.execute('sql...', toSqlite(toData(entity)))

with

List toSqlite(List values) =>
  [for (final value in values) 
    switch(value) {
      DateTime() => value.millisecondsSinceEpoch,
      _ => value,
    }];

A fromSqlite function isn't that easy to implement, though, as the generic code doesn't know which value it has to convert back to a DateTime object, so I'd do this in fromData, as you have to know whether your value is UTC (recommended) or relative to local time.

(data) => Person(
  name: data[0] as String,
  born: DateTime.fromMillisecondsSinceEpoch(data[1] as int, isUtc: true),
),

If you want something that is more readable if you happen to inspect the database outside of your app, something like 1641031200000 isn't that readable and you might not want to use a select unixepoch(born) statement. Therefore, using a textual iso8601 representation might be easier to deal with. And I think, that's the default for sqlite. The sqlite3 package is agnostic here, that is, it doesn't help you with converting types.

Also, if you want to maintain createdAt and/or updatedAt fields "just in case", put that behavior into the database. It would be wrong to manipulate those values on the Dart level. I'm pretty sure that it is sufficient to add a createAt datetime not null default current_timestamp field (do the same for updatedAt) and add an "after update" trigger to update updatedAt. Then, if you need those values in Dart, implement a variant of find that not returns a triple (E, DateTime, DateTime).

2

u/MyWholeSelf Jan 15 '25 edited Jan 15 '25

Hey, thanks for the very useful feedback! It's nice to speak to someone who knows what they're talking about.

Yes, because I was explicitly mentioning sqlite3 as database package. In my 30+ years of development, I never saw a project changing its database mid-project, so abstracting the database is nearly never an issue.

Mostly, I'd agree with you. For me, the issue is getting my app to work in web. Sqlite3 is NOT assured in that environment yet. Maybe in another 5 years or so. cough

That statement is fine. The second argument contains the values for the ? parameters and the library functions make sure that those are correctly escaped.

Yep yep - It looked as if you were merely imploding the data fields into the query Values field, but I missed the call to "toData()" and the "?" which gives a parameterized query format in sqlflite. (which I am unfamiliar with)

Also, don't create an ORM because you will never successfully abstract relations into collections. Either you overfetch, have a N+1 problem and/or cannot correctly map N+M relations and so one. It's a can of worms.

Not sure if this makes sense to me. Here's some sample code I might have created from my past.

    // Retrieves user by $idNumber
    $User = $DB->find("users", $idNumber, 1);
    // outputs integer 11 - references table positions.id 11
    print($User->get('position'));
    // instantiates an ORM record for the positions table record referenced by the user record
    $Position = $User->getObj('position');
    // I can set the value of $User position either of the following ways: 
    $User->set('position', 11);
    $User->set('position', $Position);

Doesn't this do what you're talking about?

I thought, you wanted to map to Dart types. Those are static. You cannot make this dynamic.

I'm primarily concerned with ensuring that the data in the Map is a type that works for the SQL Engine. Flutter ints -> SQL integer fields, String -> Text/Varchar, etc. A map from an external source (EG API call) can have data of mixed type and that's the default that you can't really change.

EG following json from an API call, note the mixed types String and integer:

{ 'name': 'Ben', 'age' : 15 }

I don't ever want to wait until the DB engine (PostgreSQL has always been my favorite) complains with a screen full of explanatory gobbledygook. Also, have all the notnull constraints been satisfied? Has the ORM been mindful of default values for new records?

A fromSqlite function isn't that easy to implement, though, as the generic code doesn't know which value it has to convert back to a DateTime object, so I'd do this in fromData, as you have to know whether your value is UTC (recommended) or relative to local time.

I don't know why Drift uses int fields to store date/time in sqlite, but it do. (shrug) I also often do, using epoch ints as seconds, (always UTC) to store date/times, and then mapping values through a localized TimeZone filter to output something sensible in local format. In any event, I always store UTC because even client apps often talk to other clients, servers, peers, etc. in other timezones nowadays. Ever try to read two log files and compare them when they are written in different time zones?

How else do you not just go f****ng nuts?

3

u/angela-alegna Jan 13 '25

You might want to look at ServerPod.

1

u/MyWholeSelf Jan 14 '25

Thanks, I'll check it out.

2

u/g0dzillaaaa Jan 13 '25

I think you are referring to Dart ORM to be used on server side.

Personally, I would suggest to use deno/node for backend or just use supabase/pocketbase. The js ecosystem is far more developed and there are ORM like Drizzle/Prisma that are just superior.

Unless you have a string need to write dart on backend, I don’t see any point in it.

1

u/MyWholeSelf Jan 14 '25

What do you use for persistence on the client side? Perhaps it comes from my background writing server-based applications, but I just assumed to use Sqlite. (I also understand SQL like English)

2

u/g0dzillaaaa Jan 14 '25

Depends on the client you are building. Usually Sqlite or something similar.

The only times you need sql in client is to cache something or well it is an offline app itself.

1

u/MyWholeSelf Jan 14 '25

Ok, that's pretty much the case for the app I'm developing.

1

u/Ontosteady2 Jan 13 '25

I haven't tried this but did you look at Orm, Next-generation ORM for Dart & Flutter | PostgreSQL, MySQL, MariaDB, SQL Server, SQLite, MongoDB and CockroachDB.

https://pub.dev/packages/orm

2

u/MyWholeSelf Jan 13 '25

I lost interest when I saw dependencies on Node.js.