A type-safe typescript SQL query builder
MIT License
Bot releases are visible (Hide)
allowUnorderedMigrations
option for the migrator. #723 Awesome work by @tracehelms ❤️Kysely<any>
.UpdateQueryBuilder#set
and InsertQueryBuilder#values
methods.Published by koskimas 10 months ago
$notNull
type helperfor update of table
and friends #683insert into "person" default values
#685limit
and offset
fn.agg
regression where two type arguments were required #829Published by koskimas 10 months ago
json_agg
and to_json
functions to function moduleis distinct from
operator #673set('first_name', 'Jennifer')
variant for update query's set
method #672as
statement support for createTable #771. Thank you @viraxslot ❤️nulls not distinct
option for constraints #770. Thank you @viraxslot ❤️addIndex
& dropIndex
@ AlterTableBuilder #720. Thank you @Gasperostream()
support for sqlite dialect #754. Thank you @tgriesser ❤️$if
#793. Thank you @igalklebanov ❤️onConflict..doUpdateSet
used select types instead of update types. #792. Thank you @igalklebanov ❤️eb.jsonPath<$>
#791. Thank you @igalklebanov ❤️$narrowType
supports new type tag NotNull
for an easier way to mark columns not nullable manuallymin
and max
aggregate functions.selectNoFrom
is removed from ExpressionBuilder
due to severe typescript performance issues. selectNoFrom
still exists in the Kysely
instance, and in most cases, you can use that instead. See this example on how to migrate: https://kyse.link/?p=s&i=sqAZIvTQktxgXYzHGkqX.where('first_name', '=', sql`something`)
. You need to explicitly give a type for the sql
expressions like this sql<string>`something`
eb.cmpr
and eb.bxp
have been removed. Use eb
as a function instead.Published by koskimas about 1 year ago
select(eb => [autocompletion works here now])
.Published by koskimas about 1 year ago
from
clause. The function is called selectNoFrom. The function name was selected after a lot of discussion. The most natural name would just be select
, but new users would find that in a list of autocompletions before selectFrom
and naturally use it when trying to create a select from
query. This would be especially true for people coming from knex where a select from
query is started using a select
call. #605and
and or
functions. Allows easy where(eb => eb.and(object))
filters. #583any
function to function module. #612between
method to expression builder. #602lit
method to expression builder. #600orderBy
. #423 Thank you @igalklebanov ❤️An example of an object and
call:
const persons = await db
.selectFrom('person')
.selectAll()
.where((eb) => eb.and({
first_name: 'Jennifer',
last_name: eb.ref('first_name')
}))
.execute()
select * from "person"
where "first_name" = $1 and "last_name" = "first_name"
Published by koskimas over 1 year ago
We improved the expression builder based on excellent feedback from the community in this issue in addition to many discord discussions. Unfortunately this means deprecating the recently added cmpr
and bxp
methods, but the migration should be painless. Read more @ #565.
Before you could create comparisons and arbitrary binary expressions using cmpr
and bxp
respectively:
where((eb) => eb.or([
eb.cmpr('first_name', '=', 'Jennifer'),
eb.cmpr('first_name', '=', 'Sylvester'),
]))
set((eb) => ({
age: eb.bxp('age', '+', 1)
}))
After this release, you'd do this instead:
where((eb) => eb.or([
eb('first_name', '=', 'Jennifer'),
eb('first_name', '=', 'Sylvester'),
]))
set((eb) => ({
age: eb('age', '+', 1)
}))
As you can see we made the expression builder callable and it can create all kinds of binary expressions. You can still use destructuring as before since the expression builder has a new property eb
that returns itself:
where(({ eb, or }) => or([
eb('first_name', '=', 'Jennifer'),
eb('first_name', '=', 'Sylvester'),
]))
or
and and
chainingWe've also added new way to create and
and or
expressions using chaining
where((eb) =>
eb('first_name', '=', 'Jennifer').or('first_name', '=', 'Sylvester')
]))
The old and
and or
methods are still there and are not going anywhere.
The expression builder's ref
function can now be used to reference nested JSON columns' fields and array items in a type-safe way:
// Postgres syntax: "addresses"->0->'postalCode'
where(({ eb, ref }) =>
eb(ref('addresses', '->').at(0).key('postalCode'), '=', '61710')
)
// MySQL syntax: `addresses`->'$[0].postalCode'
where(({ eb, ref }) =>
eb(ref('addresses', '->$').at(0).key('postalCode'), '=', '61710')
)
The JSON reference builder is just our first guess of a good API. We're eager to hear your feedback. More examples and a recipe on the subject will follow shortly after this release. Read more @ #440.
Published by koskimas over 1 year ago
Large amount of contributions from many awesome people in this one, but one definitely stands out:
Using his sorcerous knowledge of the typescript compiler internals @schusovskoy was able to significantly reduce the possibility of the notorious Type instantiation is excessively deep and possibly infinite
compiler error throughout Kysely. Check out the PR here #483 🧙. In our typing tests, we were able to at least double the complexity of the troublesome queries without hitting slowdowns or compiler errors. In some cases the issue seems to have gone away completely.
Simply amazing work. Thank you so much @schusovskoy ❤️
Other fixes and improvements in no particular order:
case when then end
builder #404. Thanks @igalklebanov ❤️$narrowType
helper for narrowing the query output type #380. Thanks @igalklebanov ❤️agg
method to expression builder for arbitrary aggregate function calls #417 @igalklebanov ❤️$if
method should no longer cause performance issues or excessively deep typesIn addition to this there were small fixes from multiple awesome people including:
Published by koskimas over 1 year ago
jsonArrayFrom
and jsonObjectFrom
functions. See this recipe for more info.Published by koskimas over 1 year ago
So much new stuff and big improvements, I don't know where to start! 🎉. There should be no breaking changes, but with this amount of changes and new features, it's always possible we've broken something 😬. As always, please open an issue if something is broken.
Let's start with the reason some of you are here: the deprecated filter methods and the improved ExpressionBuilder
:
ExpressionBuilder
We've deprecated most of the where
, having
, on
and other filter methods like whereExists
, whereNotExists
, orWhere
, orWhereExists
in favour of the new expression builder. To get an idea what the expression builder is and what it can do, you should take a look at this recipe. Here are some of the most common migrations you should do:
// Old
where(eb => eb
.where('first_name', '=', 'Jennifer')
.orWhere('last_name', '=', 'Aniston')
)
// New
where(({ or, cmpr }) => or([
cmpr('first_name', '=', 'Jennifer'),
cmpr('last_name', '=', 'Aniston')
]))
// Old
whereNotExists(eb => eb
.selectFrom('pet')
.select('pet.id')
.whereRef('pet.owner_id', '=', 'person.id')
)
// New
where(({ not, exists, selectFrom }) => not(exists(
selectFrom('pet')
.select('pet.id')
.whereRef('pet.owner_id', '=', 'person.id')
)))
You can fine more examples here and here.
The first version of kysely.dev is out 🎉 A huge thanks to @fhur for the initiative and for doing allmost all work on that ❤️
Now that the site is out, we'll concentrate on adding more documentation and examples there. For now, it's pretty minimal.
returningAll('table')
overload for DeleteQueryBuilder
. Thank you @anirudh1713 ❤️ #314update
, insert
and delete
query results on postgres. Thank you @igalklebanov ❤️ #377where
method toCreateIndexBuilder
. Thank you @igalklebanov ❤️ #371kysely/helpers/postgres
for some postgres-spcific higher level helpers. Similar packages will be added for other dialects too in the future. See this recipe for the newly available helpers.Published by koskimas over 1 year ago
Thank you @steida for pointing me to the Simplify
helper.
Published by koskimas almost 2 years ago
ifNotExists
for CreateIndexBuilder
#253using
clause support for DeleteQueryBuilder
#241. Thank you @igalklebanov ❤️match
to the list of supported comparison operators #280. Thank you @jonluca ❤️Published by koskimas almost 2 years ago
where
, having
and other comparison methods.Published by koskimas almost 2 years ago
Type instantiation is excessively deep and possibly infinite
errors.clearSelect
, clearWhere
etc. methods. Thank you @wirekang ❤️Published by koskimas almost 2 years ago
Published by koskimas almost 2 years ago
So many fixes and features in this one. @igalklebanov has been on fire 🔥. Almost all of these are his awesome work.
ArrayBuffer
usage with CamelCasePlugin
#193. Thank you @igalklebanov ❤️fn.coalesce
helper #179. Thank you @igalklebanov ❤️exactOptionalPropertyTypes
typescript setting #210. Thank you @igalklebanov ❤️sql
in distinctOn #239. Thank you @naorpeled ❤️alter table
query #238. Thank you @naorpeled ❤️call
method to create/alter table statement builders #242. Thank you @jacobpgn ❤️sql
expressions or the RawBuilder
class, you may need to specify a type for the expression like this:sql<string>`something`
Before:
await db.schema
.alterTable('person')
.alterColumn('first_name')
.setDataType('text')
.execute();
After:
await db.schema
.alterTable('person')
.alterColumn('first_name', (ac) => ac.setDataType('text'))
.execute()
Published by koskimas about 2 years ago
CreateIndexBuilder
used to invalidly add two sets of parentheses in some cases. For example, before you could write a query like this:
db.schema
.createIndex('idx')
.on('table')
.expression('a < 10')
and it worked because Kysely added the needed double parentheses for that particular case. The problem is that not all expressions should have double parentheses and the expression
method shouldn't add the second set.
If you've used db.schema.createIndex
with a custom expression
you may need to add the extra set of parentheses depending on the query (check the database docs). For example in case of our example, you'll need to change it into:
db.schema
.createIndex('idx')
.on('table')
.expression('(a < 10)')
Published by koskimas about 2 years ago
default
instead of null
for missing values in multi-row inserts on supported dialects.Published by koskimas about 2 years ago
returning
for sqlite. Thank you @waynebloss ❤️Published by koskimas about 2 years ago
cascade
method to drop table
, drop schema
, drop view
and drop index
builders. Thank you @jaym910 ❤️Published by koskimas about 2 years ago