kysely_postgres_type_safe_sql_query_quickstart.ts

1import {
2  Kysely,
3  PostgresDialect,
4  Generated,
5  ColumnType,
6  Selectable,
7  Insertable,
8  Updateable,
9} from 'kysely'
10import { Pool } from 'pg'
11
12export interface Database {
13  person: PersonTable
14  pet: PetTable
15}
16
17export interface PersonTable {
18  // Columns that are generated by the database should be marked
19  // using the `Generated` type. This way they are optional
20  // in inserts and updates.
21  id: Generated<number>
22
23  first_name: string
24  gender: 'man' | 'woman' | 'other'
25
26  // If the column is read-only in the app, you can use `ColumnType`
27  // to make it impossible to update or insert.
28  last_name: string | null
29  created_at: ColumnType<Date, string | undefined, never>
30}
31
32export interface PetTable {
33  id: Generated<number>
34  name: string
35  owner_id: number
36  species: 'dog' | 'cat'
37}
38
39// You should not use these types in your code. They are for Kysely.
40// Instead, you can create these types to help you use the types in your code.
41export type Person = Selectable<PersonTable>
42export type NewPerson = Insertable<PersonTable>
43export type PersonUpdate = Updateable<PersonTable>
44
45const db = new Kysely<Database>({
46  dialect: new PostgresDialect({
47    pool: new Pool({
48      database: 'test',
49      host: 'localhost',
50      user: 'admin',
51      port: 5432,
52      max: 10,
53    }),
54  }),
55})
56
57async function demo() {
58  const persons = await db
59    .selectFrom('person')
60    .selectAll()
61    .where('id', '=', 1)
62    .execute()
63
64  console.log(persons[0].first_name)
65}