import Tab from '@mdx/Tab.astro'; import Tabs from '@mdx/Tabs.astro'; import Npm from "@mdx/Npm.astro"; import Callout from '@mdx/Callout.astro'; import Steps from '@mdx/Steps.astro'; import AnchorCards from '@mdx/AnchorCards.astro'; import Breadcrumbs from '@mdx/Breadcrumbs.astro'; import CodeTabs from "@mdx/CodeTabs.astro"; import Prerequisites from "@mdx/Prerequisites.astro"; import IntrospectPostgreSQL from '@mdx/get-started/postgresql/IntrospectPostgreSQL.mdx'; import FileStructure from '@mdx/get-started/FileStructure.mdx'; import InstallPackages from '@mdx/get-started/InstallPackages.mdx'; import SetupConfig from '@mdx/get-started/SetupConfig.mdx'; import SetupEnv from '@mdx/get-started/SetupEnv.mdx'; import TransferCode from '@mdx/get-started/TransferCode.mdx'; import ApplyChanges from '@mdx/get-started/ApplyChanges.mdx'; import RunFile from '@mdx/get-started/RunFile.mdx'; import ConnectNile from '@mdx/get-started/postgresql/ConnectNile.mdx' import QueryNile from '@mdx/get-started/postgresql/QueryNile.mdx'; import QueryDatabaseUpdated from '@mdx/get-started/QueryDatabaseUpdated.mdx'; import UpdateSchema from '@mdx/get-started/postgresql/UpdateSchema.mdx';

Get Started with Drizzle and Nile in existing project

Step 1 - Install postgres package

Step 2 - Setup connection variables

Step 3 - Setup Drizzle config file

Step 4 - Introspect your database

Drizzle Kit provides a CLI command to introspect your database and generate a schema file with migrations. The schema file contains all the information about your database tables, columns, relations, and indices.

For example, you have such table in your database:

CREATE TABLE IF NOT EXISTS "todos" (
  "id" uuid DEFAULT gen_random_uuid(),
  "tenant_id" uuid,
  "title" varchar(256),
  "estimate" varchar(256),
  "embedding" vector(3),
  "complete" boolean
);

Pull your database schema:

npx drizzle-kit pull

The result of introspection will be a schema.ts file, meta folder with snapshots of your database schema, sql file with the migration and relations.ts file for relational queries.

Here is an example of the generated schema.ts file:

// table schema generated by introspection
import { pgTable, uuid, text, timestamp, varchar, vector, boolean } from "drizzle-orm/pg-core"
import { sql } from "drizzle-orm"

export const tenants = pgTable("tenants", {
	id: uuid().default(sql`public.uuid_generate_v7()`).primaryKey().notNull(),
	name: text(),
	created: timestamp({ mode: 'string' }).default(sql`LOCALTIMESTAMP`).notNull(),
	updated: timestamp({ mode: 'string' }).default(sql`LOCALTIMESTAMP`).notNull(),
	deleted: timestamp({ mode: 'string' }),
});

export const todos = pgTable("todos", {
	id: uuid().defaultRandom(),
	tenantId: uuid("tenant_id"),
	title: varchar({ length: 256 }),
	estimate: varchar({ length: 256 }),
	embedding: vector({ dimensions: 3 }),
	complete: boolean(),
});

Learn more about introspection in the documentation.

Step 5 - Transfer code to your actual schema file

Step 6 - Connect Drizzle ORM to the database

Step 7 - Query the database

Step 8 - Run index.ts file

Step 9 - Update your table schema (optional)

If you want to update your table schema, you can do it in the schema.ts file. For example, let's add a new column deadline to the todos table`:

import { pgTable, uuid, text, timestamp, varchar, vector, boolean } from "drizzle-orm/pg-core"
import { sql } from "drizzle-orm"

export const tenants = pgTable("tenants", {
	id: uuid().default(sql`public.uuid_generate_v7()`).primaryKey().notNull(),
	name: text(),
	created: timestamp({ mode: 'string' }).default(sql`LOCALTIMESTAMP`).notNull(),
	updated: timestamp({ mode: 'string' }).default(sql`LOCALTIMESTAMP`).notNull(),
	deleted: timestamp({ mode: 'string' }),
});

export const todos = pgTable("todos", {
	id: uuid().defaultRandom(),
	tenantId: uuid("tenant_id"),
	title: varchar({ length: 256 }),
	estimate: varchar({ length: 256 }),
	embedding: vector({ dimensions: 3 }),
	complete: boolean(),
  deadline: timestamp({ mode: 'string' })
});

Step 10 - Applying changes to the database (optional)

Step 11 - Query the database with a new field (optional)

If you run the index.ts file again, you'll be able to see the new field that you've just added. The field will be null since we did not populate deadlines when inserting todos previously.