Nessie

A modular database migration tool for Deno inspired by Laravel. Currently supports PostgreSQL, MySQL 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.

See documentation for the query builder.

See documentation for the clients.

Nessie is available through:

Usage

init: Generates a nessie.config.ts file

deno run --allow-net --allow-read --allow-write https://deno.land/x/nessie/cli.ts init
make [name]: Create migration

deno run --allow-net --allow-read --allow-write https://deno.land/x/nessie/cli.ts make create_users
make:seed [name]: Create seed

deno run --allow-net --allow-read --allow-write https://deno.land/x/nessie/cli.ts make:seed create_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 --allow-net --allow-read https://deno.land/x/nessie/cli.ts migrate

deno run --allow-net --allow-read https://deno.land/x/nessie/cli.ts migrate 1

deno run --allow-net --allow-read 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 --allow-net --allow-read https://deno.land/x/nessie/cli.ts rollback

deno run --allow-net --allow-read https://deno.land/x/nessie/cli.ts rollback 2

deno run --allow-net --allow-read 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 --allow-net --allow-read https://deno.land/x/nessie/cli.ts seed

deno run --allow-net --allow-read https://deno.land/x/nessie/cli.ts seed seed_file.js

deno run --allow-net --allow-read https://deno.land/x/nessie/cli.ts seed ".+.ts"

Flags

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

Contributing

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

Uses

Denomander
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

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 { ClientPostgreSQL } from "./clients/ClientPostgreSQL.ts";

const nessieOptions = {
  migrationFolder: "./db/migrations",
  seedFolder: "./db/seeds",
  experimental: true,
};

const connectionOptions = {
  database: "nessie",
  hostname: "localhost",
  port: 5432,
  user: "root",
  password: "pwd",
};

export default {
  client: new ClientPostgreSQL(nessieOptions, connectionOptions),
};

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, Info } from "https://deno.land/x/nessie/mod.ts";
import type { Client } from "https://deno.land/x/postgres@v0.4.5/mod.ts";

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

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

Seed file (experimental)

import { AbstractSeed, Info } from "https://deno.land/x/nessie/mod.ts";
import type { Client } from "https://deno.land/x/postgres@v0.4.5/mod.ts";

export default class ExperimentalSeed extends AbstractSeed<Client> {
  async run({ dialect }: Info): Promise<void> {
    this.client.query("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";
};

Using the native query builder (exposeQueryBuilder: true) (legacy)

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

export const up: Migration<Schema> = ({ queryBuilder }) => {
  queryBuilder.create("users", (table) => {
    table.id();
    table.string("name", 100).nullable();
    table.boolean("is_true").default("false");
    table.custom("custom_column int default 1");
    table.timestamps();
  });

  queryBuilder.queryString(
    "INSERT INTO users VALUES (DEFAULT, 'Deno', true, 2, DEFAULT, DEFAULT);",
  )
  
  return queryBuilder.query
};

export const down: Migration<Schema> = ({ queryBuilder }) => {
  return queryBuilder.drop("users");
};

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 and implement the ClientI interface.

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.