kristiandupont / kanel Goto Github PK

Anything other than the 'connection' object in ',kanelrc.js' isn't so easy to understand.

Would be great if the docs could be expanded a bit. If nothing else, to cover options in the README

Hi!

Would it be possible to add a /* @generated */ comment on top of each generated file? This will help hide the generated files in PRs/diffs, etc.

Hi!

I've just tried 1.0.0 and it does exactly what I want for custom file and model naming - excellent work 😄 However, it seems like propertyCasing is gone, which is something I was relying on (I'm using slonik configured to auto convert property names to camel case). Is there a way for me to implement that same functionality somehow using 1.0.0?

git clone https://github.com/kristiandupont/kanel.git
cd kanel
npx tsc
./bin/kanel

/Users/jribakoff/kanel/build/cli.js:43
var chalk_1 = __importDefault(require("chalk"));
                              ^

Error [ERR_REQUIRE_ESM]: require() of ES Module /Users/jribakoff/kanel/node_modules/chalk/source/index.js from /Users/jribakoff/kanel/build/cli.js not supported.
Instead change the require of index.js in /Users/jribakoff/kanel/build/cli.js to a dynamic import() which is available in all CommonJS modules.
    at Object.<anonymous> (/Users/jribakoff/kanel/build/cli.js:43:31)
    at Object.<anonymous> (/Users/jribakoff/kanel/bin/kanel:2:1) {
  code: 'ERR_REQUIRE_ESM'
}

node -v
v16.13.2

Am I doing something wrong? Thanks in advance!

I set up my environment exactly the same as the example. After executing npx kanel nothing happens, and I don't get any errors or messages. My database has 73 tables, so I'm not sure if it's taking a while to process these or if my implementation is incorrect. However, the README was pretty straightforward so I don't think it's that.

edit: I was able to generate the models through the programmatic example. (I had to add the dot prefix on import) import config from '../database/.kanelrc';

I think the issue is on this line (obtained with cat -e -n src/generateInterface.js | head -n 55 | tail -n 10):

   51    const typeStr =$
   52      nullable && !considerDefaultValue ? `${rawType} |M-BM- null` : rawType;$

Any particular reason the null is not preceded by a regular space? Or was it just a typo?

I love that you print default value and index info in a docstring.

I noticed @slonik/typegen prints type information too.

This would probably be noise in many cases, like text for string, when there is one "obvious" mapping of the TS type to the PG type. But in on other cases, it might be nice to know, and have the info at your fingertips in your IDE as you develop. Eg;

interface Foo {
  /** 
   * Type: int 
   * Index: foo_bar
   */
  bar: number; 
}

What do you think?

The example I left in issue 4 seems to be because of a self reference. The table has a parentUuid that is a self reference so the generated file includes itself and creates a conflict with the ID.

Generated Example:

import { ConfigsId } from './configs';

import ConfigType from './config-type';

export type ConfigsId = number & { __flavor?: 'configs' };

export default interface Configs {
/**

Primary key. Index: configs_pkey
Index: idx_configs_on_config_UUID
*/
uuid: ConfigsId;
parentUuid: ConfigsId | null;

I have createdAt and an updatedAt columns which are identical in SQL, but the createdAt field is omitted from the initializer interface. This happens with multiple different tables.

interface Customers {
  /** Primary key. Index: customers_pkey */
  id: CustomersId;

  name: string;

  email: string | null;

  createdAt: Date;

  updatedAt: Date;
}

interface CustomersInitializer {
  /**
   * Default value: uuid_generate_v4()
   * Primary key. Index: customers_pkey
  */
  id?: CustomersId;

  name: string;

  email?: string;

  // createdAt is omitted.

