halvardssm / deno-nessie Goto Github PK

Is your feature request related to a problem? Please describe.

Add coverage report to the ci. See https://github.com/oakserver/oak/blob/main/.github/workflows/oak-ci.yml

In the initial versions of Nessie, the query-builder was exposed in the migration methods up and down. In v0.5 this was removed to reduce the files you had to download if you only wanted to use the migration tool and not the built-in query builder, or if you prefer a third-party tool like Dex (port of knex).

From what I can see, we have these options (I'm personally leaning towards the first one)

🎉 Dynamically import the query builder and expose the schema in the migration files if a property in the config file is true i.e. exposeSchema: true.

🚀 No exposure of the query builder in the migration files. If you want to use the query builder, you can import it manually.

❤️ Always expose the query builder even if it causes extra files to be downloaded.

Feel free to comment if you have any thoughts or ideas, or if you just want to vote on the idea you like the most, use the emojis 😄

Pagic is a static site generator powered by Deno + React

Here are some demos:

• pagic.org
• Deno X ranking
• TypeScript 入门教程

If it is possible to use pagic to build the website, I would like to create the pull-request

System (please complete the following information):

• OS: linux
• Deno version 1.7.2
• Nessie version 1.1.3

Describe the bug
When a migration is run with experimental mode turned on the nessie_migrations table never gets populated.

I took a look at the code in AbstractClient.ts and it seems that if experimental mode is enabled it will never get to QUERY_MIGRATION_INSERT because thats behind the else on line 223.

To Reproduce
Steps to reproduce the behavior:

1. Run a migration with experimental mode on
2. Check nessie_migrations table

Expected behavior
nessie_migrations table should have values in it.

Describe the bug
When creating a table containing a usage of table.updatedAt(); and the table is using a PostgreSQL schema the result is a error:

PostgresError: cross-database references are not implemented: "public.my_schema.my_table"
at parseError (https://deno.land/x/[email protected]/error.ts:105:10)

To Reproduce

Example failing migration:

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

export const up = (schema: Schema): void => {
    schema.queryString('CREATE SCHEMA my_schema;');
    schema.create('my_schema.my_table', (table)=>{
        table.string('container_id', 36).notNullable().unique();
        table.updatedAt();
    });
};

export const down = (schema: Schema): void => {
    schema.queryString('DROP SCHEMA my_schema;');
    schema.drop("my_schema.my_table");
};

Expected behavior
I would expect a successful migration run

Screenshots or error log

Compile file:///Users/gsm/development/personal/deno-test/migrations/1590532644696-create-test.ts
PostgresError: cross-database references are not implemented: "public.my_schema.my_table"
    at parseError (https://deno.land/x/[email protected]/error.ts:105:10)
    at Connection._processError (https://deno.land/x/[email protected]/connection.ts:421:19)
    at Connection._simpleQuery (https://deno.land/x/[email protected]/connection.ts:292:20)
    at async Connection.query (https://deno.land/x/[email protected]/connection.ts:526:14)
    at async Client.query (https://deno.land/x/[email protected]/client.ts:24:12)
    at async https://deno.land/x/nessie/cli/pgsql.ts:44:24
    at async https://deno.land/x/nessie/cli/utils.ts:78:26
    at async queryHandler (https://deno.land/x/nessie/cli/utils.ts:135:20)
    at async traverseAndMigrateFiles (https://deno.land/x/nessie/cli/utils.ts:75:22)
    at async PGSQL.migrate (https://deno.land/x/nessie/cli/pgsql.ts:41:5)

System (please complete the following information):

• OS: MacOS 10.14.6
• Deno version 1.0.2
• Nessie version https://deno.land/x/nessie/cli.ts

Cli not working.

Running rollback or migrate shows this error:

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

Compile https://deno.land/x/nessie/cli.ts
error: TS2339 [ERROR]: Property 'initClient' does not exist on type 'State'.
      await state.initClient();
                  ~~~~~~~~~~
    at https://deno.land/x/nessie/cli.ts:49:19

TS2554 [ERROR]: Expected 1 arguments, but got 0.
        await state.client!.migrate();
                            ~~~~~~~~~
    at https://deno.land/x/nessie/cli.ts:52:29

    An argument for 'amount' was not provided.
      migrate: (amount: amountMigrateT) => Promise<void>;
                ~~~~~~~~~~~~~~~~~~~~~~
        at https://deno.land/x/nessie/clients/AbstractClient.ts:15:13

TS2554 [ERROR]: Expected 1 arguments, but got 0.
        await state.client!.rollback();
                            ~~~~~~~~~~
    at https://deno.land/x/nessie/cli.ts:54:29

    An argument for 'amount' was not provided.
      rollback: (amount: amountRollbackT) => Promise<void>;
                 ~~~~~~~~~~~~~~~~~~~~~~~
        at https://deno.land/x/nessie/clients/AbstractClient.ts:16:14

Found 3 errors.

When Running migrate with a create table that has timestampsTz().

The error:
Error:CREATE OR REPLACE FUNCTION trigger_set_timestamp() RETURNS TRIGGER AS $$ BEGIN NEW.updated_at = now(); RETURN NEW; END; $$ language 'plpgsql';CREATE TABLE tbl_user (id uuid PRIMARY KEY, username varchar (25) not null UNIQUE, email varchar (35) default null UNIQUE, phone_number numeric (12) default null UNIQUE, fullname varchar (40), password varchar (100), email_confirmed boolean default false, phone_number_confirmed boolean default false, created_at timestamptz (0) default current_timestamp, updated_at timestamptz (0) default current_timestamp); DROP TRIGGER IF EXISTS set_timestamp on tbl_user; CREATE TRIGGER set_timestamp BEFORE UPDATE ON tbl_user FOR EACH ROW EXECUTE PROCEDURE trigger_set_timestamp();INSERT INTO nessie_migrations (file_name) VALUES ('1590656398438-create-user-table.ts');
PostgresError: syntax error at or near "\"
at ClientPostgreSQL.query (https://deno.land/x/nessie/clients/ClientPostgreSQL.ts:50:13)
at async ClientPostgreSQL.migrate (https://deno.land/x/nessie/clients/AbstractClient.ts:83:9)
at async ClientPostgreSQL.migrate (https://deno.land/x/nessie/clients/ClientPostgreSQL.ts:62:5)
at async run (https://deno.land/x/nessie/cli.ts:62:11)

Please consider upgrading the SQLite version, see https://github.com/dyedgreen/deno-sqlite/releases

The new version now automatically commits changes to databases to the file-system, and generally is more in-line with how a natively compiled SQLite3 package would function 😃

Is your feature request related to a problem? Please describe.
The generated migrations should to be of format YYYYMMddHHmmss for better readability and future proofing.

Describe the solution you'd like
Add a command to the cli for converting timestamps to to datetime format. Also add a new option in settings for telling Nessie that the migrations are in datetime format when generating new migrations.

In v2 this option should be datetime by default and there should be a legacy option to allow for timestamps until first minor update of Nessie.

When running Nessie in legacy mode, a warning should show up in the console and tell the user how to switch to the new format.

I am getting migrations applied in descending order.

Also from the debug log:

Files after filter and sort: 
[
 {
   name: "1589953809972-create_extension_uuid_ossp.ts",
   isFile: true,
   isDirectory: false,
   isSymlink: false
  },
 {
   name: "1589951602231-create_schema_auth.ts",
   isFile: true,
   isDirectory: false,
   isSymlink: false
  }
]

➜  server git:(master) ✗ deno-nessie migrate                     
Migrated 1590069251844-create_schema_auth.ts
Migrated 1590069327251-create_extension_uuid_ossp.ts
Migrated 1590070232932-create_table_auth_users.ts
Migration complete

➜  server git:(master) ✗ deno-nessie rollback
Rolled back 1590070232932-create_table_auth_users.ts
➜  server git:(master) ✗ deno-nessie migrate 
Nothing to migrate

In the above series of commands I run one rollback after a bulk migration. That rollback reversed one migration but trying to migrate again from this point I get Nothing to migrate. I believe the remaining un-migrated script must run but that is not the case.

I think issue #25 must be fixed before this because that gives the correct 'latest migration'. I have a solution that changes filterAndSortFiles function in utils.ts. I'll add it after issue #25 is resolved.

Your question
Hi There -- I can break these questions into multiple if that would help. I have some beginner/starter questions for you if you don't mind.

Background: I already have a basic (very) Deno + Oak + Postgres project going. For example, in my application I have a custom config.ts file that pulls in my Postgres creds from a .env file. I also have a basic db.ts file that establishes a Postgres connection using these credentials.

After initializing Nessie following your docs, here are some questions that come to mind:

1. Should I re-use my database configuration I already have inside my config.ts and db.ts? Or, is it better to keep Nessie and my application separate?

2. When using Nessie, should I use my default "postgres" user or the "root" user like in your example?

3. Related to my previous questions, in my db.ts file I use the deno-postgres query builder to perform some simple queries. Should I re-use it or configure Nessie to be totally separate? Just trying to understand what I can/cannot or should/should not re-use, generally speaking.

Thanks in advance for your help!

File names should only allow a-z, 0-9 and _, and be prefixed with the creation date in yyyymmddHHMMss format.

Example of valid filenames:

• 20200520131415_my_first_migration.ts
• 20200520131415_migration2.ts
• 20200520131415_1.ts

Example of invalid filenames:

• migration.ts
• 20200520131415-migration.ts
• 20200520131415_MIGRATION.ts
• 20200520131415_MyMigration.ts

Support to assign a table a foreign key.

I notice a change in the api.
in the example below queryString return void which has no effect I wonder if it is a mistake:

export const up = (): string => {
  let query = new Schema(dialect).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();
  });

  query += new Schema(dialect).queryString(
    "INSERT INTO users VALUES (DEFAULT, 'Deno', true, 2, DEFAULT, DEFAULT);",
  );

  return query
};

System (please complete the following information):

• OS: OSX 10.14.6
• Deno version 1.0.5

Describe the bug
After running the sample migration file in the README I get an error (pasted below). The migration seems to have worked, but there wasn't an entry made in the nessie_migrations table. Therefore when I try to rollback I get a message saying "nothing to rollback"
To Reproduce
create database
create migration file with the following code

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");
};

run the migration

deno run --allow-net --allow-read --allow-write https://deno.land/x/nessie/cli.ts make create_users

Expected behavior
The migration works without an error. If there is an error be able to roll back the migration with

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

Screenshots or error log

Database setup complete
TODO: handle notice
Error: Error:CREATE OR REPLACE FUNCTION trigger_set_timestamp() RETURNS TRIGGER AS $$ BEGIN NEW.updated_at = now(); RETURN NEW; END; $$ language 'plpgsql';,CREATE TABLE users (id bigserial PRIMARY KEY, name varchar (100), is_true boolean DEFAULT 'false', created_at timestamp (0) DEFAULT current_timestamp, updated_at timestamp (0) DEFAULT current_timestamp, custom_column int default 1);,DROP TRIGGER IF EXISTS set_timestamp on users;,CREATE TRIGGER set_timestamp BEFORE UPDATE ON users FOR EACH ROW EXECUTE PROCEDURE trigger_set_timestamp();,INSERT INTO users VALUES (DEFAULT, 'Deno', true, 2, DEFAULT, DEFAULT);,INSERT INTO nessie_migrations (file_name) VALUES ('1591974934942-create_users.ts');
PostgresError: column "created_at" is of type timestamp without time zone but expression is of type integer
    at ClientPostgreSQL.query (https://deno.land/x/nessie/clients/ClientPostgreSQL.ts:57:13)
    at async ClientPostgreSQL._migrationHandler (https://deno.land/x/nessie/clients/AbstractClient.ts:222:5)
    at async ClientPostgreSQL.migrate (https://deno.land/x/nessie/clients/AbstractClient.ts:97:9)
    at async ClientPostgreSQL.migrate (https://deno.land/x/nessie/clients/ClientPostgreSQL.ts:69:5)
    at async run (https://deno.land/x/nessie/cli.ts:74:11)

Hello again and sorry to be a bother,

I'm currently getting the following error when attempting to run migrations:

AddrNotAvailable: The requested address is not valid in its context. (os error 10049)
    at processResponse (deno:core/core.js:223:11)    at Object.jsonOpAsync (deno:core/core.js:240:12)
    at async Object.connect (deno:runtime/js/30_net.js:206:13)
    at async Connection.startup (connection.ts:143:17)
    at async Client.connect (client.ts:14:5)     
    at async ClientPostgreSQL.prepare (ClientPostgreSQL.ts:38:5)
    at async run (cli.ts:71:9)

I'm using the same connection object when connecting to my database with Postgres and it is connecting fine and I'm able to run queries. I've ran the script with reload and I've done other debugging making sure that the correct config file is being loaded. If I dont' specify a config location, I get an error for being the wrong user, and when I specify the correct location I get this error instead, so I know it's loading properly. Unsure how to procedd and curious if you've seen this error before or could at least point me in the right direction?

System (please complete the following information):

• OS: Ubuntu 20.04.1
• Deno version: 1.3.3
• Nessie version: 1.0.4

Describe the bug
When running on 1.3.3 it gives the error.

To Reproduce
Steps to reproduce the behavior:

1. Setup a migration
2. Run: deno run --allow-net --allow-read --allow-write https://deno.land/x/[email protected]/cli.ts migrate
3. See error

Expected behavior
Migrations run.

Screenshots or error log

❯ deno run --allow-net --allow-read --allow-write https://deno.land/x/[email protected]/cli.ts migrate
Check https://deno.land/x/[email protected]/cli.ts
error: TS2790 [ERROR]: The operand of a 'delete' operator must be optional.
    delete this.conn;
           ~~~~~~~~~
    at https://deno.land/x/[email protected]/connection.ts:612:12

TS2790 [ERROR]: The operand of a 'delete' operator must be optional.
    delete this.bufReader;
           ~~~~~~~~~~~~~~
    at https://deno.land/x/[email protected]/connection.ts:613:12

TS2790 [ERROR]: The operand of a 'delete' operator must be optional.
    delete this.bufWriter;
           ~~~~~~~~~~~~~~
    at https://deno.land/x/[email protected]/connection.ts:614:12

TS2790 [ERROR]: The operand of a 'delete' operator must be optional.
    delete this.packetWriter;
           ~~~~~~~~~~~~~~~~~
    at https://deno.land/x/[email protected]/connection.ts:615:12

Found 4 errors.

Additional context

I believe this is happening because of TypeScript 4.0, which has just been introduced in Deno, it wasn't failing in the previous version.

This is the project and commit that I'm using, if you want to repro: https://github.com/giggio-samples/deno-api-starter-oak/tree/a66ba95ea8aaa0f0dc0ce4812b15d17a8171f8fc

System (please complete the following information):

• OS: Mac 10.13.6
• Deno version 1.0.0
• Nessie version v1.0.0-rc2

Describe the bug
I run the following to init:

➜  deno_sample deno run --allow-net --allow-read --allow-write https://deno.land/x/nessie/cli.ts init

& get [ERROR]: Property 'queryBuilder' is missing in type error.

To Reproduce

1. Run deno_sample deno run --allow-net --allow-read --allow-write https://deno.land/x/nessie/cli.ts init
2. See error

Expected behavior
nessie.config.ts is created.

Screenshots or error log

error: TS2741 [ERROR]: Property 'queryBuilder' is missing in type '{ dialect: DBDialects; connection: QueryHandler; }' but required in type 'Info<undefined>'.
    const exposedObject: Info = {
          ~~~~~~~~~~~~~
    at https://deno.land/x/nessie/clients/AbstractClient.ts:194:11

    'queryBuilder' is declared here.
      queryBuilder: T;
      ~~~~~~~~~~~~
        at https://deno.land/x/nessie/types.ts:10:3

Additional context
NA

Describe the bug
Object is possibly 'undefined'

To Reproduce
Having

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
};

I overcome it by adding exclamation mark:

export const up: Migration<Schema> = ({ queryBuilder }) => {
  return queryBuilder!.create("tbl_user", (table) => {
    table.uuid("id").primary();
    table.string("username", 25).notNullable().unique();
    table.string("email", 35).nullable().default(null).unique();
    table.numeric("phone_number", 12, 0).nullable().default(null).unique(); //9665xxxxxxxx
    table.string("fullname", 40);
    table.string("password", 100);
    table.boolean("email_confirmed").default(false);
    table.boolean("phone_number_confirmed").default(false);
    table.uuid("email_hash");
    table.string("otp_secret", 60);
    table.timestampsTz();
  });
};

When trying to run deno run --allow-net --allow-read --allow-write https://deno.land/x/nessie/cli.ts make example, I have the following error:

error: Uncaught Error: Import 'https://deno.land/[email protected]/fmt/colors.ts' failed: 404 Not Found

I'm using deno 1.0.0-rc3.

Hey @halvardssm , I'm using using nessie with local containers and they're working great. However, when I tried to use it in github actions, nessie won't connect (using postgres), it gives me the following error:

PostgresError: password authentication failed for user "root"
    at parseError (https://deno.land/x/[email protected]/error.ts:105:10)
    at Connection._readAuthResponse (https://deno.land/x/[email protected]/connection.ts:199:13)
    at async Connection.handleAuth (https://deno.land/x/[email protected]/connection.ts:188:9)
    at async Connection.startup (https://deno.land/x/[email protected]/connection.ts:150:5)
    at async Client.connect (https://deno.land/x/[email protected]/client.ts:14:5)
    at async ClientPostgreSQL.prepare (https://deno.land/x/nessie/clients/ClientPostgreSQL.ts:34:5)
    at async run (https://deno.land/x/nessie/cli.ts:71:9)
##[error]Process completed with exit code 1.

Have you already faced this error? Can you help me?
Here are the files:
nessie.config.ts
.env.test
ci.yml

Move the query builder to its own repo and remove it from Nessie

Hi,

I'm trying out nessie at the moment for managing a greenfield, small PostgreSQL database in a really small Deno project. I'm new to Deno and server-side JS but very used to building web apps with Ruby on Rails.

I just created a migration to add an index but had to use raw SQL to do it. This is fine with me for what I'm doing now, but do you have plans to extend out the Schema / Table classes to have an API for a migration that just adds one or more indexes to existing tables?

For reference, this is the format of migration that I'm using:

import { Migration } from "https://deno.land/x/[email protected]/mod.ts";

export const up: Migration = () => {
  return "CREATE INDEX idx_logs_info ON logs(info);";
};

export const down: Migration = () => {
  return "DROP INDEX idx_logs_info;";
};

So I am trying to follow the read me and create a migration. I have ran both

deno run --allow-net --allow-read --allow-write https://deno.land/x/nessie/cli.ts init
deno run --allow-net --allow-read --allow-write https://deno.land/x/nessie/cli.ts make create_users

What is strange is that the make create_users creates a sqllite.db file. I would like to use postgres. Is there some setting that I am missing?

Here is my nessie.config

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

/** These are the default config options. */
const clientOptions = {
  migrationFolder: "./db/migrations",
  seedFolder: "./db/seeds",
};

/** Select one of the supported clients */
const clientPg = new ClientPostgreSQL(clientOptions, {
  database: "deno-demo",
  hostname: "localhost",
  port: 5432,
  user: "postgres",
  password: "postgres",
});
const clientMySql = new ClientMySQL(clientOptions, {
  hostname: "localhost",
  port: 3306,
  username: "root",
  // password: "pwd", // uncomment this line for <8
  db: "nessie",
});
const clientSqLite = new ClientSQLite(clientOptions, "./sqlite.db");

/** This is the final config object */
const config = {
  client: clientPg,
  // Defaults to false, if you want the query builder exposed in migration files, set this to true.
  exposeQueryBuilder: true,
};

export default config;

It would a time-saver to add more examples. It took me some time to figure out how to make an enum.

I had to check how to do it in PostgreSQL and find out what is wrong.

Before:

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

export const up = (schema: Schema): void => {
  schema.create("tbl_deposit", (table) => {
    ...
    table.enum(
      "deposit_type",
      ["monthlyContribution", "monthlyInstallment"],
    );
   ...
    console.log(table.toSql());
  });
};

export const down = (schema: Schema): void => {
  schema.drop("tbl_deposit");
  schema.queryString("DROP TYPE deposit_type;");
};

So I had to make a single quote string inside a double-quoted string
After:

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

export const up = (schema: Schema): void => {
  schema.create("tbl_deposit", (table) => {
    ...
    table.enum(
      "deposit_type",
      ["'monthlyContribution'", "'monthlyInstallment'"],
    );
   ...
    console.log(table.toSql());
  });
};

export const down = (schema: Schema): void => {
  schema.drop("tbl_deposit");
  schema.queryString("DROP TYPE deposit_type;");
};

Following features are needed for v1.0:

Deno version:

• Deno v1.0 denoland/deno#2473

Databases:

• PostgreSQL
• MySQL
• SQLite

Migration CLI:

• Communicates with DB and tracks migrations using a table
• Can rollback a migration
• Can migrate and rollback multiple files (currently only migrate multiple files) #34

Structural:

• Support for env or settings file
• Query tests
• Add integration test with databases
• Lazy import DB Modules to only be fetched if needed #34
• Support custom DB clients using a modular system #34
• Support any query builder using a modular system #34
• Fix bundling issue #22
• Support for seed files #51

Query Builder:

• Rework the codebase to have a cleaner interface and chained opperations
• Add support for multiple DB schemas #34
• Table returns array instead of string #43
• Add rename method for table #45
• Add rename method for column #45
• Add drop method for column #45
• Add unsigned method for integers #45
• Add alter method for schema
• Add support for foreign key contraints
• Support soft deletes

System (please complete the following information):

• Linux 5.9.15-200.fc33.x86_64
• Deno version 1.6.1
• Nessie version 1.1.2

Describe the bug
Import fails with the following message:
Warning std versions prefixed with 'v' were deprecated recently. Please change your import to https://deno.land/[email protected]/fmt/colors.ts (at https://deno.land/[email protected]/fmt/colors.ts) error: Import 'https://deno.land/[email protected]/fmt/colors.ts' failed: 404 Not Found at https://deno.land/x/[email protected]/deps.ts:3:0

To Reproduce
Steps to reproduce the behavior:
deno cache https://deno.land/x/[email protected]/mod.ts

Some breaking changes was added to Deno v0.42 so we need to change the code where the compiler and tests are failing.

Steps:

1. Upgrade Deno deno upgrade
2. Run the tests make test or make test-clean
3. Fix broken code
4. Run deno fmt

Is your feature request related to a problem? Please describe.

The seed files are currently run without any set order (if any it is alphabetical). We should add a property to the migration files which tells Nessie in which order the files should be run and if there are any dependencies/priorities.

As far as my imagination goes, we could do it the following ways, but feel free to chime in if you have another idea (not ordered, but numbered for easy reference):

1. Have a property in the abstract seed class to set priority
   • Seed files with higher priority (larger number) are done first.
   • When two files have the same priority, the order doesn't matter and it will default to alphabetical.
   • Might be slower (and heavier) if the seed files are large as it requires each seed file to be imported before sorting.
   • My preference, but I am not too fond of the cons
2. Have the priority in the file name
   • Will be faster as sorting can be done prior to module import.
   • I think this will be ugly and I would prefer not to go for this option (my opinionated opinion).
3. Use a comment on the first line of the file which can be read before importing
   • Will probably be faster 1, but slower than 2
   • Could be an antipattern
4. Have a separate priority const export similar to 1, but for the file instead of the class
   • Not sure if this is also an antipattern
   • Could be as slow as 1

Update:
I will leave this open until I get some feedback or I figure out a solid way of doing this. It will not be included in the v2 release, but might be a part of a v2 minor release further down the line

➜  server git:(master) ✗ nessie migrate 
Migrated 1590069251844-create_schema_auth.ts
Migrated 1590069327251-create_extension_uuid_ossp.ts
Migrated 1590070232932-create_table_auth_users.ts
Migration complete

➜  server git:(master) ✗ nessie rollback
Rolled back 1590070232932-create_table_auth_users.ts

➜  server git:(master) ✗ nessie rollback
Rolled back 1590069327251-create_extension_uuid_ossp.ts

➜  server git:(master) ✗ nessie rollback
Rolled back 1590069251844-create_schema_auth.ts

➜  server git:(master) ✗ nessie rollback
Nothing to rollback

=============================================

➜  server git:(master) ✗ nessie migrate 
Migrated 1590069251844-create_schema_auth.ts
Migrated 1590069327251-create_extension_uuid_ossp.ts
Migrated 1590070232932-create_table_auth_users.ts
Migration complete

➜  server git:(master) ✗ nessie rollback
Rolled back 1590070232932-create_table_auth_users.ts

➜  server git:(master) ✗ nessie rollback
PostgresError: cannot drop schema auth because other objects depend on it
    at parseError (https://deno.land/x/[email protected]/error.ts:105:10)
    at Connection._processError (https://deno.land/x/[email protected]/connection.ts:421:19)
    at Connection._simpleQuery (https://deno.land/x/[email protected]/connection.ts:292:20)
    at async Connection.query (https://deno.land/x/[email protected]/connection.ts:526:14)
    at async Client.query (https://deno.land/x/[email protected]/client.ts:24:12)
    at async https://deno.land/x/nessie/cli/pgsql.ts:60:28
    at async queryHandler (https://deno.land/x/nessie/cli/utils.ts:135:20)
    at async https://deno.land/x/nessie/cli/pgsql.ts:57:9
    at async traverseAndRollbackFiles (https://deno.land/x/nessie/cli/utils.ts:110:5)
    at async PGSQL.rollback (https://deno.land/x/nessie/cli/pgsql.ts:53:5)

Above is a sample run, reproduced a number of times. The migrate and rollback sequence seems to work at first but upon a further run, nessie fails at rollback. I think the reason is due to the migrations being carried out in a short time frame, such that when doing a rollback it can't figure out which one was the last migration.
Here's a part of the debug log:

Rollback query: 
DROP SCHEMA auth;
Queries: 
[
  "DROP SCHEMA auth",
  "DELETE FROM nessie_migrations WHERE file_name = '1590069251844-create_schema_auth.ts'"
]
Query: 
DROP SCHEMA auth
PostgresError: cannot drop schema auth because other objects depend on it

It skipped the latest migration record in the nessie_migrations table in favor of an older one.

The code in utils.ts has the following line:

export const QUERY_GET_LATEST =
  `select ${COL_FILE_NAME} from ${TABLE_MIGRATIONS} order by ${COL_CREATED_AT} desc limit 1`;

I believe when carrying out migrations in bulk the COL_CREATED_AT doesn't provide a fine enough distinction while adjacent migration scripts run.

I propose using:
select max(${COL_FILE_NAME}) from ${TABLE_MIGRATIONS};
OR
select ${COL_FILE_NAME} from ${TABLE_MIGRATIONS} order by ${COL_FILE_NAME} desc limit 1;

The first one however causes a bug in another issue I'll mention after solving this one. The second one will work but might have to keep this in mind (just discovered): Year 2038 Problem

Thanks.

Latest everything on windows:

docker exec -it api deno run --allow-net --allow-read --allow-write https://deno.land/x/nessie/cli.ts migrate

Check https://deno.land/x/[email protected]/cli.ts
error: TS2345 [ERROR]: Argument of type 'string | URL' is not assignable to parameter of type 'string'.
  Type 'URL' is not assignable to type 'string'.
  return new URL(url).pathname
                 ~~~
    at https://deno.land/[email protected]/path/win32.ts:917:18

TS2345 [ERROR]: Argument of type 'string | URL' is not assignable to parameter of type 'string'.
  Type 'URL' is not assignable to type 'string'.
  return new URL(url).pathname;
                 ~~~
    at https://deno.land/[email protected]/path/posix.ts:438:18

TS2345 [ERROR]: Argument of type 'string | URL' is not assignable to parameter of type 'string'.
  Type 'URL' is not assignable to type 'string'.
  return new URL(url).pathname
                 ~~~
    at https://deno.land/[email protected]/path/win32.ts:911:18

TS2345 [ERROR]: Argument of type 'string | URL' is not assignable to parameter of type 'string'.
  Type 'URL' is not assignable to type 'string'.
  return new URL(url).pathname;
                 ~~~
    at https://deno.land/[email protected]/path/posix.ts:433:18

Found 4 errors.

• OS: Win 64
• Deno version [1.2.0]
• Nessie version [latest]

hi,
i can init configuration in first step with this command:
deno run --allow-net --allow-read --allow-write https://deno.land/x/nessie/cli.ts init

this is output:

error: TS2345 [ERROR]: Argument of type 'string | URL' is not assignable to parameter of type 'string'.
Type 'URL' is not assignable to type 'string'.
return new URL(url).pathname
~~~
at https://deno.land/[email protected]/path/win32.ts:917:18

TS2345 [ERROR]: Argument of type 'string | URL' is not assignable to parameter of type 'string'.
Type 'URL' is not assignable to type 'string'.
return new URL(url).pathname;
~~~
at https://deno.land/[email protected]/path/posix.ts:438:18

TS2345 [ERROR]: Argument of type 'string | URL' is not assignable to parameter of type 'string'.
Type 'URL' is not assignable to type 'string'.
return new URL(url).pathname
~~~
at https://deno.land/[email protected]/path/win32.ts:911:18

TS2345 [ERROR]: Argument of type 'string | URL' is not assignable to parameter of type 'string'.
Type 'URL' is not assignable to type 'string'.
return new URL(url).pathname;
~~~
at https://deno.land/[email protected]/path/posix.ts:433:18

Found 4 errors.

Default client should be removed from state.ts as it does not improve the user experience of this library. When no client is specified, it should simply throw an error.

I just tried to execute one arbitrary unit test using the deno (v.0.42) instalated on my enviroment. Typing:

> deno test [link_to_test.ts]

But got an compilation Error.

My question: Is this I'm doing possible (calling a test without cloning the repository)? Should Deno work as I expected (fetch all the test dependencies and run the test in my machine giving the test result)? Or am I understanding deno wrong?

Because if I MUST clone the repository before run the test, what is the point of deno in relation to nodejs?

PS: I'm newcomer to Deno.
PS2: I think it could be just a temporarily break change, but would like someone more experienced than me to confirm.
PS3: I saw you commit the upgrade code for v.0.42 just 3 hours ago.

Thank you!

#6 #7

I want to run Nessie with a shorter command than to type out the whole URL. So I opted for this 'bundling' solution as pointed out in the discord server of deno. However, I have a problem.

➜ deno bundle https://deno.land/x/nessie/cli.ts deps/nessie_cli.ts

Emitting bundle to "deps/nessie_cli.ts"
1450193 bytes emitted.

[Okay, bundling is done]

➜ deno run --allow-net --allow-read --allow-write https://deno.land/x/nessie/cli.ts make initialize_db

Created migration 1589728502895-initialize_db.ts at /home/***/***/migrations

[Migration gets created when running from a remote URL]

➜ deno run --allow-net --allow-read --allow-write deps/nessie.ts make initialize_db

[However, this does nothing]

System (please complete the following information):

• OS: Mac OS Catalina
• Deno version 1.4.2
• Nessie version 1.1.2

Describe the bug
When I initialize Nessie running the standard command: deno run --allow-net --allow-read --allow-write https://deno.land/x/nessie/cli.ts init, it creates a project/db directory inside my current working directory (project), as well as the default nessie.config.ts file (project/nessie.config.ts). That seems standard and no issue there.

However, if I want to store my migrations and seeds into a different directory/path structure by changing the migrationFolder and seedFolder paths, it does not seem to work.

Here is my current project/nessie.config.ts config:

import { ClientOptions, ClientPostgreSQL, ConnectionOptions } from "./deps.ts";
import config from "./src/app/config/config.ts";

/** These are the default config options. */
const nessieOptions: ClientOptions = {
  migrationFolder: "./src/app/db/migrations",
  seedFolder: "./src/app/db/seeds",
  experimental: true,
};

// Official example imports deno-postgres ConnectionOptions
// https://github.com/halvardssm/deno-nessie/blob/master/examples/config-postgres.ts
const connectionConfig: ConnectionOptions = {
  database: config.DB_DATABASE,
  hostname: config.DB_HOST,
  port: +config.DB_PORT,
  user: config.DB_USER,
  password: config.DB_PASSWORD,
};

export default {
  nessiePostgresClient: new ClientPostgreSQL(nessieOptions, connectionConfig),
};

To Reproduce
Steps to reproduce the behavior:

1. For example, I can change the default migrationFolder: "./db/migrations" to, say, migrationFolder: "./src/app/db/migrations" and seedFolder: "./src/app/db/seeds".

2. I save my changes in nessie.config.ts and then, inside my project root folder, I run my first make [name] command to create my first migration: deno run --allow-net --allow-read --allow-write https://deno.land/x/nessie/cli.ts make create_users. After this command runs, I still end up with a project/db/migrations/12345_create_users.ts file, instead of my preferred and configured path of project/src/app/db/migrations/12345_create_users.ts.

3. It's only when I run the Nessie command from my project/src/app directory, does Nessie create the nested project/src/app/db/migrations and project/src/app/db/seeds directories.

Expected behavior
My expectation is that my nessie configuration will create my nested directories and store the new migration file inside project/src/app/db/migrations/11111_create_users.ts. However, it instead saves the migration file inside project/db/migrations/11111_create_users.ts. I have even deleted the default ./db directory (created when I first ran init with Nessie) and re-ran the make [name] to create another migration. It still doesn't seem to use the paths specified in my nessie.config.ts file.

Perhaps I'm missing something. I just assumed that my nessie.config.ts config would store my migration files and seed files in the relative paths I specify for migrationsFolder and seedFolder, but perhaps I'm missing something. For now, I just make sure I am in my project/src/app directory before I run any Nessie commands. Please let me know if I can provide anything else.

System (please complete the following information):

• OS: macos
• Deno v1.2.0
• Nessie 0.10.0

Describe the bug

Deno v1.2 has introduced a breaking change around the URL constructor, strictly requiring a type of string. This means any modules referencing std need to be bumped to v0.61 to work with Deno v1.2. See denoland/deno#5365 (comment) for more details.

I've started on an upgrade to nessie here: https://github.com/njpearman/deno-nessie/tree/deno-1.2 but there are dependencies that need version bumps first, so I can't open a PR until they have published upgrades. I'm creating this issue to keep a track of things. Hope that's useful :)

• denomander upgrade, PR here: siokas/denomander#23
• deno-postgres upgrade, PR here: denodrivers/postgres#152

Probably upgrades needed for MySQL and SQLite too?

Is your feature request related to a problem? Please describe.
When using other modules, sub projects or the likes, there could be multiple sources of migrations and seed files. This is more of an edge case, and will most likely not impact the average user of this library, but it is a good feature to have. This should allow for multiple directories in the config file and optionally also by using regex. In the future (v2), only array will be supported.

Describe the solution you'd like
Add new option to the ClientOptions, namely plural of the current ones. For each array element, check if it is a folder, if not, check for files by using regex. The migration order still depends on the timestamp, so the migration will combine all the migrations and run them from oldest to newest.

Describe the bug
The migration file name after timestamp became 'true' after make command.
1591215665344-true.ts

To Reproduce
Steps to reproduce the behavior:
deno run --allow-net --allow-read --allow-write https://deno.land/x/nessie/cli.ts make create_users

Expected behavior
1591215665344-create_users.ts

System (please complete the following information):

• OS: Windows 10
• Deno version : 1.0.3
• Nessie version: 0.5.2

Your question
Question is in the title. I see that Nessie uses deno-postgres as a dependency, so I'm wondering should I just use Nessie's db client and query builder, or the basic deno-postgres version? Does it matter which one I use?

The deno-postgres query builder has a couple of helpful interfaces (i.e., QueryConfig, QueryResult) that have been helpful. Anyway, just curious as I do eventually want to use Nessie to help with db migrations.

Thanks!

Is your feature request related to a problem? Please describe.
When running a migration, it should start a transaction as default, unless this is disabled in config. The transaction should also be possible to commit in migration file, and to start new transaction. This will allow the migration to not make any changes in case of errors.

Describe the solution you'd like
Utilize the transaction interface from the clients when making a migration.

Additional context
https://github.com/denodrivers/mysql#transaction

Consider delaying this until jeremyBanks/database#9 is implemented across databases?

Your question
Hello - Alright, this is slow going for me but I'll get there eventually. I'm pretty sure it's not a bug, so I wanted to first confirm my basic configuration with you. For some reason whenever I try to perform my first migrate with a basic create_users.ts migration file, I encounter a TypeError: MigrationClass is not a constructor (see below).

My Basic Config

// https://github.com/halvardssm/deno-nessie/blob/master/examples/config-postgres.ts
import {
  ClientOptions,
  ClientPostgreSQL,
} from "https://deno.land/x/nessie/mod.ts";
import type { ConnectionOptions } from "https://deno.land/x/[email protected]/connection_params.ts";
import config from "./src/app/config/config.ts";

const clientConfig: ClientOptions = {
  migrationFolder: "./src/app/db/migrations",
  seedFolder: "./src/app/db/seeds",
  experimental: true,
};

const connectionConfig: ConnectionOptions = {
  database: "deno_postgres_db",
  hostname: "localhost",
  port: 5432,
  user: "postgres",
  password: "postgres",
};

export default {
  /* exposeQueryBuilder: true, */
  client: new ClientPostgreSQL(clientConfig, connectionConfig),
};

import { AbstractMigration, Info } from "https://deno.land/x/nessie/mod.ts";
import type { Client } from "https://deno.land/x/[email protected]/mod.ts";

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

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

❯ deno-vue-postgres-starter/backend add-nessie [!?] deno run --allow-net --allow-read --allow-write https://deno.land/x/nessie/cli.ts migrate -d
State:
[
  true,
  "file:///Users/gaylonalfano/Code/deno-vue-postgres-starter/backend/nessie.config.ts"
]
Checking config path
Amount pre:
undefined
Latest migrations:
undefined
Filtered and sorted migration files:
[ "1602682202673-create_users.ts" ]
TypeError: MigrationClass is not a constructor
    at ClientPostgreSQL._migrationHandler (AbstractClient.ts:215:25)
    at async ClientPostgreSQL.migrate (AbstractClient.ts:97:9)
    at async ClientPostgreSQL.migrate (ClientPostgreSQL.ts:73:5)
    at async run (https://deno.land/x/[email protected]/cli.ts:74:11)

I looked inside your AbstractClient.ts file for the MigrationClass. Here is a snippet that the error log points to:

if (this.experimental) {
  const MigrationClass: new (
    props: AbstractMigrationProps<Client>,
  ) => AbstractMigration<Client> = await import(
    parsePath(
      this.migrationFolder,
      fileName,
    )
  );

  const migration = new MigrationClass({ client: this.client });

I've hit a roadblock to be honest. My code attempts to follow your basic examples on the README.md and within your examples folder (experimental only). I create the ClientPostgeSQL as client inside the nessie.config.ts. However, I'm not sure whether I need to use/import this client into the migration file or not. No rush. Just trying to get a working basic example before I try to add anything else e.g., Dex (like in your more extensive example).

having a varchar with default value
table.string("name", 100).default("update wallet name");

toSql:
CREATE TABLE tbl_wallet (name varchar (100) default update wallet name)

expected:
CREATE TABLE tbl_wallet (name varchar (100) default 'update wallet name')

Currently the queries and system are optimized for PostgreSQL. This refactoring should separate the logic between the database plugins, and make sure they can work under one interface.

System (please complete the following information):

• OS: macos
• node v12.19.0
• typescript 4.1.3

Describe the bug
when i making a simple post on my node aplication i recibe the request body i making a normal post for a user so when i take all the user data and i execute a save before i send my json i have to delete the password for use in the get so is when the bug appears in a message: "{
"resource": "/Users/dariocoronel/Desktop/practices/Gostack/gobarber/backend/src/routes/users.routes.ts",
"owner": "typescript",
"code": "2790",
"severity": 8,
"message": "The operand of a 'delete' operator must be optional.",
"source": "ts",
"startLineNumber": 24,
"startColumn": 20,
"endLineNumber": 24,
"endColumn": 33
}"

To Reproduce
Steps to reproduce the behavior:

1. create a simple normal post in a node with express
2. get all the request body data then save it to the basedata
3. when you try to send a json with this that but you want delete user password before send all the data to a get for a list
4. See error "The operand of a 'delete' operator must be optional."ts(2790) [24.20]

Expected behavior
i want to solve this problem

Screenshots or error log
here we can see clarily how the bug appears

Additional context
someone can helpme please

Is your feature request related to a problem? Please describe.
I want to implement repository pattern. It makes the code robust.

Describe the solution you'd like
maybe you can get those fields from model itself.

System:

• OS: Windows
• Deno 1.9.0 (error also on 1.8.3)
• Nessie version 1.2.0

Error when running deno run -A --unstable https://deno.land/x/nessie/cli.ts migrate -c .\nessie.config.ts

error: Uncaught (in promise) Error: The filename, directory name, or volume label syntax is incorrect. (os error 123)
    await Deno.lstat(filePath);
    ^
    at unwrapOpResult (deno:core/core.js:100:13)
    at async Object.lstat (deno:runtime/js/30_fs.js:200:17)
    at async exists (https://deno.land/[email protected]/fs/exists.ts:7:5)

Error is caused by this line. Looks like Deno has problem reading file path with the file:// prefix. The problem can be reproduced with the following code:

import { resolve } from "https://deno.land/[email protected]/path/mod.ts";
await Deno.lstat("file://" + resolve("./nessie.config.ts"));

No problems on Linux, however.

The example needs to be updated.
I also wonder why the schema was removed from the input it was saving some typing. I understand the dialect was removed from the nessie.config.ts file however I think we only need it to be defined once.

query += ...; does not work anymore since the return type is string[]

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

const dialect: dbDialects = "mysql"

export const up: Migration = () => {

  let query = new Schema(dialect).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();
  });

  query += new Schema(dialect).queryString(
    "INSERT INTO users VALUES (DEFAULT, 'Deno', true, 2, DEFAULT, DEFAULT);",
  );

  return query
};

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

I think the ultimate would be something like:

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

const dialect: dbDialects = "mysql"

export const up: Migration = () => {
  const schema = new Schema(dialect);
  schema.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();
  });

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

export const down: Migration = () => {
  const schema = new Schema(dialect);
  schema.drop("users");
  return schema.query;
};

i'm running postgres through docker, exposing 5432 as the port.

i've setup the nessie config

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

const config: nessieConfig = {
  migrationFolder: "./migrations",
  connection: {
    user: Deno.env.get("DB_USER"),
    password: Deno.env.get("DB_PWD"),
    database: Deno.env.get("DB_NAME"),
    hostname: Deno.env.get("DB_HOST"),
    port: 5432,
  },
  dialect: "pg",
};

export default config;

and running deno run --allow-net --allow-env --allow-read https://deno.land/x/nessie/cli.ts migrate -c ./nessie.config.ts yields

ConnectionRefused: Connection refused (os error 61)
    at unwrapResponse ($deno$/ops/dispatch_json.ts:43:11)
    at Object.sendAsync ($deno$/ops/dispatch_json.ts:98:10)
    at async Object.connect ($deno$/net.ts:180:11)
    at async Connection.startup (https://deno.land/x/[email protected]/connection.ts:138:17)
    at async Client.connect (https://deno.land/x/[email protected]/client.ts:14:5)
    at async State.initClient (https://deno.land/x/nessie/cli/state.ts:99:9)
    at async run (https://deno.land/x/nessie/cli.ts:35:7)

from what i can tell by some cheap console.log debugging, the specified config isn't read and always defaults to

{
 database: "nessie",
 hostname: "localhost",
 port: 5000,
 user: "root",
 password: "pwd"
}