Date: Sun, 14 Mar 2021 09:40:05 +0000
To: Tom Lane <tgl@sss.pgh.pa.us>
From: Simon Connah <simon.n.connah@protonmail.com>
Cc: "pgsql-novice@lists.postgresql.org" <pgsql-novice@lists.postgresql.org>
Reply-To: Simon Connah <simon.n.connah@protonmail.com>
Subject: Re: Database migrations
Message-ID: 
 <iEmEeBaqwt6XevYlGSsaqpYCHDqm6I_dy7udGkeHDvXJ-N13TDsKtI_-2mKFiX-9aMSsbobpbCMt7rJ2x9KjKb2zEZx9Pd8jWwVRUnHiqj8=@protonmail.com>
In-Reply-To: <2731142.1615672715@sss.pgh.pa.us>
References: 
 <DZDT1SCFF7_nJRcZylu8tod4dlTmOX1hAuAlselavvkA4HJAFbOUPC_33W7IxDtMdbzQBPYGPN6p0N58SRHjP3D-JuI8CEyRn3xL1VF4t30=@protonmail.com>
 <2731142.1615672715@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/iEmEeBaqwt6XevYlGSsaqpYCHDqm6I_dy7udGkeHDvXJ-N13TDsKtI_-2mKFiX-9aMSsbobpbCMt7rJ2x9KjKb2zEZx9Pd8jWwVRUnHiqj8%3D%40protonmail.com>
Precedence: bulk


=E2=80=90=E2=80=90=E2=80=90=E2=80=90=E2=80=90=E2=80=90=E2=80=90 Original Me=
ssage =E2=80=90=E2=80=90=E2=80=90=E2=80=90=E2=80=90=E2=80=90=E2=80=90

On Saturday, March 13th, 2021 at 21:58, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Simon Connah simon.n.connah@protonmail.com writes:
>
> > I'm sorry if this is a rather stupid question but I'm fairly new to dat=
abases and was wondering if anyone could offer some advice?
>
> > I have an app I am developing using Node.js and PostgreSQL 13.2. I have=
 the basic tables I want to start with but I know that as development progr=
esses change is going to be required. Since this is an open-source project =
people are going to want to upgrade from one version to the next and that m=
ight require changes to the database structure. What is the best way to han=
dle this?
>
> > I was thinking about just having a bunch of SQL files with a version nu=
mber and date and applying them to the database from oldest to newest (with=
 a table storing information for files that have already been processed). D=
oes this sound like a reasonable solution?
>
> > If not could someone point me in the right direction, please?
>
> If I understand your requirements properly, this is already largely
>
> implemented in PG's "extension" infrastructure. The system can keep
>
> track of which version of an extension is installed, and apply the
>
> correct delta script on request for an upgrade (or downgrade).
>
> See here:
>
> https://www.postgresql.org/docs/current/extend-extensions.html
>
> The documentation mostly talks about extensions that have some
>
> underlying C code (in a .so library file). But it's perfectly possible
>
> to have an extension that consists only of SQL definitions.
>
> regards, tom lane

Hi Tom,

Thank you for the suggestion. I had no idea PostgreSQL had a feature for th=
ings like that.

I'll do some reading on the subject but it certainly seems interesting.

Simon.