snorre/ajanottaja-backend
1
0
Fork 0
Code Issues 2 Pull requests Projects Releases Packages Wiki Activity Actions
Supabase backend for Ajanottaja https://ajanottaja.app
6 commits 1 branch 0 tags 58 KiB
  • PLpgSQL 100%
Find a file
Exact
Snorre Magnus Davøen d7f58700c2
 Add json casts for timestamptz and intervals 
Instead of trying to set the default interval and date styles to
iso_8601 we can add json casts for these value types instead. The users
of the Supabase backend then needs to type cast to json if they want
timestamptz and interval values as iso8601 strings.

For json aggregated columns the values will be automatically returned as
iso8601. This should fit the expectation that json aggregation returns
all contained values in json format.

Signed-off-by: Snorre Magnus Davøen <post@snorre.io>
2022-08-21 13:05:15 +02:00
supabase Add json casts for timestamptz and intervals 2022-08-21 13:05:15 +02:00
.gitignore Complete statistics views and functions + format 2022-08-11 21:03:19 +02:00
LICENSE Initial commit 2022-06-01 19:22:45 +02:00
README.md Add json casts for timestamptz and intervals 2022-08-21 13:05:15 +02:00

README.md

backend

Experimental supabase backend for Ajanottaja.

Getting up an running on Supabase

Create new Supabase organization (if you don't already have one) and project. Wait for project to finish setting up, this can take a bit of time. Go to SQL Editor, create new query and make the postgres user a superuser so it has appropriate rights during migration.

alter role postgres SUPERUSER

Link your Supabase command line to your Supabase project (see Reference ID on settings page) and push migrations:

supabase link --project-ref <yourref>
supabase db push

Finally remove the superuser grant from the postgres user:

alter role postgres NOSUPERUSER

Your Supabase backend should now be fully up and running and you can connect frontends to it.

Development

Install Supabase CLI and any other dependencies.

supabase start

To create a new database migration run:

supabase migration new <name-of-migration>

After creating a new migration you need to reset the local db:

supabase db reset

Usage

The API can be consumed using any of the Supabase client integration libraries.

Handling Date, timestamptz, tstzrange, interval

Ajanottaja makes use of the timestamptz, tstzrange, and interval types to handle time related data. By default Postgres (and thus Supabase) will return these in the SQL (Postgres) format.

For simple columns Ajanottaja will not automatically convert to ISO8601. To convert values to the ISO format you can cast to json, e.g. .select("target::json"). For JSON-aggregated columns like target and tracks in the calendar function values are already converted to ISO8601. This seems to be a nice compromise.

Unfortunately setting intervalStyle seem to behave differently between self-hosted (dev) and Supabase production environments. It is also better to be more explicit when values are converted or not.

License

The DB migration scripts and any other project source code files are licensed under AGPLV3. See LICENSE for details.