  /** Default value: CURRENT_TIMESTAMP */
  updatedAt?: Date;
}

Not a huge problem since I dont ever want to set createdAt manually, but it seems like a bug

Hi, I've found your project via HN comment section

We've been using https://www.npmjs.com/package/schemats + https://sqorn.org/docs/about.html

But both projects are stagnant now 😔

Basically I'm looking for somePgNodeAdapter in this example, to complement your project

// GeneratedDb.Account is provided by kanel

const account = await somePgNodeAdapter<GeneratedDb.Account>
    .from(tables.accountTable)
    .insert({
      // These would get checked against GeneratedDb.Account
      firebaseUid,
      firstName: input.firstName,
      lastName: input.lastName,
      langCode: "en",
      email: input.email,
      createdAt: nowTimestamptzSq(),
      updatedAt: nowTimestamptzSq(),
    })
    .return("id") // These would also be checked against GeneratedDb.Account
    .one()

Are you using something like this?

Hi there!

Thanks for putting out this great library.

I got the following errors when generating types for my schema tonight.

Unrecognized type: 'text[]'
Unrecognized type: 'date[]'

My schema uses the postgres array type, ie
studyInstances date[] NOT NULL,.

Fortunately, I could resolve this error by adding the following to my kanelrc.js

  customTypeMap: {
    "text[]": "string[]",
    "date[]": "Date[]",
  },

I am just leaving this issue here for two reasons:

1. so other people with a similar problem can see how your excellent library provides a simple solution for issues like this, and
2. In case you weren't aware of this kind of issue, so you could address it if that is something you are interested in doing.

Sorry I don't have the time to submit a PR myself, and once again, thanks for saving me many hours by providing this code :)

When using the example command I'm receiving an error:

$ npx kanel -d "postgresql://localhost:5432/acme" -o ./src/models
# Invalid value for option 'database' - expected type string, received value: postgresql://localhost:5432/acme.

When passing connectionString to client using the .kanelrc.js:

require("dotenv").config();

/** @type {import('kanel').Config} */
module.exports = {
  connection: {
    connectionString: process.env.DATABASE_URL,
  },

  preDeleteModelFolder: true,
  outputPath: "./src/types",
};

Error: SASL: SCRAM-SERVER-FIRST-MESSAGE: client password must be a string
    at Object.continueSession (/Users/hex/repos/8aa8/webapp/node_modules/pg/lib/sasl.js:24:11)
    at Client._handleAuthSASLContinue (/Users/hex/repos/8aa8/webapp/node_modules/pg/lib/client.js:257:10)
    at Connection.emit (node:events:527:28)
    at /Users/hex/repos/8aa8/webapp/node_modules/pg/lib/connection.js:114:12
    at Parser.parse (/Users/hex/repos/8aa8/webapp/node_modules/pg-protocol/dist/parser.js:40:17)
    at Socket.<anonymous> (/Users/hex/repos/8aa8/webapp/node_modules/pg-protocol/dist/index.js:11:42)
    at Socket.emit (node:events:527:28)
    at addChunk (node:internal/streams/readable:315:12)
    at readableAddChunk (node:internal/streams/readable:289:9)
    at Socket.Readable.push (node:internal/streams/readable:228:10)

Postgraphile have this feature where it allows you to generate GraphQL enum types from each row in a specified table in the database schema.

Something similar for kanel would be nice. I haven't seen that this is possible, but maybe I missed it?

Is it possible also to add option, to extract types for postgres functions?
function name, parameters types and return types ?

Currently, the primary keys are expected to be called id. Other naming schemes (such as the one in the example!) should be supported.

I know it would complicate things by a lot, but in our project, we have some custom data from the client that cannot be simply mapped to atomic columns, so we keep them inside a single, jsonb column.

It would be awesome if this could walk such table and collect types from those and create a type for the entire column

Tried to run on a cockroach db instance and got

$ ./node_modules/.bin/kanel -c ./src/sql/configs/kanel.js
Kanel
Connecting to foo on localhost
 - Clearing old files in ./libs/foo/src/generated/sql
./node_modules/extract-pg-schema/node_modules/knex/lib/dialects/postgres/index.js:108
        resolver(/^PostgreSQL (.*?)( |$)/.exec(resp.rows[0].version)[1]);
                                                                    ^

TypeError: Cannot read property '1' of null
    at Query.<anonymous> (./node_modules/extract-pg-schema/node_modules/knex/lib/dialects/postgres/index.js:108:69)
    at Query.handleReadyForQuery (./node_modules/pg/lib/query.js:138:12)
    at Client._handleReadyForQuery (./node_modules/pg/lib/client.js:290:19)
    at Connection.emit (node:events:378:20)
    at ./node_modules/pg/lib/connection.js:114:12
    at Parser.parse (./node_modules/pg-protocol/dist/parser.js:40:17)
    at Socket.<anonymous> (./node_modules/pg-protocol/dist/index.js:11:42)
    at Socket.emit (node:events:378:20)
    at addChunk (node:internal/streams/readable:313:12)
    at readableAddChunk (node:internal/streams/readable:288:9)
error Command failed with exit code 1.

You can run an instance with docker run -it -p 26257:26257 -p 8080:8080 cockroachdb/cockroach start-single-node --insecure

Recently (as in, four days ago), knex released an option to automagically map table names to return types:

knex/knex#4071

In practice, this means that with an interface definition like this:

declare module 'knex/types/tables' {
  interface Tables {
    [Table.Profile]: IProfile
    [Table.FitnessPackage]: IFitnessPackage
  }
}

Knex can infer the returnType when doing knex(tableName)

await knex(Table.Profile).innerJoin(
    Table.FitnessPackage,
    `${Table.FitnessPackage}.id`,
    `${Table.WorkoutPlan}.fitness_package_id`,
  )

As seen in the snippet / screenshot, this supports joins as well.

Since kanel already knows the names of the tables and their corresponding types, I think this would be a very nice addition. WDYT?
I can look into providing a PR if you think this would be a good addition

Kanel properly generates enums for Postgres enum types

enum ListenerType {
  ethereum = 'ethereum',
};

But when using the generateIndexFile hook, it exports it as a type

export type { default as ListenerType } from './public/ListenerType';

So it can't be used like so:

ListenerType.ethereum
// 'ListenerType' cannot be used as a value because it was exported using 'export type'.

As the title says, if there is - in the column name, it doesn't wrap it in quotes.

Our build process needs to know what files are being generated, so a nice feature would be to combine all the types into a single types.ts file.

I'm not sure if related to #15 , but feel free to close this one if it is.

init-db.sql

CREATE SCHEMA test1;
CREATE SCHEMA test2;

CREATE TABLE test1.users (
    id integer DEFAULT nextval('test1.users_id_seq'::regclass) PRIMARY KEY
);

CREATE TABLE test2.user_managers (
    id integer DEFAULT nextval('test2.user_managers_id_seq'::regclass) PRIMARY KEY,
    user_id integer REFERENCES test1.users(id)
);

So basically just 2 schemas with the other one pointing to other one.

.kanelrc.js

const path = require("path");

module.exports = {
  // connection: {},
  filenameCasing: "dash",
  typeCasing: "pascal",
  preDeleteModelFolder: true,

  schemas: [
    {
      name: "test1",
      modelFolder: path.join(__dirname, "models", "test1"),
    },
    {
      name: "test2",
      modelFolder: path.join(__dirname, "models", "test2"),
    },
  ],
};

Output: ./models/test2/user-managers.ts

// Automatically generated. Don't change this file manually.

import { UsersId } from './users'; // <-- ERROR HERE! Refers to same folder, even though the UsersId is in "test1".

export type UserManagersId = number & { __flavor?: 'user_managers' };

Would love to see support for composite types. e.g.

CREATE TYPE inventory_item AS (
    name            text,
    supplier_id     integer,
    price           numeric
);

From a quick look at the source, looks like this would require adding support in extract-pg-schema first.

I may be able to spend time working on a PR for this for both projects if there is any interest.

Maybe this could be added as compositeTypes: [] in the extractSchema return value to prevent breaking changes?

Hi! Thx for a great lib!

I'm moving a project which has around 100 tables to typescript and it seems that I have only one error from kanel after initial types generation and little adjusting of customTypeMap. Let's consider following DB schema:

pageTiles.sql

-- auto-generated definition
create table "pageTiles" (
  "pageId"       uuid                                                                                     not null
    constraint "pageTiles_pageId_fkey"
      references pages
      on delete cascade,
  "tileId"       uuid                                                                                     not null,
  "tileType"     varchar(50)                                                                              not null,

  constraint "pageTiles_pkey"
    primary key ("pageId", "tileId", "tileType")
);

create unique index "pageTiles_tileId_key"
  on "pageTiles"("tileId");

componentFormTiles.sql

-- auto-generated definition
create table "componentFormTiles" (
  id          uuid                     default public.gen_random_uuid() not null
    constraint "componentFormTiles_pkey"
      primary key
    constraint "componentFormTiles_id_fkey"
      references "pageTiles"("tileId")
      on update cascade on delete cascade
      deferrable
);

pageTiles.ts

take a look at tileId

// Automatically generated. Don't change this file manually.

import { PagesId } from './pages';

export default interface PageTiles {
  /**
   * Index: pageTiles_pageId_tileId_key
   * Index: pageTiles_pageId_tileOrder_unique
   * Index: pageTiles_pageId_uri_unique
   * Primary key. Index: pageTiles_pkey
  */
  pageId: PagesId;

