Flutter Drift Helper - Create DDL sql file from module folder (bash)

[Blackstareye]

I wanted to share my DDL generator for my drift files :)

#!/usr/bin/env bash

# Little Snippet for setting cwd to script path
SCRIPT=$(realpath "$0")
SCRIPTPATH=$(dirname "$SCRIPT")
cd $SCRIPTPATH

# path to folder which containts the drift files
path="../lib/models/database/create/modular/*.drift";

echo "" > create.sql

for p in $path; do 
    echo "--from $(basename $p)" >> create.sql;
    sed -n '/^CREATE TABLE/,/;$/p' $p | sed 's/ WITH [^;]*//g' >> create.sql;
    echo "" >> create.sql
done 

gist:https://gist.github.com/Blackstareye/af7f5ca3a8c49949cb420bba5334a78b

[simolus3]

Nice, it only captures tables though (you can also declare views, triggers and indexes in drift files). There's also some drift-specific syntax like the one used for type converters that drift needs to desugar.

What are you using the exported DDL statements for? We can probably offer a CLI command to export DDL statements from a drift file (Dart-based definitions will be much harder) if there's interest in such a thing.

[Blackstareye]

Yeah, I also thought that this can be expanded.
the first one fetches the table, second one gets rid of the WITH foo.
So it is expandable so that it could grep views etc and get rid of type converter syntax if they can be catched from a regex as well :)

What are you using the exported DDL statements for? We can probably offer a CLI command to export DDL statements from a drift file (Dart-based definitions will be much harder) if there's interest in such a thing.

Right now, I used it for visualization of the created android db of my app without exporting it the hardway.

It would be great if that is possible. I love storing my DDLs in a separated folder.
Most of my project has a structure like sql/create and sql/insert.
This makes it rather easy to try other databases or to quickly setup a testdb if I want to continue the design (ER diagram etc) of my DDL.

---

Right now I am still in the experimental phase with drift and the sqllite backend.
Usually I use something like mysql, oracle db etc ... so it's a bit different than the familar ones for me.

This is also the reason why I asked #3123

I am still evaluating if I should use drift files for the DDLs or dart scripts.
Right now, my choice are the dart scripts cause it's much easier and more intuitive.
But I think the cleaner way would be dart files.

Other Question:
If I see it correctly there are no foreign key based relationships within drift right ?
The recommended way is using joints and fetch the data before, right ?

[simolus3]

It would be great if that is possible. I love storing my DDLs in a separated folder.

I've added that in ee0e50c. You run dart run drift_dev schema export path/to/your/database.dart, it analyzes everything and prints all the CREATE statements making up its schema.

If I see it correctly there are no foreign key based relationships within drift right ?

You can define tables with a REFERENCES clause. The statement builders have no builtin functionality to automatically create joins for references, but @dickermoshe has built an easier API for CRUD statements that will be able to automatically resolve foreign key relationships. I think that should also work for tables defined with CREATE TABLE statements in .drift files.

[Blackstareye]

amazing :O

I will try this on monday :O thx! :> ❤️

@references
I will look into that on monday as well :)

Thx for your answer