kysely_postgres_type_safe_crud_with_generated_columns.ts

1import { 
2  Kysely, 
3  PostgresDialect, 
4  Generated, 
5  ColumnType, 
6  Selectable, 
7  Insertable, 
8  Updateable 
9} from 'kysely'
10import { Pool } from 'pg'
11
12// 1. Define the database structure
13interface Database {
14  person: PersonTable
15}
16
17interface 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  first_name: string
23  last_name: string | null
24  gender: 'man' | 'woman' | 'other'
25
26  // If you use a custom type for a column, you can use the `ColumnType`
27  // helper to specify different types for select, insert and update.
28  created_at: ColumnType<Date, string | undefined, never>
29}
30
31// 2. Initialize Kysely with a dialect
32const db = new Kysely<Database>({
33  dialect: new PostgresDialect({
34    pool: new Pool({
35      database: 'test',
36      host: 'localhost',
37      user: 'admin',
38      port: 5432,
39      max: 10,
40    })
41  })
42})
43
44async function demo() {
45  // 3. Perform type-safe queries
46  const person = await db
47    .selectFrom('person')
48    .select(['id', 'first_name', 'last_name'])
49    .where('first_name', '=', 'Arnold')
50    .executeTakeFirst()
51
52  if (person) {
53    console.log(person.first_name)
54  }
55
56  // Insert example
57  await db.insertInto('person')
58    .values({
59      first_name: 'Jennifer',
60      last_name: 'Aniston',
61      gender: 'woman'
62    })
63    .execute()
64}