ariga / entimport Goto Github PK

Hello, Thanks for creating this tool.

For team with DBA, these tool seems to be of great help.

I have the following schema and I'm running into a problem.

create table outbox
(
    id             bigint auto_increment
        primary key,
    aggregate_type varchar(255) not null,
    aggregate_id   varchar(255) not null,
    type           varchar(255) not null,
    payload        json         not null
);

when I tried

go run ariga.io/entimport/cmd/entimport -dialect mysql -dsn "root:root@tcp(localhost:3306)/db"

exit status 1 with

2021/10/26 10:46:12 entimport: schema writing failed - schemast: unsupported type TypeJSON

Am I using it wrong? Or is it a bug?

Mysql: mysql 8
go: 1.17.0
entimport: v0.0.0-20211024085919-20c40b8b2009

In my DB I have a few schemas and when I run:

go run ariga.io/entimport/cmd/entimport -dsn "postgres://user:password@localhost:5432/db_name?sslmode=disable"

it generates Ent schemas only for the public schema. I know that I can specify search_path=<schema_name> in the DSN query.
But again, I have tables with the same names but inside different schemas. How did it suppose to work?

Both mysql and mariadb can accept a precision for current_timestamp

mysql: https://dev.mysql.com/doc/refman/8.0/en/timestamp-initialization.html
mariadb: https://mariadb.com/kb/en/current_timestamp/

So the line case "default_generated on update current_timestamp", "on update current_timestamp": in extraAttr() function may need to be adjusted.

Schema:

create table test
(
    id            bigint unsigned auto_increment
        primary key,
    expires_at    timestamp default current_timestamp() not null on update current_timestamp()
)
    collate = utf8mb4_unicode_ci;

I would send a PR but I cannot find the code in https://github.com/ariga/atlas

entimport should include newlines in generated output

Current roadmap for entimport.
This is an open discussions, the list may be changed.
Contributions to the task content and task themselves are welcome.

• Add support for special types:
  • JSON
  • postgres.NetworkType
  • mysql.Set type
  • postgres.BitType
  • SpatialType
  • postgres.CurrencyType
  • postgres.XMLType
  • postgres.ArrayType
  • postgres.UserDefinedType
  • UUID.
• Support table renaming: schema name prefix or custom prefix. See: #29 and #30
• Add support for non go driver MySQL DSNs in the mux.
• Index support (currently Unique index is supported).
• Support for editing schema both manually and automatically (real upsert and not only overwrite)
• Export API
• Add indentations (pretty print). See #34

Error | missing PostgreSQL fields inet:

2022/02/16 12:18:41 entimport: schema import failed - entimport: unsupported type &{<nil> "inet" '\x00'}
exit status 1

So I tried generating my ent schema using entimport and everything seemed to work just fine until I noticed that my Join table wasn't in the folder.
I ran this command trying to generate the table:

go run ariga.io/entimport/cmd/entimport -tables friendship  -dsn "<mydsn>"

but I still got an error telling me that schema import failed:

entimport: schema import failed - entimport: join tables must be inspected with ref tables - append 
`tables` flag

I've tried the append flag but I can't seem to find it in docs

see documents on ent site sql.DB Integration
we can set db options in the DB() in normal code.but in the generated code, we could not access the DB object.
so if it is possible to add some functional option to set SetMaxIdleConns SetMaxOpenConns SetConnMaxLifetime

I think citext is such a common extension that it is worth supporting it out of the box. What do you think?

go run ariga.io/entimport/cmd/entimport -dsn 'postgresql://postgres:postgres@localhost:5432/postgres?sslmode=disable'
2022/03/23 10:22:11 entimport: schema import failed - entimport: unsupported type &{<nil> "citext"}
exit status 1

When I generate the fields for a MySQL table with DECIMAL columns (DECIMAL(19, 6) in this case), I'm getting fields like this:

field.Float64("cost")

However, this leads to a compilation error, because the method name is actually Float, not Float64. Right now I have to change it by hand to:

field.Float("cost")

While following the tutorial from this https://entgo.io/blog/2021/10/11/generating-ent-schemas-from-existing-sql-databases
got this error.

