Starting with PostgreSQL

Working with a lot of data requires some kind of data storage, proabably a database if we have a lot of data records. For now I think Postgres is a great pick to startup your project for massive adoption.
Postgres is not only SQL, but it comes packed with all the things we need as developers to build a product that works and reduces complexity.

Postgres provides us with policies to protect records, triggers to auto cleanup records, functions with nested composite types to depict any kind of business logic in your database but still using SQL, specific tables that register types, and the possiblity to LISTEN to table changes. That way we can not only have real authenticating workflows written in simple SQL for our tables, but also increase performance of inserting large complex structs of data in a single function call.
We can easily notify subscribers via websockets of new data that was written in the database and thereby give realtime feedback in our applications.

To get started locally on MacOS all we need to do is install Postgres.app and we can already run a database locally on our own machine. We can develop a full major app on our own machine without incurring any costs and with the fewest abstractions possible, keeping everything simple.

I think there are a few amazing products using PostgreSQL that even integrate Authentication flows with many OAuth 2 compatible providers. For example Supabase has great tooling for this and I have used it greatly. And I think there are also very good horizontal scaling distributed databases available like CockroachDB.

Therefore my current stack currently consists of PostgreSQL unless I have billions of users, I'd start more research on how to distribute the data for the specific use-case and user locations. There are also notable mentions like PlanetScale, MongoDB, Cassandra or Redis that have established themselves in the database ecosystem. Thsi being said, take into account that perfect sharding is not a given in every NoSQL database.

Here are a few examples, from my upcoming flashcard application. They use the auth.uid() function from Supabase and its storage.

Create a card table. 

create table public.card(
  card_id bigint generated by default as identity,
  title citext null,
  front_type d_card_type not null,
  front_value text not null,
  back_type d_card_type not null,
  back_value text not null,
  auth_users_id uuid not null,
  constraint card_pkey primary key (card_id),
  constraint card_and_auth_users_fkey foreign key (auth_users_id) references auth.users(id) on delete cascade
) tablespace pg_default;

Create a simple policy for authenticated users to access their own cards.

create policy "authenticated can access owned card"
  on card for all to authenticated
  using ((select auth.uid()) = public.card.auth_users_id);

Create a simple function that returns a trigger, which deletes images that were stored on Supabase if a card gets deleted. 

create or replace function public.f_tr_delete_card() returns trigger as $$
begin
  if old.front_type = 'image' then
    delete from storage.objects where storage.objects.name = old.front_value;
  end if;
  if old.back_type = 'image' then
    delete from storage.objects where storage.objects.name = old.back_value;
  end if;
  return old;
end;
$$ language plpgsql;

Apply the trigger to run on every delete of a card row. 

create trigger tr_delete_card_trigger
after delete on card
for each row execute function public.f_tr_delete_card();

For working with PostgreSQL I recomment the following tools:


← Back to Homepage