Security News
ESLint is Now Language-Agnostic: Linting JSON, Markdown, and Beyond
ESLint has added JSON and Markdown linting support with new officially-supported plugins, expanding its versatility beyond JavaScript.
Kysely is a type-safe SQL query builder for TypeScript. It allows developers to write SQL queries in a type-safe manner, ensuring that the queries are correct at compile time. Kysely supports various SQL databases and provides a fluent API for building complex queries.
Type-safe Query Building
Kysely allows you to build SQL queries in a type-safe manner. The above code demonstrates a simple SELECT query that retrieves the 'id' and 'first_name' columns from the 'person' table where the 'id' is equal to 1.
const result = await db.selectFrom('person').select(['id', 'first_name']).where('id', '=', 1).execute();
Insert Queries
Kysely supports type-safe insert queries. The above code demonstrates how to insert a new row into the 'person' table with the specified values.
const result = await db.insertInto('person').values({ first_name: 'John', last_name: 'Doe' }).execute();
Update Queries
Kysely allows you to perform type-safe update queries. The above code demonstrates how to update the 'first_name' column of the 'person' table where the 'id' is equal to 1.
const result = await db.updateTable('person').set({ first_name: 'Jane' }).where('id', '=', 1).execute();
Delete Queries
Kysely supports type-safe delete queries. The above code demonstrates how to delete a row from the 'person' table where the 'id' is equal to 1.
const result = await db.deleteFrom('person').where('id', '=', 1).execute();
Joins
Kysely allows you to perform type-safe join operations. The above code demonstrates an inner join between the 'person' and 'address' tables, selecting the 'id' from the 'person' table and the 'city' from the 'address' table.
const result = await db.selectFrom('person').innerJoin('address', 'person.id', 'address.person_id').select(['person.id', 'address.city']).execute();
Knex.js is a SQL query builder for Node.js that supports various SQL databases. It provides a fluent API for building queries but lacks the type-safety features of Kysely. Knex is widely used and has a large community.
Sequelize is a promise-based Node.js ORM for various SQL databases. It provides a higher-level abstraction over SQL queries and includes features like model definitions, associations, and migrations. Unlike Kysely, Sequelize is more focused on being an ORM rather than just a query builder.
TypeORM is an ORM for TypeScript and JavaScript that supports various SQL databases. It provides a high-level API for defining entities, relationships, and performing database operations. TypeORM offers type-safety but is more feature-rich compared to Kysely, as it includes migrations, caching, and more.
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.
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.
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.
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.
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>
If you want to generate the table types automatically from the database schema please check out this awesome project.
You can find examples of select queries in the documentation of the select method and the where method among other places.
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 male of db.selectFrom("person")
.selectAll()
.where("person.gender", "=", "male")
.stream()) {
console.log(`Hello mr. ${male.first_name}!`)
if (male.first_name === "John") {
// After this line the db connection is released and no more
// rows are streamed from the database to the client
break;
}
}
}
See the set method and the updateTable method documentation.
See the values method and the insertInto method documentation.
See the deleteFrom method documentation.
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.
import { Kysely } 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())
.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()
}
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.
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)
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)
})
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.
FAQs
Type safe SQL query builder
The npm package kysely receives a total of 418,724 weekly downloads. As such, kysely popularity was classified as popular.
We found that kysely demonstrated a healthy version release cadence and project activity because the last version was released less than a year ago. It has 0 open source maintainers collaborating on the project.
Did you know?
Socket for GitHub automatically highlights issues in each pull request and monitors the health of all your open source dependencies. Discover the contents of your packages and block harmful activity before you install or update your dependencies.
Security News
ESLint has added JSON and Markdown linting support with new officially-supported plugins, expanding its versatility beyond JavaScript.
Security News
Members Hub is conducting large-scale campaigns to artificially boost Discord server metrics, undermining community trust and platform integrity.
Security News
NIST has failed to meet its self-imposed deadline of clearing the NVD's backlog by the end of the fiscal year. Meanwhile, CVE's awaiting analysis have increased by 33% since June.