# `drizzle-kit pull`

<Prerequisites>
- Get started with Drizzle and `drizzle-kit` - [read here](/docs/get-started)
- Drizzle schema fundamentals - [read here](/docs/sql-schema-declaration) 
- Database connection basics - [read here](/docs/connect-overview) 
- Drizzle migrations fundamentals - [read here](/docs/migrations) 
- Drizzle Kit [overview](/docs/kit-overview) and [config file](/docs/drizzle-config-file) docs
</Prerequisites>

`drizzle-kit pull` lets you literally pull(introspect) your existing database schema and generate `schema.ts` drizzle schema file, 
it is designed to cover [database first](/docs/migrations) approach of Drizzle migrations.

<Callout collapsed="How it works under the hood?">
When you run Drizzle Kit `pull` command it will:
1. Pull database schema(DDL) from your existing database
2. Generate `schema.ts` drizzle schema file and save it to `out` folder

<Section>
```
                                  ┌────────────────────────┐      ┌─────────────────────────┐ 
                                  │                        │ <---  CREATE TABLE "users" (
┌──────────────────────────┐      │                        │        "id" SERIAL PRIMARY KEY,
│ ~ drizzle-kit pull       │      │                        │        "name" TEXT,
└─┬────────────────────────┘      │        DATABASE        │        "email" TEXT UNIQUE
  │                               │                        │       );
  └ Pull datatabase schema -----> │                        │
  ┌ Generate Drizzle       <----- │                        │
  │ schema TypeScript file        └────────────────────────┘
  │
  v
```
```typescript
import * as p from "drizzle-orm/pg-core";

export const users = p.pgTable("users", {
  id: p.serial().primaryKey(),
  name: p.text(),
  email: p.text().unique(), 
};
```
</Section>
</Callout>

It is a great approach if you need to manage database schema outside of your TypeScript project or 
you're using database, which is managed by somebody else.

<br/>
<hr/>
<br/>

`drizzle-kit pull` requires you to specify `dialect` and either
database connection `url` or `user:password@host:port/db` params, you can provide them
either via [drizzle.config.ts](/docs/drizzle-config-file) config file or via CLI options:

<CodeTabs items={["With config file", "With CLI options"]}>
<Section>
```ts
// drizzle.config.ts
import { defineConfig } from "drizzle-kit";

export default defineConfig({
  dialect: "postgresql",
  dbCredentials: {
    url: "postgresql://user:password@host:port/dbname",
  },
});
```
```shell
npx drizzle-kit pull
```
</Section>

```shell
npx drizzle-kit pull --dialect=postgresql --url=postgresql://user:password@host:port/dbname
```
</CodeTabs>

### Multiple configuration files in one project

You can have multiple config files in the project, it's very useful when you have multiple database stages or multiple databases or different databases on the same project:

<Npx>
  drizzle-kit pull --config=drizzle-dev.config.ts
  drizzle-kit pull --config=drizzle-prod.config.ts
</Npx>
```plaintext {5-6}
📦 <project root>
 ├ 📂 drizzle
 ├ 📂 src
 ├ 📜 .env
 ├ 📜 drizzle-dev.config.ts
 ├ 📜 drizzle-prod.config.ts
 ├ 📜 package.json
 └ 📜 tsconfig.json
```

