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:
Code snippet for Integer primary key:
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)
Code snippet for Integer primary key:
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 to create 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:
Connect to your PostgreSQL database with a user that has administrative privileges (e.g., the superuser or an account with the
CREATEDB
privilege).Use the
CREATE ROLE
command to create a new user for Stacksync. You can grant the required privileges using theGRANT
command.
The Stacksync user also needs to have either
replication
rights on your database orcreate 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)
Option 2: create schema
(if your database does not allow you to activate replication
rights)
Grant user read and write access rights on a specific exiting table
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:
Here's an example that adds a new column named age
with the data type integer
to a table named persons
:
To add a new column named name
with the data type varchar
(i.e. string) to a table named persons
:
Last updated