Generate Dart Types from Database Schema

[atreeon]

Are there any current ways or future plans to generate Dart types from Supabase tables?

for example, currently I do this where I use strings inside the methods and the output is a dynamic map that I manually convert

  var result = await supabase //
      .from('TestTable')
      .select()
      .eq('id', 1)
      .execute();

  var dataFirst = result.data[0];
  var data = TestTable(id: dataFirst["id"], name: dataFirst["name"]);

and I would like to do this; with the type generated automatically for me (a bit more like c#'s entity framework or dart's Conduit), where the type returned is a specific type that matches the database.

  var testTable = await supabase //
      .TestTable
      .select()
      .eq((x) => x.id, 1)
      .execute();

  print(testTable.name);

[DanMossa]

What I've been doing until a better solution comes up is to make an enum of my tables, and then create a method that takes in the table enum and uses that to call Supabase.

I then only interact with Supabase using my wrapper method.

[bdlukaa]

Maybe you're looking for withConverter.

final User user = await postgrest
  .from('users')
  .select()
  .withConverter<User>((data) => User.fromJson(data));

[atreeon]

Thanks @bdlukaa, so is the data variable json? Do you use the json_serializable package to create your fromJson code?

[bdlukaa]

is the data variable json?

data is the result given from the server. It can be either a Map or a List, depending on your query

Do you use the json_serializable package to create your fromJson code?

There are a lot of options to generate the fromJson/toJson code

• https://pub.dev/packages/json_serializable
• https://pub.dev/packages/freezed (I like this better)

Or you can do it by hand.

[atreeon]

thanks @bdlukaa, that is very helpful and gets me a bit further. It would still be helpful if it were more type safe of course and the models were generated from the db automatically (I'm used to .net's Entity Framework which does everything for you). For example if the table name or a field name changed in the database there would be a runtime error. Also you have to manually write the table name and the withConverter doesn't know what type it should be.

Thanks again though, much better than doing the mappings manually.

[bdlukaa]

It would still be helpful if it were more type safe of course and the models were generated from the db automatically

@dshukertjr we could create a tool that would convert schemas into dart classes. What do you think?

[dshukertjr]

@bdlukaa That sounds amazing! We could possibly add it to the Supabase cli? https://github.com/supabase/cli

[DanMossa]

This is something that exists. I haven't personally used it but it's definitely worth looking into as a starting point
https://pub.dev/packages/schema_dart

[kkalisz]

It's possible to generate models using swagger-codegen and exposed API definition.

swagger-codegen generate -i https://yourproject.supabase.co/rest/v1/?apikey=your_api_key -l dart -o ./test -Dmodels

To generate only required types it's possible to add a filter to -Dmodels argument

swagger-codegen generate -i https://yourproject.supabase.co/rest/v1/?apikey=your_api_key -l dart -o ./test -Dmodels=type1,type2

All of the generated types contain convert methods.
Type.fromJson(Map<String, dynamic> json)
and
List<Type> listFromJson(List<dynamic> json)

[atreeon]

So, I've tried some of these solutions out.

1. swagger-codegen generate version 2 isn't compatible with null safety so I couldn't use that

2. there is a package called swagger_dart_code_generator
   It generates CRUD operations ok. I can sort and order and do some basic filtering but then it seems to fail and get tricky when trying to combine multiple logical operators (or / and).
   myService.employeesGet(employeeId: "lt.4")
   as you can see, it still uses raw text for operators so it isn't very safe.

3. what I'm going to do for now is use the supabase package and use the withConverter using the toJson from the swagger dart code generator.

However, this still doesn't get me what I want, ie I can't do this .eq((x) => x.id, 1) in a safe way because the column name and the value still need to be text.

I think it is possible to build something, possibly using postgrest as that is what supabase uses; I'll think about giving it a go.

(apologies for the delay in responding until now)

[dshukertjr]

I have been looking into type generation for Dart a bit, and wanted some comments/suggestions.

First, I really like this API here to access tables

final data = await supabase // data here would be dynamic
      .testTable
      .select();

This should be possible if we generate something like this for all of the tables and views

extension TypeSafeTable on SupabaseClient {
  SupabaseQueryBuilder get posts {
    return from('posts');
  }

  SupabaseQueryBuilder get profiles {
    return from('profiles');
  }
}

I think the challenge lies on how to implement type safe filters like .eq. The proposed solution .eq((x) => x.id, 1) is nice, but it is a bit of a drastic API change. It would be nice to also be able to support .eq('id', 1) format that we currently have, but Dart doesn't support function overload. Another issue with this notation is, if I'm not wrong, there is no way in for the underlying method to obtain what the value of (x) => x.id evaluates to. I wonder if we can simply provide some static members on the generated models that returns the name of the column so that it can be used like this:

await supabase.posts.select().eq(Posts.columns.id, 1);

Just throwing it out there, comments and feedbacks are greatly appreciated!

[Vinzent03]

I don't think Dart's typesystem is advanced enough for this. Handling filter via enums may work, but what about column select. I see no way on how to implement that. I don't know much about it, but Static Metagrogramming may solve this in a few years.

[atreeon]

I wrote this as a simple ORM.

https://github.com/atreeon/easy_orm

It does column select like this:

await Db().products.selectQuery2Cols(
   ((e) => e.supplier_id),
   ((e) => e.category_id));

...of course you would need a list of 2Cols, 3Cols, 4Cols methods. I meant to implement joins to this project but didn't find the time (or the need in my project). I think it would work with how I envisaged joins to work.

Dart 3 (in alpha now) has, I think, more abilities to create anonymous types.

[dshukertjr]

@atreeon
I like the suggestion, but I'm afraid the syntax might deviate a bit too much from what we currently have.

We can start with something as simple as generating all the data classes for each table.

Say we had a posts table, we can then generate a file containing the following code:

class Post {
  final int id;
  final String title;
  final String? body;

  Post.fromJson(Map<String, dynamic> json) ...
  
  Map<String, dynamic> toJson() {
    ...
  }
}

We can call it an alpha version and maybe can collect more feedback to improve it.

[DanMossa]

I use json_serializable for all my data classes and that's what I generate for them.

[dshukertjr]

Sorry, what I meant was that we could have a CLI command that generates a single file with all of the type definitions of the tables like this:

class Post {
  ...
}

class Profiles {
  ...
}

...

I think with the current Dart's capability, this might be as good as type generation goes. It's not as sophisticated as Typescript, but might be a small time saver.

[atreeon]

Dart 3 is in the flutter Beta version now which would suggest it will be in the next release; that will help quite a lot.

[dshukertjr]

Hmm, curious to hear how the new features on Dart might be helpful!

I played around with the record feature a bit, but couldn't really find a good use case out of it. It just seemed like a quicker way of defining data classes without writing all the boilerplate code.

[atreeon]

I'm thinking if multiple columns are required then instead of returning a Tuple the user could define their own anonymous classes. C#s Entity Framework makes good use of that but...I'm not exactly sure of how you are thinking about imlementing things.

[Navil]

Is there any update to this?

MongoDB has something similar:

import 'package:realm_dart/realm.dart';
--
part 'realm_models.g.dart';
 
// NOTE: These Realm models are private and therefore should be copied into the same .dart file.
 
@RealmModel()
class _AiChat {
@PrimaryKey()
@MapTo('_id')
late ObjectId id;
 
late List<_AiMessage> messages;
}

These are automatically generated from the Schemas and also include the foreign relations to other tables. I feel it is pretty much boilerplate implementing all that from scratch. SQL usually comes with an ORM to deal with structured/typed data, that also assists in loading related objects. Supabase seem to lack such functionality completely.

[hbock-42]

Sorry, what I meant was that we could have a CLI command that generates a single file with all of the type definitions of the tables like this:

class Post {
  ...
}

class Profiles {
  ...
}

...

I think with the current Dart's capability, this might be as good as type generation goes. It's not as sophisticated as Typescript, but might be a small time saver.

One "easy" but involving 2 external library might be to generate a json schema from the database with a tool like https://www.npmjs.com/package/pg-tables-to-jsonschema (untested), then use this schema to generate the dart classe using https://github.com/glideapps/quicktype (11k stars on github).

The interesting part with this solution is that you the quicktype library supports around 20 target languages (including dart), so it could be used to generate type for other languages supported by supabase.

Taking 2 minutes to try there tool online (link in the doc), this json schema:

{
  "id": "http://json-schema.org/geo",
  "$schema": "http://json-schema.org/draft-06/schema#",
  "description": "A geographical coordinate",
  "type": "object",
  "properties": {
    "latitude": {
      "type": "number"
    },
    "longitude": {
      "type": "number"
    }
  },
  "required": ["longitude"]
}

becomes this dart class

///A geographical coordinate
class Coordinate {
    double? latitude;
    double longitude;

    Coordinate({
        this.latitude,
        required this.longitude,
    });

    factory Coordinate.fromJson(Map<String, dynamic> json) => Coordinate(
        latitude: json["latitude"]?.toDouble(),
        longitude: json["longitude"]?.toDouble(),
    );

    Map<String, dynamic> toJson() => {
        "latitude": latitude,
        "longitude": longitude,
    };
}

they also have an option to generate freezed classes:

///A geographical coordinate
@freezed
class Coordinate with _$Coordinate {
    const factory Coordinate({
        double? latitude,
        required double longitude,
    }) = _Coordinate;

    factory Coordinate.fromJson(Map<String, dynamic> json) => _$CoordinateFromJson(json);
}

They have a more complex example with nested schema that seems to work but is a bit too long to post here. So here is a link https://app.quicktype.io/ -> click on the folder (top left), then choose the pokedex example.

I'll check how it works this weekend.

[mmvergara]

Hello, based on the idea gathered in this issue, i made the tool to generate the dart classes and the client extension based on your supabase schema using the API,

Assuming the following table schema

create table
  public.books (
    id bigint generated by default as identity,
    book_name text not null,
    created_at timestamp with time zone not null default now(),
    book_description text null,
    sold boolean not null default false,
    price double precision not null,
    constraint books_pkey primary key (id),
    constraint books_id_key unique (id)
  ) tablespace pg_default;

The tool will generate this

class Books implements SupadartClass<Books> {
  final BigInt id;
  final String name;
  final String? description;
  final int price;
  final DateTime created_at;

  const Books({
    required this.id,
    required this.name,
    this.description,
    required this.price,
    required this.created_at,
  });

  static String get table_name => 'books';
  static String get c_id => 'id';
  static String get c_name => 'name';
  static String get c_description => 'description';
  static String get c_price => 'price';
  static String get c_created_at => 'created_at';
  static Map<String, dynamic> insert({
    BigInt? id,
    required String name,
    String? description,
    required int price,
    DateTime? created_at,
  }) {
    return {
      if (id != null) 'id': id.toString(),
      'name': name.toString(),
      if (description != null) 'description': description.toString(),
      'price': price.toString(),
      if (created_at != null) 'created_at': created_at.toUtc().toString(),
    };
  }

  static Map<String, dynamic> update({
    BigInt? id,
    String? name,
    String? description,
    int? price,
    DateTime? created_at,
  }) {
    return {
      if (id != null) 'id': id.toString(),
      if (name != null) 'name': name.toString(),
      if (description != null) 'description': description.toString(),
      if (price != null) 'price': price.toString(),
      if (created_at != null) 'created_at': created_at.toUtc().toString(),
    };
  }

  factory Books.fromJson(Map<String, dynamic> json) {
    return Books(
      id: json['id'] != null
          ? BigInt.tryParse(json['id'].toString()) as BigInt
          : BigInt.from(0),
      name: json['name'] != null ? json['name'].toString() : '',
      description:
          json['description'] != null ? json['description'].toString() : '',
      price: json['price'] != null ? json['price'] as int : 0,
      created_at: json['created_at'] != null
          ? DateTime.tryParse(json['created_at'].toString()) as DateTime
          : DateTime.fromMillisecondsSinceEpoch(0),
    );
  }
}

2. Using the generated class

we now have a typesafe'ish to interact with the database.

Getting Table Name

  Books.table_name // "books"

Fetch Data

// allBooks is a typeof List<Books>
final allBooks = await supabase
      .books
      .select("*")
      .withConverter(Books.converter);

Insert Data

// Yes we know which one's are optional or required.
final data = Books.insert(
  name: 'Learn Flutter',
  description: 'Endless brackets and braces',
  price: 2,
);
await supabase.books.insert(data);

Inset Many Data

final many_data = [
  Books.insert(
    name: 'Learn Minecraft',
    description: 'Endless blocks and bricks',
    price: 2,
  ),
  Books.insert(
    name: 'Description is optional',
    created_at: DateTime.now(),
    price: 2,
  ),
];
await supabase.books.insert(many_data);

Update Data

final newData = Books.update(
  name: 'New Book Name',
);
await supabase.books.update(newData).eq(Books.c_id, 1);

Delete Data

await supabase.books.delete().eq(Books.c_id, 1);

There is a lot of room for improvement,

like we can probably mess with the API so we don't need the withConverter at the end, its still WIP,
static properties also as mentioned in this issue for Equality.

It's still WIP, i'd love to hear your feedback.

[dshukertjr]

@mmvergara This is awesome!

[mmvergara]

Hello again, made some updates and lost half of my brain cells in a happy way

Getting runtime DateType as a DateTime and not as a String is now possible 🎉🎉

-- FIXED

[JakesMD]

Hi!

I had a shot at type-safety yesterday and took a different approach, which makes the filters fool-proof too.

This will add minor limitations to the functionality, though, because the actual SupabaseClient is abstracted.

All the code's on my GitHub repo.

Let me know if you find this interesting, hate it, or see any major flaws before I pursue this any further. Feel free to contribute.

Usage

You just create a table:

// books.dart

@SupaTableHere()
class Books extends SupaTable<BooksCore, BooksRecord> {
  /// {@macro Books}
  const Books({required super.supabaseClient})
      : super(BooksRecord._, tableName: 'books', primaryKey: 'id');

  @SupaColumnHere<BigInt>(hasDefault: true)
  static final id = SupaColumn<BooksCore, BigInt, int>(
    name: 'id',
    valueFromJSON: BigInt.from,
    valueToJSON: (v) => v.toInt(),
  );

  @SupaColumnHere<String>()
  static const title = SupaColumn<BooksCore, String, String>(name: 'title');

  @SupaColumnHere<String>()
  static const author = SupaColumn<BooksCore, String, String>(name: 'author');

  @SupaColumnHere<int?>()
  static const pages = SupaColumn<BooksCore, int?, int?>(name: 'pages');
}

Run the generator to generate a small piece of code.

And then you're done. This is how you would use it:

// Create the books table.
final books = Books(supabaseClient: supabaseClient);

// Fetch all Paddington books.
final records = await books.fetch(
  columns: {Books.id, Books.title},
  filter: books.textSearch(Books.title('Paddington')),
);

// The title of the first book.
final title = records.first.title;

// Insert a new Paddington book.
await books.insert(
  records: [
    const BooksInsert(
      title: 'All About Paddington',
      author: 'Bond',
      pages: 160,
    ),
  ],
);

// Update the title and author of the book with the ID 4.
await books.update(
  values: {
    Books.title('Paddington Here and Now'),
    Books.author('Michael Bond'),
  },
  filter: books.equals(Books.id(BigInt.from(4))),
);

// Delete all Paddington books that were not written by Michael Bond.
await books.delete(
  filter: books
      .textSearch(Books.title('Paddington'))
      .notEquals(Books.author('Michael Bond')),
);

[Kemerd]

I published my solution here
https://github.com/Kemerd/supabase-flutter-codegen/tree/main

If anyone needs it

[cspecter]

I published my solution here https://github.com/Kemerd/supabase-flutter-codegen/tree/main

If anyone needs it

This looks really great. You are calling a stored procedure called "get_schema_info", which is not included. Can you let us know what that procedure looks like so that it will work with your system?

[jwelmac]

@Kemerd
I was inspired by your solution and decided to build it out into a full library for easy integration and distribution for use by the dart community.
It can be found at https://github.com/jwelmac/supabase_codegen.

Aiming to publish it soon at pub.dev.