### Specifying database driver
<Callout type="warning">
**Expo SQLite** and **OP SQLite** are on-device(per-user) databases, there's no way to `pull` database schema from there.<br/>
For embedded databases Drizzle provides **embedded migrations** - check out our [get started](/docs/get-started/expo-new) guide.
</Callout>
Drizzle Kit does not come with a pre-bundled database driver, 
it will automatically pick available database driver from your current project based on the `dialect` - [see discussion](https://github.com/drizzle-team/drizzle-orm/discussions/2203).

Mostly all drivers of the same dialect share the same set of connection params, 
as for exceptions like `aws-data-api`, `pglight` and `d1-http` - you will have to explicitely specify `driver` param.

<CodeTabs items={["AWS Data API", "PGLite", "Cloudflare D1 HTTP"]}>
```ts {6}
import { defineConfig } from "drizzle-kit";

export default defineConfig({
  dialect: "postgresql",
  driver: "aws-data-api",
  dbCredentials: {
    database: "database",
    resourceArn: "resourceArn",
    secretArn: "secretArn",
  },
};
```
```ts {6}
import { defineConfig } from "drizzle-kit";

export default defineConfig({
  dialect: "postgresql",
  driver: "pglite",
  dbCredentials: {
    // inmemory
    url: ":memory:"
    
    // or database folder
    url: "./database/"
  },
};
```
```ts {6}
import { defineConfig } from "drizzle-kit";

export default defineConfig({
  dialect: "sqlite",
  driver: "d1-http",
  dbCredentials: {
    accountId: "accountId",
    databaseId: "databaseId",
    token: "token",
  },
};
```
</CodeTabs>

### Initial pull

<Callout type='error'>
This feature is available on  `1.0.0-beta.2` and higher.
</Callout>

<Npm>
drizzle-orm@beta
drizzle-kit@beta -D
</Npm>

<br/>

You can use the `--init` flag to mark the pulled schema as an applied migration in your database, 
so that all subsequent migrations are diffed against the initial one

```shell
npx drizzle-kit push --init
```

### Including tables, schemas and extensions
`drizzle-kit push` will by default manage all tables in `public` schema.
You can configure list of tables, schemas and extensions via `tablesFilters`, `schemaFilter` and `extensionFilters` options.

|                     |                                                                                               |
| :------------------ | :-------------------------------------------------------------------------------------------- |
| `tablesFilter`      | `glob` based table names filter, e.g. `["users", "user_info"]` or `"user*"`. Default is `"*"` |
| `schemaFilter`      | `glob` based schema names filter, e.g. `["public", "drizzle"]` or `"drizzle*"`. Default is `"*"`                     |
| `extensionsFilters` | List of installed database extensions, e.g. `["postgis"]`. Default is `[]`                    |
<br/>

Let's configure drizzle-kit to only operate with **all tables** in **public** schema
and let drizzle-kit know that there's a **postgis** extension installed, 
which creates it's own tables in public schema, so drizzle can ignore them.

<Section>
```ts filename="drizzle.config.ts" {9-11}
import { defineConfig } from "drizzle-kit";

export default defineConfig({
  dialect: "postgresql",
  schema: "./src/schema.ts",
  dbCredentials: {
    url: "postgresql://user:password@host:port/dbname",
  },
  extensionsFilters: ["postgis"],
  schemaFilter: ["public"],
  tablesFilter: ["*"],
});
```
```shell
npx drizzle-kit push
```
</Section>

### Extended list of configurations
We recommend configuring `drizzle-kit` through [drizzle.config.ts](/docs/drizzle-config-file) file, 
yet you can provide all configuration options through CLI if necessary, e.g. in CI/CD pipelines, etc.

|                     |            |                                                                           |
| :------------------ | :--------- | :------------------------------------------------------------------------ |
| `dialect`           | `required` | Database dialect, one of <Dialects/>                                      |
| `driver`            |            | Drivers exceptions <Drivers/>                                             |
| `out`               |            | Migrations output folder path, default is `./drizzle`                     |
| `url`               |            | Database connection string                                                |
| `user`              |            | Database user                                                             |
| `password`          |            | Database password                                                         |
| `host`              |            | Host                                                                      |
| `port`              |            | Port                                                                      |
| `database`          |            | Database name                                                             |
| `config`            |            | Configuration file path, default is `drizzle.config.ts`                          |
| `introspect-casing` |            | Strategy for JS keys creation in columns, tables, etc. `preserve` `camel` |
| `tablesFilter`      |            | Table name filter                                                         |
| `schemaFilter`      |            | Schema name filter. Default: `["public"]`                                 |
| `extensionsFilters` |            | Database extensions internal database filters                             |

<Npx>
drizzle-kit pull --dialect=postgresql --url=postgresql://user:password@host:port/dbname
drizzle-kit pull --dialect=postgresql --driver=pglite url=database/
drizzle-kit pull --dialect=postgresql --tablesFilter='user*' --extensionsFilters=postgis url=postgresql://user:password@host:port/dbname
</Npx>

![](@/assets/gifs/introspect_mysql.gif)


Source: https://orm.drizzle.team/docs/drizzle-kit-push

import CodeTab from "@mdx/CodeTab.astro";
import CodeTabs from "@mdx/CodeTabs.astro";
import Section from "@mdx/Section.astro";
import Tab from "@mdx/Tab.astro";
import Tabs from "@mdx/Tabs.astro";
import Callout from "@mdx/Callout.astro";
import Prerequisites from "@mdx/Prerequisites.astro";
import Npx from "@mdx/Npx.astro";
import SchemaFilePaths from "@mdx/SchemaFilePaths.mdx";
import Drivers from "@mdx/Drivers.mdx"
import Dialects from "@mdx/Dialects.mdx"
import DriversExamples from "@mdx/DriversExamples.mdx"