  /**
   * Index: pageTiles_pageId_tileId_key
   * Primary key. Index: pageTiles_pkey
   * Index: pageTiles_tileId_key
  */
  tileId: string;

  /** Primary key. Index: pageTiles_pkey */
  tileType: string;
}

componentFormTiles.ts

first import is not correct - there is no such export in pageTiles.ts

// Automatically generated. Don't change this file manually.

import { PageTilesId } from './pageTiles';   // <--- ERROR!
import { FormsId } from './forms';

export type ComponentFormTilesId = string & { __flavor?: 'componentFormTiles' };

export default interface ComponentFormTiles {
  /** Primary key. Index: componentFormTiles_pkey */
  id: ComponentFormTilesId;
}

Could you suggest any workaround?

Nullable fields in the initializer currently dont take null values, example

interface Customers {
  id: CustomersId;

  name: string;

  age: number | null;
}

interface CustomersInitializer {
  id?: CustomersId;

  name: string;

  age?: number; // should be `age?: number | null`
}

Some things make result little bit dirty and provide some functionality that can be unnecessary.

No way to disable:

• comments (indexes, defaults, primaries)
• index.ts at all
• extra type modelInitializer
• extra index types type ModelId, interface ModelIdTypeMap type Initializer
• strange IDs with __flavor export type ModelId = number & { " __flavor"?: 'model_history' };

I looked through the source and didn't see an easy way to change the case of the output interfaces. It appears to always use camel case. To have the types match the returned values from the database it would be useful to have an option to have the interface keys match the db column names so that a column created_at is not remapped to createdAt. Otherwise you can't type the database returned values to the generated interfaces.

Something like that:

https://www.npmjs.com/package/ts-morph

Thanks for a great project/tool!

Adding on to this issue #18 I've often found myself wanting to transform the generated file name.

What I'd like to do is really prepend both the generated file names and types with Db_, so Db_Users.ts -> type Db_Users, etc. This is all to help navigate my code bases which for various reasons tend to have a bunch of type representations for the same concept, but coming from/going to different sources (imagine I have a User type that has a representation in the DB I export through kanel, and then one for the User GraphQL type I return in my API, and then one for some other source, etc..).

What do you think about this idea? Does it make sense to you?

I know the technique may be called flavoring, but it feels weird to refer to a table class as a flavor – it's a table! or a model, or a "class", etc.
What do you think?

This issue lists Renovate updates and detected dependencies. Read the Dependency Dashboard docs to learn more.

Ignored or Blocked

These are blocked by an existing closed PR and will not be recreated unless you click a checkbox below.

• fix(deps): update dependency chalk to v5

Detected dependencies

• Check this box to trigger a request for Renovate to run again on this repository

Hi. First of all, great project. It has the exact scope and simplicity that I'm looking for.

I have one question/feature request though:
I would like to use kanal with kysely and as you can see in the kysely example, the following syntax is used for auto-generated columns like ids and dates (so they are automatically made optional in inserts and updates).

import { Generated} from 'kysely';
...
interface PersonTable {
  // Columns that are generated by the database should be marked
  // using the `Generated` type. This way they are automatically
  // made optional in inserts and updates.
  id: Generated<number>;
  ...
}

So, is is possible with kanel to format the property type, so I can do something like the following?:
Pseudo code:

propertyTypeNominator: (...) => {
  if id ...bla bla bla... return `Generated<number>`;
}

Thanks in advance.

Here, the primary key on bar references the primary key on foo

CREATE TABLE foo (id uuid primary key);
CREATE TABLE bar (id uuid primary key references foo(id));

Foo.ts

export type FooId = string & { __flavor?: 'foo' }; ;

export default interface Foo {
  /** Primary key. Index: foo_pkey */
  id: FooId;
}

export interface FooInitializer {
  /** Primary key. Index: foo_pkey */
  id: FooId;
}

export const tableName = 'foo'

Bar.ts

import { FooId } from './Foo';

export type BarId = string & { __flavor?: 'bar' };

export default interface Bar {
  /** Primary key. Index: bar_pkey */
  id: BarId;
}

export interface BarInitializer {
  /** Primary key. Index: bar_pkey */
  id: BarId;
}

Notice how FooId is imported, but is not actually used, which causes the typescript compiler to complain.

Firstly, awesome job and thanks for your work! Is there a way to adjust the Initializer type for models? I'd like to be able to make all of the values in the initializer type optional, while keeping the main model type as is.

Hi - firstly, thanks for a great library.

Issue

When using generated columns which are NOT NULL, those columns are required in the Initializer interface but should in some cases be omitted or made optional.

Details

I've recently started using IDENTITY columns rather than SEQUENCE or SERIAL for a number of reasons (see StackOverflow).

However when you use IDENTITY as below, the generated UsersInitializer requires the user_id column. Similarly, the (slightly contrived) full_name column, is also required.

In the case of user_id this column will always be generated as it is defined as GENERATED ALWAYS, and attempting to insert a value will result in an error. Given that it is always generated, it should probably be omitted from the interface. In the case of full_name, as it is defined as GENERATED BY DEFAULT, the column will be generated unless another value is provided and should probably be optional.

CREATE TABLE users (
    user_id integer NOT NULL GENERATED ALWAYS AS IDENTITY,
    first_name text NOT NULL,
    last_name text NOT NULL,
    email text NOT NULL,
    full_name text NOT NULL GENERATED BY DEFAULT AS (first_name || ' ' || last_name) STORED,
);

export interface UsersInitializer {
    /** Primary key. Index: pk_users */
    userId: UsersId;

