subzerocloud / subzero-cli Goto Github PK

Running subzero-migrations add on Windows 10 and nodejs 13.0 fails with an error. The source is the node module "sleep" which has not been updated / recompiled for nodejs 13.x.

Error output:

C:\dev\foo>subzero-migrations add
internal/modules/cjs/loader.js:1041
  return process.dlopen(module, path.toNamespacedPath(filename));
                 ^

Error: The module '\\?\C:\Users\bar\AppData\Roaming\npm\node_modules\subzero-cli\node_modules\sleep\build\Release\node_sleep.node'
was compiled against a different Node.js version using
NODE_MODULE_VERSION 72. This version of Node.js requires
NODE_MODULE_VERSION 79. Please try re-compiling or re-installing
the module (for instance, using `npm rebuild` or `npm install`).
�[90m    at Object.Module._extensions..node (internal/modules/cjs/loader.js:1041:18)�[39m
�[90m    at Module.load (internal/modules/cjs/loader.js:822:32)�[39m
�[90m    at Function.Module._load (internal/modules/cjs/loader.js:730:14)�[39m
�[90m    at Module.require (internal/modules/cjs/loader.js:864:19)�[39m
�[90m    at require (internal/modules/cjs/helpers.js:74:18)�[39m
    at Object.<anonymous> (C:\Users\bar\AppData\Roaming\npm\node_modules\�[4msubzero-cli�[24m\node_modules\�[4msleep�[24m\index.js:1:13)
�[90m    at Module._compile (internal/modules/cjs/loader.js:971:30)�[39m
�[90m    at Object.Module._extensions..js (internal/modules/cjs/loader.js:1011:10)�[39m
�[90m    at Module.load (internal/modules/cjs/loader.js:822:32)�[39m
�[90m    at Function.Module._load (internal/modules/cjs/loader.js:730:14)�[39m

Versions:

C:\>node --version
v13.0.1
C:\>npm --version
6.12.0

Can the subzero-migrations command ignore space or tab?

Steps to reproduce the error:

1. subzero base-project
2. cd project/
3. docker-compose up -d
4. subzero migrations init

Error:

bash-3.2# subzero migrations init
Writing database dump to /Users/adasia/Desktop/project/tmp/dev-initial.sql
pg_dumpall: could not open the output file "/src/tmp/dev-initial.sql.roles": Permission denied
pg_dump: [archiver] could not open output file "/src/tmp/dev-initial.sql.schema": Permission denied
fs.js:651
return binding.open(pathModule._makeLong(path), stringToFlags(flags), mode);
^

Error: ENOENT: no such file or directory, open '/Users/adasia/Desktop/project/tmp/dev-initial.sql.roles'
at Object.fs.openSync (fs.js:651:18)
at Object.fs.readFileSync (fs.js:553:33)
at dumpSchema (/Users/adasia/PycharmProjects/subzero-cli/dist/migrations.js:162:28)
at initMigrations (/Users/adasia/PycharmProjects/subzero-cli/dist/migrations.js:45:3)
at Command. (/Users/adasia/PycharmProjects/subzero-cli/dist/migrations.js:208:32)
at Command.listener (/Users/adasia/PycharmProjects/subzero-cli/node_modules/commander/index.js:315:8)
at emitTwo (events.js:125:13)
at Command.emit (events.js:213:7)
at Command.parseArgs (/Users/adasia/PycharmProjects/subzero-cli/node_modules/commander/index.js:653:12)
at Command.parse (/Users/adasia/PycharmProjects/subzero-cli/node_modules/commander/index.js:475:21)

The tmp folder is empty. Thanks in advance!

Previous versions of subzero-cli (not sure which) were creating "CREATE ROLE" statements, e.g. for user api.

Current version 0.1.38 is not creating these statements anymore when initializing migrations. Subsequently, adding migrations with diff fails due to missing roles.

Steps to reproduce:
subzero-base-project -d foo -k subzero -m
cd foo
subzero-migrations init
sed -i 's/todo text not null/todo text not null,\nfoo text/g' db/src/data/todo.sql
subzero-migrations add --note "foo" foo

Last command results in the following output:

2019-11-15 16:58:13.549 UTC [75] ERROR:  role "api" does not exist
2019-11-15 16:58:13.549 UTC [75] STATEMENT:  ALTER TABLE api.todos OWNER TO api;
psql:/docker-entrypoint-initdb.d/20191115165501-initial.sql:285: ERROR:  role "api" does not exist
Gave up on waiting for db. The last 30 lines of log are above.

Hello
on Windows 10 64bit. I followed all the steps described on the corresponding GitHub page and subzero base-project which takes me through the questions and finishes with "Downloading the base project..". But my project directory always stays empty afterwards. Switching between between the options (subzero or pure postgrest, database structure/migrations here yes/no) makes no difference, neither does running it within a Powershell session with admin privileges.

git branch --unset-upstream is needed after
https://github.com/subzerocloud/subzero-cli/blob/master/src/main.js#L62

I did not want openresty and created a frontend service called "client" (react app). Working locally was a joy with hot reload of the DB. This all worked fine until I went to deploy. I followed your Amazon ECS+RDS guide (also tried Fargate but did not have any luck). I got everything setup as per your guide. Then the problem came with the deploy. There is an expectation that openresty will be there. I had to modify (fork - https://github.com/rkoberg/subzero-cli ) the subzero-cli to get to the point where I could deploy. [Edit] My fork broke the subzero dashboard (I can see logs in Kitematic, but can't reload/reset). The fork did work to deploy the client container to AWS.

We are using auth0 for authentication and so do not even need a user table.
[/Edit]

The problem with the deploy happened at the SQL/postgresql level. I did not touch anything from the db/src/libs dir. Got the error at:

  + 0000000001-initial .. psql:deploy/0000000001-initial.sql:1248: ERROR:  syntax error at or near "AS"
LINE 2:     AS integer
            ^
not ok
"psql" unexpectedly returned exit value 3

Deploy failed

and in the sql deploy file where the error occurs looks like:

CREATE SEQUENCE user_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

Dear Ruslan Talpă

I am stuck with an old Macbook, that doesn't have hypervisor support, so I can't run docker on it. I do have a raspberry Pi server humming along in my home though, doing all kinds of cool things. Docker runs just fine on it, being a recent Linux machine. There's just one catch: the containers need to be compiled for arm architecture.

My wish would be for subzero to provide arm compatible containers. I have no idea how much effort this would be. Maybe it would be a good exercise for the upcoming shift to Apple ARM for Macbooks and co.?

Here's an example of some containers being offered in multiple architectures: https://hub.docker.com/r/pihole/pihole/tags

All the best
Michi

it would be nice to have a key which reloads the schema in case one is feeding the DB from outside (postgrest as well as openresty).
Additionally having that feature usable as own CLI command as well without the UI would be great.

Kind regards,

I just got this error, when I try to reset the db:

TypeError: Cannot read property 'replace' of undefinedro dashboard
    at /home/schmittr/.node_modules_global/lib/node_modules/subzero-cli/dist/common.js:35:45
    at Array.reduce (<anonymous>)
    at runCmd (/home/schmittr/.node_modules_global/lib/node_modules/subzero-cli/dist/common.js:33:63)
    at runSql (/home/schmittr/.node_modules_global/lib/node_modules/subzero-cli/dist/watch.js:73:29)
    at resetDb (/home/schmittr/.node_modules_global/lib/node_modules/subzero-cli/dist/watch.js:41:14)
    at Dashboard._this.handleKeyPress (/home/schmittr/.node_modules_global/lib/node_modules/subzero-cli/dist/dashboard.js:197:30)
    at Element.<anonymous> (/home/schmittr/.node_modules_global/lib/node_modules/subzero-cli/dist/dashboard.js:247:23)
    at Element.EventEmitter._emit (/home/schmittr/.node_modules_global/lib/node_modules/subzero-cli/node_modules/blessed/lib/events.js:94:20)
    at Log.EventEmitter.emit (/home/schmittr/.node_modules_global/lib/node_modules/subzero-cli/node_modules/blessed/lib/events.js:131:12)
    at Program.<anonymous> (/home/schmittr/.node_modules_global/lib/node_modules/subzero-cli/node_modules/blessed/lib/widgets/screen.js:602:15)

When running a cloned copy of the postgrest-starter-kit, get this error when running

subzero migrations init

error is: server version: 9.6.5 pg_dumpall version: 9.5.8
error message is: aborting because of server version mismatch

subzero version: 0.1.8
Native postgresql: 9.5.8 psql: 9.5.8

Mismatch between native postgresql client and postgresql server bundled in the container may cause subzero cli to abort!!

Right now the main functionality is the console/dashboard but we want to have other command in the future.
Right now we call it like this:
sz #in project directory or sz some/path/to/.env and this brings up the dashbaord

Let's refactor the code a little bit to prepare for the following way to call devtools.

• sz #called in the root of the project, open dashbaord

• sz -e some/path/to/.env # open dashboard

• sz dashboard #called in the root of the project

• sz -e some/path/to.env dashboard

• sz -h or sz help

There should be a "main.js" file that reads the params and decides what to do then each module (dashboard/help) should be in a separate file.
all js files should move to src/

Hi

Thank you for providing us with a great package.

I have been using it since version 0.1, but when I built a repository with the following monorepo format, it stopped working with both updates.

packages
|-- subzero
    |-- db
    |-- node_modules
    |-- openresty
    `-- Dockerfile
|-- pkg1  
     `-- Dockerfile
|-- pkg2
     `-- Dockerfile
`-- pkg3
     `-- Dockerfile
`-- docker-compose.yml

I checked the contents of the update and found that it had changed to using docker-compose command to get containers, so I am trying to stop using the subzero containers and run them on the host machine.

However, we do not want to change the existing directory structure significantly, so we would like to keep what is under packages/subzero.
I tried to give the APP_DIR value set in env.js from an environment variable to make it work, but this value seems to be forced to be set in the same location as the .env file.

If I can set that value arbitrarily (the one specified in the environment variable has a higher priority), it appears to solve my problem.

I would like to send a PR if you approve this content, can I get your opinion?

subzero-base-project is outputting "Downloading the base project...", then exits with exit code 0 and leaves the directory empty.

Environment:

Windows 10 Home
Docker Toolbox
subzero-cli 0.1.28 

It's useful to be able to select queries in the log output, it's not possible now

Right now the sub0_devtools tries to run the user sql scripts and recreates the db in case of failure, this cycle is slower on later stages of development and in projects with a considerable size of master data.

I have a modest dataset related to some local GIS app that takes 12 seconds to recreate.

For now I propose using apgdiff, with this migrations are easier, say we have:

-- v1.sql
CREATE TABLE user (
    email TEXT,
    pass TEXT NOT NULL CHECK (length(pass) < 512),
    first_name TEXT
);
-- v2.sql
CREATE TABLE user (
    email TEXT,
    pass TEXT NOT NULL CHECK (length(pass) < 512),
    first_name TEXT,
    last_name TEXT
);

To migrate from v1.sql to v2.sql we can do this:

java -jar apgdiff-2.4.jar v1.sql v2.sql                                                                                                   

This outputs:

ALTER TABLE "user" ADD COLUMN last_name TEXT;

To reverse from v2.sql to v1.sql:

java -jar apgdiff-2.4.jar v2.sql v1.sql                                                                         

This outputs:

ALTER TABLE "user" DROP COLUMN last_name;

I think this tool approach for migrations is better than the ones that rely on convention and discipline like sqitch or pgrebase.

The thing is apgdiff only operates on sql files, so I could do a pg_dump -s on the user database and just run apgdiff with this and the user current db defined in his folder structure then apply the diff.

important grant statements are missing

migration generated on 0.1.40 looks somewhat like this:

START TRANSACTION;

SET search_path = data, pg_catalog;

ALTER TABLE test_table
	ADD COLUMN test character varying;


SET search_path = api, pg_catalog;

DROP VIEW test_table;

CREATE VIEW test_table AS
	SELECT test_table.id,
    test_table.test
   FROM data.test_table;

ALTER VIEW test_table OWNER TO api;

REVOKE ALL ON TABLE test_table FROM anonymous;
GRANT SELECT ON TABLE test_table TO anonymous;
REVOKE ALL ON TABLE test_table FROM member;
GRANT SELECT ON TABLE test_table TO member;

COMMIT TRANSACTION;

migration generated on 0.2.1 looks somewhat like this:

BEGIN;

SET search_path = data, pg_catalog;

ALTER TABLE test_table
	ADD COLUMN test character varying;


SET search_path = api, pg_catalog;

DROP VIEW test_table;

CREATE VIEW test_table AS
	SELECT test_table.id,
    test_table.test
   FROM data.test_table;

ALTER VIEW test_table OWNER TO api;

COMMIT;

Hi team, I'm new to npm, so forgive me here, but I get the below when I run npm install -g subzero-cli (same thing happens with sudo npm install -g subzero-cli):

npm WARN deprecated [email protected]: Please update to minimatch 3.0.2 or higher to avoid a RegExp DoS issue
npm WARN deprecated [email protected]: Please update to minimatch 3.0.2 or higher to avoid a RegExp DoS issue
npm WARN deprecated [email protected]: Please update to minimatch 3.0.2 or higher to avoid a RegExp DoS issue
npm WARN deprecated [email protected]: please upgrade to graceful-fs 4 for compatibility with current and future versions of Node.js
npm WARN deprecated [email protected]: Use uuid module instead
npm WARN deprecated [email protected]: The major version is no longer supported. Please update to 4.x or newer
npm WARN [email protected] requires a peer of react-devtools-core@^3.0.0 but none is installed. You must install peer dependencies yourself.
npm WARN [email protected] requires a peer of ws@^6.0.0 but none is installed. You must install peer dependencies yourself.

npm ERR! path /usr/local/lib/node_modules/subzero-cli/node_modules/highlight.js/tools/build.js
npm ERR! code ENOENT
npm ERR! errno -2
npm ERR! syscall chmod
npm ERR! enoent ENOENT: no such file or directory, chmod '/usr/local/lib/node_modules/subzero-cli/node_modules/highlight.js/tools/build.js'
npm ERR! enoent This is related to npm not being able to find a file.
npm ERR! enoent 

npm ERR! A complete log of this run can be found in:
npm ERR!     /Users/jambo/.npm/_logs/2019-02-25T08_02_57_131Z-debug.log

See full debug log here

I mostly use tmux for developing in terminals, when doing: sub0 console screen is runned inside tmux, screen conflicts with tmux prefix "ctrl+.." so I can't choose any screen split for scrolling the logs and also I can't quit screen.

I think it would be better not to break any user workflow nor force them to learn screen to navigate the logs.

I propose to visualize the logs with blessed, a nodejs terminal ui lib, some examples:

Webpack dashboard

Nodejs dashboard

With that we have scrolling working without terminal multiplexers and also the text widgets collapse so it may be better for smaller screen sizes.

"Reset DB" only drop the database, but the roles will not be dropped.

I drop the roles in db/src/sample_data/reset.sql. With a quick solution, execute "db/src/sample_data/reset.sql" by modifying the resetDb function.

Is there a better solution to it? Maybe we can add an environment variable to specify the reset file.

Clear log is not working properly in a worker I'm currently debugging. After clearing the log, new messages aren't shown anymore. This affects all logs, not only the one of the worker.

Scrolling can/should be better (mouse or keyboard).
It gets really slow when the interface has been running for a while and the logs are long (especially on the db screen).
To reproduce, "reset db" a few times.

Is it possible to use this cli (specifically the migrations feature) without the postgrest stack?

I want to try postgraphile, what are the steps I need to take in order to clean the subzero boilerplate and use the migrations feature?

If I chose No for the manage DB structure I receive an error. I posted the command line output below. I did not look into it at this time I just thought I would post for visibility.

➜  subzero_postgrest subzero base-project
? Enter the directory path where you want to create the project .
? Choose the starter kit postgrest-starter-kit (REST)
? Do you want to manage your database structure/migrations here? No
Downloading the base project..
sed: 1: "docker-compose.yml": extra characters at the end of d command
(node:10060) UnhandledPromiseRejectionWarning: Error: Command failed:
      docker run -u 501:20 -v /Users/knik/dev/scratch/subzero_postgrest/:/src subzerocloud/subzero-cli-tools
      sh -c 'rm -rf /Users/knik/dev/scratch/subzero_postgrest/./db && rm -rf /Users/knik/dev/scratch/subzero_postgrest/./tests/db/{rls,structure}.sql && rm -rf /Users/knik/dev/scratch/subzero_postgrest/./tests/rest/{auth,common,read}.js && sed -i "/### DB START/,/### DB END/d" docker-compose.yml && sed -i "/3000/,/db/{/3000/!d}" docker-compose.yml && sed -i "/db/d" docker-compose.yml'
sed: 1: "docker-compose.yml": extra characters at the end of d command

    at checkExecSyncError (child_process.js:574:11)
    at Object.execSync (child_process.js:611:13)
    at baseProject (/usr/local/lib/node_modules/subzero-cli/dist/main.js:93:29)
    at /usr/local/lib/node_modules/subzero-cli/dist/main.js:80:12
    at <anonymous>
    at process._tickCallback (internal/process/next_tick.js:160:7)
(node:10060) UnhandledPromiseRejectionWarning: Unhandled promise rejection. This error originated either by throwing inside of an async function without a catch block, or by rejecting a promise which was not handled with .catch(). (rejection id: 1)
(node:10060) [DEP0018] DeprecationWarning: Unhandled promise rejections are deprecated. In the future, promise rejections that are not handled will terminate the Node.js process with a non-zero exit code.

I have created a table with
For example :
create table aaa (
ac integer NOT NULL
,bc TIMESTAMP NOT NULL
,primary key (ac)

)partition by list (ac);
create table aaa1 partition of aaa for values in (1);
create table aaa2 partition of aaa for values in (2);
Then I run subzero migrations add --dry-run --with-privileges --note ****
I got an error :
Exception in thread "main" cz.startnet.utils.pgdiff.parsers.ParserException: Cannot parse string: CREATE TABLE aa (

)
PARTITION BY LIST (ab_partitions_num);
Unsupported command at position 668 'PARTITION BY LIST (a'
at cz.startnet.utils.pgdiff.parsers.Parser.throwUnsupportedCommand(Parser.java:404)
at cz.startnet.utils.pgdiff.parsers.CreateTableParser.parse(CreateTableParser.java:103)
at cz.startnet.utils.pgdiff.loader.PgDumpLoader.loadDatabaseSchema(PgDumpLoader.java:244)
at cz.startnet.utils.pgdiff.loader.PgDumpLoader.loadDatabaseSchema(PgDumpLoader.java:322)
at cz.startnet.utils.pgdiff.PgDiff.createDiff(PgDiff.java:35)
at cz.startnet.utils.pgdiff.Main.main(Main.java:39)

And files created in the deploy, revert and verify folder are all empty.

│./db/src/api/todos.sql changed                                                                                                                                                                            │
│Starting code reload ------------------------                                                                                                                                                             │
│psql: FATAL:  role "superuser" does not exist

Using non-default port in postgresql would fail the live reloading.

https://github.com/subzerocloud/subzero-cli/blob/master/src/migrations.js#L330
should be .action((options) => { checkIsAppDir(); initMigrations(options.debug, options.dbDockerImage);});

Hi,

I am using postgres-starter-kit and it seems like authenticator role gets missing when I try to generate init migration script via subzero. The script only creates anonymous, api and webuser roles. However, the role membership section does seem to use authenticator role which makes the init script to fail while executing.
Steps to reproduce:

• executing subzero-base-project and choosing postgres-starter-kit
• executing the stack using docker-compose up -d
• generating init script using: subzero-migrations init --with-roles --with-privileges --debug