# encore.dev/storage/sqldb ## Classes ### Connection Represents a dedicated connection to a database. #### Extends - `BaseQueryExecutor` #### Constructors ##### Constructor `new Connection(impl): Connection` ###### Parameters ###### impl `SQLConn` ###### Returns [`Connection`](#connection) ###### Overrides `BaseQueryExecutor.constructor` #### Properties ##### impl `protected readonly impl: SQLConn` ###### Overrides `BaseQueryExecutor.impl` #### Methods ##### close() `close(): Promise` Returns the connection to the database pool. ###### Returns `Promise`\<`void`\> ##### exec() `exec(strings, ...params): Promise` exec executes a query without returning any rows. ###### Parameters ###### strings `TemplateStringsArray` ###### params ...[`Primitive`](#primitive)[] ###### Returns `Promise`\<`void`\> ###### Example ```ts const email = "foo@example.com"; const result = database.exec`DELETE FROM users WHERE email=${email}` ``` ###### Inherited from `BaseQueryExecutor.exec` ##### query() `query(strings, ...params): AsyncGenerator` query queries the database using a template string, replacing your placeholders in the template with parametrised values without risking SQL injections. It returns an async generator, that allows iterating over the results in a streaming fashion using `for await`. ###### Type Parameters ###### T `T` *extends* [`ResultRow`](#resultrow) = `Record`\<`string`, `any`\> ###### Parameters ###### strings `TemplateStringsArray` ###### params ...[`Primitive`](#primitive)[] ###### Returns `AsyncGenerator`\<`T`\> ###### Example ```ts const email = "foo@example.com"; const result = database.query`SELECT id FROM users WHERE email=${email}` This produces the query: "SELECT id FROM users WHERE email=$1". ``` ###### Inherited from `BaseQueryExecutor.query` ##### queryAll() `queryAll(strings, ...params): Promise` queryAll queries the database using a template string, replacing your placeholders in the template with parametrised values without risking SQL injections. It returns an array of all results. ###### Type Parameters ###### T `T` *extends* [`ResultRow`](#resultrow) = `Record`\<`string`, `any`\> ###### Parameters ###### strings `TemplateStringsArray` ###### params ...[`Primitive`](#primitive)[] ###### Returns `Promise`\<`T`[]\> ###### Example ```ts const email = "foo@example.com"; const result = database.queryAll`SELECT id FROM users WHERE email=${email}` This produces the query: "SELECT id FROM users WHERE email=$1". ``` ###### Inherited from `BaseQueryExecutor.queryAll` ##### queryRow() `queryRow(strings, ...params): Promise` queryRow is like query but returns only a single row. If the query selects no rows it returns null. Otherwise it returns the first row and discards the rest. ###### Type Parameters ###### T `T` *extends* [`ResultRow`](#resultrow) = `Record`\<`string`, `any`\> ###### Parameters ###### strings `TemplateStringsArray` ###### params ...[`Primitive`](#primitive)[] ###### Returns `Promise`\<`T` \| `null`\> ###### Example ```ts const email = "foo@example.com"; const result = database.queryRow`SELECT id FROM users WHERE email=${email}` ``` ###### Inherited from `BaseQueryExecutor.queryRow` ##### rawExec() `rawExec(query, ...params): Promise` rawExec executes a query without returning any rows. ###### Parameters ###### query `string` The raw SQL query string. ###### params ...[`Primitive`](#primitive)[] The parameters to be used in the query. ###### Returns `Promise`\<`void`\> A promise that resolves when the query has been executed. ###### Example ```ts const query = "DELETE FROM users WHERE email=$1"; const email = "foo@example.com"; await database.rawExec(query, email); ``` ###### Inherited from `BaseQueryExecutor.rawExec` ##### rawQuery() `rawQuery(query, ...params): AsyncGenerator` rawQuery queries the database using a raw parametrised SQL query and parameters. It returns an async generator, that allows iterating over the results in a streaming fashion using `for await`. ###### Type Parameters ###### T `T` *extends* [`ResultRow`](#resultrow) = `Record`\<`string`, `any`\> ###### Parameters ###### query `string` The raw SQL query string. ###### params ...[`Primitive`](#primitive)[] The parameters to be used in the query. ###### Returns `AsyncGenerator`\<`T`\> An async generator that yields rows from the query result. ###### Example ```ts const query = "SELECT id FROM users WHERE email=$1"; const email = "foo@example.com"; for await (const row of database.rawQuery(query, email)) { console.log(row); } ``` ###### Inherited from `BaseQueryExecutor.rawQuery` ##### rawQueryAll() `rawQueryAll(query, ...params): Promise` rawQueryAll queries the database using a raw parametrised SQL query and parameters. It returns an array of all results. ###### Type Parameters ###### T `T` *extends* [`ResultRow`](#resultrow) = `Record`\<`string`, `any`\> ###### Parameters ###### query `string` ###### params ...[`Primitive`](#primitive)[] ###### Returns `Promise`\<`T`[]\> ###### Example ```ts const query = "SELECT id FROM users WHERE email=$1"; const email = "foo@example.com"; const rows = await database.rawQueryAll(query, email); ``` ###### Inherited from `BaseQueryExecutor.rawQueryAll` ##### rawQueryRow() `rawQueryRow(query, ...params): Promise` rawQueryRow is like rawQuery but returns only a single row. If the query selects no rows, it returns null. Otherwise, it returns the first row and discards the rest. ###### Type Parameters ###### T `T` *extends* [`ResultRow`](#resultrow) = `Record`\<`string`, `any`\> ###### Parameters ###### query `string` The raw SQL query string. ###### params ...[`Primitive`](#primitive)[] The parameters to be used in the query. ###### Returns `Promise`\<`T` \| `null`\> A promise that resolves to a single row or null. ###### Example ```ts const query = "SELECT id FROM users WHERE email=$1"; const email = "foo@example.com"; const result = await database.rawQueryRow(query, email); console.log(result); ``` ###### Inherited from `BaseQueryExecutor.rawQueryRow` *** ### SQLDatabase Constructing a new database object will result in Encore provisioning a database with that name and returning this object to represent it. If you want to reference an existing database, use `Database.Named(name)` as it is a compile error to create duplicate databases. #### Extends - `BaseQueryExecutor` #### Constructors ##### Constructor `new SQLDatabase(name, cfg?): SQLDatabase` ###### Parameters ###### name `string` ###### cfg? [`SQLDatabaseConfig`](#sqldatabaseconfig) ###### Returns [`SQLDatabase`](#sqldatabase) ###### Overrides `BaseQueryExecutor.constructor` #### Properties ##### impl `protected readonly impl: SQLDatabase` ###### Overrides `BaseQueryExecutor.impl` #### Accessors ##### connectionString ###### Get Signature `get connectionString(): string` Returns the connection string for the database ###### Returns `string` #### Methods ##### acquire() `acquire(): Promise` Acquires a database connection from the database pool. When the connection is closed or is garbage-collected, it is returned to the pool. ###### Returns `Promise`\<[`Connection`](#connection)\> a new connection to the database ##### begin() `begin(): Promise` Begins a database transaction. Prefer the `await using` pattern, which automatically rolls back the transaction if neither `commit` nor `rollback` is called before the variable goes out of scope: ```ts await using tx = await db.begin(); await tx.exec`INSERT INTO ...`; await tx.commit(); ``` If you can't use `await using`, make sure to always call `commit` or `rollback` yourself to prevent hanging transactions. ###### Returns `Promise`\<[`Transaction`](#transaction)\> a transaction object that implements `AsyncDisposable` ##### exec() `exec(strings, ...params): Promise` exec executes a query without returning any rows. ###### Parameters ###### strings `TemplateStringsArray` ###### params ...[`Primitive`](#primitive)[] ###### Returns `Promise`\<`void`\> ###### Example ```ts const email = "foo@example.com"; const result = database.exec`DELETE FROM users WHERE email=${email}` ``` ###### Inherited from `BaseQueryExecutor.exec` ##### query() `query(strings, ...params): AsyncGenerator` query queries the database using a template string, replacing your placeholders in the template with parametrised values without risking SQL injections. It returns an async generator, that allows iterating over the results in a streaming fashion using `for await`. ###### Type Parameters ###### T `T` *extends* [`ResultRow`](#resultrow) = `Record`\<`string`, `any`\> ###### Parameters ###### strings `TemplateStringsArray` ###### params ...[`Primitive`](#primitive)[] ###### Returns `AsyncGenerator`\<`T`\> ###### Example ```ts const email = "foo@example.com"; const result = database.query`SELECT id FROM users WHERE email=${email}` This produces the query: "SELECT id FROM users WHERE email=$1". ``` ###### Inherited from `BaseQueryExecutor.query` ##### queryAll() `queryAll(strings, ...params): Promise` queryAll queries the database using a template string, replacing your placeholders in the template with parametrised values without risking SQL injections. It returns an array of all results. ###### Type Parameters ###### T `T` *extends* [`ResultRow`](#resultrow) = `Record`\<`string`, `any`\> ###### Parameters ###### strings `TemplateStringsArray` ###### params ...[`Primitive`](#primitive)[] ###### Returns `Promise`\<`T`[]\> ###### Example ```ts const email = "foo@example.com"; const result = database.queryAll`SELECT id FROM users WHERE email=${email}` This produces the query: "SELECT id FROM users WHERE email=$1". ``` ###### Inherited from `BaseQueryExecutor.queryAll` ##### queryRow() `queryRow(strings, ...params): Promise` queryRow is like query but returns only a single row. If the query selects no rows it returns null. Otherwise it returns the first row and discards the rest. ###### Type Parameters ###### T `T` *extends* [`ResultRow`](#resultrow) = `Record`\<`string`, `any`\> ###### Parameters ###### strings `TemplateStringsArray` ###### params ...[`Primitive`](#primitive)[] ###### Returns `Promise`\<`T` \| `null`\> ###### Example ```ts const email = "foo@example.com"; const result = database.queryRow`SELECT id FROM users WHERE email=${email}` ``` ###### Inherited from `BaseQueryExecutor.queryRow` ##### rawExec() `rawExec(query, ...params): Promise` rawExec executes a query without returning any rows. ###### Parameters ###### query `string` The raw SQL query string. ###### params ...[`Primitive`](#primitive)[] The parameters to be used in the query. ###### Returns `Promise`\<`void`\> A promise that resolves when the query has been executed. ###### Example ```ts const query = "DELETE FROM users WHERE email=$1"; const email = "foo@example.com"; await database.rawExec(query, email); ``` ###### Inherited from `BaseQueryExecutor.rawExec` ##### rawQuery() `rawQuery(query, ...params): AsyncGenerator` rawQuery queries the database using a raw parametrised SQL query and parameters. It returns an async generator, that allows iterating over the results in a streaming fashion using `for await`. ###### Type Parameters ###### T `T` *extends* [`ResultRow`](#resultrow) = `Record`\<`string`, `any`\> ###### Parameters ###### query `string` The raw SQL query string. ###### params ...[`Primitive`](#primitive)[] The parameters to be used in the query. ###### Returns `AsyncGenerator`\<`T`\> An async generator that yields rows from the query result. ###### Example ```ts const query = "SELECT id FROM users WHERE email=$1"; const email = "foo@example.com"; for await (const row of database.rawQuery(query, email)) { console.log(row); } ``` ###### Inherited from `BaseQueryExecutor.rawQuery` ##### rawQueryAll() `rawQueryAll(query, ...params): Promise` rawQueryAll queries the database using a raw parametrised SQL query and parameters. It returns an array of all results. ###### Type Parameters ###### T `T` *extends* [`ResultRow`](#resultrow) = `Record`\<`string`, `any`\> ###### Parameters ###### query `string` ###### params ...[`Primitive`](#primitive)[] ###### Returns `Promise`\<`T`[]\> ###### Example ```ts const query = "SELECT id FROM users WHERE email=$1"; const email = "foo@example.com"; const rows = await database.rawQueryAll(query, email); ``` ###### Inherited from `BaseQueryExecutor.rawQueryAll` ##### rawQueryRow() `rawQueryRow(query, ...params): Promise` rawQueryRow is like rawQuery but returns only a single row. If the query selects no rows, it returns null. Otherwise, it returns the first row and discards the rest. ###### Type Parameters ###### T `T` *extends* [`ResultRow`](#resultrow) = `Record`\<`string`, `any`\> ###### Parameters ###### query `string` The raw SQL query string. ###### params ...[`Primitive`](#primitive)[] The parameters to be used in the query. ###### Returns `Promise`\<`T` \| `null`\> A promise that resolves to a single row or null. ###### Example ```ts const query = "SELECT id FROM users WHERE email=$1"; const email = "foo@example.com"; const result = await database.rawQueryRow(query, email); console.log(result); ``` ###### Inherited from `BaseQueryExecutor.rawQueryRow` ##### named() `static named(name): SQLDatabase` Reference an existing database by name, if the database doesn't exist yet, use `new Database(name)` instead. ###### Type Parameters ###### name `name` *extends* `string` ###### Parameters ###### name `StringLiteral`\<`name`\> ###### Returns [`SQLDatabase`](#sqldatabase) *** ### Transaction Represents a database transaction. `Transaction` implements `AsyncDisposable`, so the recommended usage is the `await using` pattern — it automatically rolls back the transaction if neither `commit` nor `rollback` is called before the variable goes out of scope: ```ts await using tx = await db.begin(); await tx.exec`INSERT INTO ...`; await tx.commit(); ``` If you can't use `await using`, make sure to always call `commit` or `rollback` yourself to prevent hanging transactions. #### Extends - `BaseQueryExecutor` #### Implements - `AsyncDisposable` #### Constructors ##### Constructor `new Transaction(impl): Transaction` ###### Parameters ###### impl `Transaction` ###### Returns [`Transaction`](#transaction) ###### Overrides `BaseQueryExecutor.constructor` #### Properties ##### impl `protected readonly impl: Transaction` ###### Overrides `BaseQueryExecutor.impl` #### Methods ##### \[asyncDispose\]() `asyncDispose: Promise` ###### Returns `Promise`\<`void`\> ###### Implementation of `AsyncDisposable.[asyncDispose]` ##### commit() `commit(): Promise` Commit the transaction. ###### Returns `Promise`\<`void`\> ##### exec() `exec(strings, ...params): Promise` exec executes a query without returning any rows. ###### Parameters ###### strings `TemplateStringsArray` ###### params ...[`Primitive`](#primitive)[] ###### Returns `Promise`\<`void`\> ###### Example ```ts const email = "foo@example.com"; const result = database.exec`DELETE FROM users WHERE email=${email}` ``` ###### Inherited from `BaseQueryExecutor.exec` ##### query() `query(strings, ...params): AsyncGenerator` query queries the database using a template string, replacing your placeholders in the template with parametrised values without risking SQL injections. It returns an async generator, that allows iterating over the results in a streaming fashion using `for await`. ###### Type Parameters ###### T `T` *extends* [`ResultRow`](#resultrow) = `Record`\<`string`, `any`\> ###### Parameters ###### strings `TemplateStringsArray` ###### params ...[`Primitive`](#primitive)[] ###### Returns `AsyncGenerator`\<`T`\> ###### Example ```ts const email = "foo@example.com"; const result = database.query`SELECT id FROM users WHERE email=${email}` This produces the query: "SELECT id FROM users WHERE email=$1". ``` ###### Inherited from `BaseQueryExecutor.query` ##### queryAll() `queryAll(strings, ...params): Promise` queryAll queries the database using a template string, replacing your placeholders in the template with parametrised values without risking SQL injections. It returns an array of all results. ###### Type Parameters ###### T `T` *extends* [`ResultRow`](#resultrow) = `Record`\<`string`, `any`\> ###### Parameters ###### strings `TemplateStringsArray` ###### params ...[`Primitive`](#primitive)[] ###### Returns `Promise`\<`T`[]\> ###### Example ```ts const email = "foo@example.com"; const result = database.queryAll`SELECT id FROM users WHERE email=${email}` This produces the query: "SELECT id FROM users WHERE email=$1". ``` ###### Inherited from `BaseQueryExecutor.queryAll` ##### queryRow() `queryRow(strings, ...params): Promise` queryRow is like query but returns only a single row. If the query selects no rows it returns null. Otherwise it returns the first row and discards the rest. ###### Type Parameters ###### T `T` *extends* [`ResultRow`](#resultrow) = `Record`\<`string`, `any`\> ###### Parameters ###### strings `TemplateStringsArray` ###### params ...[`Primitive`](#primitive)[] ###### Returns `Promise`\<`T` \| `null`\> ###### Example ```ts const email = "foo@example.com"; const result = database.queryRow`SELECT id FROM users WHERE email=${email}` ``` ###### Inherited from `BaseQueryExecutor.queryRow` ##### rawExec() `rawExec(query, ...params): Promise` rawExec executes a query without returning any rows. ###### Parameters ###### query `string` The raw SQL query string. ###### params ...[`Primitive`](#primitive)[] The parameters to be used in the query. ###### Returns `Promise`\<`void`\> A promise that resolves when the query has been executed. ###### Example ```ts const query = "DELETE FROM users WHERE email=$1"; const email = "foo@example.com"; await database.rawExec(query, email); ``` ###### Inherited from `BaseQueryExecutor.rawExec` ##### rawQuery() `rawQuery(query, ...params): AsyncGenerator` rawQuery queries the database using a raw parametrised SQL query and parameters. It returns an async generator, that allows iterating over the results in a streaming fashion using `for await`. ###### Type Parameters ###### T `T` *extends* [`ResultRow`](#resultrow) = `Record`\<`string`, `any`\> ###### Parameters ###### query `string` The raw SQL query string. ###### params ...[`Primitive`](#primitive)[] The parameters to be used in the query. ###### Returns `AsyncGenerator`\<`T`\> An async generator that yields rows from the query result. ###### Example ```ts const query = "SELECT id FROM users WHERE email=$1"; const email = "foo@example.com"; for await (const row of database.rawQuery(query, email)) { console.log(row); } ``` ###### Inherited from `BaseQueryExecutor.rawQuery` ##### rawQueryAll() `rawQueryAll(query, ...params): Promise` rawQueryAll queries the database using a raw parametrised SQL query and parameters. It returns an array of all results. ###### Type Parameters ###### T `T` *extends* [`ResultRow`](#resultrow) = `Record`\<`string`, `any`\> ###### Parameters ###### query `string` ###### params ...[`Primitive`](#primitive)[] ###### Returns `Promise`\<`T`[]\> ###### Example ```ts const query = "SELECT id FROM users WHERE email=$1"; const email = "foo@example.com"; const rows = await database.rawQueryAll(query, email); ``` ###### Inherited from `BaseQueryExecutor.rawQueryAll` ##### rawQueryRow() `rawQueryRow(query, ...params): Promise` rawQueryRow is like rawQuery but returns only a single row. If the query selects no rows, it returns null. Otherwise, it returns the first row and discards the rest. ###### Type Parameters ###### T `T` *extends* [`ResultRow`](#resultrow) = `Record`\<`string`, `any`\> ###### Parameters ###### query `string` The raw SQL query string. ###### params ...[`Primitive`](#primitive)[] The parameters to be used in the query. ###### Returns `Promise`\<`T` \| `null`\> A promise that resolves to a single row or null. ###### Example ```ts const query = "SELECT id FROM users WHERE email=$1"; const email = "foo@example.com"; const result = await database.rawQueryRow(query, email); console.log(result); ``` ###### Inherited from `BaseQueryExecutor.rawQueryRow` ##### rollback() `rollback(): Promise` Rollback the transaction. ###### Returns `Promise`\<`void`\> ## Interfaces ### SQLDatabaseConfig Configuration for a `SQLDatabase`. #### Properties ##### migrations? `optional migrations?: string | SQLMigrationsConfig` *** ### SQLMigrationsConfig Configures how database migrations are managed for a `SQLDatabase`. #### Properties ##### path `path: string` ##### source? `optional source?: "prisma" | "drizzle" | "drizzle/v1"` ## Type Aliases ### Primitive ```ts type Primitive = | string | string[] | number | number[] | boolean | boolean[] | Buffer | Date | Date[] | Record | Record[] | BigInt | BigInt[] | null | undefined; ``` Represents a type that can be used in query template literals *** ### ResultRow `type ResultRow = Record` Represents a single row from a query result