halvardssm / deno-nessie Goto Github PK
View Code? Open in Web Editor NEWA modular Deno library for PostgreSQL, MySQL, MariaDB and SQLite migrations
License: MIT License
A modular Deno library for PostgreSQL, MySQL, MariaDB and SQLite migrations
License: MIT License
System (please complete the following information):
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#23deno-postgres
upgrade, PR here: denodrivers/postgres#152Probably upgrades needed for MySQL and SQLite too?
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:
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?
When using Nessie, should I use my default "postgres" user or the "root" user like in your example?
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!
System:
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.
➜ 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.
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"
}
System (please complete the following information):
Describe the bug
When running on 1.3.3 it gives the error.
To Reproduce
Steps to reproduce the behavior:
deno run --allow-net --allow-read --allow-write https://deno.land/x/[email protected]/cli.ts migrate
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
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?
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):
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
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
System (please complete the following information):
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
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.
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
}
]
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
.
System (please complete the following information):
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:
For example, I can change the default migrationFolder: "./db/migrations"
to, say, migrationFolder: "./src/app/db/migrations"
and seedFolder: "./src/app/db/seeds"
.
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
.
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):
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:
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
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.
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):
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]
➜ 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.
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;");
};
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();
});
};
Pagic is a static site generator powered by Deno + React
Here are some demos:
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):
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
deno_sample deno run --allow-net --allow-read --allow-write https://deno.land/x/nessie/cli.ts init
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
System (please complete the following information):
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?
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.
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
System (please complete the following information):
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:
Expected behavior
nessie_migrations table should have values in it.
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')
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):
Following features are needed for v1.0:
Deno version:
Databases:
Migration CLI:
Structural:
Query Builder:
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;
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).
// 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).
Move the query builder to its own repo and remove it from Nessie
Support to assign a table a foreign key.
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.
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;";
};
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.
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.
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!
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)
Some breaking changes was added to Deno v0.42 so we need to change the code where the compiler and tests are failing.
Steps:
deno upgrade
make test
or make test-clean
deno fmt
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;
};
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.
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 😃
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!
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
};
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 😄
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
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.