A modular database migration tool for Deno inspired by Laravel and Phinx.
_{Supports PostgreSQL, MySQL, MariaDB and SQLite.}

Call for donations: If you are using Nessie commercially, please consider supporting the future development. See this issue for more information.

🎉 Version 2 is released: To migrate from version 1 follow the steps in the migration section bellow.

If you would like to see your DB flavor supported, take a look at how to make a client plugin with examples in the clients folder or in the section How to make a client.

See documentation for the clients.

Nessie is available through:

• https://deno.land/x/nessie
• https://raw.githubusercontent.com/halvardssm/deno-nessie
• https://nest.land/package/Nessie
• https://hub.docker.com/repository/docker/halvardm/nessie

Even though all examples in this readme applies unversioned usage, you should always use a version when using Nessie.

• init: Generates a nessie.config.ts file and also the db folder where migration and seed files will be placed. Two options are available: --mode and --dialect.

  --mode can be one of config or folders. If mode is not set, it will create a nessie.config.ts file and the db folder structure, otherwise it will create the selected one.

  --dialect is used for the config file and can be one of pg, mysql or sqlite. If not set, it will create a general config file including all three dialects, otherwise it will include only the selected one.

  deno run -A --unstable https://deno.land/x/nessie/cli.ts init
  
  deno run -A --unstable https://deno.land/x/nessie/cli.ts init --mode folders
  
  deno run -A --unstable https://deno.land/x/nessie/cli.ts init --mode config --dialect pg
  
  deno run -A --unstable https://deno.land/x/nessie/cli.ts init --mode config --dialect sqlite
  
  deno run -A --unstable https://deno.land/x/nessie/cli.ts init --mode config --dialect mysql

• make:migration [name] & make [name]: Create migration, name has to be snake- and lowercase, it can also include numbers.

  deno run -A --unstable https://deno.land/x/nessie/cli.ts make:migration create_users
  
  deno run -A --unstable https://deno.land/x/nessie/cli.ts make create_users

• make:seed [name]: Create seed, name has to be snake- and lowercase, it can also include numbers.

  deno run -A --unstable https://deno.land/x/nessie/cli.ts make:seed add_users

• migrate [amount?]: Run migration - will migrate your migrations in your migration folder (sorted by timestamp) newer than the latest migration in your db. Amount defines how many migrations, defaults to all available if not set.

  deno run -A --unstable https://deno.land/x/nessie/cli.ts migrate
  
  deno run -A --unstable https://deno.land/x/nessie/cli.ts migrate 1
  
  deno run -A --unstable https://deno.land/x/nessie/cli.ts migrate -c ./nessie.config.ts

• rollback [amount?]: Rollback - will rollback your migrations. Amount defines how many migrations, defaults to 1 if not set.

  deno run -A --unstable https://deno.land/x/nessie/cli.ts rollback
  
  deno run -A --unstable https://deno.land/x/nessie/cli.ts rollback 2
  
  deno run -A --unstable https://deno.land/x/nessie/cli.ts rollback all

• seed [matcher?]: Seed - will seed your database. Optional matcher will match all files in your seed folder by string literal or RegExp.

  deno run -A --unstable https://deno.land/x/nessie/cli.ts seed
  
  deno run -A --unstable https://deno.land/x/nessie/cli.ts seed seed_file.js
  
  deno run -A --unstable https://deno.land/x/nessie/cli.ts seed ".+.ts"

• status: Outputs the status of Nessie. Will output detailed information about current state of the migrations. Includes two flags: --output and --file-names.

  --output sets the output format and can be one of log or json. If not set, log is chosen by default. If result is used programatically, chose json as the format of log can change with every patch version, while the format of json will be considered more stable.

  --file-names adds the file names to each of the outputs.

  deno run -A --unstable https://deno.land/x/nessie/cli.ts status
  totalAvailableMigrationFiles: 1
  completedMigrations: 0
  newAvailableMigrations: 1
  
  deno run -A --unstable https://deno.land/x/nessie/cli.ts status --output json
  {"totalAvailableMigrationFiles":1,"completedMigrations":0,"newAvailableMigrations":1}
  
  deno run -A --unstable https://deno.land/x/nessie/cli.ts status --file-names
  totalAvailableMigrationFiles: 1
    20210508115213_test1.ts
  completedMigrations: 0
  newAvailableMigrations: 1
    20210508115213_test1.ts
  
  deno run -A --unstable https://deno.land/x/nessie/cli.ts status --file-names --output json
  {"totalAvailableMigrationFiles":1,"completedMigrations":0,"newAvailableMigrations":1,"totalAvailableMigrationFileNames":["20210508115213_test1.ts"],"completedMigrationNames":[],"newAvailableMigrationNames":["20210508115213_test1.ts"]}

• update_timestamps: Update timestamps - will update timestamps to the new format. Will only update timestamps where the value is less than 1672531200000 (2023-01-01) so that the timestamps won't be updated multiple times.

  deno run -A --unstable https://deno.land/x/nessie/cli.ts update_timestamps

• -c, --config: Path to config file, will default to ./nessie.config.ts
• -d, --debug: Enables verbose output

While the examples simply show -A as the permission flag, you can also limit the permissions according to your needs. Bellow you will see what Nessie actually needs.

