Database Migration

After installing the package, add the following script to your package.json to apply the migrations:

package.json

"scripts": {
  "migrate-dataqueue": "dataqueue-cli migrate"
}

Next, run this command to apply the migrations:

npm run migrate-dataqueue

pnpm run migrate-dataqueue

yarn migrate-dataqueue

bun run migrate-dataqueue

This will apply all the necessary schema migrations so your Postgres database is ready to use with DataQueue.

Make sure the PG_DATAQUEUE_DATABASE environment variable is set to your Postgres connection string. The CLI uses this environment variable to connect to your database. For example:

PG_DATAQUEUE_DATABASE=postgresql://postgres:password@localhost:5432/my_database

You must run these migrations before using the job queue. For example, if you are deploying your app to Vercel, run this command before deploying in the Vercel's pipeline. If you have used Prisma or other ORMs, you may be familiar with this process.

Using a custom .env file

You can use the --envPath option to specify a custom path to your environment file. For example:

npm run migrate-dataqueue -- --envPath .env.local

pnpm run migrate-dataqueue --envPath .env.local

yarn migrate-dataqueue --envPath .env.local

bun run migrate-dataqueue --envPath .env.local

This will load environment variables from .env.local before running the migration.

Schema selection

You can explicitly set the schema for migrations using the -s or --schema CLI option. This option is passed directly to node-pg-migrate and will ensure the schema is created if it does not exist.

Example CLI usage with explicit schema:

npm run migrate-dataqueue -- --envPath .env.local --schema dataqueue

pnpm run migrate-dataqueue --envPath .env.local --schema dataqueue

yarn migrate-dataqueue --envPath .env.local --schema dataqueue

bun run migrate-dataqueue --envPath .env.local --schema dataqueue

Specifying the schema is optional but recommended when you're using the same database as your main application. If you don't specify the schema, the CLI will use the default schema which is public. If you use Prisma, the prisma migration will fail because of the additional tables added by DataQueue.

You have to use the --schema option even when PG_DATAQUEUE_DATABASE contains the schema name in search_path.

Other options

You can pass other options supported by node-pg-migrate to the migration command. For example:

npm run migrate-dataqueue -- --envPath .env.local --schema dataqueue --verbose

pnpm run migrate-dataqueue --envPath .env.local --schema dataqueue --verbose

yarn migrate-dataqueue --envPath .env.local --schema dataqueue --verbose

bun run migrate-dataqueue --envPath .env.local --schema dataqueue --verbose

For more information, see the node-pg-migrate documentation.

Running migrations with SSL and a custom CA

Most managed Postgres providers (like DigitalOcean, Supabase, etc.) require SSL connections and provide a CA certificate (.crt file). You can use the CA certificate to validate the server's identity. In order to successfully run the migration with custom CA, you must set the NODE_EXTRA_CA_CERTS environment variable to the path of your CA certificate. This tells Node.js to trust your provider's CA for outgoing TLS connections, including Postgres.

NODE_EXTRA_CA_CERTS=/absolute/path/to/ca.crt \
PG_DATAQUEUE_DATABASE=your_connection_string \
npm run migrate-dataqueue

Migration without Certificate Validation

For convenience, you can run the migration without certificate validation by adding the --no-reject-unauthorized flag to the command.

npm run migrate-dataqueue -- --no-reject-unauthorized

pnpm run migrate-dataqueue --no-reject-unauthorized

yarn migrate-dataqueue --no-reject-unauthorized

bun run migrate-dataqueue --no-reject-unauthorized

Using a CA certificate in environments where you cannot upload files

In some serverless or cloud environments (like Vercel, AWS Lambda, etc.), you cannot upload files directly, but you still need Node.js to trust your managed Postgres provider's CA certificate.

In this case, you can store the CA certificate as an environment variable and write it to a temporary file in your pipeline shell script before running the migration.

Store the PEM content as an environment variable
- Copy the full contents of your .crt file into a new environment variable, e.g. PGSSLROOTCERT_CONTENT.
- Make sure your environment supports multi-line secrets.
Write the CA certificate to a file and set NODE_EXTRA_CA_CERTS in your pipeline script

# Write the CA cert to a file
printf "%s" "$PGSSLROOTCERT_CONTENT" > /tmp/ca.crt
# Set NODE_EXTRA_CA_CERTS and run the migration
NODE_EXTRA_CA_CERTS=/tmp/ca.crt npm run migrate-dataqueue