npm package discovery and stats viewer.

Discover Tips

  • General search

    [free text search, go nuts!]

  • Package details

    pkg:[package-name]

  • User packages

    @[username]

Sponsor

Optimize Toolset

I’ve always been into building performant and accessible sites, but lately I’ve been taking it extremely seriously. So much so that I’ve been building a tool to help me optimize and monitor the sites that I build to make sure that I’m making an attempt to offer the best experience to those who visit them. If you’re into performant, accessible and SEO friendly sites, you might like it too! You can check it out at Optimize Toolset.

About

Hi, 👋, I’m Ryan Hefner  and I built this site for me, and you! The goal of this site was to provide an easy way for me to check the stats on my npm packages, both for prioritizing issues and updates, and to give me a little kick in the pants to keep up on stuff.

As I was building it, I realized that I was actually using the tool to build the tool, and figured I might as well put this out there and hopefully others will find it to be a fast and useful way to search and browse npm packages as I have.

If you’re interested in other things I’m working on, follow me on Twitter or check out the open source projects I’ve been publishing on GitHub.

I am also working on a Twitter bot for this site to tweet the most popular, newest, random packages from npm. Please follow that account now and it will start sending out packages soon–ish.

Open Software & Tools

This site wouldn’t be possible without the immense generosity and tireless efforts from the people who make contributions to the world and share their work via open source initiatives. Thank you 🙏

© 2024 – Pkg Stats / Ryan Hefner

sql-migrate-up

v4.0.1

Published

Simple SQL Migration tool

Downloads

361

Readme

sql-migrate-up

NPM Version NPM Downloads

Simple SQL migration tool.

Tested with SQLite, PostgreSQL, Snowflake

Install

npm i sql-migrate-up

Usage

fp-ts version

Here is an example of your migrate command:

import * as TE from "fp-ts/TaskEither";
import * as T from "fp-ts/Task";
import { pipe } from "fp-ts/lib/function";
import { cli } from "sql-migrate-up";

// this is your db client dependecy implementation
import { withDbClient } from "./client";

cli({
  schema: "public",
  folder: "./migrations",
  table: "migrations",
  parameters: ({ schema }) => TE.of({ schema }),
  select: <T>(sql: string) =>
    pipe(
      withDbClient(),
      TE.flatMap(({ select }) => select<T>(sql)),
    ),
  execute: (sql: string) =>
    pipe(
      withDbClient(),
      TE.flatMap(({ execute }) => execute(sql)),
      TE.asUnit,
    ),
  end: () =>
    pipe(
      withDbClient(),
      TE.flatMap(({ end }) => end()),
      T.asUnit,
    ),
});

Promise version

import { cliPromise } from "sql-migrate-up";

// this is your db client dependecy implementation
import { withDbClient } from "./client";

const db = withDbClient()

cliPromise({
  schema: "public",
  folder: "./migrations",
  table: "migrations",
  parameters: ({ schema }) => Promise.resolve({ schema }),
  select: <T>(sql: string) => Promise.resolve(db.select<T>(sql)),
  execute: (sql) =>
    new Promise((resolve) => {
      db.exec(sql);
      resolve();
    }),
  end: () => Promise.resolve(db.end());
});

The example above is in TypeScript, use your favorite build tool to make an executable script or simply add it to package.json scripts section.

If you run it with no parameters you'll see help:

❯ ./migrate
Usage: migrate [options] [command]

Options:
  -V, --version               output the version number
  -h, --help                  display help for command

Commands:
  up [options]                run all migrations
  test [options]              tests all migrations for errors
  create [options] [name...]  create a new migration file
  help [command]              display help for command

You can run help for any command like migrate help create.

All commands take the following arguments to override default values:

  • --schema schema to migrate
  • --table migrations history table name
  • --folder folder with migrations files

Test migrations

You can test all of the migrations to have all the paramters and, optionaly, syntax errors

❯ ./migrate test --schema <schema>

To turn on the syntax errors add parser options as a second argument for your cli implementation like this:

import { cli } from "sql-migrate-up";

cli({...}, { dialect: "sqlite" });

Syntax check only supports:

  • SQLite - full support.
  • BigQuery - full support.
  • MySQL - experimental.
  • MariaDB - experimental.
  • PostgreSQL - experimental.

For the progress you can follow sql-parser-cst

run-once vs. run-always

All migrations are split into two categories:

  • run-once - default, normal migration file that will be kept in a history table and run only once.
  • run-always - migration file that needs to be run after all migrations every time. Useful for create or replace views, functions, etc.

run-once always runs first and then run-always.

Always use migrate create <name> [--run-always] to create a new migration

SQLUp Options

  • name, ("migrate") - the name of the script.
  • version, ("version of this package") - version of the script.
  • schema, <string | null> ("public") - schema, if schema is set to null to schema will be enforced.
  • folder, or <(schema: string) => string> ("./migrations") - folder with migrations files, or a functinon that returns a folder.
  • table, ("migrations") - the name of the table to keep the history of migration.
  • now, ("now()") - the sql function for getting current timestamp.
  • parameters, async function that should resolve into a data object that will be applied to every migration file.
  • select, async function that returns results from your SQL db.
  • execute, async function that executes arbitrary SQL in your db and does not return results.
  • end, async function that will be run after all is done. The perfect place to close your connections.

API: migrateUp

migrateUp take all the same arguments except end. Returns a number of applied migrations.

import { migrateUp } from "sql-migrate-up"; // or migrateUpPromise

// this is your db client dependecy implementation
import { withDbClient } from "./client";

const migrations = await migrateUp({
  schema: "public",
  folder: "./migrations",
  table: "migrations",
  parameters: ({ schema }) => TE.of({ schema }),
  select: <T>(sql: string) =>
    pipe(
      withDbClient(),
      TE.flatMap(({ select }) => select<T>(sql)),
    ),
  execute: (sql: string) =>
    pipe(
      withDbClient(),
      TE.flatMap(({ execute }) => execute(sql)),
      TE.asUnit,
    ),
});

Versioning

This is advance option and you probably will never need it. However it is very usefull when you have mutliple parallel instances of the same script trying to migrate one schema.

Options

  • version, required version of your package
  • useVersioning, sets the migrations to be in versioning mode.

How it works. If version changes works as usual, if version did not change no migration (not run-once, not run-always) will be applied.

When using versioning you can use --force flag to force run migrations for the same version.

Dependecies

If there is migration dependency on external folder, ie. npm package there is a way to include that in migration process as well. Create a file migrations.json in the migrations folder:

/*
 /migrations
   [/schema]
     run-once
     migrations.json
*/

{
  "before": ["path/to/migrations", "path/to/another/migrations"],
  "after": ["path/to/migrations"]
}
  • All paths should have the same structure as local migrations.
  • migrations.json from external migrations will be ignored
  • before and after are both optional but the file should have at least one
  • Migrations history would have full path to migration file relative to the current working directory
  • for before folders both run-once and run-always migration will be applied before all local migrations
  • for after folders both run-once and run-always migration will be applied after all local migrations

License

MIT