    firstName: string;

    lastName: string;

    email: string;

    fullName: string;
}

Suggestion

The applicable postgres infoschema columns are below. If the generated column is an identity, identity_generation is set and is_generated is 'NULL', and vice versa.

    is_identity: 'YES' | 'NO',
    identity_generation: 'ALWAYS' | 'BY DEFAULT' | 'NULL' ,
    is_generated: 'ALWAYS' | 'BY DEFAULT' | 'NULL',
    generation_expression: string

I would suggest, in addition to defaultValue in generateProperty.ts, an additional property signifying a generated column is added, something like isGenerated that can be either be always or default. In the case of always, that column would be omitted from the Initializer, and in the case of default, that column would be optional. The result would be something like below:

CREATE TABLE users (
    user_id integer NOT NULL GENERATED ALWAYS AS IDENTITY,
    first_name text NOT NULL,
    last_name text NOT NULL,
    email text NOT NULL,
    full_name text NOT NULL GENERATED BY DEFAULT AS (first_name || ' ' || last_name) STORED,
);

export interface UsersInitializer {
    firstName: string;

    lastName: string;

    email: string;

    fullName?: string;
}

Thanks!

CREATE TABLE test (
  samples DOUBLE PRECISION[] NOT NULL,
  more_samples NUMERIC[] NOT NULL
);

Generated types

export default interface Test {
  more_samples: Numeric[]; // Cannot find name 'Numeric'. ts(2304)
  samples: Double precision[]; // syntax error
}

Quick fix; I've made kanel.d.ts where I've declared some of these types:

declare type Uuid = string;
declare type Interval = string;
declare type Numeric = number;
declare type Gender = "MALE" | "FEMALE" | "OTHER"; // enum type in my db

But with for example double precision, the type should be DoublePrecision or similar, because TypeScript doesn't allow a space in type name

Hi,
is it possible to generate simple id types like

export type PostId = number

instead of

export type PostId = number & { " __flavor"?: 'posts' };

I'm trying to use the kanel generated types with json schema validators and they don't like number & { " __flavor"?: 'posts' } very much.

Thanks in advance.

using the custom type map solved almost all the issues I had. For some reason either I can't figure out the correct key to use or there is still a problem with Macaddr, Int4, and Float8. Int4 and Float8 are aliases but Macaddr is a regular type and it worked for inet which seems strange.

Removed a bit of the info to make it easier to read but basically something like this was generated. Notice the line where it says type Initializer =

// @generated
// Automatically generated. Don't change this file manually.

import Account, { AccountId } from './Account';
import AccountAPIKey, { AccountAPIKeyId } from './AccountAPIKey';


type Model =
  | Account
  | AccountAPIKey

type Initializer =

interface InitializerTypeMap {
}

export type {
  Account, AccountId,
  AccountAPIKey, AccountAPIKeyId,

  Initializer,
  InitializerTypeMap
};

the knex db connection is not exposed or closed, so using kanel programmatically results in a leaked connection and a hang on exit

Cool, project. Ran it against a schema I had and saw two issues both seem to be because of assumptions about postgres types.

The tables that used uuid for id do not seem to create Id values that are exported correctly.

Example:

import { ConfigsId } from './configs';

import DeviceType from './device-type';
import ConfigType from './config-type';

export type ConfigsId = number & { __flavor?: 'configs' };

export default interface Configs {
/**

• Primary key. Index: configs_pkey
• Index: idx_configs_on_config_UUID
  */
  uuid: ConfigsId;

Also ran into some postgres type issues. Types like Uuid, Macaddr, Geography, etc are extracted but they are not defined. Since Geography is from an extension it probably makes sense to add configuration that allows types to be defined and included into the generated files to handle the more unusual postgres types.

Right now I'm using modelHooks to replace the types of some of the generated columns. For example, providing more specific types for JSON columns.

Right now I have to loop through all lines and find the ones matching the properties, to replace the type. I'm thinking this could be more smooth if kanel provided an option to override the types on a per property basis - We could call it propertyHooks

WDYT?

Hello,
congrats on releasing v3!

Would you consider bring this functionality back?
Perhaps pre/post hooks can be used to generate index file.
But generating it by adding to config some option like generateIndexFile would be great! :)

Thanks.

Hi,

It seems kanel is very similar to @rmp135/sql-ts which seems to be over 2 years older. What was the motivation for creating kanel / what are the key differences?

Hey mate,

I noticed in https://github.com/kristiandupont/kanel/blob/master/src/defaultTypeMap.ts that int8 is mapped to a javascript number. However, I believe the javascript number type cannot support as large integer values as bigint/int8. If I am correct, it might be safer to map that type to string.

Is it possible to specify different types for reading and writing in customTypeMap?

When I read a timestamp field it always returns a string, but when I write to it it should allow both Date and string.

Hello,
Partitions duplicates the same types.
In my case I have close to 1000 extra files : /

There is an error with this repository's Renovate configuration that needs to be fixed. As a precaution, Renovate will stop PRs until it is resolved.

Error type: undefined. Note: this is a nested preset so please contact the preset author if you are unable to fix it yourself.

Hi @kristiandupont , i use the citext extension in my DB and have text column with citext as the data type. can this be included in the type conversion to string alongside Varchar,text, uuid.
Thanks

It's a common convention for database tables to have plural names, eg. "users", so Kanel will generate a "Users" type for the table, which goes against TS conventions. Id ask for a config options to address exactly this, but converting a word for plural to singular isn't as simple removing an s, so a lower level transform function should be simple and appease many more use cases.