fortunejs / fortune-postgres Goto Github PK

So i'm just getting started on an expressjs backend.. i'm using a postgresdb and of course am making use of Sequelize.

Does this REPLACE Sequelize or do I use them in conjunction somehow?

I'm very early, so making changes now would be easy -- and I really like the look of the fortunejs project (and want a json-api compliant API)

(And there is Bookshelf hah. Man i'm new to this particular nodejs ORM world)

Hi,

Is there a way to let the database generate the ID?

At the moment, it seems that the ID is created manually with the generatePrimaryKey.

Thanks.

First of all, thank you for your work!

I’m using FortuneJS with the PostgreSQL adapter and Ember Data.
Now, when the server has been up for a while, the following error occurs:

Unhandled rejectionError: Connection terminated

It comes from pg/lib/client.js.

My guess is that this error occurs because connections aren't properly closed, or there are too much open connections. This error occurred in a test-environment with only 2 users.

After looking through the code I found out that there was no client pooling configured.
Is there a reason client pooling isn’t implemented? I’m willing to set up a PR if you think it would be a good addition.

More details: https://github.com/brianc/node-postgres#client-pooling

Thanks again!

Attempting to use the Number primaryKeyType does not actually product numbers. It instead tries to create a record with the ID "YBL6QamdkjSeH6ncqpAX", which causes postgres to return an error (making me think the postgres schema is correct, but the ID generation is not).

Store Definition

const store = fortune({
  user: {
    email: { type: String },
    password: { type: String },
    name: { type: String }
  }
}, {
  adapter: [
    postgresAdapter,
    {
      url: `postgres://pguser:pgpass@${DB_URL.hostname}:${DB_URL.port}/pguser`,
      primaryKeyType: Number
    }
  ]
});

Response to a post

ERROR:  invalid input syntax for type double precision: "YBL6QamdkjSeH6ncqpAX"`
STATEMENT:  insert into "user" ("id", "email", "name", "password") values ($1, $2, $3, $4)

The use of pg.connect here works to solve #14, but it's a soft-deprecated API (source). We should update to use the Pool constructor. I can PR this soon.

Hi, I'm using the json-api serializer with this adapter. JSON API serializer throws an error about toString of undefined— it's trying to stringify the id field received from Postgres, but that field is empty because it doesn't exist in the database.

In this adapter, I can get this to work by changing this and specifying my primary key:

I can't figure out how to do this through the public API. Do I need to extend the class to specify a primary key? Would that be the only way to do this?

More features:

• Unsafe mode for destructive database operations, such as automatically dropping tables, dropping columns, altering data types, renaming columns.
• Option to automatically create indexes on specified columns.
• CockroachDB compatibility.

Hi

I'm getting an error for doing this.

store.defineType('accommodation', {
    parent: { link: 'accommodation' },
    name: { type : String},
    description: { type: String }
});

Is this feature not included?

As opposed to integer[] columns. Or any thoughts on why it's not worth implementing. I've read lots of conflicting opinions on join tables vs array columns for many to many relationships. I think I would be more comfortable with join tables (I've mainly worked with MySQL and SQLServer) but Fortune is such a good fit for my current needs I'm loathe to leave it for potentially unfounded worries.

Currently the adapter attempts to alter existing tables and add missing columns, but it doesn't work if the table already has rows in it, and the new column is an array since the adapter try to apply a "not null" request to the column.

Here's an example query generated by the adapter :

alter table "animal" add column "surnames" text[] not null

If there is preexisting rows in the table postgres will report the following error:

ERROR: column "surnames" contains null values

I'm not sure what should be done here, the not null constraint (if needed at all) could be added after adding the column and setting default values for preexisting rows, but this constraint may be not needed initially

Let's say I have a schema named schema-v1 and another named schema-v2 that both have a table called users. If schema-v1 has a column in users like users.email then the below if statement will wrongly think that the column exists in schema-v2. This is because information_schema.columns returns a list of columns in all schemas.

Solution should be to specify the exact schema to search in.

Hello Dali :),

I know that this package is not ready to production yet, but I was testing it to import some mysql data to my mongodb and I lost some time trying to find why the ORM always connect as root if I told him to connect with other user.

I found that this is the correct orm.connect options:
orm.connect({
host: options.host,
database: options.db,
protocol: options.adapter,
user: options.username,
password: options.password,
port: options.port,
query: options.flags
});

When I enter here on your github, I saw that you already had the same problem and fix it. Could you please send this to the npm? I believe that will help more people :D.

And really thanks for the lib, it`s fantastic :D.

[]`s

Babel considered harmful.

I was playing around with CRUD'ing some resources with fortune-postgres+the JSON API adapter, and an ID was generated like this:

"PDyp40Yu/lUBSHbhvtuP"

This seems unexpected, as I'd need to encode the ID to send it over the wire. I'm using some generic JSON API code on the client, and it produces incorrect requests from this ID; such as

DELETE /things/PDyp40Yu/lUBSHbhvtuP

@daliwali , if I updated this ID generation logic to ensure that / are never created, would you be 👍 to that change?

Right now, they are still the stub methods. Would be nice to actually guarantee transactions per request.

I'm trying to connect to a database hosted on Heroku and keep getting the error permission denied for database "postgres". Looking at the source code, it seems this plugin attempts to create a connection to a database named "postgres" before connecting to the specified database (so the database can be created if necessary). This is problematic when using Heroku, since database names are automatically generated for you and it's not possible to change them. 😕

Is there a workaround for this or is having a DB named "postgres" a requirement?

Upstream has new query options.

Currently tables are initialized once and not touched again. Need to add, drop, & modify columns based on type definitions if they changed.

OS Version Mac OS 11.4
Node Version 14.17.3
Npm version 6.14.13

They fixed this BUG and suggest to update the dependency
#2171

Hej, I have this tests

  beforeEach(function () {
    return store.adapter.delete('user').then(() => {
      console.log('A');
      return store.adapter.delete('customer');
    }).then(() => console.log('B'));
  });

  describe('customers', function () {
    it('should create a default customer', function () {
      return database.addDefaultCustomer();
    });
  });

  describe('users', function () {
    it('should create a default user for the default customer', function () {
      console.log('C')
      return database.addDefaultCustomer()
        .then(() => database.addDefaultUser());
    });

    it('should add two users for the default customer', function () {
      console.log('D')
      return database.addDefaultCustomer()
        .then(() => database.addDefaultUser())
        .then(() => database.addAdminUser());
    });
  });

And I get this error on the first user test (the one with console.log('C');)

ConflictError: Unique constraint violated.
    at client.query [as callback] (node_modules/fortune-postgres/lib/index.js:360:27)
    at NativeQuery.handleError (node_modules/pg/lib/native/query.js:64:10)
    at after (node_modules/pg/lib/native/query.js:84:19)
    at onError (node_modules/pg-native/index.js:160:5)
    at Client._readError (node_modules/pg-native/index.js:81:8)
    at Client._read (node_modules/pg-native/index.js:121:19)

This error happened because the customer table is not properly truncated. But I'm sure that the beforeEach hook is executed before. Here is the order of the console.log() I get

A
B
C

which is correct. That is why I think the store.adapter.delete() function resolve to early. Is it a bug or did I miss something ?

Often times, a RESTful API is just one thing that accesses a DB in an app. Atm, there's no way to share the same pool of connections between fortune and other things in your app that may connect that I can find.

For instance, in a simple app, I've got three separate things that access the database:

1. a library for managing user sessions
2. some code in my server, unrelated to any lib or framework
3. fortune-postgres

Due to 2, I make a pg connection myself. Then, the lib I'm using for 1 has a hook to pass it a pg instance, so that the connection can be shared between those two – that lib doesn't make its own pool of clients using pg. But fortune-postgres only accepts a URL, so it creates a new instance of pg, and therefore, a new set of connections.

I know it's nice to abstract away the underlying npm module, but exposing a pg option as an alternative to url would be useful, I think.

Another option would be letting devs pass in more pg options, so that store.adapter.client could be used for other connections.

Hello,

we have the following model:

user = {
  favouriteColours: { link: 'colour', isArray: true },
}

colour = {
  name: { type: String }
}

It seems on startup of fortune, the following schema is created:

                                                        Table "public.user"
                    Column                     |  Type  | Collation | Nullable |   Default    | Storage  | Stats target | Description 
-----------------------------------------------+--------+-----------+----------+--------------+----------+--------------+-------------
id                    | text                        |           | not null |              | extended |              | 
favouriteColours                                   | text[] |           | not null | '{}'::text[] | extended |              | 

and

                                                Table "public.colour"n
              Column              |  Type  | Collation | Nullable |   Default    | Storage  | Stats target | Description 
----------------------------------+--------+-----------+----------+--------------+----------+--------------+-------------
 id                               | text   |           | not null |              | extended |              | 
 name                             | text   |           |          |              | extended |              | 
 __user_colours_inverse | text[] |           | not null | '{}'::text[] | extended |              | 

I don't understand why __user_colours_inverse is created, while, when managing the relation, only the user.favouriteColourshas values.

Hello,

I was upgrading my fortune-postgres dependency from 1.6.5 to 1.6.9 using node 8.x LTS, but it cannot be upgraded:

error [email protected]: The engine "node" is incompatible with this module. Expected version ">=10".
error Found incompatible module
info Visit https://yarnpkg.com/en/docs/cli/add for documentation about this command.

Is node 10.x now necessary for running fortune-postgres?

Thanks!

When all connections in the pool are exhausted it appears to result in a deadlock situation. Connections are not returned to the pool and any subsequent requests timeout waiting for a connection. I have a working example with steps to reproduce.

This appears to be as a result of acquiring new connections during a transaction. According to the node-pg-pool docs, you should use the same client instance for all queries within a transaction. Currently fortune-postgres does not do this and calls pool.query() in multiple places rather than reusing the client instance acquired from pool.connect().

It seems that previously this was handled correctly and may have been lost with the refactor from this.client to this.pool. In beginTransaction I can see that client is being set in the scope but this is no longer used. I would say that we still need to retain and use this.client to refer to the current connection.

When making a post request, It won't auto increment the primary key. With these options below, it will try to insert a null value in the field instead.

options: {
    url: `postgres://${username}:${password}@${host}:${port}/${db}`,
    primaryKeyType: Number,
    generatePrimaryKey: null
}

This is what it says:
Unhandled rejection error: null value in column "id" violates not-null constraint

P.S. Happy New Year :)