https://github.com/koskimas/kysely

Skip to content Toggle navigation
 
Sign up

  * Product
      +  
        Actions
        Automate any workflow
      +  
        Packages
        Host and manage packages
      +  
        Security
        Find and fix vulnerabilities
      +  
        Codespaces
        Instant dev environments
      +  
        Copilot
        Write better code with AI
      +  
        Code review
        Manage code changes
      +  
        Issues
        Plan and track work
      +  
        Discussions
        Collaborate outside of code
      + Explore
      + All features
      + Documentation
      + GitHub Skills
      + Blog
  * Solutions
      + For
      + Enterprise
      + Teams
      + Startups
      + Education
      + By Solution
      + CI/CD & Automation
      + DevOps
      + DevSecOps
      + Case Studies
      + Customer Stories
      + Resources
  * Open Source
      +  
        GitHub Sponsors
        Fund open source developers
      +  
        The ReadME Project
        GitHub community articles
      + Repositories
      + Topics
      + Trending
      + Collections
  * Pricing

[                    ] 

  *  
    #
    In this repository All GitHub |
    Jump to |

  * No suggested jump to results

  *  
    #
    In this repository All GitHub |
    Jump to |
  *  
    #
    In this user All GitHub |
    Jump to |
  *  
    #
    In this repository All GitHub |
    Jump to |

Sign in
Sign up
{{ message }}
koskimas / kysely Public

  * Notifications
  * Fork 73
  * Star 2.8k

A type-safe typescript SQL query builder

koskimas.github.io/kysely

License

MIT license
2.8k stars 73 forks
Star
Notifications

  * Code
  * Issues 20
  * Pull requests 6
  * Actions
  * Projects 1
  * Security
  * Insights

More

  * Code
  * Issues
  * Pull requests
  * Actions
  * Projects
  * Security
  * Insights

koskimas/kysely

This commit does not belong to any branch on this repository, and may
belong to a fork outside of the repository.
master
Switch branches/tags
[                    ]
Branches Tags
Could not load branches
Nothing to show
{{ refName }} default View all branches
Could not load tags
Nothing to show
{{ refName }} default
View all tags

Name already in use

