8 ms·
Postgres to TypeScript Interfaces and Enums
- PhilipTrauner 5y agoQuoting the issue that the creator opened on the original repo (https://github.com/SweetIQ/schemats/issues/127 https://github.com/SweetIQ/schemats/issues/127): > I figured having one that upgrades all the dependencies and uses some of the newer JS features would be a nice win. Is this "fork" also going to address all the other Postgres-related issues of the original?
- yasserf 5y agoSo far I have addressed the issues related to using it with node 14, package dependency issues and getting it inline with modern typescript (removing concept of namespaces and favouring Pick/Omit helpers instead). That being said given how lightweight the actual package is I would be more than happy to address those issues when raised.
- kristiandupont 5y agoI am making a competitor to this and I am really happy that the approach seems to be gaining traction. Having the database as the source of truth and migrating it with SQL feels very right for me. It does mean that architectural decisions start in the database as well, which I am trying to govern with "schema linting" (https://github.com/kristiandupont/schemalint https://github.com/kristiandupont/schemalint). If you are using Postgres, I would love help with building this.
- yasserf 5y agoThats very cool! I'm also massively in favour of having the db as the source of truth. When I first started looking into building my own SaaS I actually tried to go down the database first design, where most of your functionality runs within postgres triggers. But it wasn't near as easy to do things and writing SQL logic is much harder to test. Figured the next best thing is to just tie the database in extremely closely using typescript into the backend (I rarely define custom types, always try to use Pick/Omit/Partial combinations all the way to the client SDKs). I ended building generating forms based on the meta data in postgres (select fields know the values from enums and multiselect from enum arrays), so if you define a table in postgres, you just specify which fields you want to render (out of the available columns) and in what order and it pretty much gives you a UI form that is directly rendered in react. Works really well when you have a form component library at your disposal.
- random_kris 5y agoYou have any of this on github?
- yasserf 5y agoThe form building part I don't no, I built it for a client that has a few dozen forms with hundreds of fields / options which is hard to keep up with. So we settled on using a csv file that is essentially a products owner way of saying, i want this field on this entity of this type. For example: entity,field,type,constraints delivery,arrived_at,Date,{ before: 0 } And in the form: form,field_1,field_2,... delivery_details,left_at,arrived_at,... Our CI then checks that the field actually exists in the typescript schema, and if not the build fails on our development environment (and suggests the basic SQL to fix it). The company is small enough for us to be able copy the sql into a migration script and let it run without skipping a beat. It's not a magical pill, renaming, deleting and custom migrations need to be dealt with manually. But for the most part 80%_of our changes are additions and this way it just works.
- yasserf 5y agoI am looking to release the setup I use for deployment but tbh it isn't tested (via tests, since I rely on typescript for most of my data shuttling) and there's alot to it so need to write alot of documentation which i don't have time for. But, the jist is, we APIs like this (this can be improved with less generic json schemas): export const updateSamf: APIFunction<UpdateSamf, void> = async ({ database }, { samfId, ...data }) => { if (Object.keys(data).length === 0) { throw new InvalidParametersError('No fields to update') } if (data.title && data.title.length < 3) { throw new InvalidParametersError('Title needs to be at least 3 characters') } if (data.tags) { data.tags = data.tags.map((t) => t.toLowerCase()) } await database.crudUpdate<Samf>('samf', data, { samfId }, new SamfNotFoundError()) if (data.cover && previousCover) { await content.delete(previousCover) } } export const routes: APIRoutes = [{ type: 'patch', route: StudioRoutePath.SAMF_CRUD, func: updateSamf, schema: 'UpdateSamf', permissions: isSamfOwner }]
- PhilipTrauner 5y agoThere's a similar project (https://github.com/erik/squabble https://github.com/erik/squabble), that uses a portable version of Postgres' SQL parser to spit out an AST, which lints can consequently traverse. This approach alleviates the need for bringing up a Postgres cluster to lint the schema. I'd be curious to know if there's something to be gained by depending on a running database instance (especially because it complicates CI).
- yasserf 5y agoThat's an interesting approach. I usually run a postgres db (as a docker image on circleCI) when I'm running the build. I'm not sure how scalable it is (currently we have a hundred migrations and it's a few seconds so not bad). The flow is: - run migrations on an empty DB - create the interfaces and enums - run typescript - derive all the json schemas from the interfaces for API validation This means we gitignore the actual DB Types which is great in order to reduce the amount of sources of truth and deal with possible issues This all runs as a pre-commit hook as well. I guess by doing so we sort of validate the SQL is legit via postgres itself. The only issue we face after a commit gets passed the setup stage and tries to run the migration itself is if theres a data* inconsistency we weren't aware of (trying an enum value we thought wasn't used but was). But that sort of things requires special tasks anyways and the migration fails so nothing ever goes wierd (which is why I <3 SQL migrations )
- PhilipTrauner 5y ago> This all runs as a pre-commit hook as well. Keep in mind that this might become problematic for folks that are running Postgres inside Docker on macOS or Windows, as IO performance is quite poor. I put together a up / down migration validation system a while ago (start up two databases, apply all-1 up migrations on A and all up migrations + final down migration on B) for a pretty sizable schema. Folks that were using Docker had to wait upwards of two minutes, while natively installed Postgres would finish in under 10sec.
- yasserf 5y ago
- brailsafe 5y agoWhat exactly do you mean by competitor? Is there a reason you'd want more people using your open source project than another?
- taylorlapeyre 5y agoWe are using Kanel[1] for this right now — is there an advantage to using this instead? [1]: https://github.com/kristiandupont/kanel https://github.com/kristiandupont/kanel
- yasserf 5y agoIt looks like Kanel is actually alot more feature rich, I wouldn't see any advantage of switching. I started using schemats because it's a single CLI interface and gives me all I need. I put together a few tools for a typescript heavy backend development experience and one of my requirements was that each one would be powerful enough to achieve my goals + not powerful enough to trip on how to use it (or in schemats case, patch it). Currently schemats and ts-json-schema-generator got me there, only issue was schemats was a few years out of date so the types weren't as friendly.
- jbaudanza 5y agoI really like the idea of building TypeScript off of SQL types. But my ideal tool would be able to infer types from queries, not schemas. For example.. if I did: await db.query("SELECT user_id, COUNT(*) AS count FROM comments GROUP BY user_id"); I would like a TypeScript object that looks like: Array<{ user_id: number, count: number }>
- febrilian 5y agodoesn't it already exist? but it converts from your json https://quicktype.io/ https://quicktype.io/
- morelisp 5y agoThese barely work. Even their example shows it generating `type : string[]` but `weaknesses : Weakness[]`. You would need to start from a schema definition (JSON Schema, SQL CREATE TABLE, etc.) to make the result any good.
- yasserf 5y agoYeah the good thing about using such a low level tool is that everything remains typed until you reach the query statement. Postgres and typescript enums work great together. For one of my projects I check in the generated types and replace 'unknown' (from jsonb) with the specific object manually. That way the concept of any or unknown gets pushed mostly out of the codebase (for simple crud. When joining we use normal SQL queries with some helper tools that verify the fields we are picking exist). Hopefully will manage to figure out how to do with with postgres comments at somepoint.
- yasserf 5y agoYup! I actually use that in my own node-postgres api. So the code itself with typescript is here: https://github.com/vramework/vramework/blob/main/backend-common/src/services/database/database-postgres-client.ts#L27 https://github.com/vramework/vramework/blob/main/backend-com... But the general gist is we have generic crud operators that are away of what the table are and based on that know the types of what we are returned. const {viewId } = await database.crudInsert<UserJournal>( 'user_journal', { viewId, userId, srcOgg: src[0], sprite: JSON.stringify(sprite), duration }, ['viewId'] ) await database.crudUpdate<UserJournal>('user_journal', { srcOgg: src[0], sprite: JSON.stringify(sprite), duration }, { viewId }) const { srcOgg, sprite, duration } = await database.crudGet<UserJournal>( 'user_journal', ['srcOgg', 'sprite', 'duration'], { viewId, userId }, new UserJournalNotFoundError() )
- peter_neumark 5y agoI’ve used vramework, and it’s an incredible tool for rapid development. Really good stuff!
- yasserf 5y agoThanks Peter!
- yashap 5y agoSeems like it generates TS types, but doesn’t help with data access? i.e. not trying to compete with tools that generate full data access layers, like Prisma https://www.prisma.io/ https://www.prisma.io/ ? If so, out of curiosity, what’s the use case for generating just the types? How are you actually getting data to/from the DB?
- yasserf 5y agoI think the comment I made above in in regards to our db crud updates might help answer that! But short answer is we just use node-postgres. If the get/update/delete/insert is a simple fields/table/where we use a helper function. If it's more complex we write custom SQL and use the following approach: database.query<DB.User & DB.Pet>(` SELECT * FROM "pet" LEFT JOIN user on pet.owner = user.id `)
- yashap 5y agoAh cool, makes sense :)
- stadium 5y agoThis is really interesting. What are the tradeoffs of generating the unique ID uuid's from the database vs from the code? Compared to ORM's, is one of the main differences that the db objects define the types and constraints and those propagate out to the code? And ORM's start with code and propagate out to the DB?
- yasserf 5y agoHonestly I don't know what the tradeoffs are . I usually rely on the db to create the id (regardless if its SERIAL / UUID). The only time I get create a UUID outside is if the client needs a UUID in its initial state. For example if tracking a users progress through a form it allows you to skip a useless creation roundtrip in order to perform the first update). Yeah thats pretty much it! Less code in the functions -> less inconsistencies with the db. And for example removing an enum in the db would fail all your front end components using it, so it's really across the whole pipeline.
- tengbretson 5y agoIve been dreaming up a TS SQL library that would use the new template literal features to implement a full postgres SQL parser in the type system. I imagine this would allow you to define a series of db migration statements (alter table, add column, etc) and the net result of that series of migrations would be rolled up into a schema definition as a type. Then you could take this schema type and feed it into some function as a generic to generate a raw query executor that could again fully parse the SQL and perfectly infer the result type. I believe all this is possible but I don't really know where to start.
- lukeramsden 5y agoMaybe this may help as some inspiration https://github.com/codemix/ts-sql https://github.com/codemix/ts-sql
- phpnode 5y agoAuthor of ts-sql here. Shin Ando has forked my proof of concept / toy implementation and made it much more practical, if you're thinking about spending time on this idea that's probably a better starting point: https://github.com/andoshin11/sqlpture https://github.com/andoshin11/sqlpture
- nawgz 5y agoHasura / GraphQL / GraphQL-codegen does pretty much exactly what you describe wrt fully typed query results, except you type graphql queries rather than sql ones
- seer 5y agoWe’ve been using pg-typed to give us almost the exact thing you are talking about. While it does have its limitations, its vastly better than raw sql queries. https://pgtyped.vercel.app/ https://pgtyped.vercel.app/
- furstenheim 5y agoIt looks interesting, but how does it behave with dynamically created queries?
- danvk 5y agoThere are many forks of schemats out there that add various features. Here’s mine: https://github.com/danvk/pg-to-ts https://github.com/danvk/pg-to-ts It copies Postgres comments over to JSDoc/TSDoc comments, emits some data about foreign key relationships and supports TS types for json/jsonb columns via @type comments. Feel free to copy any of those feature if you think they’re good ideas :)
- yasserf 5y agoThis is awesome! the json types part is perfect. If I found your fork earlier I would have just used that as its what I need for jsonb. The dates solution I fixed in a rest API wrapper on the client side. It's not great but it hides away the date serialisation issues https://github.com/vramework/vramework/blob/243db4830d22c5378326a725aa703903fd14cda2/generic/src/rest.ts#L29 https://github.com/vramework/vramework/blob/243db4830d22c537... Is it performant? Not really. But it stopped us from having to deal with the string issue which worked great.
- krzkaczor 5y agoIt seems like it only generates types for entities. My dream library would be basically a generated client that looks like knex or objection but with full type-safety and mapping. I don't like prisma that much as it takes control/power from the developer. But it can be great if you don't know SQL.
- topicseed 5y agoCheck out zapatos[0]. Codegen tool that generates interfaces and type-safe queries from a running schema (db). I've tried them all and this one allows me the most flexibility without abstracting much. [0] https://github.com/jawj/zapatos https://github.com/jawj/zapatos
- edem 5y agoHow does this relate to Prisma? https://www.prisma.io/ https://www.prisma.io/ Do any of you have some experience with it? Also, I think it would be even better if there was a library like JOOQ for Java: https://www.jooq.org/ https://www.jooq.org/ It not only generates your entity classes, but also gives a full-blown type-safe query DSL!
- random_savv 5y agoOne alternative approach: one could write SQL queries to generate the interfaces for a set of tables (or views), and customize those to your codebase/needs.
- iddan 5y agoPrisma is a really mature project that does just and much more https://www.prisma.io/docs/concepts/components/introspection https://www.prisma.io/docs/concepts/components/introspection
- brycelarkin 5y agoI think Prisma requires you to use their client to get the typing benefits, while this package just generates typings.
- EarthLaunch 5y agoPrisma is not mature, they keep putting major breaking changes in patch (not even minor) semvar updates. They're the only package I've had to lock patch version on. They just underwent an obvious coder restructuring/hiring after recent funding, which has caused some of the chaos. Migrations are only recently out of alpha, don't support some common operations (rename?), and DOWN migrations are not supported. Prisma is innovative, and there may be some fair tradeoffs to their declarative approach and fast pace. But I've never used a less mature ORM.
- topicseed 5y agoHow does it compare to zapatos?
- sa46 5y agoI wrote a similar tool for Go and Postgres but it also generates the serialization code. [1] - I prefer generating code from queries instead of generating active record style models based on tables. - I think it’s worth it to spin up Postgres to get type info instead of trying to parse queries. Reimplementing Postgres’ type inference is a Herculean task even with an AST. Sqlc, which directly inspired pggen does this. - It’s hard to support customization of generated code in a structured way. The way I’m leaning towards is if you need customization, you need to use the SDK which provides plugin hooks. Using flags for customization is the road to madness. - What I would like to do is serialize directly to protobuf to avoid the serialization dance of Go structs to Proto structs. [1]: https://github.com/jschaf/pggen https://github.com/jschaf/pggen
- gmac 5y agoIf you like this idea, Zapatos[1] does something similar but provides more comprehensive types (differentiated into what you get from a SELECT, what you can INSERT, etc.), plus some help using these types to interact with the database. [1] https://jawj.github.io/zapatos/ https://jawj.github.io/zapatos/
- throwout12345x 5y agoWow! I need to try this out.
- ht85 5y agoI've been using a fork of schemats for a while, it is a great tool. We quickly figured that using the table definitions as our app layer models wouldn't cut it, notably to handle polymorphism and non ideal (sic) table design. We decided to "hand write" (mostly codegen) the types we use in the business logic and have a translation layer between those and the interfaces generated by schemats. For simple tables the mapping is 1:1, but for complicated tables we centralize type checks and defensive assertions there. For polymorphism it would look like this: CREATE TYPE invoice_line_type AS ENUM('product', 'shipment'); CREATE TABLE invoice_lines AS ( id SERIAL PRIMARY KEY, type invoice_line_type NOT NULL, product_id INT REFERENCES products(id), shipment_id INT REFERENCES shipments(id), amount NUMERIC NOT NULL, CHECK (CASE WHEN type = 'product' THEN product_id IS NOT NULL ELSE TRUE END), CHECK (CASE WHEN type = 'shipment' THEN shipment_id IS NOT NULL ELSE TRUE END) ); The translation layer: import { invoice_lines, invoice_line_type } from 'src/schema' export type ProductInvoiceLine = { id: number type: invoice_line_type.product product_id: number amount: number } export type ShipmentInvoiceLine = { id: number type: invoice_line_type.shipment shipment_id: number amount: number } export const parse = (row: invoice_lines): ProductInvoiceLine | ShipmentInvoiceLine => { const base = { id: row.id, amount: row.amount, } switch (row.type) { case invoice_line_type.product: return { ...base, type: invoice_line_type.product, product_id: ensureNumber(row.product_id), } case invoice_line_type.shipment: return { ...base, type: invoice_line_type.shipment, shipment_id: ensureNumber(row.shipment_id), } } }
- oauea 5y agoI've been generating Java code from postgres (created by liquibase migrations) using Jooq, then that Java code is transformed into a OpenAPI spec using Springfox, which in turn is turned into any language of choice (such as typescript types). It's really good.