❯ go run ariga.io/entimport/cmd/entimport -dialect mysql -dsn "root:usertcp(mysql:3306)/test"
# ariga.io/atlas/sql/schema
../../../go/pkg/mod/ariga.io/[email protected]/sql/schema/dsl.go:754:17: type parameter requires go1.18 or later (-lang was set to go1.16; check go.mod)
../../../go/pkg/mod/ariga.io/[email protected]/sql/schema/inspect.go:32:58: predeclared any requires go1.18 or later (-lang was set to go1.16; check go.mod)
../../../go/pkg/mod/ariga.io/[email protected]/sql/schema/inspect.go:33:57: predeclared any requires go1.18 or later (-lang was set to go1.16; check go.mod)
# github.com/hashicorp/hcl/v2
../../../go/pkg/mod/github.com/hashicorp/hcl/[email protected]/diagnostic_typeparams.go:22:22: type parameter requires go1.18 or later (-lang was set to go1.16; check go.mod)
../../../go/pkg/mod/github.com/hashicorp/hcl/[email protected]/diagnostic_typeparams.go:22:24: predeclared any requires go1.18 or later (-lang was set to go1.16; check go.mod)

go version go1.19.5 darwin/arm64

Actual
A DB has a clients table that collides with ent.Client and a subsequent change to the ent.Schema is necessary to fix the error.

Suggestion
When importing an existing database with entimport, there should be a prefix (or suffix) for the generated methods.
In the configuration for entimport there should be an option to specify the prefix.

Example
prefix: legacy
table: clients
Result: LegacyClient

go run ariga.io/entimport/cmd/entimport -dsn "mysql://root:pass@tcp(192.168.10.153:3306)/zzz" -tables "xxx"^C

Same command do generate schema.go file properly in linux

What does the above error mean? The table I try to import has a primary key, not partioned.

create table artikel
(
    `Artikel-Id` int auto_increment primary key,
    requires_internal_protocol varchar(255) null
)
    collate = latin1_german2_ci;

Storage engine InnoDB with MySQL 5.7

When trying to run entimport on a shopware 6 mysql database, I'm met with the following error:

schema import failed - mysql: unknown attribute "STORED GENERATED"

I have never encountered a "STORED GENERATED" type before.
Searching, I found:
https://dev.mysql.com/doc/refman/8.0/en/create-table-generated-columns.html

They seem to be some kind of virtual field. Or something that extracts field of a json document.

Shopware version 6.5.8.6

I can do a database dump, if you need it, it's 235 tables or 290k.

grep STORED sw6-20240305.sql 
  `document_number` varchar(255) COLLATE utf8mb4_unicode_ci GENERATED ALWAYS AS (json_unquote(json_extract(`config`,_utf8mb4'$.documentNumber'))) STORED,
  `order_date` date GENERATED ALWAYS AS (cast(`order_date_time` as date)) STORED,
  `reversed` varchar(500) COLLATE utf8mb4_unicode_ci GENERATED ALWAYS AS (reverse(`keyword`)) STORED,
  `referenced_id` binary(16) GENERATED ALWAYS AS (coalesce(unhex(json_unquote(json_extract(`entity_id`,_utf8mb4'$.id'))),0x00)) STORED,
  `referenced_version_id` binary(16) GENERATED ALWAYS AS (coalesce(unhex(json_unquote(json_extract(`entity_id`,_utf8mb4'$.version_id'))),0x00)) STORED,

grep GENERATED sw6-20240305.sql
  `document_number` varchar(255) COLLATE utf8mb4_unicode_ci GENERATED ALWAYS AS (json_unquote(json_extract(`config`,_utf8mb4'$.documentNumber'))) STORED,
  `order_date` date GENERATED ALWAYS AS (cast(`order_date_time` as date)) STORED,
  `amount_total` double GENERATED ALWAYS AS (json_unquote(json_extract(`price`,_utf8mb4'$.totalPrice'))) VIRTUAL,
  `amount_net` double GENERATED ALWAYS AS (json_unquote(json_extract(`price`,_utf8mb4'$.netPrice'))) VIRTUAL,
  `position_price` double GENERATED ALWAYS AS (json_unquote(json_extract(`price`,_utf8mb4'$.positionPrice'))) VIRTUAL,
  `tax_status` varchar(255) COLLATE utf8mb4_unicode_ci GENERATED ALWAYS AS (json_unquote(json_extract(`price`,_utf8mb4'$.taxStatus'))) VIRTUAL,
  `shipping_total` double GENERATED ALWAYS AS (json_unquote(json_extract(`shipping_costs`,_utf8mb4'$.totalPrice'))) VIRTUAL,
  `total_price` int GENERATED ALWAYS AS (json_unquote(json_extract(`price`,_utf8mb4'$.totalPrice'))) VIRTUAL,
  `unit_price` int GENERATED ALWAYS AS (json_unquote(json_extract(`price`,_utf8mb4'$.unitPrice'))) VIRTUAL,
  `quantity` int GENERATED ALWAYS AS (json_unquote(json_extract(`price`,_utf8mb4'$.quantity'))) VIRTUAL,
  `unit_price` double GENERATED ALWAYS AS (json_unquote(json_extract(`price`,_utf8mb4'$.unitPrice'))) VIRTUAL,
  `total_price` double GENERATED ALWAYS AS (json_unquote(json_extract(`price`,_utf8mb4'$.totalPrice'))) VIRTUAL,
  `reversed` varchar(500) COLLATE utf8mb4_unicode_ci GENERATED ALWAYS AS (reverse(`keyword`)) STORED,
  `referenced_id` binary(16) GENERATED ALWAYS AS (coalesce(unhex(json_unquote(json_extract(`entity_id`,_utf8mb4'$.id'))),0x00)) STORED,
  `referenced_version_id` binary(16) GENERATED ALWAYS AS (coalesce(unhex(json_unquote(json_extract(`entity_id`,_utf8mb4'$.version_id'))),0x00)) STORED,

