Nessie

A modular database migration tool for Deno inspired by Laravel and Phinx. Currently supports PostgreSQL, MySQL (and MariaDB) and SQLite.

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.

The query builder is no longer maintained. If you need the code, it has been moved to its own repo. It can also be found in Nessie version 1.1.3 or older.

See documentation for the clients.

Nessie is available through:

Usage

init: Generates a nessie.config.ts file and also the db folder where migration and seed files will be placed.
```
deno run -A --unstable https://deno.land/x/nessie/cli.ts init
```

make:migration [name] & make [name]: Create migration

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

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"

update_timestamps: Update timestamps - will update timestamps to the new format. Will only update timestams where the value is less than 1672531200000 (2023-01-01) so that the timestamps wont be updated multiple times.
```
deno run -A --unstable https://deno.land/x/nessie/cli.ts update_timestamps
```

Flags

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

Deno flags and Permissions

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 precense of the config file.
--allow-write: When creating a new migration or seed file, Nessie needs write access.
--allow-net: Nessie downloads the config, migration and seed files from https://deno.land/x/nessie, this is the only url needed.

Contributing

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

Uses

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

Examples

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

Previously Nessie required the functions to return a string (or array of strings), but this will be changed in the next major release. There now exists an abstract migration class which you can extend to access the client and its properties. This enables better flexibility in migrations and allows a more complex workflow. To check this feature out and to help discover bugs, you can already take this new syntax for a test by checking out the example folder, or the examples below with the experimental tag.

nessie.config.ts with all default values

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

const clientOptions: ClientOptions = {
  migrationFolders: ["./db/migrations"],
  seedFolders: ["./db/seeds"],
};

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

const config: NessieConfig = {
  client: clientPg,
  experimental: true, // Will allow class based migrations
  useDateTime: true, // Will use new format for migration file timestamp
};

export default config;

We are moving towards class based migration files, so please consider taking a look at the new syntax. If you are seeking the legacy methods, please look in the example folder or further down on this page.

Minimal example of a migration file (experimental)

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

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

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

Seed file (experimental)

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

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

Minimal example of a migration file (legacy)

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

export const up: Migration = () => {
  return "CREATE TABLE table1 (id int);";
};

export const down: Migration = () => {
  return "DROP TABLE table1";
};

Seed file (legacy)

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

export const run: Seed = () => {
  return "INSERT INTO testTable VALUES (1)";
};

See the example folder for more

How to make a client

A client needs to extend AbstractClient.

query: Takes a query string or array of query strings and sends them of to the batabase 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.