A tag already exists with the provided branch name. Many Git commands
accept both tag and branch names, so creating this branch may cause
unexpected behavior. Are you sure you want to create this branch?
Cancel Create
1 branch 75 tags
Code

  * Local
  * Codespaces

  *  
    Clone
    HTTPS GitHub CLI
    [https://github.com/k]

    Use Git or checkout with SVN using the web URL.

    [gh repo clone koskim]

    Work fast with our official CLI. Learn more.

  * Open with GitHub Desktop
  * Download ZIP

Sign In Required

Please sign in to use Codespaces.

Launching GitHub Desktop

If nothing happens, download GitHub Desktop and try again.

Launching GitHub Desktop

If nothing happens, download GitHub Desktop and try again.

Launching Xcode

If nothing happens, download Xcode and try again.

Launching Visual Studio Code

Your codespace will open once ready.

There was a problem preparing your codespace, please try again.

Latest commit

@otaviosoares
otaviosoares CamelCasePlugin option to maintainJson data format (#284
)
...
122aa6d Jan 24, 2023
CamelCasePlugin option to maintainJson data format (#284)
122aa6d

Git stats

  * 712 commits

Files

Permalink
Failed to load latest commit information.
Type
Name
Latest commit message
Commit time
.github/workflows
 
 
assets
 
 
docs
 
 
example
 
 
recipes
 
 
scripts
 
 
src
 
 
test
 
 
.gitignore
 
 
.npmignore
 
 
.prettierignore
 
 
.prettierrc.json
 
 
CONTRIBUTING.md
 
 
LICENSE
 
 
README.md
 
 
docker-compose.yml
 
 
docs-theme.css
 
 
package-lock.json
 
 
package.json
 
 
tsconfig-base.json
 
 
tsconfig-cjs.json
 
 
tsconfig.json
 
 
View code
[                    ]
Kysely Table of contents Installation 3rd party dialects Minimal
example Playground Generating types Query examples Select queries
Stream select query results Update queries Insert queries Delete
queries Recipes Migrations PostgreSQL migration example MySQL
migration example Deno Browser Why not just contribute to knex How to
contribute to Kysely

README.md

Stand With Ukraine

Discord Tests

 Kysely

Kysely (pronounce "Key-Seh-Lee") is a type-safe and
autocompletion-friendly typescript SQL query builder. Inspired by
knex. Mainly developed for node.js but also runs on deno and in the
browser.

[demo]

Kysely makes sure you only refer to tables and columns that are
visible to the part of the query you're writing. The result type only
has the selected columns with correct types and aliases. As an added
bonus you get autocompletion for all that stuff.

As shown in the gif above, through the pure magic of modern
typescript, Kysely is even able to parse the alias given to pet.name
and add the pet_name column to the result row type. Kysely is able to
infer column names, aliases and types from selected subqueries,
joined subqueries, with statements and pretty much anything you can
think of.

Of course there are cases where things cannot be typed at compile
time, and Kysely offers escape hatches for these situations. See the
sql template tag and the DynamicModule for more info.

All API documentation is written in the typing files and you can
simply cmd-click on the module, class or method you're using to see
it. The same documentation is also hosted here.

If you start using Kysely and can't find something you'd want to use,
please open an issue or join our discord server.

You can find a more thorough introduction here.

 Table of contents

  * Installation
      + 3rd party dialects
  * Minimal example
  * Playground
  * Generating types
  * Query examples
      + Select queries
          o Stream select query results
      + Update queries
      + Insert queries
      + Delete queries
  * Recipes
  * Migrations
      + PostgreSQL migration example
      + MySQL migration example
  * Deno
  * Browser
  * Why not just contribute to knex

 Installation

Kysely currently works on PostgreSQL, MySQL and SQLite. You can
install it using:

# PostgreSQL
npm install kysely pg

# MySQL
npm install kysely mysql2

# SQLite
npm install kysely better-sqlite3

More dialects will be added soon. Kysely also has a simple interface
for 3rd party dialects.

 3rd party dialects

  * AWS Data API
  * PlanetScale Serverless Driver
  * SingleStore Data API
  * D1
  * SurrealDB

 Minimal example

All you need to do is define an interface for each table in the
database and pass those interfaces to the Kysely constructor:

import { Pool } from 'pg'
import {
  Kysely,
  PostgresDialect,
  Generated,
  ColumnType,
  Selectable,
  Insertable,
  Updateable,
} 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>

  first_name: string
  gender: 'male' | 'female' | 'other'

  // If the column is nullable in the database, make its type nullable.
  // Don't use optional properties. Optionality is always determined
  // automatically by Kysely.
  last_name: string | null

  // You can specify a different type for each operation (select, insert and
  // update) using the `ColumnType<SelectType, InsertType, UpdateType>`
  // wrapper. Here we define a column `modified_at` that is selected as
  // a `Date`, can optionally be provided as a `string` in inserts and
  // can never be updated:
  modified_at: ColumnType<Date, string | undefined, never>
}

interface PetTable {
  id: Generated<number>
  name: string
  owner_id: number
  species: 'dog' | 'cat'
}

interface MovieTable {
  id: Generated<string>
  stars: number
}

// Keys of this interface are table names.
interface Database {
  person: PersonTable
  pet: PetTable
  movie: MovieTable
}

// You'd create one of these when you start your app.
const db = new Kysely<Database>({
  // Use MysqlDialect for MySQL and SqliteDialect for SQLite.
  dialect: new PostgresDialect({
    pool: new Pool({
      host: 'localhost',
      database: 'kysely_test'
    })
  })
})

async function demo() {
  const { id } = await db
    .insertInto('person')
    .values({ first_name: 'Jennifer', gender: 'female' })
    .returning('id')
    .executeTakeFirstOrThrow()

  await db
    .insertInto('pet')
    .values({ name: 'Catto', species: 'cat', owner_id: id })
    .execute()

  const person = await db
    .selectFrom('person')
    .innerJoin('pet', 'pet.owner_id', 'person.id')
    .select(['first_name', 'pet.name as pet_name'])
    .where('person.id', '=', id)
    .executeTakeFirst()

  if (person) {
    person.pet_name
  }
}

// You can extract the select, insert and update interfaces like this
// if you want (you don't need to):
type Person = Selectable<PersonTable>
type InsertablePerson = Insertable<PersonTable>
type UpdateablePerson = Updateable<PersonTable>

 Playground

@wirekang has created a playground for Kysely. You can use to quickly
test stuff out and for creating code examples for your issues, PRs
and discord messages.

 Generating types

If you want to generate the table types automatically from the
database schema please check out this awesome project.

 Query examples

 Select queries

You can find examples of select queries in the documentation of the
select method and the where method among other places.

 Stream select query results

Currently only supported by postgres and mysql dialects.

import { Pool } from 'pg'
// or `import * as Cursor from 'pg-cursor'` depending on your tsconfig
import Cursor from 'pg-cursor'
import { Kysely, PostgresDialect } from 'kysely'

const db = new Kysely<Database>({
  // PostgresDialect requires the Cursor dependency
  dialect: new PostgresDialect({
    pool: new Pool({
      host: 'localhost',
      database: 'kysely_test'
    }),
    cursor: Cursor
  }),
  // MysqlDialect doesn't require any special configuration
})

async function demo() {
  for await (const adult of db.selectFrom('person')
    .selectAll()
    .where('age', '>', 18)
    .stream()
  ) {
    console.log(`Hello ${adult.first_name}!`)

    if (adult.first_name === 'John') {
      // After this line the db connection is released and no more
      // rows are streamed from the database to the client
      break;
    }
  }
}

 Update queries

See the set method and the updateTable method documentation.

 Insert queries

See the values method and the insertInto method documentation.

 Delete queries

See the deleteFrom method documentation.

 Recipes

The recipes folder contains a bunch of small tutorials or "recipes"
for common use cases.

  * Conditional selects
  * Deduplicate joins
  * Extending kysely
  * Raw SQL
  * Schemas
  * Dealing with the Type instantiation is excessively deep and
    possibly infinite error

 Migrations

Migration files should look like this:

import { Kysely } from 'kysely'

export async function up(db: Kysely<any>): Promise<void> {
  // Migration code
}

export async function down(db: Kysely<any>): Promise<void> {
  // Migration code
}

The up function is called when you update your database schema to the
next version and down when you go back to previous version. The only
argument for the functions is an instance of Kysely<any>. It's
important to use Kysely<any> and not Kysely<YourDatabase>.

Migrations should never depend on the current code of your app
because they need to work even when the app changes. Migrations need
to be "frozen in time".

The migrations can use the Kysely.schema module to modify the schema.
Migrations can also run normal queries to modify data.

Execution order of the migrations is the alpabetical order of their
names. An excellent way to name your migrations is to prefix them
with an ISO 8601 date string. A date prefix works well in large teams
where multiple team members may add migrations at the same time in
parallel commits without knowing about the other migrations.

You don't need to store your migrations as separate files if you
don't want to. You can easily implement your own MigrationProvider
and give it to the Migrator class when you instantiate one.

 PostgreSQL migration example

import { Kysely, sql } from 'kysely'

export async function up(db: Kysely<any>): Promise<void> {
  await db.schema
    .createTable('person')
    .addColumn('id', 'serial', (col) => col.primaryKey())
    .addColumn('first_name', 'varchar', (col) => col.notNull())
    .addColumn('last_name', 'varchar')
    .addColumn('gender', 'varchar(50)', (col) => col.notNull())
    .addColumn('created_at', 'timestamp', (col) => col.defaultTo(sql`now()`).notNull())
    .execute()

  await db.schema
    .createTable('pet')
    .addColumn('id', 'serial', (col) => col.primaryKey())
    .addColumn('name', 'varchar', (col) => col.notNull().unique())
    .addColumn('owner_id', 'integer', (col) =>
      col.references('person.id').onDelete('cascade').notNull()
    )
    .addColumn('species', 'varchar', (col) => col.notNull())
    .execute()

  await db.schema
    .createIndex('pet_owner_id_index')
    .on('pet')
    .column('owner_id')
    .execute()
}

export async function down(db: Kysely<any>): Promise<void> {
  await db.schema.dropTable('pet').execute()
  await db.schema.dropTable('person').execute()
}

 MySQL migration example

import { Kysely } from 'kysely'

export async function up(db: Kysely<any>): Promise<void> {
  await db.schema
    .createTable('person')
    .addColumn('id', 'integer', (col) => col.autoIncrement().primaryKey())
    .addColumn('first_name', 'varchar(255)', (col) => col.notNull())
    .addColumn('last_name', 'varchar(255)')
    .addColumn('gender', 'varchar(50)', (col) => col.notNull())
    .execute()

  await db.schema
    .createTable('pet')
    .addColumn('id', 'integer', (col) => col.autoIncrement().primaryKey())
    .addColumn('name', 'varchar(255)', (col) => col.notNull().unique())
    .addColumn('owner_id', 'integer', (col) => col.notNull())
    .addColumn('species', 'varchar(255)', (col) => col.notNull())
    .addForeignKeyConstraint(
      'pet_owner_id_fk', ['owner_id'], 'person', ['id'],
      (cb) => cb.onDelete('cascade')
    )
    .execute()

  await db.schema
    .createIndex('pet_owner_id_index')
    .on('pet')
    .column('owner_id')
    .execute()
}

export async function down(db: Kysely<any>): Promise<void> {
  await db.schema.dropTable('pet').execute()
  await db.schema.dropTable('person').execute()
}

You can then use

const migrator = new Migrator(migratorConfig);
await migrator.migrateToLatest(pathToMigrationsFolder)

to run all migrations that have not yet been run. See the Migrator
class's documentation for more info.

Kysely doesn't have a CLI for running migrations and probably never
will. This is because Kysely's migrations are also written in
typescript. To run the migrations, you need to first build the
typescript code into javascript. A CLI would cause confusion over
which migrations are being run, the typescript ones or the javascript
ones. If we added support for both, the CLI would need to depend on a
typescript compiler, which most production environments don't (and
shouldn't) have. You will probably want to add a simple migration
script to your projects like this:

import * as path from 'path'
import { Pool } from 'pg'
import { promises as fs } from 'fs'
import {
  Kysely,
  Migrator,
  PostgresDialect,
  FileMigrationProvider
} from 'kysely'

async function migrateToLatest() {
  const db = new Kysely<Database>({
    dialect: new PostgresDialect({
      pool: new Pool({
        host: 'localhost',
        database: 'kysely_test',
      })
    }),
  })

  const migrator = new Migrator({
    db,
    provider: new FileMigrationProvider({
      fs,
      path,
      migrationFolder: 'some/path/to/migrations',
    })
  })

  const { error, results } = await migrator.migrateToLatest()

  results?.forEach((it) => {
    if (it.status === 'Success') {
      console.log(`migration "${it.migrationName}" was executed successfully`)
    } else if (it.status === 'Error') {
      console.error(`failed to execute migration "${it.migrationName}"`)
    }
  })

  if (error) {
    console.error('failed to migrate')
    console.error(error)
    process.exit(1)
  }

  await db.destroy()
}

migrateToLatest()

The migration methods use a lock on the database level and parallel
calls are executed serially. This means that you can safely call
migrateToLatest and other migration methods from multiple server
instances simultaneously and the migrations are guaranteed to only be
executed once. The locks are also automatically released if the
migration process crashes or the connection to the database fails.

 Deno

Kysely doesn't include drivers for deno, but you can still use Kysely
as a query builder or implement your own driver:

// We use jsdeliver to get Kysely from npm.
import {
  DummyDriver,
  Generated,
  Kysely,
  PostgresAdapter,
  PostgresIntrospector,
  PostgresQueryCompiler,
} from 'https://cdn.jsdelivr.net/npm/kysely/dist/esm/index.js'

interface Person {
  id: Generated<number>
  first_name: string
  last_name: string | null
}

interface Database {
  person: Person
}

const db = new Kysely<Database>({
  dialect: {
    createAdapter() {
      return new PostgresAdapter()
    },
    createDriver() {
      // You need a driver to be able to execute queries. In this example
      // we use the dummy driver that never does anything.
      return new DummyDriver()
    },
    createIntrospector(db: Kysely<unknown>) {
      return new PostgresIntrospector(db)
    },
    createQueryCompiler() {
      return new PostgresQueryCompiler()
    },
  },
})

const query = db.selectFrom('person').select('id')
const sql = query.compile()

console.log(sql.sql)

 Browser

Kysely also runs in the browser:

import {
  Kysely,
  Generated,
  DummyDriver,
  SqliteAdapter,
  SqliteIntrospector,
  SqliteQueryCompiler,
} from 'kysely'

interface Person {
  id: Generated<number>
  first_name: string
  last_name: string | null
}

interface Database {
  person: Person
}

const db = new Kysely<Database>({
  dialect: {
    createAdapter() {
      return new SqliteAdapter()
    },
    createDriver() {
      return new DummyDriver()
    },
    createIntrospector(db: Kysely<unknown>) {
      return new SqliteIntrospector(db)
    },
    createQueryCompiler() {
      return new SqliteQueryCompiler()
    },
  },
})

window.addEventListener('load', () => {
  const sql = db.selectFrom('person').select('id').compile()

  const result = document.createElement('span')
  result.id = 'result'
  result.innerHTML = sql.sql

  document.body.appendChild(result)
})

 Why not just contribute to knex

Kysely is very similar to knex, but it also attempts to fix things
that I personally find not-so-good in knex. Bringing the type system
and the changes to knex would mean very significant breaking changes
that aren't possible at this point of the project. Knex was also
originally written for javascript and the typescript typings were
added afterwards. That always leads to compromises in the types.
Designing a library for typescript from the ground up produces much
better and simpler types.

 How to contribute to Kysely

See CONTRIBUTING.md.

About

A type-safe typescript SQL query builder

koskimas.github.io/kysely

Topics

nodejs mysql typescript browser sql database sqlite postgresql 
type-safe query-builder deno

Resources

Readme

License

MIT license

Stars

2.8k stars

Watchers

18 watching

Forks

73 forks

Releases 73

 
0.23.4 Latest
Jan 15, 2023
+ 72 releases

Packages 0

No packages published

Used by 497

 

  * @seamapi
  * @Baselime
  * @jtlapp
  * @Sife-ops
  * @SL-RP
  * @joshmwebi
  * @feyest

+ 489

Contributors 30

  * @koskimas
  * @igalklebanov
  * @elderapo
  * @waynebloss
  * @wirekang
  * @samsouder
  * @naorpeled
  * @jaylmiller
  * @Ericnr
  * @DavesBorges
  * @diogob

+ 19 contributors

Languages

  * TypeScript 99.1%
  * Other 0.9%

Footer

 (c) 2023 GitHub, Inc.

Footer navigation

  * Terms
  * Privacy
  * Security
  * Status
  * Docs
  * Contact GitHub
  * Pricing
  * API
  * Training
  * Blog
  * About

You can't perform that action at this time.
You signed in with another tab or window. Reload to refresh your
session. You signed out in another tab or window. Reload to refresh
your session.