https://github.com/ent/ent/tree/master/examples/o2mrecur

1. Created a NODE table in MySQL.
   `// Fields of the Node.
   func (Node) Fields() []ent.Field {
   return []ent.Field{
   field.Int("value"),
   }
   }

// Edges of the Node.
func (Node) Edges() []ent.Edge {
return []ent.Edge{
edge.To("children", Node.Type).
From("parent").
Unique(),
}
}`

2. Generating Ent Schemas
   go run ariga.io/entimport/cmd/entimport -dialect mysql -dsn

`func (Node) Fields() []ent.Field {
return []ent.Field{field.Int("id"), field.Int("value"), field.

	// Edges of the Node.
	Int("node_children").Optional()}

}

func (Node) Edges() []ent.Edge {
return []ent.Edge{edge.To("child_nodes", Node.Type), edge.From("parent_node", Node.Type).Unique().Field("node_children")}

}`

go generate ./ent

entc/gen: missing reference name for inverse edge: Node.parent_node

entimport fails, if MySQL set type is used.

entimport: schema import failed - entimport: unsupported type &{<nil> ["user" "system"]}

DDL

CREATE TABLE `ftp_user` (
  `ftp_user_id` int unsigned NOT NULL AUTO_INCREMENT,
  
  .... more columns
  
  `user_type` set('user','system') NOT NULL DEFAULT 'user',
  PRIMARY KEY (`ftp_user_id`),
) DEFAULT CHARSET=utf8mb3;

Error:

entimport: invalid primary key - single part key must be present

i have a problem with the schema i am trying to import with entimport
there is a column which has name "id"
when i change it's name it successfully generates schema but when the column name is "id" then it gives panic error

panic: runtime error: invalid memory address or nil pointer dereference
[signal SIGSEGV: segmentation violation code=0x1 addr=0x18 pc=0xbecc2c]

then i debug ran the command in debug mode and found the problem on this file in line number 300
whenever id column comes the fields map have no key named "id" that's why it gives nil pointer exception

hope you find the solution for this problem because every table in my database have id column which is a primary key.

I believe entimport does not currently match the field.UUID API which accepts a second argument.

I just got a field generated as:
field.UUID("fooer_id").Optional(uuid.UUID{})

where I believe the appropriate syntax is
field.UUID("fooer_id", uuid.UUID{}).Optional()

