Question: Is there support for schema migrations?

[jgomes94]

I tried to the best of my ability find documentation regarding schema migrations, however, couldn't find any information regarding it.

Does data api client support schema migrations? Is there any documentation?

[Valindo]

hey @jgomes94 did you find something for schema migrations, if yes please share :)

[jgomes94]

Hi Valindo, in the end I didn't use it. I'm using Typeorm with data api client but I didn't use the migrations, but from what i read it is supported.

[skilbjo]

hey @Valindo , did you ever come up with a solution for migrations + aurora serverless? i don't really need/use an orm, but still looking for a library that will help me with migrations.

if i don't find anything, i will try writing something like this, that is either run manually in a lambda or triggered after a deploy:

migrations/

1/
  - up.sql
  - down.sql
2/
  - up.sql
  - down.sql
etc

js

for folder in migrations/
  try {
    await db.exec(folder + up.sql)
  } catch (err) {
    await db.exec(folder + down.sql)
    break // exit the for loop, do not continue processing up.sql files
  }

would be great tho if there was a library that did this, because there are probably edge cases i haven't considered, and it would be great to have the db itself hold the state of the migrations as opposed to processing them all after every deploy

[skilbjo]

the other complicating issue is migrations files usually have multiple sql commands inside a single file, ie

1/up.sql

create table products (
  id uuid primary key,
  name text
);

create table sales (
  date timestamp, 
  product_id uuid references products.id, 
  amount bigint
);

with data-api-client (actually i believe this is more of a postgres limitation, not data-api-client) i believe you cannot just fs.readFileSync('1/up.sql') because of the ; delimiters inside the file - only the first command will run. Either you manually split this like

const contents = fs.readFileSync(...)

contents.split(';').forEach(command => db.query(command))

or (preferably) use a library do it for you

[skilbjo]

Ok, for future reference/anyone else's benefit, I want to explain how I did this:

first, a correction:

the other complicating issue is migrations files usually have multiple sql commands inside a single file

this is actually not true - all commands are executed. ie await db.query('create table blah (id text); select 1+1;'), both with will run/work.

ok, so now on to how I did it

i used @slonik/migrator (which requires also these dependencies: slonik, pg) this lets you do up/down migrations in raw sql

pros:

• use a library instead of implementing something adhoc as i described in a prior comment
• migration state is kept in the database
• can rollback to a prior state (the hand-rolled approach i described above would not per-se allow you to do that)

cons:

• (biggest con): networking. slonik requires a traditional connection string connection to the db instead of using the rds data api. to get a connection string working, you have to create a lambda for this, put the lambda inside the subnet where the database lives, set up networking for this, etc. using AWS CDK makes this easier but still a lift.
• networking drawbacks: you should (probably) have your database in a private subnet and deny all network traffic. that's what makes having the rds data api so great - you leverage AWS IAM to figure this all out and you can just deny all traditional network traffic. however due to not being able to use the data api, now you put your lambda in that private subnet, well now you can't access the internet, which includes using the aws-sdk (its service endpoints internally are implemented using the public internet). so, if you have the database secured via SecretsManager, and the lambda needs to retrieve the secret from SecretsManager, now you are faced with allowing egress internet from your private subnet (i didn't want to do this because i don't want allow unnecessary security surface area), or setting up a VPC Endpoint (min charge: $7 per AZ per month) just to allow access to SecretsManager. AWS has something called VPC Gateway Endpoint where they intercept the public AWS call and route it internally instead so you don't have to do any of this, but it only works for S3 and Dynamo. So tl;dr dealing with the effects of a private subnet (instead of the much easier data api) is a pain.
• differs from pg-mem migrations (tbh I don't think any library would be a great fit with pg-mem migrations because it is so different however it is very simple and does the trick), which is what i was using in my project for testing

i think now knowing this, i am 50/50 if i should have gone with a hand-rolled/use data-api-client approach instead. (the drawback there being hand-rolled approach and lack of ability to rollback migrations and/or hold migration state in the db)

best of luck to anyone in the future who is considering migrations and is using the data api. please let me know how you did it and how it worked for you, future reader