kysely-tables

Use the same Kysely types for your SQL table schema, migrations and queries.

• No need for an intermediary schema defining API or schema language.
• No need to connect to the database to retrieve the schema structure.
• No need to bloat your setup mixing multiple database libraries.

Your Kysely types become the single source of truth for your CREATE TABLE statements.

And also for creating and running migrations, very much like Prisma and Drizzle.

Important

This is a proof-of-concept project.

The idea is to use annotation types to enhance regular Kysely types while keeping them working as before.

The annotations are then used by a processor to generate SQL schemas and migrations (when diffing).

It's in a state where it's just about good enough to start being tested in real projects.

Bug reports and feature requests are extremely welcome.

Read the blog post.

Tutorial

1. Check out this repository, pnpm install and cd to ./examples/sqlite.

2. Inspect db.ts to see how tables are defined. Note that these types are fully Kysely-compatible. The schema types serve as hints for schema generation, but Kysely receives the underlying types it expects.

   export interface UsersTable {
     id: Generated<Primary<number>>
     name: Sized<string, 100> | null
     email: Unique<Sized<string, 255>>
     passwordHash: Text<string>
     role: Default<string, "'member'">
     createdAt: Default<Date, 'now()'>
     updatedAt: Default<Date, 'now()'>
     deletedAt: null | Date 
   }

   In order for a table to recognized as such, the interface name needs to end with Table. Note also how we can use Kysely's Generated type together with this library's schema types. Same is true for ColumnType.

3. Still in db.ts, you'll notice how the Kysely database instance is created through a wrapper, createDatabase(), and also that dialect is a top-level export.

   const driver = new SQLite3Database('db.sqlite')
   export const dialect = new SqliteDialect({ database: driver })
   
   export default createDatabase<Database>({
     driver,
     config: {
       dialect,
     },
   })

   This is to ensure the runner knows which dialect to use.

4. Now it gets interesting: instead of packing a CLI, kysely-tables turns your schema file into one. This is what the createDatabase() wrapper is responsible for: parsing and understanding certain CLI flags when this file is executed directly. This is called the runner.

5. Let's begin by creating the database and applying the initial table schema:

   % tsx db.ts --create

   

   Proceed and you'll see that both db.sql and db.snapshot.ts are created.

   

   This snapshot file is used for diffing purposes: when you change db.ts, the runner uses it to know how the schema changed. Now let's create a migration, referred to as schema revision in this library.

6. Edit db.ts and remove any column from UsersTable (adding/removing tables also work):

     export interface UsersTable {
       id: Generated<Primary<number>>
       name: Sized<string, 100> | null
       email: Unique<Sized<string, 255>>
   -   passwordHash: Text<string>
       role: Default<string, "'member'">
       createdAt: Default<Date, 'now()'>
       updatedAt: Default<Date, 'now()'>
       deletedAt: null | Date
     }

   Then run:

   % tsx db.ts --revision

   

Caution

Automated revisions are the most fragile part of this library at this moment. There are no tests for this feature yet, and it may break badly depending on the schema changes you carry out. It should handle most basic cases fine, but if you see anything wrong, please file a bug report. To goal is to polish it to perfection. In case you want to dive in and debug it yourself, check out the schemaDiff() function, though that is one extremely delicate piece of code. See the Internals section for more info.

Running with --revision --empty creates stub empty revisions, when you really need to write the SQL yourself.

Running with --revision <rev> gives a custom name to the revision.

Running with --apply bypasses the prompt check.

Running only with --apply will sync up to the latest revision.

Running only with --apply <rev> will sync up (or down) to the specified revision.

Even though kysely-tables is responsible for diffing and generating the SQL statements, the migrations run through Postgrator under the hood. Postgrator is a mature and extremely well tested migration runner with support for PostgreSQL, SQLite, MySQL and MSSQL. It's used by Platformatic.

Reset

For convenience, a --reset flag is also available:

Syntax

Type Utility	Description
Sized<T, Size extends number>	Generates VARCHAR columns (when available).
Text<T>	Generates TEXT columns.
Reference<Table, Key, T>	Generates FOREIGN KEY constraints.
Default<T, value>	Generates DEFAULT <value> clauses.
Primary<T>	Generates PRIMARY KEY clauses.
Unique<T>	Generates UNIQUE clauses and associated indexes.

Internals

I wrote this because I was unhappy with the APIs and workflows available in other libraries. Even Kysely itself has its own API for migrations, which differs from the types used to define tables. I wanted my database management layer to be extremely light, but also architected in an transparent way, that would make me feel like I know what's going on behind the scenes.

The main class is KyselyTables, which provides the buildSchema(), buildSchemaReset() and buildSchemaRevision() methods. The main method that analyzes the table interfaces and their column fields is #registerTableColumns(). They all use TypeScript's compiler API to properly parse the source file, no regexes involved. The whole API is heavily inspired by Kysely, and of course, compatible with Kysely.

The main class uses a DialectAdapter to generate the correct SQL statements for the database used.

As for parsing each column definition, it's done by a helper function called extractType(), which will check for all special types and use them to populate flags in each ColumnDefinition.

The trickiest part of the library is the schema diff detection.

This first iteration uses json-diff, which is quite nice, but it still required some massive data reconciliation glue code. I aged six months in a week writing that function and do not recommend obsessing over it unless you have a very good alternative in mind and are willing to venture into the dark.

The embedded runner that turns db.ts into a CLI is as simple as it can get. It uses minimist for process.argv parsing and @clack/prompts for the nice flows.

This should be enough for you to start digging in and contribute if you wish!

License

MIT