package as a TLE

[kiwicopple]

would you like to package this up so that it can be used in dbdev (https://databases.dev)?

then it can be installed like:

select dbdev.install('point_source-tenant_rbac)');

context: https://supabase.com/blog/dbdev

[point-source]

Great idea! I'll start looking into that. Thanks

[point-source]

@kiwicopple I'm starting to look into this and have run into a couple questions:

• Is best practice that my TLE creates the tables it needs in order to function? What if those tables already exist because the user made them for another purpose (or because of a previous version of this script)?
• My code makes use of the uuid_generate_v4() function from the uuid-ossp extension. This doesn't appear to be accessible from within a TLE for some reason.
• It looks like database.dev is not yet accepting third party packages. Once I have this updated as a TLE, what do I do with it?

[kiwicopple]

Is best practice that my TLE creates the tables it needs in order to function?

yes

What if those tables already exist because the user made them for another purpose (or because of a previous version of this script)?

The user can specify a new schema to install into so that there are no clashes, using the command create extension "point-source@rbac" with schema "rbac";. There is an important point here though - you should remove all the references to public in your code

My code makes use of the uuid_generate_v4() function from the uuid-ossp extension. This doesn't appear to be accessible from within a TLE for some reason.

this is no longer necessary. Postgres now has the gen_random_uuid() function to generate UUIDs. You can remove the dependency for uuid-ossp completely: https://www.postgresql.org/docs/current/functions-uuid.html

It looks like database.dev is not yet accepting third party packages. Once I have this updated as a TLE, what do I do with it?

it's now public :)

[kiwicopple]

btw, you can see a "minimum viable extension" here: https://github.com/kiwicopple/pg-extensions/tree/main/is_odd

[point-source]

@kiwicopple When a new version of a TLE is released, what happens to existing deployments? Do tables persist? Are they recreated? What if there is a breaking change to the table schema that the new version depends on?

Although the minimum viable extension was somewhat helpful, I think a "getting started creating a TLE" guide with best practices would also be great. I may even be able to help write this once I understand more about how this works. Just trying to get caught up right now and there are very few docs about the development side, only consumption of TLEs.

Edit: I just found the publishing guide: https://supabase.github.io/dbdev

Edit 2: The publishing guide answered my questions above. Disregard please. Thanks!

Edit 3: New question, how do I test this locally before publishing? Obviously I can just run the sql file but is that the same as "creating" or "updating" an "extension"?

Edit 4: How do I update the readme without updating the SQL? If I increment the control version number, will it require me to also have a correlated sql file ending with that version? How does a user upgrade a TLE? Do they just run the installation command again? (SQL would normally reject installation of an existing object) Would it have to have a version specified in that case?

[kiwicopple]

how do I test this locally before publishing?

At the moment I just use supabase locally: https://github.com/kiwicopple/pg-extensions/tree/main/supabase

I think we should do some better tooling for this over time

How do I update the readme without updating the SQL?

you can't - you need to bump the version. I'll start documenting everything that you've asked here in dbdev docs

[point-source]

Thanks! So for bumping the version just for a readme update - would I just leave that version's sql file blank since there are no functional changes?

[kiwicopple]

you would copy/paste the SQL from your old file to the new file eg: cp is_even--0.0.1.sql is_even--0.0.2.sql

[point-source]

Ah okay, so it doesn't work like a migration where all get run in order during installation.

So when a user installs a specific version, it only runs the file for that version?

Does that mean that when authoring upgrades, I need to account for both previous as well as new first-time installs?

If I were to change table schema in v2, I can't just assume it exists from v1 but have to check first, yeah?

I appreciate your patience and willingness to help me understand all the intricacies of this. I just want to make sure I don't upload a tle that is broken and can't be removed or fixed. I also want to release the most stable updates possible. Thanks for your time

Edit: Please let me know if you need any further modifications for this in order to include it in the upcoming launch week.

[imor]

@point-source I hope your questions are answered in the discussion in supabase/dbdev#164.

[point-source]

@imor, yep, thanks to you. Closing now.