{"_id":"@advena/bq-migrate","name":"@advena/bq-migrate","dist-tags":{"latest":"1.0.0"},"versions":{"1.0.0":{"name":"@advena/bq-migrate","version":"1.0.0","description":"BigQuery Schema Migration handler","main":"./src/bq-migrate.js","type":"commonjs","keywords":["bq-migrate"],"author":{"name":"advename"},"license":"MIT","peerDependencies":{"@google-cloud/bigquery":"^6.2.0"},"repository":{"type":"git","url":"git+https://github.com/advename/bq-migrate.git"},"gitHead":"9092271720a9645c29859a170d34a862c71cfe68","bugs":{"url":"https://github.com/advename/bq-migrate/issues"},"homepage":"https://github.com/advename/bq-migrate#readme","_id":"@advena/bq-migrate@1.0.0","_nodeVersion":"16.15.0","_npmVersion":"8.5.5","dist":{"integrity":"sha512-fKcjtBzHCQjK1HeouIBQHLyUoUVxfnKhaTjxEhLwyEqxPJLRu1Wly9aleTASl4wfw6oc8uaI7dBSpZONOOP5vw==","shasum":"52acbf05d91bbdb485272cdaccf2d247555e7ddd","tarball":"https://registry.npmjs.org/@advena/bq-migrate/-/bq-migrate-1.0.0.tgz","fileCount":11,"unpackedSize":28806,"signatures":[{"keyid":"SHA256:jl3bwswu80PjjokCgh0o2w5c2U4LhQAE57gj9cz1kzA","sig":"MEQCICaMrqJYvJvQEMCKjtG+PtmtuqZess044n3uounSBX37AiA+98eO4cGDzZSoz+hGvXwmRwoEbr85gIVHaDutBGwOQw=="}],"npm-signature":"-----BEGIN PGP SIGNATURE-----\r\nVersion: OpenPGP.js v4.10.10\r\nComment: https://openpgpjs.org\r\n\r\nwsFzBAEBCAAGBQJkOXubACEJED1NWxICdlZqFiEECWMYAoorWMhJKdjhPU1b\r\nEgJ2Vmr6qg/7BgVt4IgS9uhNGvx+s3tutmenLrzcVE+YUARnd883r/kY1/cS\r\n3Tt/N1XhYl+5RROiDMv35+xAPhPuh12KAIJkN5G2KHEKlbHE2tMQIcxidKML\r\nk3QyPBbqrWsd39rbRrtGYeORXRiRuK/9YHm3l2zm3Tk3FNJYicwU9Rxh0LZK\r\nTJsPQS5i9LhqQ+mqKlTtBs2sXv0N5X2Ha4igjTzDevBo/eCzzRCbhJpvwGVG\r\nC4bddluMTTU3yDOrarDlZtQGttG7VnMX1dpwHRR83BJ1FSDs4NBMismDyuPM\r\nhi4g4gU6KvN2KfhuDd01jN+sLnKnRH6yojGkCkiEj9TiLe9Ni+mj5s9kaNdL\r\n7VX1w06FMK8UQ3PY3qT7YniYSPUhpQ+Pnn7HDyNfl40qnj1crQaClMpmG0A+\r\n9IyqHSarPg0wv5m05BdPgMswBxE+NpCfCR3HqPHlW1dYJa6poboq3/Zv6nii\r\nwLee3zjwWr/Huo+0yXwxolyxcGIymWLbPFV+vWb70DDTCCfCjkeZyj3EC605\r\nai06jLKG7ICe5VlKiNtPd3DdZI9SjqJ+FQ6IsBM72uNwSrAaeSdXOF5HF95G\r\nN9F+6KeF8xcdV2x/RfgeZC6TfjpQcVnv7GQNuXwl4V0PmaOr7MGU1CIO+grX\r\nS+noje0uChjDtvTLzUIcTsiDSqPGrf6qeD0=\r\n=KVEz\r\n-----END PGP SIGNATURE-----\r\n"},"_npmUser":{"name":"advena","email":"larstru@gmail.com"},"directories":{},"maintainers":[{"name":"advena","email":"larstru@gmail.com"}],"_npmOperationalInternal":{"host":"s3://npm-registry-packages","tmp":"tmp/bq-migrate_1.0.0_1681488794890_0.5365773948696269"},"_hasShrinkwrap":false}},"time":{"created":"2023-04-14T16:13:14.833Z","1.0.0":"2023-04-14T16:13:15.207Z","modified":"2023-04-14T16:13:15.380Z"},"maintainers":[{"name":"advena","email":"larstru@gmail.com"}],"description":"BigQuery Schema Migration handler","homepage":"https://github.com/advename/bq-migrate#readme","keywords":["bq-migrate"],"repository":{"type":"git","url":"git+https://github.com/advename/bq-migrate.git"},"author":{"name":"advename"},"bugs":{"url":"https://github.com/advename/bq-migrate/issues"},"license":"MIT","readme":"# BigQuery Schema Migration\n\n`@advena/bq-migrate` is a Node.JS library for managing BigQuery schema migrations. It provides an interface to create, run, and rollback migrations for your BigQuery schema.\n\nSupported features:\n- Migration locks + expiration time for time locks (in seconds)\n- Migration Batches\n- Timezone\n- Uses query-jobs instead of streams\n\n## Installation\n\n```sh\nnpm install @advena/bq-migrate\n```\n\n## Quickstart\n\nCreate a `bqMigration` instance.\n\n```js\n// bqMigration.js\nconst BQMigrate = require(\"@advena/bq-migrate\")\nconst bigquery = require(\"/somewhere/bigquery.js\")\nconst path = require(\"path\");\n\nconst config = {\n  bigquery: bigqueryClient, // required: bigquery instance \n  datasetId: \"your_dataset_id\", // required: the ID of the dataset where migrations will be applied\n  migrationsDir: path.resolve(__dirname, \"migrations\") // required: the directory of the migration files\n};\n\nconst bqMigration = new BQMigration(config);\n\nasync function migrate(){\n    await bqMigration.runMigrations();\n}\n\nasync function rollback(){\n    await bqMigration.rollbackMigrations();\n}\n```\n\nLook inside `./example/migrations` how migration files should be structured. The `bigquery` instance with the `datasetId` is passed along to the `up` and `down` methods.\n\n```js\n// migrations/001_init.js\nconst tableId = \"person\";\n\nexports.up = async function (bigquery, datasetId) {\n    // Create table\n    const [table] = await bigquery.dataset(datasetId).createTable(tableId, {\n        schema: [\n            { name: \"name\", type: \"STRING\" },\n            { name: \"age\", type: \"INTEGER\" },\n        ],\n    });\n};\n\nexports.down = async function(bigquery,datasetId){\n    await bigquery.dataset(datasetId).table(tableId).delete()\n}\n```\n\n## About `bq-migrations`\n\n### Inspiration\nIndustry tools like [Flyway](https://flywaydb.org/documentation/database/big-query) or [Liquibase](https://github.com/liquibase/liquibase-bigquery) require you to install the CLI package + a JDCB driver which may require Java Engine on your machine.\nBit overkil, init?\n\nI was looking for a pure Node.JS approach, but didn't find one. So I ended up making my own after doing LOTS of research how Flyway and Liquibase tackle BigQuery with schema migrations.\n\n### Batches\nMigrations are run in batches. Meaning if you have `001_init.js` and `002_person.js`, these are run together and are assigned the batch number `1`. When rolling back, these are then also rolled back together.\n\n### Transactions\nBigQuery recently introduced [Multi-statement transactions](https://cloud.google.com/bigquery/docs/reference/standard-sql/transactions). Unfortunately, DDL (`CREATE TABLE`) statements are only supported for [**temporary**](https://cloud.google.com/bigquery/docs/reference/standard-sql/transactions#statements_supported_in_transactions) entities, making them unusable for migrations.\n\n### Query jobs vs stream & Quota Limitations\n**Streaming Inserts:** You cannot modify data with `UPDATE`, `DELETE`, or `MERGE` for the first 30 minutes after inserting it using streaming `INSERT`s. It may take up to 90 minutes for the data to be ready for copy operations. Streaming inserts are limited to 50,000 rows per request.([1](https://cloud.google.com/bigquery/docs/reference/standard-sql/data-manipulation-language#limitations), [2](https://cloud.google.com/bigquery/quotas#streaming_inserts))\n\n**Query Jobs**: [Jobs are actions that BigQuery](https://cloud.google.com/bigquery/docs/jobs-overview) runs on your behalf to [load data](https://cloud.google.com/bigquery/docs/loading-data), [export data](https://cloud.google.com/bigquery/exporting-data-from-bigquery), [query data](https://cloud.google.com/bigquery/docs/running-queries), or [copy data](https://cloud.google.com/bigquery/docs/managing-tables#copy-table). Query Jobs in particular are basically all _\"vanilla\"_ SQL queries that you run against BigQuery. [BigQuery's Node.JS library (`@google-cloud/bigquery`)](https://github.com/googleapis/nodejs-bigquery), which is used to run migrations uses a combination of streams and Query jobs. Only query jobs have been carefully selected for migrations to not run into the above mentioned limitations.\n\n**⚠️ WARNING ⚠️**\nIt's important that you don't use any streaming queries for migrations such as [`.insert()`](https://cloud.google.com/nodejs/docs/reference/bigquery/latest/bigquery/table#_google_cloud_bigquery_Table_insert_member_1_). Use instead the [`.query()`](https://cloud.google.com/nodejs/docs/reference/bigquery/latest/bigquery/dataset#_google_cloud_bigquery_Dataset_query_member_1_) method with SQL.\n\n## Configuration\nWhen creating a new instance of the `BQMigration` class, you must provide an configuration object with the following required and optional properties:\n\n#### Required\n- `bigquery` (Object): The BigQuery client instance.\n- `datasetId` (string): The ID of the dataset where migrations will be applied.\n- `migrationsDir` (string): The path to the directory containing migration files.\n\n#### Optional\n- `migrationTableName` (string, default: \"schema_migrations\"): The name of the table that stores the migration history.\n- `migrationLockTableName` (string, default: \"schema_migrations_lock\"): The name of the table that stores the migration lock.\n- `migrationLockExpirationTime` (number, default: 30): The duration (in seconds) after which a migration lock will expire.\n- `timezone` (string, default: \"Etc/UTC\"): The timezone used for date and time operations. Must be a valid tz database timezone (https://en.wikipedia.org/wiki/List_of_tz_database_time_zones).\n\n## Migration Files\n\nMigrations files should be created in the specified `migrationsDir` and should follow the naming convention `<3-digit number>_<migration_name>.js` (or `.ts`), for example `012_add_sales_attribute.js`. Each migration file should export an `up` and `down` function for applying and rolling back the migration, respectively. The `bigquery` and `datasetId` are automatically passed on to these methods.\n\n```js\n// Example migration file: 001_create_table.js\n\nexports.up = async (bigquery, datasetId) => {\n  // Code to apply the migration\n};\n\nexports.down = async (bigquery, datasetId) => {\n  // Code to rollback the migration\n};\n```\n\n## API Reference\n\n### runMigrations()\n\nAsynchronously runs any pending migrations for the BigQuery schema. Returns a Promise that resolves when all pending migrations have been executed.\n\n### rollbackMigrations()\n\nRolls back the latest batch of migrations applied to the BigQuery schema. Returns a Promise that resolves when all pending migrations have been rolled back.\n\n### getAppliedMigrations(batch = null)\n\nGet the list of applied migrations from the migration table. If a batch number is provided, only migrations from that batch will be fetched. Returns a Promise that resolves to an array of migration names.\n\n### createMigrationTable()\n\nCreate a migration table to store migration data in BigQuery. Returns a Promise that resolves when the migration table is created or already exists.\n\n### createMigrationLockTable()\n\nCreates the migration lock table if it doesn't exist. Returns a Promise that resolves when the lock table is created, or it already exists.\n\n### lockMigration()\n\nLocks the migration process by updating the migration lock table. Returns a Promise that resolves when the lock is acquired, and rejects with an error if the lock fails.\n\n### unlockMigration()\n\nUnlocks the migration lock table. Returns a Promise that resolves when the lock is removed, and rejects with an error if the lock is not removed successfully.\n\n### getMigrationFiles()\n\nAsynchronously reads the migration directory and returns a sorted list of Javascript or Typescript migration files. Returns a Promise that resolves to an array of sorted migration file names.\n\n\n## Todo\nI've built this package for personal use. However, If it should ever gain traction then I would consider adding:\n- tests\n- CJS/ESM dual build\n- rewrite in typescript and provide types\n- improved error reporting\n- repair failed migrations\n- ...?\n\n## Disclaimer\nPlease note that I am not responsible for any errors or issues that may arise from using this package. By using this package, you acknowledge that you are using it at your own risk and that I cannot be held accountable for any problems or damages that may occur as a result of using this v. Please ensure that you have adequate backups and precautions in place before implementing or using this package in any production or critical environments.\n\nI myself have used it on one large Next.js client project and have not seen any issues yet.\n","readmeFilename":"README.md"}