ent generates an error trying to parse the generated schema from the ddl below. The provided error is:
"mismatch field type between edge field "username" and id of type "User" (string != int32)"

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `token` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`),
  UNIQUE KEY `token` (`token`)
) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8;

CREATE TABLE `programme_uploads` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `upload_time` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `username` (`username`),
  KEY `upload_time_index` (`upload_time`),
  CONSTRAINT `programme_uploads_ibfk_1` FOREIGN KEY (`username`) REFERENCES `users` (`username`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8;

I think it might be missing the foreign key reference in the generation?

I'm having an issue generating the schema from my existing postgres database. Ive been followign this post - https://entgo.io/blog/2021/10/11/generating-ent-schemas-from-existing-sql-databases/

When i execgo generate ./ent i get the following error:

entc/load: load schema dir: /home/kay/powerlevel/backend/ent/schema/authentication.go:19:276: not enough arguments in call to field.UUID
have (string)
want (string, driver.Valuer)
exit status 1
ent/generate.go:3: running "go": exit status 1

database

-- CreateTable
CREATE TABLE "users" (
    "id" UUID NOT NULL DEFAULT gen_random_uuid(),
    "email" TEXT NOT NULL,
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP(3),
    "deleted_at" TIMESTAMP(3),

    CONSTRAINT "users_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "authentication" (
    "id" UUID NOT NULL DEFAULT gen_random_uuid(),
    "type" TEXT NOT NULL,
    "service_id" TEXT NOT NULL,
    "session_id" TEXT NOT NULL,
    "password" TEXT NOT NULL,
    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP(3),
    "deleted_at" TIMESTAMP(3),
    "user_id" UUID NOT NULL,

    CONSTRAINT "authentication_pkey" PRIMARY KEY ("id")
);

-- AddForeignKey
ALTER TABLE "authentication" ADD CONSTRAINT "authentication_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

I exec the following to get the code generated - go run ariga.io/entimport/cmd/entimport -dsn "postgres://root:password@localhost:5432/powerlevel?sslmode=disable"

ent/schema/authentication.go

// Code generated by entimport, DO NOT EDIT.

package schema

import (
	"entgo.io/ent"
	"entgo.io/ent/dialect/entsql"
	"entgo.io/ent/schema"
	"entgo.io/ent/schema/edge"
	"entgo.io/ent/schema/field"
	"github.com/google/uuid"
)

type Authentication struct {
	ent.Schema
}

func (Authentication) Fields() []ent.Field {
	return []ent.Field{field.UUID("id", uuid.UUID{}), field.String("type"), field.String("service_id"), field.String("session_id"), field.String("password"), field.Time("created_at"), field.Time("updated_at").Optional(), field.Time("deleted_at").Optional(), field.UUID("user_id").Optional(uuid.UUID{})}
}
func (Authentication) Edges() []ent.Edge {
	return []ent.Edge{edge.From("user", User.Type).Ref("authentications").Unique().Field("user_id")}
}
func (Authentication) Annotations() []schema.Annotation {
	return []schema.Annotation{entsql.Annotation{Table: "authentication"}}
}

ent/schema/user.go

// Code generated by entimport, DO NOT EDIT.

package schema

import (
	"entgo.io/ent"
	"entgo.io/ent/schema"
	"entgo.io/ent/schema/edge"
	"entgo.io/ent/schema/field"
	"github.com/google/uuid"
)

type User struct {
	ent.Schema
}

func (User) Fields() []ent.Field {
	return []ent.Field{field.UUID("id", uuid.UUID{}), field.String("email"), field.Time("created_at"), field.Time("updated_at").Optional(), field.Time("deleted_at").Optional()}
}
func (User) Edges() []ent.Edge {
	return []ent.Edge{edge.To("authentications", Authentication.Type)}
}
func (User) Annotations() []schema.Annotation {
	return nil
}

This is my first time using entgo, am i doing something wrong?

go version go1.18.4 linux/amd64
ariga.io/entimport v0.0.0-20220722070026-e5b57d96ab7c // indirect
entgo.io/ent v0.11.1 // indirect

Just playing around with it, it seems like in ent/schema/authentication.go this field.UUID("user_id").Optional(uuid.UUID{}) should have been field.UUID("user_id", uuid.UUID{}).Optional()? After editing that change and then attempting to generate, it does generate without errors. But it seems like i should not be editing these generated files. Although i dont think this manual change has worked properly to setup the edges. as the following statement brings back no authentication data despite it being the db. users, err := services.EntClient.User.Query().WithAuthentications().All(services.EntContext)

I create a table with a bigint column,but when generated scheme using entimport struct field is int ,should it be int64 not int?

for mysql database column field, some filed type import by entimport is wrong, such as :
mysql go ent correct type
bigint int int64
decimal float64 float

Postgres varchar[] and text[] are not introspected correctly.

Did this get a blog post in the Ent web site ?

the reason I raise this is because
when I discuss ent with long time devs they hate that ent is not database first in terms of the source of the schema.

Attempting to import from a postgres 13 database with this command:

go run ariga.io/entimport/cmd/entimport -dialect postgres -dsn "host=localhost port=5432 user=postgres dbname=testdb password=tesdbPass sslmode=disable" -tables "services"

It crashes with the error:
entimport: schema import failed - postgres: querying schema tables: pq: argument of AND must be type boolean, not type information_schema.sql_identifier

If I do not include the -tables param, it crashes with:
schema import failed - entimport: invalid primary key - single part key must be present

I am new in ent and entimport.

I used to use gen and gorm as orm.

I find that entimport will generate wrong import package in schema file. It uses gorm.io/gen/field instead of subpackage of ent:

Then I tried to remove gen package (using go get gorm.io/gen@none and go clean -cache -modcache) and re-generated code, everything goes well:

And after I get gen again in my another project which need it, entimport will generate wrong import again.

It really bothers me for I am maintain some proj. using gen now, I need both of gen and ent work well.