• --unstable: Needed by std/fs/copy as it uses Deno.utimeSync and Deno.utime which are still unstable.
• --allow-read: Nessie needs read access to be able to read the migration and seed folders, it also checks for the presence of the config file.
• --allow-write: When initiating Nessie or creating a new migration or seed file, write access is required.
• --allow-net: This is needed to import the nessie code in your config file. It is also needed if you use a remote config or migration files, this flag is needed to get access.

The config interface is exported from mod.ts as NessieConfig.

export interface NessieConfig {
  /** Can be any class which extends `AbstractClient`. */
  client: AbstractClient<any>;
  /**
   * The folders where migration files are located.
   * Can be a relative path or an absolute path.
   * Defaults to ['./db/migrations/'] if additionalMigrationFiles is not populated
   */
  migrationFolders?: string[];
  /**
  * The folders where seed files are located.
  * Can be a relative path or an absolute path.
  * Defaults to ['./db/seeds/'] if additionalSeedFiles is not populated
  */
  seedFolders?: string[];
  /**
  * Additional migration files which will be added to the
  * list to parse when running the migrate or rollback command.
  * Can be any format supported by `import()` e.g. url or path
  */
  additionalMigrationFiles?: string[];
  /**
  * Additional seed files which will be added to the list to
  * match against when running the seed command.
  * Can be any format supported by `import()` e.g. remote url or path
  */
  additionalSeedFiles?: string[];
  /** Enables verbose output for debugging */
  debug?: boolean;
}

With the introduction of additionalMigrationFiles and additionalSeedFiles, you can now include remote migration and seed files which you can fetch for example via ftp or using api's like gihub or gitlab. Any input which can be given to the dynamic import() can be provided.

...
additionalMigrationFiles: ['https://example.com/some_migration_file.ts'],
...

See the example folder for more examples.

See the specific Nessie image docs for using Nessie with a docker image.

All contributions are welcome, make sure to read the contribution guideline.

• Cliffy
• Deno Postgres
• Deno MySQL - Currently it works with password for 5.*, but for >=8 you have to send a blank password, see issue 37
• Deno SQLite

See example repo for a REST API which uses Oak and Nessie.

See the example folder for more examples.

Nessie uses the AbstractMigration class which you can extend to access the client and its properties. This enables better flexibility in migrations and seeds and allows a more complex workflow.

nessie.config.ts with all default values

import {
  ClientPostgreSQL,
  NessieConfig,
} from "https://deno.land/x/nessie/mod.ts";

const clientPg = new ClientPostgreSQL({
  database: "nessie",
  hostname: "localhost",
  port: 5432,
  user: "root",
  password: "pwd",
});

const config: NessieConfig = {
  client: clientPg,
  migrationFolders: ["./db/migrations"],
  seedFolders: ["./db/seeds"],
  additionalMigrationFiles: [],
  additionalSeedFiles: [],
  debug: false,
};

export default config;

Minimal example of a migration file

import {
  AbstractMigration,
  ClientPostgreSQL,
  Info,
} from "https://deno.land/x/nessie/mod.ts";

export default class extends AbstractMigration<ClientPostgreSQL> {
  async up({ dialect }: Info): Promise<void> {
    await this.client.queryArray("CREATE TABLE table1 (id int)");
  }

  async down({ dialect }: Info): Promise<void> {
    await this.client.queryArray("DROP TABLE table1");
  }
}

Seed file

import {
  AbstractSeed,
  ClientPostgreSQL,
  Info,
} from "https://deno.land/x/nessie/mod.ts";

export default class extends AbstractSeed<ClientPostgreSQL> {
  async run({ dialect }: Info): Promise<void> {
    await this.client.queryArray("INSERT INTO table1 VALUES (1234)");
  }
}

See the example folder for more

A client needs to extend AbstractClient.

query: Takes a query string or array of query strings and sends them of to the database for execution. Should return whatever the database responds.

prepare: Will be run when the migration or rollback commands are executed. This should create the connection, set up the nessie_migrations table and prepare the database for incoming migrations.

migrate: Takes a number as an optional input, will default to all files if not set. Will run Math.min(amount, numberOfFiles) migration files. Only handles the up method.

rollback: Takes a number as an optional input, will default to 1 if not set. Will run Math.min(amount, numberOfFiles) migration files. Only handles the down method.

seed: Takes an optional matcher as input. Matcher can be regex or string. Will seed the database. Handles the run method in seed files.

close: Will be the last method run before the program is finished. This should close the database connection.

See the existing examples in the clients folder for reference.

If you are migrating from version 1, and starting from scratch is not an option, there are a couple of steps to perform. If you have any questions regarding the steps, please open a new discussion (not an issue).

⚠️ The contributors of Nessie takes no responsibility for any dataloss which might occur, so make sure to back up all existing data in your databases before migrating and test it locally before executing any changes to your production environment.

1. Back up existing data

2. Update the nessie.config.ts file to the new syntax (see the examples folder)

3. (Skip this step if you already updated the timestamps) Run the update_timestamps command from the CLI to update the migration file names and database entries (only the nessie_migrations table). Use the latest version of Nessie e.g.

   deno run -A --unstable https://deno.land/x/[email protected]/cli.ts update_timestamps

4. Update all imports of Nessie to the latest version in your migration and seed files

5. Update the migration and seed files to use the new format (class based migration files)

If you come across any issues, you can open a new discussion on GitHub.