Snippets

Handy snippets to help you set up Postgres to work with Stacksync

Create a Primary Key

When creating a table in Postgres, you must define a primary key for the table. This can be done using a UUID or an integer that is auto-generated. Here are example snippets to create a primary key with auto-generated UUIDs or integers in Postgres:

Code snippet for UUID primary key:

CREATE TABLE public."my_table" (
   id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
   my_column varchar,
   -- other columns here
);

Code snippet for integer primary key:

CREATE TABLE my_table (
   id serial PRIMARY KEY,
   my_column varchar,
   -- other columns here
);

In the above code snippets, we have defined a primary key column named id using UUID and integer data types respectively. For integer primary keys, the serial data type is used, which automatically generates a sequence of unique integer values for the id column. By using either of these techniques, you can ensure that each row in the table has a unique identifier.

Alter a Primary Key

In Postgres, you can alter a primary key on a table to set it to a new column that is auto-generated with UUIDs or integers. This can be useful when you need to change the primary key column of a table to make it work with Stacksync's requirements. Here is an example code snippet that demonstrates how to alter a primary key on a table to set it to a new column that is auto-generated with UUIDs (BEST PRACTICE) or integers:

Code snippet for UUID primary key: (recommended)

-- first, create the new column with the desired data type
ALTER TABLE public."my_table" ADD COLUMN new_id uuid DEFAULT uuid_generate_v4() PRIMARY KEY;

-- then, drop the old primary key constraint
ALTER TABLE public."my_table" DROP CONSTRAINT my_table_pkey;

-- finally, add the new primary key constraint using the new column
ALTER TABLE public."my_table" ADD PRIMARY KEY (new_id);

Code snippet for Integer primary key:

-- first, create the new column with the desired data type
ALTER TABLE public."my_table" ADD COLUMN new_id serial PRIMARY KEY;

-- then, drop the old primary key constraint
ALTER TABLE public."my_table" DROP CONSTRAINT my_table_pkey;

-- finally, add the new primary key constraint using the new column
ALTER TABLE public."my_table" ADD PRIMARY KEY (new_id);

In the above code snippets, we have created a new column named new_id with the desired data type and auto-generation mechanism for the primary key. We have then dropped the old primary key constraint on the table and added a new primary key constraint using the new column. Note that this operation may require data type conversion and may cause data loss, so you should be careful when modifying the primary key column of a table.

Create a User with restricted access rights on your database

When you use Stacksync on production databases, you might feel more comfortable creating a user where Stacksync has only the necessary access rights to access the data.

Usually, Stacksync will create some tables directly and read and write data on these. For this setup, it is recommended to create a user for Stacksync that has the create table and read + write access rights.

Here is how to do it:

1. Connect to your PostgreSQL database with a user that has administrative privileges (e.g., the superuser or an account with the CREATEDB privilege).

2. Use the CREATE ROLE command to create a new user for Stacksync. You can grant the required privileges using the GRANT command.

-- Create the user
CREATE ROLE stacksync_user WITH LOGIN PASSWORD 'your_password';

-- Grant the necessary privileges
-- To create tables in a specific schema (e.g., public)
GRANT CREATE ON SCHEMA public TO stacksync_user;

-- Grant read and write privileges on all tables in the public schema
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO stacksync_user;

1. The Stacksync user also needs to have either replication rights on your database or create schema. This is necessary for Stacksync to be able to capture data changes in real-time and make the sync work.

Option 1: replication rights (recommended)

-- Grant replication rights to the Stacksync user (you must be a superuser to give replication rights to another user)
ALTER ROLE stacksync_user WITH REPLICATION;

Option 2: create schema (if your database does not allow you to activate replication rights)

-- Grant create schema rights to the Stacksync user
GRANT CREATE ON DATABASE your_database_name TO stacksync_user;

Grant user read and write access rights on a specific existing table

-- Grant read and write access to the table for the user
GRANT SELECT, INSERT, UPDATE, DELETE ON your_table TO your_user;

The user "your_user" now has read and write access rights to the "your_table" table in the database. They can perform SELECT, INSERT, UPDATE, and DELETE operations on that table as needed.

Create a new column in Postgres

To create a new column, here is a generic code snippet:

ALTER TABLE table_name
ADD COLUMN new_column_name data_type;

Here's an example that adds a new column named age with the data type integer to a table named persons:

ALTER TABLE persons
ADD COLUMN age integer;

To add a new column named name with the data type varchar (i.e. string) to a table named persons:

ALTER TABLE persons
ADD COLUMN name VARCHAR;